How to build Avro Hive table based on avro files with schema inside?

0
votes

We have a dataset in Avro format with schema inside each Avro file.

I want to build Hive table on top of these files,

I got the below recommendation from an old question asked here in the community:

CREATE EXTERNAL TABLE sampe_table STORED AS AVRO LOCATION 'hdfs:///user/hive/;

But whenever I try it I get always the error:

 java.lang.Exception: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException Encountered AvroSerdeException determining schema. Returning signal schema to indicate problem: Neither avro.schema.literal nor avro.schema.url specified, can't determine table schema)

Any Suggestions? or do you know any online tool that can split Schema from the file?

1
hiveschemaavro

1 Answers

1
votes

First generate an avsc file and use that file to create a table

create external table myavro 
stored as avro 
location '/user/cloudera/myavro'
tblproperties('avro.schema.url' = 'file:///home/cloudera/myavsc.avsc')

To generate avsc from the existing avro data files, use avro-tools, like this

avro-tools getschema your_avro_file