CODEWITHSUNDEEP
apachehadoophiveavro
KrunalParmar
KrunalParmar

Reputation: 1114

Import data from .avro files to hive table

I created a hive table by following command and avro schema i had. 

CREATE TABLE table_name
PARTITIONED BY (t string, y string, m string, d string, h string, hh string)
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'='hdfs://location/schema.avsc');

Now i want to dump data i have in HDFS to created table.

I have an HDFS location, where i have data in a directory structure as t/y/m/d/h/hh/data.avro I have multiple directories according to partition because that are my partition columns.

I want to dump all the data into created table.

I tried using External table, but it is giving exceptions.

Upvotes: 4

Views: 9181

Answers (4)

Neha Kumari
Neha Kumari

Reputation: 787

You can mount the avro data in hive using the avro schema: 

CREATE TABLE dummy_table
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'='hdfs:///pathtoschema/schema.avsc');

Coming to your question about loading the data in dummy_table, you can do this by :

LOAD DATA INPATH 'hdfs:///pathtoinputdata/inputdata' OVERWRITE INTO TABLE dummy_table;

Upvotes: 1

isudarsan
isudarsan

Reputation: 477

The below syntax will be helpful.

CREATE EXTERNAL TABLE table_name
    PARTITIONED BY (part string)
    ROW FORMAT SERDE
    'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
    WITH SERDEPROPERTIES (
    'avro.schema.url'='hdfs:///path/to/avro/schema/')
    STORED AS INPUTFORMAT
    'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
    OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
    LOCATION 'path/to/location'

    ALTER TABLE table_name ADD PARTITION (part='part_name') LOCATION 'location'

Upvotes: 1

Andrea
Andrea

Reputation: 4483

You could insert the LOCATION 'path_hdfs' command during the query for the creation of an external table, or using the command LOAD DATA LOCAL INPATH 'path_hdfs' OVERWRITE INTO TABLE table_name; using a physical table.

UPDATE:

As asked by OP, I add more info about the partitioning. You have to specify the command PARTITIONED BY (day string) (example for a 'day' variable of datatype 'string') during the creation of the query. For a full example, refer to the answer provided by lake. Then, if your data on creation already has following values for 'day':

  • day = 2017-11-02
  • day = 2017-11-03
  • day = 2017-11-04

When you run the command MSCK REPAIR TABLE <table>, partitions for those three values will be added. The next day, assuming you receive data for day = 2017-11-05, when you run MSCK REPAIR TABLE <table> will be added a new partition for the new value:

  • day = 2017-11-05

Physically, adding a partition will organize data into different folders on HDFS. You need writing permissions on your data in order to create a partition. But if you already have partitions, just check that the folder system will have each folder named in the following format: "day=2017-11-02". In this way, when you run MSCK REPAIR command, the partitions will be loaded as metadata automatically. I always worked with external tables, and I used this pipeline flawlessly.

Upvotes: 1

hlagos
hlagos

Reputation: 7947

if you follow the hive convention in your hdfs folder and create the hive table pointing to the table location, you should run the msck repair table statement.

For example

CREATE TABLE table_name
PARTITIONED BY (t string, y string, m string, d string, h string, hh string)
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'='hdfs://location/schema/schema.avsc')
location "hdfs:///location/data;

and load the data like

/location/data/y=2016/m=02/d=03/h=03/hh=12/data.avro /location/data/y=2016/m=02/d=03/h=03/hh=13/data2.avro

in that way you will be able to load the data with the following statement because Hive will recognize the partitions

msck repair table table_name;

if you don't want to do this, you can use the add partition like

ALTER TABLE table_nameADD PARTITION (y='01',m='02',d='03',h='03',hh='12') location '/yourpath/y=2016/m=02/d=03/h=03/hh=12/'; (or any other folder)

Upvotes: 4

Related Questions