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

Error creating partitioned external table using AvroSerDe

Error creating partitioned external table using AvroSerDe

Explorer

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

DDL:

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 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' 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 https://community.hortonworks.com/questions/22135/is-there-a-way-to-create-hive-table-based-on-avro.... as a reference doesn't seem working for me. any suggestions, how to get this working?

Thanks

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)
1 REPLY 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 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat';

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