create database if not exists tpcds_holodesk_10000;
use tpcds_holodesk_10000;
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 catalog_returns;
create table catalog_returns (
    cr_returned_time_sk       integer                       ,
    cr_item_sk                integer               not null,
    cr_refunded_customer_sk   integer                       ,
    cr_refunded_cdemo_sk      integer                       ,
    cr_refunded_hdemo_sk      integer                       ,
    cr_refunded_addr_sk       integer                       ,
    cr_returning_customer_sk  integer                       ,
    cr_returning_cdemo_sk     integer                       ,
    cr_returning_hdemo_sk     integer                       ,
    cr_returning_addr_sk      integer                       ,
    cr_call_center_sk         integer                       ,
    cr_catalog_page_sk        integer                       ,
    cr_ship_mode_sk           integer                       ,
    cr_warehouse_sk           integer                       ,
    cr_reason_sk              integer                       ,
    cr_order_number           bigint                not null,
    cr_return_quantity        integer                       ,
    cr_return_amount          decimal(7,2)                  ,
    cr_return_tax             decimal(7,2)                  ,
    cr_return_amt_inc_tax     decimal(7,2)                  ,
    cr_fee                    decimal(7,2)                  ,
    cr_return_ship_cost       decimal(7,2)                  ,
    cr_refunded_cash          decimal(7,2)                  ,
    cr_reversed_charge        decimal(7,2)                  ,
    cr_store_credit           decimal(7,2)                  ,
    cr_net_loss               decimal(7,2)
) 
partitioned by range(cr_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 (MAXVALUE))
 STORED AS HOLODESK WITH TABLESIZE 630GB;

insert into table catalog_returns 
select
cr_returned_time_sk,cr_item_sk,cr_refunded_customer_sk,cr_refunded_cdemo_sk,cr_refunded_hdemo_sk,cr_refunded_addr_sk,
cr_returning_customer_sk,cr_returning_cdemo_sk,cr_returning_hdemo_sk,cr_returning_addr_sk,cr_call_center_sk,
cr_catalog_page_sk,cr_ship_mode_sk,cr_warehouse_sk,cr_reason_sk,cr_order_number,cr_return_quantity,cr_return_amount,
cr_return_tax,cr_return_amt_inc_tax,cr_fee,cr_return_ship_cost,cr_refunded_cash,cr_reversed_charge,cr_store_credit,
cr_net_loss,cr_returned_date_sk
from tpcds_text_10000.catalog_returns  ;
