Creating hive table in AVRO format

In Hive, when we are creating the table in AVRO format, will the records be populated into the table?

Because, when I run the below query, the table is being created. But, the I couldn’t find any data/records in that table.

Query:
use retail_stage_2105;

create table departments
stored as avro
tblproperties (‘avro.schema.url’ = ‘hdfs://nn01.itversity.com/user/sbalaji2105/avsc/department.avsc’);

For your reference I am attaching the screenshots:

AVSC File Location:

Hive Table Creation:

@sbalaji2105, you just created a managed table by using avro schema please load avro data into this managed table by using an external table or load data statement

@email2dgk

I tried creating the external table. Still, I am not able to fetch the records.

@sbalaji2105 LOCATION should refer the HDFS Path which has .avro file

1 Like

@email2dgk

Please find below my AVRO file location for departments table.

Should LOCATION be pointing to AVRO file or the location where we need to create the table?

I am working on this by watching Durga Sir’s video.

CREATE TABLE departments
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’
TBLPROPERTIES (‘avro.schema.url’ = ‘/user/cloudera/cca175/retail_db_avro_schemas/sqoop_import_departments.avsc’);

LOAD DATA INPATH ‘/user/cloudera/cca175/retail_db_avro_format/departments’ OVERWRITE INTO TABLE departments;

SELECT COUNT(*) FROM departments;

@email2dgk

Sorry to bug you.

So, if we create HIVE managed/external table using AVRO file format, then we have to load the data using ‘Hive Load’ command. Am I right?

I just want to confirm, whether the records/data will be automatically loaded into the table or not. Could you please confirm this.

Your location in create table command for file is not correct, you are giving /apps/…/retail_stage_2015.db but data is under /apps/…/avro_2015.db.
Change the location in create table command. It will work.

1 Like

yes., To load a managed table either you can use LOAD DATA Statement or INSERT INTO <your_manged_table>
Select * FROM <you_external_table> assuming external table is pointing to your avro data file

@email2dgk @N_Chakote

Thanks for your response. After changing the location, I am able to see the records.