Support Questions
Find answers, ask questions, and share your expertise
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


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

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