HIve: Complex /collection data queries

Hi,

 

In this blog i will write some queries , which i done today ( consists of map , array and structure) and will also provide employee dataset for thes querie

 

i will not explain much , because these are simple ones :- i am just copy pasting my hive -shell commands to you , If have any doubt .. please comment or write me an email

here we goo …

 

hive> create table testmap(deductions MAP<String,int>)

> row format delimited

> collection items terminated by “\t”

> MAP Keys terminated by “,”;

OK

Time taken: 0.641 seconds

hive> load data local inpath “/home/cloudera/Desktop/harittest/employee” OVERWRITE into table testmap;

Loading data to table default.testmap

Table default.testmap stats: [numFiles=1, numRows=0, totalSize=45, rawDataSize=0]

OK

Time taken: 2.733 seconds

hive> select * from testmap;

OK

{“FederalTaxes”:0,”State Taxes”:0,”Insurance”:0}

Time taken: 0.569 seconds, Fetched: 1 row(s)

 

 

Now add columns 1 by 1

hive> alter table testmap add columns(name String);

OK

Time taken: 1.358 seconds

hive> select * from testmap;

OK

{“FederalTaxes”:0,”State Taxes”:0,”Insurance”:0}             NULL

Time taken: 0.698 seconds, Fetched: 1 row(s)

 

 

 

create table testarray(subordinates Array<String>)

row format delimited

collection items terminated by “\t”

 

 

hive> load data local inpath “/home/cloudera/Desktop/harittest/employee” OVERWRITE into table testarray;

Loading data to table default.testarray

Table default.testarray stats: [numFiles=1, numRows=0, totalSize=23, rawDataSize=0]

OK

Time taken: 2.46 seconds

hive> select * from testarray;

OK

[“john”,”johny”]

[“Smith”,”relax”]

Time taken: 0.534 seconds, Fetched: 2 row(s)

hive> select subordinates[0] from testarray ;

OK

john

Smith

Time taken: 0.62 seconds, Fetched: 2 row(s)

hive> select subordinates[1] from testarray ;

OK

johny

relax

Time taken: 0.574 seconds, Fetched: 2 row(s)

hive>

hive> CREATE TABLE employees (

> 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

 

 

 

load data local inpath “/home/cloudera/Desktop/harittest/employee” OVERWRITE into table employees;

 

hive> select * from employees;

OK

John Doe             100000.0              [“Mary Smith”,”Todd Jones”]     {“FederalTaxes”:0.2,”StateTaxes”:0.05,”Insurance”:0.1}                {“street”:”1″,”city”:”Michigan Ave.”,”state”:”ChicagoIL”,”zip”:60600}

Mary Smith         80000.0 [“Bill King”]         {“FederalTaxes”:0.2,”StateTaxes”:0.05,”Insurance”:0.1}                {“street”:”100″,”city”:”Ontario St.”,”state”:”ChicagoIL”,”zip”:60601}

Todd Jones         70000.0 []             {“FederalTaxes”:0.15,”StateTaxes”:0.03,”Insurance”:0.1}                {“street”:”200″,”city”:”Chicago”,”state”:” Ave.Oak ParkIL”,”zip”:60700}

Bill King 60000.0 []             {“FederalTaxes”:0.15,”StateTaxes”:0.03,”Insurance”:0.1}                {“street”:”300″,”city”:”Obscure”,”state”:”Dr.ObscuriaIL”,”zip”:60100}

Boss Man            200000.0              [“John”,”DoeFred Finance”]       {“FederalTaxes”:0.3,”StateTaxes”:0.07,”Insurance”:0.05}                {“street”:”1″,”city”:”Pretentious Drive.”,”state”:”ChicagoIL”,”zip”:60500}

Fred Finance      150000.0              [“Stacy Accountant”]      {“FederalTaxes”:0.3,”StateTaxes”:0.07,”Insurance”:0.05}                {“street”:”2″,”city”:”Pretentious Drive.”,”state”:”ChicagoIL”,”zip”:60500}

Stacy Accountant             60000.0 []             {“FederalTaxes”:0.15,”StateTaxes”:0.03,”Insurance”:0.1}                {“street”:”300″,”city”:”Main St.Naperville”,”state”:”IL”,”zip”:60563}

Time taken: 0.531 seconds, Fetched: 7 row(s)

hive> describe employees;

OK

name                    string

salary                    float

subordinates                     array<string>

deductions                         map<string,float>

address                struct<street:string,city:string,state:string,zip:int>

Time taken: 0.885 seconds, Fetched: 5 row(s)

hive> select deductions from employees;

OK

{“FederalTaxes”:0.2,”StateTaxes”:0.05,”Insurance”:0.1}

{“FederalTaxes”:0.2,”StateTaxes”:0.05,”Insurance”:0.1}

{“FederalTaxes”:0.15,”StateTaxes”:0.03,”Insurance”:0.1}

{“FederalTaxes”:0.15,”StateTaxes”:0.03,”Insurance”:0.1}

{“FederalTaxes”:0.3,”StateTaxes”:0.07,”Insurance”:0.05}

{“FederalTaxes”:0.3,”StateTaxes”:0.07,”Insurance”:0.05}

{“FederalTaxes”:0.15,”StateTaxes”:0.03,”Insurance”:0.1}

Time taken: 0.547 seconds, Fetched: 7 row(s)

hive> select deductions[“StatTaxes”] from employees;

OK

NULL

NULL

NULL

NULL

NULL

NULL

NULL

Time taken: 0.85 seconds, Fetched: 7 row(s)

hive> select deductions[“StateTaxes”] from employees;

OK

0.05

0.05

0.03

0.03

0.07

0.07

0.03

Time taken: 0.47 seconds, Fetched: 7 row(s)

 

Employee data set :-

 

John Doe 100000.0 Mary Smith,Todd Jones FederalTaxes .2,StateTaxes .05,Insurance .1 1,Michigan Ave.,ChicagoIL,60600
Mary Smith 80000.0 Bill King FederalTaxes .2,StateTaxes .05,Insurance .1 100,Ontario St.,ChicagoIL,60601
Todd Jones 70000.0 FederalTaxes .15,StateTaxes .03,Insurance .1 200,Chicago, Ave.Oak ParkIL,60700
Bill King 60000.0 FederalTaxes .15,StateTaxes .03,Insurance .1 300,Obscure,Dr.ObscuriaIL,60100
Boss Man 200000.0 John,DoeFred Finance FederalTaxes .3,StateTaxes .07,Insurance .05 1,Pretentious Drive.,ChicagoIL,60500
Fred Finance 150000.0 Stacy Accountant FederalTaxes .3,StateTaxes .07,Insurance .05 2,Pretentious Drive.,ChicagoIL,60500
Stacy Accountant 60000.0 FederalTaxes .15,StateTaxes .03,Insurance .1 300,Main St.Naperville,IL,60563

 

 

hi ,, if any query please comment or write to me at harit.singh03@gmail.com

also if his dataset not work , just send me your email id , will send u the file

 

Cheers .. Enjoy

 

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