question how to create and store the avro files in hive table? in Support Questions

how to create and store the avro files in hive table?

Manikandan_a — Tue, 10 May 2016 14:51:10 GMT

i was trying to create hive table for storing avro file and i have stored my avro shema(.avsc file),my avro file in single location.

could anyone help me to create the table in hive?

Re: how to create and store the avro files in hive table?

ahadjidj — Tue, 10 May 2016 16:24:29 GMT

Hi @manikandan ayyasamy,

Have you looked to the Hive doc on AvroSerDe : https://cwiki.apache.org/confluence/display/Hive/AvroSerDe ? you have examples on how to do it following your Hive version.

Please try to implement it and ask the community if you have a particular problem with your approach.

Note also that ORC can give the best performance with cost base optimization and Vectorization. Give it a try if this is a viable option.

Re: how to create and store the avro files in hive table?

pminovic — Tue, 10 May 2016 18:08:47 GMT

If you already have your Avro file and Avro schema, upload them to HDFS and use

CREATE EXTERNAL TABLE my_avro_tbl
  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/...'
  TBLPROPERTIES ('avro.schema.url'='hdfs://name-node.fqdn:8020/user/.../schema.avsc');

If your Avro file already contains the schema in its header you can just say

CREATE EXTERNAL TABLE tbl-name(... declarations ...) STORED AS AVRO LOCATION '...';

without specifying the schema. I have been testing it last few days and can confirm that it works on HDP-2.4 (Hive-1.2) for all scalar types like string, int, float, double, boolean etc. If you are using some complex types (like union) it might not work.

Re: how to create and store the avro files in hive table?

Manikandan_a — Tue, 10 May 2016 20:49:38 GMT

i have tried the following,but i am not able to create the table.

create external table tweets(name string,time int,tweet string) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.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'='http://trvlhaddv2gw1.tsh.thomson.com:8888/filebrowser/view/user/c400351/tweet.avsc');

i did not get any error messages.

Re: how to create and store the avro files in hive table?

ahadjidj — Tue, 10 May 2016 20:56:25 GMT

What's the problem you are facing If you didn't get any error messages ? can you see the table tweets when you do :

show tables;

Re: how to create and store the avro files in hive table?

pminovic — Tue, 10 May 2016 21:23:31 GMT

You are missing the "LOCATION" for your external table. Uplaod your Avro file somewhere in HDFS and provide the directory where your Avro file is located as your LOCATION. There is also no need to declare name, time etc., they are given in your avsc file. See my answer. After that try "SELECT * FROM tweets LIMIT 10;".

Re: how to create and store the avro files in hive table?

markdoutre — Tue, 10 May 2016 21:37:26 GMT

@Predrag Minovic So I've been battling with Avro and Hive for days and am getting nowhere. See thread here and here. If you can shed any light, it would be greatly appreciated.

Re: how to create and store the avro files in hive table?

pminovic — Tue, 10 May 2016 21:48:28 GMT

Mark, here is what I did: Having a csv file as an input I created a Hive text table, loaded csv file onto the text table, created an Avro table "STORED AS AVRO", and inserted all recored from the text table into the avro table. I also tried to create an Avro table using a schema file. Then I tested external tables using avro table data and scheme file. I'll create an article and post this week.

Re: how to create and store the avro files in hive table?

markdoutre — Tue, 10 May 2016 21:54:30 GMT

The issue I am having is that I have avro objects that don't have the schema in the header. When I try and access these objects by specifying the schema via the schema.url parameter in the TBLPROPERTIES I am unable to access the data. However, if the Avro object includes the schema, I have no problem.

I'm pretty sure that the Avro objects are ok as I can extract the data from them using Avro-tools and providing the same schema. So what I would be interesting in seeing is if someone can load a schema-less avro object into HDFS then get a Hive table to access it, by providing the schema file.

Re: how to create and store the avro files in hive table?

pminovic — Tue, 10 May 2016 22:03:30 GMT

Okay, please uplaod your files somewhere (one of your exisiting questions, or a new one), and I'll try to read them with Hive.

Re: how to create and store the avro files in hive table?

ahadjidj — Tue, 10 May 2016 23:26:07 GMT

@manikandan ayyasamy

Ok, from reading your several posts and comments I think that Hive is not able to access your schema on HDFS. I see that you are trying to give the schema that's stored on HDFS. However, you are using http and port 8888 which is not good. Can you try with this modification

TBLPROPERTIES ('avro.schema.url'='hdfs://trvlhaddv2gw1.tsh.thomson.com:8020/PATH_TO_YOUR_AVSC_FILE');

Re: how to create and store the avro files in hive table?

2hanu_valluri — Fri, 21 Oct 2016 00:11:27 GMT

Hi Mani,

Below are my observations

1. specifying schema in your DDL and also the Avro schema. i.e column names as part of DDL and Avro URL. use only one.

2. Is the schema in provided URL accessible from HDFS? you can move it HDFS and test

3. The serde is pointing to hcatalog, try using org.apache.hadoop.hive.serde2.avro.AvroSerDe

Re: how to create and store the avro files in hive table?

vinod_h_chhabri — Thu, 08 Dec 2016 15:31:07 GMT

When you say declarations above, you mean actually defining the columns and datatypes? What is the syntax for that? I am writing hdfs files from NiFi so it stores the schema header in the Avro file. I want to leverage that to create the hive table but do not have any good example to do that. If you could elaboratethe declarations part with some examples of a few columns it would really help.

Re: how to create and store the avro files in hive table?

sancar_sn — Mon, 06 Nov 2017 20:02:18 GMT

@Predrag Minovic could you please let me know how did you created hive avro table from hive text table? did you select from text table and created avro table using CREATE AS SELECT?