hive :- creation of complex table employee data , important queries

Hi,

in this tutorial we will create table for emplouee data set , which will be in for of like :-

“name”: “John Doe”,
“salary”: 100000.0,
“subordinates”: [“Mary Smith”, “Todd Jones”],
“deductions”: {
“Federal Taxes”: .2,
“State Taxes”: .05,
“Insurance”: .1
},
“address”: {
“street”: “1 Michigan Ave.”,
“city”: “Chicago”,
“state”: “IL”,
“zip”: 60600
}
}

But before creation of that we need to look into this table :-

Delimiter Description
\n  For text files, each line is a record, so the line feed character separates records.
^A (“control” A)    Separates all fields (columns). Written using the octal code \001 when explicitly
specified in CREATE TABLE statements.
^B                           Separate the elements in an ARRAY or STRUCT, or the key-value pairs in a MAP.
Written using the octal code \002 when explicitly specified in CREATE TABLE
statements.
^C                            Separate the key from the corresponding value in MAP key-value pairs. Written using
the octa l code \003 when explicitly specified in CREATE TABLE statements

Sample data :-

JohnDoe 100000.0 MarySmith,ToddJones FederalTaxes.20,StateTaxes.05,Insurance.10 1MichiganAve,Chicago,IL,60600
Todd Jones 70000.0 FederalTaxes.15,StateTaxes.03,Insurance.1 200Chicago,Aveak Park,IL,60700
Bill King 60000.0 Federal Taxes.15,State Taxes.03,Insurance.1 300Obscure ,DrObscuria,IL,60100

Now lets go to hive CLI

hive> show databases;
OK
default
mydb
Time taken: 0.178 seconds, Fetched: 2 row(s)

we will use :-  mydb databse and create table

hive> create table employee (name STRING,salary FLOAT,subordinates ARRAY<STRING>,deductions MAP<STRING,FLOAT>,address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>)
> row format delimited
> fields terminated by ‘\001’
> COLLECTION ITEMS TERMINATED BY ‘\002’
> MAP KEYS TERMINATED BY ‘\003’
> LINES TERMINATED BY ‘\n’
> STORED AS Textfile ;
OK
Time taken: 5.573 seconds

hive> show tables;
OK
employee
Time taken: 0.311 seconds, Fetched: 1 row(s)

lets create table for the data :-

hive> create table employee (name STRING,salary FLOAT,subordinates ARRAY<STRING>,deductions MAP<STRING,FLOAT>,address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>)
> row format delimited
> fields terminated by “\t”
> COLLECTION ITEMS TERMINATED BY “,”
> MAP KEYS TERMINATED BY “.”
> LINES TERMINATED BY “\n”
> STORED AS Textfile ;
OK
Time taken: 0.205 seconds

hive> LOAD DATA LOCAL INPATH “/home/user/Desktop/hadoop/employeedata” OVERWRITE INTO TABLE employee;

Loading data to table default.employee
Table default.employee stats: [numFiles=1, numRows=0, totalSize=298, rawDataSize=0]
OK
Time taken: 0.862 seconds

hive> select * from employee;
OK
JohnDoe 100000.0 [“MarySmith”,”ToddJones”] {“FederalTaxes”:20.0,”StateTaxes”:5.0,”Insurance”:10.0} {“street”:”1MichiganAve”,”city”:”Chicago”,”state”:”IL”,”zip”:60600}
Todd Jones 70000.0 [] {“FederalTaxes”:15.0,”StateTaxes”:3.0,”Insurance”:1.0} {“street”:”200Chicago”,”city”:”Aveak Park”,”state”:”IL”,”zip”:60700}
Bill King 60000.0 [] {“Federal Taxes”:15.0,”State Taxes”:3.0,”Insurance”:1.0} {“street”:”300Obscure “,”city”:”DrObscuria”,”state”:”IL”,”zip”:60100}
Time taken: 7.343 seconds, Fetched: 3 row(s)

Some useful queries :-

hive> describe employee;
OK
name string
salary float
subordinates array<string>
deductions map<string,float>
address struct<street:string,city:string,state:string,zip:int>

hive> select deductions[“FederalTaxes”] from employee;
OK
20.0
15.0
15.0
Time taken: 0.384 seconds, Fetched: 3 row(s)

hive> select subordinates[0] from employee;
OK
MarySmith
NULL
NULL
Time taken: 2.975 seconds, Fetched: 3 row(s)
hive> select subordinates from employee;
OK
[“MarySmith”,”ToddJones”]
[]
[]
Time taken: 0.341 seconds, Fetched: 3 row(s)

hive>select address.city from employee;
OK
Chicago
Aveak Park
DrObscuria
Time taken: 0.406 seconds, Fetched: 3 row(s)

hope guys this tutorial will help you too create table in hive.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s