Description

hbase(main):010:0> create
‘my_sales’,’order’,’sales’,’product’,’info’,’location’,’contact_details’,’deal_details’ 0 row(s) in 1.2740 seconds

=> Hbase::Table – my_sales hbase(main):011:0>

2) Now create a landing table in Hive create external table my_hive_sales_landing( key int,
ORDERNUMBER int,
QUANTITYORDERED int,
PRICEEACH float,
ORDERLINENUMBER int,
SALE float,
STATUS string,
QUATER int,
MONTH int,
YEAR int,
PRODUCTNAME string,
MRP int,
PRODUCTCODE string,
PHONE string,
CITY string,
STATE string,
POSTALCODE string,
COUNTRY string,
TERRITORY string,
CONTACTLASTNAME string,
CONTACTFIRSTNAME string,
DEALSIZE string
)
row format delimited fields terminated by ‘,’ tblproperties(“skip.header.line.count”=”1”);

3)load the data from local to landing table load data local inpath ‘/home/cloudera/sales_order_date.csv’ into table my_hive_sales_landing

4)select * from my_hive_sales_landing limit 5;

5)Now create the actual table

create external table my_hive_sales_staging( key int,
ORDERNUMBER int,
QUANTITYORDERED int,
PRICEEACH float,
ORDERLINENUMBER int,
SALE float,
STATUS string,
QUATER int,
MONTH int,
YEAR int,
PRODUCTNAME string,
MRP int,
PRODUCTCODE string,
PHONE string,
CITY string,
STATE string,
POSTALCODE string,
COUNTRY string,
TERRITORY string,
CONTACTLASTNAME string,
CONTACTFIRSTNAME string,
DEALSIZE string
)
row format delimited fields terminated by ‘,’
STORED by ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
WITH SERDEPROPERTIES
(“hbase.columns.mapping”=”:key,order:ORDERNUMBER,order:QUANTITYORDERED,order:PRICEEACH,or der:ORDERLINENUMBER,sales:SALE,sales:STATUS,sales:QUATER,sales:MONTH,sales:YEAR,product:PROD UCTNAME,product:MRP,product:PRODUCTCODE,info:PHONE,info:CITY,info:STATE,info:POSTALCODE,inf o:COUNTRY,info:TERRITORY,contact_details:CONTACTLASTNAME,contact_details:CONTACTFIRSTNAME, deal_details:DEALSIZE”)
TBLPROPERTIES(“hbase.table.name”=”my_sales”);

5)Now we need to load the data from landing table to staging table insert into table my_hive_sales_staging select * from my_hive_sales_landing

6)select * from my_hive_sales_staging limit 5;

7)scan ‘my_sales’,{‘LIMIT’=5}

8)Adding values from H-base table hbase(main):005:0> put ‘my_sales’,’9999′,’order:ORDERNUMBER’,’11’
0 row(s) in 0.0440 seconds

hbase(main):006:0> put ‘my_sales’,’9999′,’product:PRODUCTCODE’,’abc’
0 row(s) in 0.0120 seconds

hbase(main):007:0> put ‘my_sales’,’9999′,’info:CITY’,’Guntur’
0 row(s) in 0.0130 seconds

hbase(main):008:0> put ‘my_sales’,’9999′,’info:COUNTRY’,’India’
0 row(s) in 0.0140 seconds

9)Checking whether the data is getting refelect in Hive