Support Questions
Find answers, ask questions, and share your expertise

Error creating partitioned external table using AvroSerDe

Error creating partitioned external table using AvroSerDe


Have an issue creating a table using the below DDL, please help!


DROP TABLE IF EXISTS cm3rm1_test; CREATE EXTERNAL TABLE cm3rm1_test PARTITIONED BY (process_date string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'  STORED as INPUTFORMAT ''  OUTPUTFORMAT '' LOCATION 'hdfs://xyz/cm3rm1'  TBLPROPERTIES ('avro.schema.url'='hdfs:///abc/AutoGeneratedSchema.avsc'); 

trying to create the table using the above is resulting in an error. but works fine without partitioning. @smanjee @Jasper took this as a reference doesn't seem working for me. any suggestions, how to get this working?


Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. org.apache.hadoop.hive.ql.metadata.HiveException: Partition column name process_date conflicts with table columns. (state=08S01,code=1)

Re: Error creating partitioned external table using AvroSerDe

1)CREATE TABLE md ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' WITH SERDEPROPERTIES () 'avro.schema.url'='/user/cloudera/departments.avsc') STORED as INPUTFORMAT '' OUTPUTFORMAT '';

2)LOAD DATA INPATH '/user/cloudera/departments/part-m-00000.avro' into table md;

hive> SET hive.exec.dynamic.partition = true;

hive> SET hive.exec.dynamic.partition.mode = nonstrict;

3)create table mda(a int) partitioned by(name string);

4)nsert overwrite table mda partition(name) select department_id,department_name from md;

Try this way you can create paritions from avro file too