Hive Dynamic Partion on stock data

HI ,

In this blog i will explain how can we do dynamic partiton .

Aim :- to create a table in which we dont want to define our partition column iin the end .

Lets say we have stock data in  this format :-

exchange,stock_symbol,date,stock_price_open,stock_price_high,stock_price_low,stock_price_close,stock_volume,stock_price_adj_close
NYSE,GRT,1996-04-29,16.87,17.00,16.87,17.00,17500,3.75
NYSE,GCH,1998-02-02,10.59,10.76,10.08,10.19,190000,4.73
NYSE,GWW,2002-04-30,54.70,57.09,54.60,56.07,453200,49.10
NYSE,GGG,1991-12-02,22.75,22.75,22.62,22.62,29600,1.17
NYSE,GWW,1988-05-24,53.43,55.12,53.00,54.91,361600,9.70
NYSE,GLT,1997-04-11,14.08,14.19,13.75,14.19,16500,8.53
NYSE,GMT,2009-03-30,20.38,20.61,19.67,19.97,1448400,19.37
NYSE,GMA,2005-05-02,20.61,20.67,20.15,20.24,64100,11.89
NYSE,GJW,2009-12-10,1.62,1.62,1.59,1.59,4500,1.59
NYSE,GAJ,2001-01-25,16.81,17.69,16.81,17.50,18500,6.77

we can download this data from here

https://raw.githubusercontent.com/quidryan/HackReduce/master/datasets/nyse/daily_prices/NYSE_daily_prices_subset.csv

for that we will ceate two tables .

  1. stocks1 which we will create below , and after that we will create a partitoned data  table with name stocks .

lets begin :-

create table if not exists stocks1 (exchangename string,
stock_symbol STRING,
date STRING,
stock_price_open FLOAT,
stock_price_high FLOAT,
stock_price_low FLOAT,
stock_price_close FLOAT,
stock_volume FLOAT,
stock_price_adj_close FLOAT)
row format delimited
fields terminated by ‘,’  ;

hive> select * from stocks1 LIMIT 10;
OK
NYSE GRT 1996-04-29 16.87 17.0 16.87 17.0 17500.0 3.75
NYSE GCH 1998-02-02 10.59 10.76 10.08 10.19 190000.0 4.73
NYSE GWW 2002-04-30 54.7 57.09 54.6 56.07 453200.0 49.1
NYSE GGG 1991-12-02 22.75 22.75 22.62 22.62 29600.0 1.17
NYSE GWW 1988-05-24 53.43 55.12 53.0 54.91 361600.0 9.7
NYSE GLT 1997-04-11 14.08 14.19 13.75 14.19 16500.0 8.53
NYSE GMT 2009-03-30 20.38 20.61 19.67 19.97 1448400.0 19.37
NYSE GMA 2005-05-02 20.61 20.67 20.15 20.24 64100.0 11.89
NYSE GJW 2009-12-10 1.62 1.62 1.59 1.59 4500.0 1.59
NYSE GAJ 2001-01-25 16.81 17.69 16.81 17.5 18500.0 6.77

now we want stock_symbol as partiton column , but as we know we need to declare the partion column at last, but here stock_symbol is 2nd column , hence we will create a new stocks table as below : –

create external table if not exists stocks (exchangename string,
date STRING,
stock_price_open FLOAT,
stock_price_high FLOAT,
stock_price_low FLOAT,
stock_price_close FLOAT,
stock_volume FLOAT,
stock_price_adj_close FLOAT)
PARTITIONED BY (stock_symbol STRING)
row format delimited
fields terminated by ‘,’
location “/usr/hive/warehouse”;

now lets insert the data into this above table by using stocks1 table :-

insert overwrite table stocks PARTITION(stock_symbol)
select exchangename,date,stock_price_open,stock_price_high,stock_price_low,stock_price_close,stock_volume,
stock_price_adj_close,stock_symbol from stocks1 

stockkkk

now lets check our partiton data usig query :-

hive> select * from stocks where stock_symbol=”GYC”;
OK
NYSE 2006-09-29 21.33 21.61 21.33 21.58 12400.0 17.71 GYC
NYSE 2009-04-15 14.95 15.25 14.95 15.25 1000.0 14.73 GYC
NYSE 2007-01-11 22.72 22.72 22.72 22.72 0.0 18.97 GYC
NYSE 2009-01-16 12.91 12.91 12.89 12.9 15100.0 12.27 GYC
NYSE 2008-01-18 20.6 20.6 20.6 20.6 100.0 18.46 GYC
NYSE 2006-03-09 22.2 22.39 22.2 22.39 200.0 17.5 GYC
NYSE 2006-07-27 21.28 21.28 21.28 21.28 200.0 17.16 GYC

hope guys u like the blog ,please comment and like

thanks

NYSE,GRA,1992-06-15,7.98,8.00,7.83,7.95,217500,4.13
NYSE,GFW,2008-10-15,13.00,14.00,12.90,13.25,6200,11.48
NYSE,GDO,2009-12-09,20.01,20.02,20.00,20.02,8000,19.89
NYSE,GYB,2007-02-08,23.76,23.92,23.75,23.87,7500,19.49
NYSE,GDI,1999-10-18,12.12,12.12,11.00,11.12,70400,5.56

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