!connect jdbc:hive2://vdcalp03598.ics.cloud.ge.com:10000/hbase_test

create external table hbase_test.ext_store_sales_2
(
pk_column int,
ss_sold_date_sk int,
ss_sold_time_sk int,
ss_item_sk    int ,
ss_customer_sk   int,
ss_cdemo_sk  int,
ss_hdemo_sk  int,
ss_addr_sk    int,
ss_store_sk  int,
ss_promo_sk  int,
ss_ticket_number    int ,
ss_quantity  int,
ss_wholesale_cost   decimal(7,2) ,
ss_list_price    decimal(7,2) ,
ss_sales_price   decimal(7,2) ,
ss_ext_discount_amt    decimal(7,2) ,
ss_ext_sales_price  decimal(7,2) ,
ss_ext_wholesale_cost  decimal(7,2) ,
ss_ext_list_price   decimal(7,2) ,
ss_ext_tax    decimal(7,2) ,
ss_coupon_amt    decimal(7,2) ,
ss_net_paid  decimal(7,2) ,
ss_net_paid_inc_tax    decimal(7,2) ,
ss_net_profit    decimal(7,2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
location '/user/finance_user/store_sales_2/full';


create external table hbase_test.ext_hbase_store_sales_2
(
pk_column int,
ss_sold_date_sk int,
ss_sold_time_sk int,
ss_item_sk    int ,
ss_customer_sk   int,
ss_cdemo_sk  int,
ss_hdemo_sk  int,
ss_addr_sk    int,
ss_store_sk  int,
ss_promo_sk  int,
ss_ticket_number    int ,
ss_quantity  int,
ss_wholesale_cost   decimal(7,2) ,
ss_list_price    decimal(7,2) ,
ss_sales_price   decimal(7,2) ,
ss_ext_discount_amt    decimal(7,2) ,
ss_ext_sales_price  decimal(7,2) ,
ss_ext_wholesale_cost  decimal(7,2) ,
ss_ext_list_price   decimal(7,2) ,
ss_ext_tax    decimal(7,2) ,
ss_coupon_amt    decimal(7,2) ,
ss_net_paid  decimal(7,2) ,
ss_net_paid_inc_tax    decimal(7,2) ,
ss_net_profit    decimal(7,2)
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
WITH SERDEPROPERTIES  
("hbase.columns.mapping" = ":key,cf1:ss_sold_date_sk,cf1:ss_sold_time_sk,cf1:ss_item_sk,cf1:ss_customer_sk,cf1:ss_cdemo_sk,cf1:ss_hdemo_sk,cf1:ss_addr_sk,cf1:ss_store_sk,cf1:ss_promo_sk,cf1:ss_ticket_number,cf1:ss_quantity,cf1:ss_wholesale_cost,cf1:ss_list_price,cf1:ss_sales_price,cf1:ss_ext_discount_amt,cf1:ss_ext_sales_price,cf1:ss_ext_wholesale_cost,cf1:ss_ext_list_price,cf1:ss_ext_tax,cf1:ss_coupon_amt,cf1:ss_net_paid,cf1:ss_net_paid_inc_tax,cf1:ss_net_profit")
TBLPROPERTIES ("hbase.table.name" = "store_sales_2");


insert into hbase_test.ext_hbase_store_sales_2 select * from hbase_test.ext_store_sales_2;