How to add multi-level partition in hive?

Question

I have customer managed table in the hive, partition based on date and customerName. My directory structure is like below:

 user/hive/warehouse/test.db/customer/date1=2021-09-16/customerName=xyz

when I am doing show partitions customer it is not giving output. So I tried to add a partition with

MSCK REPAIR TABLE customer;

It give error Execution Error,return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

ALTER TABLE customer ADD PARTITION (date1='2021-09-15') PARTITION (customerName='xyz');

It also give error ValidationFailureSemanticException partition spec {customername=xyz} contain non partition column

How can I add these partitions in hive metastore.

hive> show create table customer;
 OK
CREATE TABLE `customer`(
`act` string)
 PARTITIONED BY (
   `date1` string,
   `customername` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH SERDEPROPERTIES (
  'path'='hdfs://hdcluster/user/hive/warehouse/test.db/customer')
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://hdcluster/user/hive/warehouse/test.db/customer'
TBLPROPERTIES (
 'spark.sql.create.version'='2.4.0',
 'spark.sql.partitionProvider'='catalog',
 'spark.sql.sources.provider'='orc',
 'spark.sql.sources.schema.numPartCols'='2',
 'spark.sql.sources.schema.numParts'='1',
 'spark.sql.sources.schema.part.0'=
  '{\"type\":\"struct\",\"fields\": 
  [{\"name\":\"act\",\"type\":\"string\",\"nullable\":true,\"metadata\": 
  {}},           {\"name\":\"date1\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}, 
{\"name\":\"customername\",\"type\":\"string\",\"nullable\":true,\"metadata\ 
  ":{}}]}','spark.sql.sources.schema.partCol.0'='date1',
      'spark.sql.sources.schema.partCol.1'='customername',
      'transient_lastDdlTime'='1631781225')

Did you try `ALTER TABLE customer ADD PARTITION (date1='2021-09-15', customerName='xyz'); ` — yahoo, Sep 16 '21 at 05:17
@leftjoin I am new to the hive, could you please tell me which command should I fire on hive-shell? — vishwajeet Mane, Sep 16 '21 at 07:14
`describe formatted customer` OR `show create table customer` — leftjoin, Sep 16 '21 at 07:37
create table looks good. All should work, except your ALTER TABLE, which is wrong one. What can it be is probably partition directory is in camelCase instead of lowercase, not sure is it an issue or not. — leftjoin, Sep 16 '21 at 09:07
@leftjoin camelCase is not an issue. I tried both ways but getting the same error. — vishwajeet Mane, Sep 16 '21 at 09:32

score 0 · Answer 1 · edited Jul 31 '23 at 19:37

0

Try this:

ALTER TABLE customer 
ADD PARTITION (date1='2021-09-15',customerName='xyz')
LOCATION 
    'hdfs://hdcluster/user/hive/warehouse/test.db/customer/date1=2021-09-15/customerName=xyz';

edited Jul 31 '23 at 19:37

Rich

6,470
15
32
53

answered Jul 26 '23 at 18:22

Gsprague

1

How to add multi-level partition in hive?

1 Answers1