Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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
Highlighted

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

Don't have an account?
Coming from Hortonworks? Activate your account here