create database if not exists tpcds_holodesk_1;
use tpcds_holodesk_1;
set ngmr.partition.automerge=true;

--set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set stargate.dynamic.partition.enabled=true;
set holodesk.segment.compression.codec=2;
drop table if exists web_returns;
create table web_returns (
    wr_returned_time_sk       integer                       ,
    wr_item_sk                integer               not null,
    wr_refunded_customer_sk   integer                       ,
    wr_refunded_cdemo_sk      integer                       ,
    wr_refunded_hdemo_sk      integer                       ,
    wr_refunded_addr_sk       integer                       ,
    wr_returning_customer_sk  integer                       ,
    wr_returning_cdemo_sk     integer                       ,
    wr_returning_hdemo_sk     integer                       ,
    wr_returning_addr_sk      integer                       ,
    wr_web_page_sk            integer                       ,
    wr_reason_sk              integer                       ,
    wr_order_number           bigint                not null,
    wr_return_quantity        integer                       ,
    wr_return_amt             decimal(7,2)                  ,
    wr_return_tax             decimal(7,2)                  ,
    wr_return_amt_inc_tax     decimal(7,2)                  ,
    wr_fee                    decimal(7,2)                  ,
    wr_return_ship_cost       decimal(7,2)                  ,
    wr_refunded_cash          decimal(7,2)                  ,
    wr_reversed_charge        decimal(7,2)                  ,
    wr_account_credit         decimal(7,2)                  ,
    wr_net_loss               decimal(7,2)
) 
partitioned by range(wr_returned_date_sk integer) (
partition p1 values less than (2450846),
--partition p2 values less than (2450874),
--partition p3 values less than (2450905),
partition p4 values less than (2450935),
--partition p5 values less than (2450966),
--partition p6 values less than (2450996),
partition p7 values less than (2451027),
--partition p8 values less than (2451058),
--partition p9 values less than (2451088),
partition p10 values less than (2451119),
--partition p11 values less than (2451149),
--partition p12 values less than (2451180),
partition p13 values less than (2451211),
--partition p14 values less than (2451239),
--partition p15 values less than (2451270),
partition p16 values less than (2451300),
--partition p17 values less than (2451331),
--partition p18 values less than (2451361),
partition p19 values less than (2451392),
--partition p20 values less than (2451423),
--partition p21 values less than (2451453),
partition p22 values less than (2451484),
--partition p23 values less than (2451514),
--partition p24 values less than (2451545),
partition p25 values less than (2451576),
--partition p26 values less than (2451605),
--partition p27 values less than (2451636),
partition p28 values less than (2451666),
--partition p29 values less than (2451697),
--partition p30 values less than (2451727),
partition p31 values less than (2451758),
--partition p32 values less than (2451789),
--partition p33 values less than (2451819),
partition p34 values less than (2451850),
--partition p35 values less than (2451880),
--partition p36 values less than (2451911),
partition p37 values less than (2451942),
--partition p38 values less than (2451970),
--partition p39 values less than (2452001),
partition p40 values less than (2452031),
--partition p41 values less than (2452062),
--partition p42 values less than (2452092),
partition p43 values less than (2452123),
--partition p44 values less than (2452154),
--partition p45 values less than (2452184),
partition p46 values less than (2452215),
--partition p47 values less than (2452245),
--partition p48 values less than (2452276),
partition p49 values less than (2452307),
--partition p50 values less than (2452335),
--partition p51 values less than (2452366),
partition p52 values less than (2452396),
--partition p53 values less than (2452427),
--partition p54 values less than (2452457),
partition p55 values less than (2452488),
--partition p56 values less than (2452519),
--partition p57 values less than (2452549),
partition p58 values less than (2452580),
--partition p59 values less than (2452610),
--partition p60 values less than (2452641),
partition p61 values less than (2452672),
--partition p62 values less than (2452700),
--partition p63 values less than (2452731),
partition p64 values less than (2452761),
--partition p65 values less than (2452792),
--partition p66 values less than (2452822),
partition p67 values less than (2452853),
--partition p68 values less than (2452884),
--partition p69 values less than (2452914),
partition p70 values less than (2452945),
--partition p71 values less than (2452975),
partition p72 values less than (MAXVALUE))
 STORED AS HOLODESK WITH TABLESIZE 1GB;

insert into table web_returns 
select
wr_returned_time_sk,wr_item_sk,wr_refunded_customer_sk,wr_refunded_cdemo_sk,wr_refunded_hdemo_sk,wr_refunded_addr_sk,
wr_returning_customer_sk,wr_returning_cdemo_sk,wr_returning_hdemo_sk,wr_returning_addr_sk,wr_web_page_sk,wr_reason_sk,
wr_order_number,wr_return_quantity,wr_return_amt,wr_return_tax,wr_return_amt_inc_tax,wr_fee,wr_return_ship_cost,
wr_refunded_cash,wr_reversed_charge,wr_account_credit,wr_net_loss,wr_returned_date_sk
from tpcds_text_1.web_returns   ;

