question Re: Is there a way to create Hive table based on Avro data directly ? in Archives of Support Questions (Read Only)

Is there a way to create Hive table based on Avro data directly ?

shishir_saxena4 — Wed, 09 Mar 2016 23:42:39 GMT

I have a dataset that is almost 600GB in Avro format in HDFS. Whay is the most efficient way to create a Hive table directly on this dataset ?

For smaller datasets, I can move my data to disk, use Avro tools to extract schema, upload schema to HDFS and create Hive table based on that schema. Is there a way to directly extract Avro schema from a dataset in HDFS without writing java code ?

Re: Is there a way to create Hive table based on Avro data directly ?

amcbarnett — Wed, 09 Mar 2016 23:45:05 GMT

CREATE EXTERNAL TABLE avro_hive_table
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'TBLPROPERTIES
('avro.schema.url'='hdfs://localdomain/user/avro/schemas/activity.avsc')
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION
'/user/avro/applog_avro';

Re: Is there a way to create Hive table based on Avro data directly ?

sunile_manjee — Wed, 09 Mar 2016 23:46:24 GMT

Try this:

CREATE EXTERNAL TABLE  tableName 
PARTITIONED BY (ingestiondatetime BIGINT, recordtype STRING)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serd2.avro.AvroSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
TBLPROPERTIES ('avro.schema.url'='hdfs:///user/file.avsc');

Re: Is there a way to create Hive table based on Avro data directly ?

shishir_saxena4 — Wed, 09 Mar 2016 23:57:23 GMT

Thanks @Ancil McBarnett @Sunile Manjee. I don't have .avsc file for schema. How can I extract Avro schema for this data ?

Re: Is there a way to create Hive table based on Avro data directly ?

aervits — Thu, 10 Mar 2016 00:00:38 GMT

You can try the following, cat your large file, grab a few lines output to new file on local fs. Ill be curious to know if that works with avro serialization.

http://stackoverflow.com/questions/22852063/how-to-copy-first-few-lines-of-a-large-file-in-hadoop-to-a-new-file#22852192

Then use avro-tools to extract schema.

Re: Is there a way to create Hive table based on Avro data directly ?

shishir_saxena4 — Thu, 10 Mar 2016 02:22:44 GMT

Thank You @Artem Ervits @Sunile Manjee @Ancil McBarnett. I was able to get my solution using a combination of commands.

hdfs dfs -cat $1 | head --bytes 10K > $SAMPLE_FILE 
java -jar $AVRO_TOOLS_PATH/avro-tools-1.7.7.jar getschema $SAMPLE_FILE > $AVRO_SCHEMA_FILE 
hdfs dfs -put $AVRO_SCHEMA_FILE $AVRO_SCHEMA_DIR

head command needs to be used with --bytes option to get first 10K bytes. Then I used Avro tools to retrieve schema and copied schema back to HDFS.

Re: Is there a way to create Hive table based on Avro data directly ?

aervits — Thu, 10 Mar 2016 07:25:11 GMT

You taught me something new, awesome community!

Re: Is there a way to create Hive table based on Avro data directly ?

jknulst — Fri, 23 Sep 2016 17:45:02 GMT

@Ancil McBarnett

Are you sure AVRO backed tables can be created as external tables? If I run your statement I get problems on the LOCATION predicate. Hive does not expect the LOCATION clause it seems

Edit:

Never mind, you can but the order of the statements matters:

This works:

CREATE EXTERNAL TABLE as_avro 
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 '/user/root/as_avro' 
TBLPROPERTIES ('avro.schema.url'='hdfs:///user/root/avro.avsc');

Re: Is there a way to create Hive table based on Avro data directly ?

jknulst — Fri, 23 Sep 2016 18:07:06 GMT

This statement misses the 'LOCATION' clause so it is not an external table

Re: Is there a way to create Hive table based on Avro data directly ?

strong_young_cn — Thu, 22 Dec 2016 13:41:31 GMT

Perhaps this page can help you:

create hive table based avro

Re: Is there a way to create Hive table based on Avro data directly ?

niparisco — Sun, 03 Sep 2017 00:07:47 GMT

hadoop jar avro-tools-1.8.2.jar getschema hdfs_archive/mydoc.avro

would also done the job

instead of java -jar, you can directly run it on hdfs thanks to :

hadoop jar avro-tools-1.8.2.jar getschema hdfsPathTOAvroFile.avro