>>
>>sh regrhive.ksh -v -f $REGRTSTDIR/TEST018_create_hive_tables.hive &> $REGRRUNDIR/LOG018_create_hive_tables.log ;
>>
>>obey TEST018(setup);
>>--------------------------------------------------------------------------
>>create table nulls (a char(5), b char(10));

--- SQL operation complete.
>>create table null_format_src (a varchar(5), b varchar(5));

--- SQL operation complete.
>>
>>create table customer_demographics
+>(
+>  cd_demo_sk              int not null,
+>  cd_gender               char(1),
+>  cd_marital_status       char(1),
+>  cd_education_status     char(20),
+>  cd_purchase_estimate    int,
+>  cd_credit_rating        char(10),
+>  cd_dep_count            int,
+>  cd_dep_employed_count   int,
+>  cd_dep_college_count    int,
+>  primary key (cd_demo_sk)
+>);

--- SQL operation complete.
>>
>>create table customer_demographics_salt
+>(
+>  cd_demo_sk              int not null,
+>  cd_gender               char(1),
+>  cd_marital_status       char(1),
+>  cd_education_status     char(20),
+>  cd_purchase_estimate    int,
+>  cd_credit_rating        char(10),
+>  cd_dep_count            int,
+>  cd_dep_employed_count   int,
+>  cd_dep_college_count    int,
+>  primary key (cd_demo_sk)
+>)
+>salt using 4 partitions on (cd_demo_sk);

--- SQL operation complete.
>>
>>
>>create table customer_address
+>(
+>  ca_address_sk        int not null,
+>  ca_address_id        char(16),
+>  ca_street_number     char(10),
+>  ca_street_name       varchar(60),
+>  ca_street_type       char(15),
+>  ca_suite_number      char(10),
+>  ca_city              varchar(60),
+>  ca_county            varchar(30),
+>  ca_state             char(2),
+>  ca_zip               char(10),
+>  ca_country           varchar(30),
+>  ca_gmt_offset        decimal(5,2),
+>  ca_location_type     char(20),
+>  primary key (ca_address_sk)
+>);

--- SQL operation complete.
>>
>>
>>
>>create table customer_salt
+>(
+>    c_customer_sk             int not null,
+>    c_customer_id             char(16),
+>    c_current_cdemo_sk        int,
+>    c_current_hdemo_sk        int,
+>    c_current_addr_sk         int,
+>    c_first_shipto_date_sk    int,
+>    c_first_sales_date_sk     int,
+>    c_salutation              char(10) character set utf8,
+>    c_first_name              char(20)  character set utf8,
+>    c_last_name               char(30)  character set utf8,
+>    c_preferred_cust_flag     char(1),
+>    c_birth_day               int,
+>    c_birth_month             int,
+>    c_birth_year              int,
+>    c_birth_country           varchar(20)  character set utf8,
+>    c_login                   char(13)  character set utf8,
+>    c_email_address           char(50)  character set utf8,
+>    c_last_review_date        char(50),
+>    primary key (c_customer_sk)
+>)
+>salt using 4 partitions on (c_customer_sk);

--- SQL operation complete.
>>
>>
>>create table store_sales_salt
+>(
+>    ss_sold_date_sk           int,
+>    ss_sold_time_sk           int,
+>    ss_item_sk                int NOT NULL,
+>    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 NOT NULL,
+>    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),
+>primary key (ss_item_sk, ss_ticket_number)                 
+>)
+>salt using 4 partitions 
+>;

--- SQL operation complete.
>>
>>
>>
>>obey TEST018(test_bulk_unload_simple);
>>--------------------------------------------------------------------------
>>--nulls
>>insert into nulls values ('aaa1','bbbbb1'),(null,'bbbbb2'),
+>                         ('aaa3',null),(null,null),
+>                          ('aaa5',null),(null,'bbbbb6');

--- 6 row(s) inserted.
>>
>>insert into null_format_src values 
+>   ('a', 'b'), ('a', null), (null, 'b'), ('a', ''), ('', 'b'),
+>   (null, ''), (':', null), ('', ''), (':', ':'), (null, null);

--- 10 row(s) inserted.
>>
>>--load1
>>load with no recovery into customer_address 
+>select * from hive.hive.customer_address;
Task:  LOAD            Status: Started    Object: TRAFODION.HBASE.CUSTOMER_ADDRESS
Task:  CLEANUP         Status: Started    Time: 2017-08-15 14:53:40.511
Task:  CLEANUP         Status: Ended      Time: 2017-08-15 14:53:40.528
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.018
Task:  LOADING DATA    Status: Started    Time: 2017-08-15 14:53:40.529
       Rows Processed: 50000 
       Error Rows:     0 
Task:  LOADING DATA    Status: Ended      Time: 2017-08-15 14:53:50.229
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:09.701
Task:  COMPLETION      Status: Started    Time: 2017-08-15 14:53:50.229
       Rows Loaded:    50000 
Task:  COMPLETION      Status: Ended      Time: 2017-08-15 14:53:50.635
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.406

--- 50000 row(s) loaded.
>>--
>>select count(*) from hive.hive.customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>select count(*) from customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>
>>--load2
>>load  with no recovery  into customer_demographics 
+>select * from hive.hive.customer_demographics  where cd_demo_sk <= 20000;
Task:  LOAD            Status: Started    Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS
Task:  CLEANUP         Status: Started    Time: 2017-08-15 14:53:54.217
Task:  CLEANUP         Status: Ended      Time: 2017-08-15 14:53:54.237
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.020
Task:  LOADING DATA    Status: Started    Time: 2017-08-15 14:53:54.237
       Rows Processed: 20000 
       Error Rows:     0 
Task:  LOADING DATA    Status: Ended      Time: 2017-08-15 14:54:07.423
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:13.186
Task:  COMPLETION      Status: Started    Time: 2017-08-15 14:54:07.423
       Rows Loaded:    20000 
Task:  COMPLETION      Status: Ended      Time: 2017-08-15 14:54:07.866
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.443

--- 20000 row(s) loaded.
>>--
>>select count(*) from hive.hive.customer_demographics   where cd_demo_sk <= 20000;

*** WARNING[6008] Statistics for column (CD_DEMO_SK) from table HIVE.HIVE.CUSTOMER_DEMOGRAPHICS were not available. As a result, the access path chosen might not be the best possible.

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>select count(*) from customer_demographics;

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>--load3
>>load  with no recovery into customer_demographics_salt 
+>select * from hive.hive.customer_demographics  where cd_demo_sk <= 20000;
Task:  LOAD            Status: Started    Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT
Task:  CLEANUP         Status: Started    Time: 2017-08-15 14:54:12.393
Task:  CLEANUP         Status: Ended      Time: 2017-08-15 14:54:12.404
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.011
Task:  LOADING DATA    Status: Started    Time: 2017-08-15 14:54:12.404
       Rows Processed: 20000 
       Error Rows:     0 
Task:  LOADING DATA    Status: Ended      Time: 2017-08-15 14:54:22.330
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:09.628
Task:  COMPLETION      Status: Started    Time: 2017-08-15 14:54:22.331
       Rows Loaded:    20000 
Task:  COMPLETION      Status: Ended      Time: 2017-08-15 14:54:22.384
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.352

--- 20000 row(s) loaded.
>>--                                                                              
>>select count(*) from customer_demographics_salt;

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>--load4
>>load  with no recovery  into customer_salt 
+>select * from hive.hive.customer;
Task:  LOAD            Status: Started    Object: TRAFODION.HBASE.CUSTOMER_SALT
Task:  CLEANUP         Status: Started    Time: 2017-08-15 14:54:25.112
Task:  CLEANUP         Status: Ended      Time: 2017-08-15 14:54:25.127
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.015
Task:  LOADING DATA    Status: Started    Time: 2017-08-15 14:54:25.127
       Rows Processed: 100000 
       Error Rows:     0 
Task:  LOADING DATA    Status: Ended      Time: 2017-08-15 14:54:38.572
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:13.445
Task:  COMPLETION      Status: Started    Time: 2017-08-15 14:54:38.572
       Rows Loaded:    100000 
Task:  COMPLETION      Status: Ended      Time: 2017-08-15 14:54:38.978
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.406

--- 100000 row(s) loaded.
>>--
>>select count(*) from hive.hive.customer;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>select count(*) from customer_salt;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>
>>--load5
>>load  with no recovery into store_sales_salt 
+>select * from hive.hive.store_sales where ss_item_sk <= 1000;
Task:  LOAD            Status: Started    Object: TRAFODION.HBASE.STORE_SALES_SALT
Task:  CLEANUP         Status: Started    Time: 2017-08-15 14:54:43.982
Task:  CLEANUP         Status: Ended      Time: 2017-08-15 14:54:43.114
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.016
Task:  LOADING DATA    Status: Started    Time: 2017-08-15 14:54:43.114
       Rows Processed: 160756 
       Error Rows:     0 
Task:  LOADING DATA    Status: Ended      Time: 2017-08-15 14:54:57.277
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:14.163
Task:  COMPLETION      Status: Started    Time: 2017-08-15 14:54:57.277
       Rows Loaded:    160756 
Task:  COMPLETION      Status: Ended      Time: 2017-08-15 14:54:57.644
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.367

--- 160756 row(s) loaded.
>>--
>>--select count(*) from hive.hive.store_sales  where ss_item_sk <= 1000;
>>select count(*) from store_sales_salt;

(EXPR)              
--------------------

              160756

--- 1 row(s) selected.
>>
>>------------------------------------------
>>-- handling of various null formats
>>
>>-- using insert
>>insert overwrite table hive.hive.null_format_default select * from null_format_src;

--- 10 row(s) inserted.
>>select * from hive.hive.null_format_default;

A                                                             B
------------------------------------------------------------  ------------------------------------------------------------

a                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
a                                                                                                                         
                                                              b                                                           
?                                                                                                                         
:                                                             ?                                                           
                                                                                                                          
:                                                             :                                                           
?                                                             ?                                                           

--- 10 row(s) selected.
>>
>>insert overwrite table hive.hive.null_format_empty select * from null_format_src;

--- 10 row(s) inserted.
>>select * from hive.hive.null_format_empty;

A                                                             B
------------------------------------------------------------  ------------------------------------------------------------

a                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
?                                                             ?                                                           
:                                                             ?                                                           
?                                                             ?                                                           
:                                                             :                                                           
?                                                             ?                                                           

--- 10 row(s) selected.
>>
>>insert overwrite table hive.hive.null_format_colon select * from null_format_src;

--- 10 row(s) inserted.
>>select * from hive.hive.null_format_colon;

A                                                             B
------------------------------------------------------------  ------------------------------------------------------------

a                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
a                                                                                                                         
                                                              b                                                           
?                                                                                                                         
?                                                             ?                                                           
                                                                                                                          
?                                                             ?                                                           
?                                                             ?                                                           

--- 10 row(s) selected.
>>
>>
>>-- using unload
>>unload with purgedata from target
+>   into '/user/trafodion/hive/exttables/null_format_default'
+>   select * from null_format_src;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:55:09.437
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:55:09.493
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.006
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:55:09.494
       Rows Processed: 10 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:55:09.218
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.169

--- 10 row(s) unloaded.
>>select * from hive.hive.null_format_default;

A                                                             B
------------------------------------------------------------  ------------------------------------------------------------

a                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
a                                                                                                                         
                                                              b                                                           
?                                                                                                                         
:                                                             ?                                                           
                                                                                                                          
:                                                             :                                                           
?                                                             ?                                                           

--- 10 row(s) selected.
>>
>>unload with purgedata from target
+>   into '/user/trafodion/hive/exttables/null_format_empty'
+>   select * from null_format_src;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:55:11.384
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:55:11.390
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.006
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:55:11.390
       Rows Processed: 10 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:55:11.610
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.220

--- 10 row(s) unloaded.
>>select * from hive.hive.null_format_empty;

A                                                             B
------------------------------------------------------------  ------------------------------------------------------------

a                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
?                                                             ?                                                           
:                                                             ?                                                           
?                                                             ?                                                           
:                                                             :                                                           
?                                                             ?                                                           

--- 10 row(s) selected.
>>
>>unload with purgedata from target
+>   into '/user/trafodion/hive/exttables/null_format_colon'
+>   select * from null_format_src;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:55:12.295
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:55:12.304
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.009
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:55:12.304
       Rows Processed: 10 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:55:12.440
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.136

--- 10 row(s) unloaded.
>>select * from hive.hive.null_format_colon;

A                                                             B
------------------------------------------------------------  ------------------------------------------------------------

a                                                             b                                                           
a                                                             ?                                                           
?                                                             b                                                           
a                                                                                                                         
                                                              b                                                           
?                                                                                                                         
?                                                             ?                                                           
                                                                                                                          
?                                                             ?                                                           
?                                                             ?                                                           

--- 10 row(s) selected.
>>
>>---index 0
>>create index customer_idx1 on customer_salt(c_first_name, c_last_name);

--- SQL operation complete.
>>
>>sh  regrhbase.ksh $REGRTSTDIR/TEST018_create_hbase_objects.hbase &> $REGRRUNDIR/LOG018_create_hbase_tables.log ;
>>
>>alter table hbase.customer_address generate stored descriptor;

--- SQL operation complete.
>>alter table hbase.customer_demographics_salt generate stored descriptor;

--- SQL operation complete.
>>alter table hbase.customer_salt generate stored descriptor;

--- SQL operation complete.
>>
>>--exp1
>>explain options 'f' 
+>UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_address'
+>select * from trafodion.hbase.customer_address 
+><<+ cardinality 10e10 >>;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+011
1    .    3    unload                                                1.00E+011
.    .    1    trafodion_scan                  CUSTOMER_ADDRESS      1.00E+011

--- SQL operation complete.
>>--unload1
>>UNLOAD  
+>WITH 
+> PURGEDATA FROM TARGET DELIMITER '|' RECORD_SEPARATOR '\n' NULL_STRING 'NULL'
+>MERGE FILE  'merged_customer_address.gz' OVERWRITE
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_address'
+>select * from trafodion.hbase.customer_address 
+>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:07.950
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:07.102
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:07.102
       Rows Processed: 50000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:08.375
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:01.274
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:08.375
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:08.429
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.053

--- 50000 row(s) unloaded.
>>log;
regrhadoop.ksh fs -copyToLocal  /user/trafodion/bulkload/customer_address/merged_customer_address.gz /tmp 
gunzip -f /tmp/merged_customer_address.gz
cat /tmp/merged_customer_address | wc -l
50000
>>--------------------------
>>--exp2
>>explain options 'f' 
+>UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_demographics'
+>select * from trafodion.hbase.customer_demographics 
+><<+ cardinality 10e10 >>;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+011
1    .    3    unload                                                1.00E+011
.    .    1    trafodion_scan                  CUSTOMER_DEMOGRAPHIC  1.00E+011

--- SQL operation complete.
>>--unload 2
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demogs.gz' OVERWRITE
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics'
+>select * from trafodion.hbase.customer_demographics 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:12.216
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:12.226
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.010
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:12.226
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:12.740
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.513
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:12.740
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:12.792
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.052

--- 20000 row(s) unloaded.
>>log;
regrhadoop.ksh fs -copyToLocal  /user/trafodion/bulkload/customer_demographics/merged_customer_demogs.gz /tmp 
gunzip -f /tmp/merged_customer_demogs.gz
cat /tmp/merged_customer_demogs | wc -l
20000
>>-----------
>>--unload 3
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demogs_2' OVERWRITE
+>--COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics'
+>select * from trafodion.hbase.customer_demographics 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:15.875
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:15.887
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.012
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:15.887
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:16.341
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.454
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:16.341
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:16.380
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.039

--- 20000 row(s) unloaded.
>>log;
regrhadoop.ksh fs -cat  /user/trafodion/bulkload/customer_demographics/merged_customer_demogs_2 | wc -l 
20000
>>----------------------------------
>>--exp 3
>>explain options 'f' 
+>UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

4    .    5    root                                                  1.00E+011
3    .    4    esp_exchange                    1:4(hash2)            1.00E+011
1    .    3    unload                                                1.00E+011
.    .    1    trafodion_scan                  CUSTOMER_DEMOGRAPHIC  1.00E+011

--- SQL operation complete.
>>--unload 4
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demogs_3' OVERWRITE
+>--COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:21.934
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:21.941
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:21.941
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:23.323
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:01.061
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:23.328
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:23.624
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.059

--- 20000 row(s) unloaded.
>>
>>log;
regrhadoop.ksh fs -du -s /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_3
778224  778224  /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_3
>>-------------------
>>--unload 5
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demogs_4.gz' OVERWRITE
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:26.136
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:26.145
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.009
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:26.145
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:26.720
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.575
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:26.720
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:26.785
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.065

--- 20000 row(s) unloaded.
>>
>>log;
regrhadoop.ksh fs -du -s /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_4.gz
78431  78431  /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_4.gz
>>
>>--exp4
>>explain options 'f' 
+>UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

4    .    5    root                                                  1.00E+011
3    .    4    esp_exchange                    1:4(hash2)            1.00E+011
1    .    3    unload                                                1.00E+011
.    .    1    trafodion_scan                  CUSTOMER_DEMOGRAPHIC  1.00E+011

--- SQL operation complete.
>>--unload 6
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>--MERGE FILE  '/user/trafodion/bulkload/merged_customer_demogs_2.gz' OVERWRITE
+>--COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:30.146
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:30.248
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.010
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:30.248
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:30.457
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.432

--- 20000 row(s) unloaded.
>>
>>log;
regrhadoop.ksh fs -cat /user/trafodion/bulkload/customer_demographics_salt/file* | wc -l
20000
regrhadoop.ksh fs -ls /user/trafodion/bulkload/customer_demographics_salt/file* |  grep file | wc -l
4
>>
>>--unload 7
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demogs_2.gz' OVERWRITE
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:36.164
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:36.180
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.017
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:36.180
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:36.616
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.435
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:36.616
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:36.675
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.060

--- 20000 row(s) unloaded.
>>
>>sh regrhadoop.ksh fs -copyToLocal  /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_2.gz /tmp ;
>>sh gunzip -f /tmp/merged_customer_demogs_2.gz  ;
>>sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/unload_customer_demographics/* ;
>>sh regrhadoop.ksh fs -copyFromLocal /tmp/merged_customer_demogs_2  /user/trafodion/hive/exttables/unload_customer_demographics ;
>>sh rm /user/trafodion/bulkload/merged_customer_demogs_2 ;
>>
>>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '100';

--- SQL operation complete.
>>select [first 100] * from hive.hive.unload_customer_demographics where cd_demo_sk <200 order by cd_demo_sk;

CD_DEMO_SK   CD_GENDER                                                                                             CD_MARITAL_STATUS                                                                                     CD_EDUCATION_STATUS                                                                                   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING                                                                                      CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------  ----------------------------------------------------------------------------------------------------  ------------  ---------------------  --------------------

          1  M                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          2  F                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          3  M                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          4  F                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          5  M                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          6  F                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          7  M                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          8  F                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          9  M                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         10  F                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         11  M                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         12  F                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         13  M                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         14  F                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         15  M                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         16  F                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         17  M                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         18  F                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         19  M                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         20  F                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         21  M                                                                                                     M                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         22  F                                                                                                     M                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         23  M                                                                                                     S                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         24  F                                                                                                     S                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         25  M                                                                                                     D                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         26  F                                                                                                     D                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         27  M                                                                                                     W                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         28  F                                                                                                     W                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         29  M                                                                                                     U                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         30  F                                                                                                     U                                                                                                     College                                                                                                                500  Good                                                                                                             0                      0                     0
         31  M                                                                                                     M                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         32  F                                                                                                     M                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         33  M                                                                                                     S                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         34  F                                                                                                     S                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         35  M                                                                                                     D                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         36  F                                                                                                     D                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         37  M                                                                                                     W                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         38  F                                                                                                     W                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         39  M                                                                                                     U                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         40  F                                                                                                     U                                                                                                     2 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         41  M                                                                                                     M                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         42  F                                                                                                     M                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         43  M                                                                                                     S                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         44  F                                                                                                     S                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         45  M                                                                                                     D                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         46  F                                                                                                     D                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         47  M                                                                                                     W                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         48  F                                                                                                     W                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         49  M                                                                                                     U                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         50  F                                                                                                     U                                                                                                     4 yr Degree                                                                                                            500  Good                                                                                                             0                      0                     0
         51  M                                                                                                     M                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         52  F                                                                                                     M                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         53  M                                                                                                     S                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         54  F                                                                                                     S                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         55  M                                                                                                     D                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         56  F                                                                                                     D                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         57  M                                                                                                     W                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         58  F                                                                                                     W                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         59  M                                                                                                     U                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         60  F                                                                                                     U                                                                                                     Advanced Degree                                                                                                        500  Good                                                                                                             0                      0                     0
         61  M                                                                                                     M                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         62  F                                                                                                     M                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         63  M                                                                                                     S                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         64  F                                                                                                     S                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         65  M                                                                                                     D                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         66  F                                                                                                     D                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         67  M                                                                                                     W                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         68  F                                                                                                     W                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         69  M                                                                                                     U                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         70  F                                                                                                     U                                                                                                     Unknown                                                                                                                500  Good                                                                                                             0                      0                     0
         71  M                                                                                                     M                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         72  F                                                                                                     M                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         73  M                                                                                                     S                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         74  F                                                                                                     S                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         75  M                                                                                                     D                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         76  F                                                                                                     D                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         77  M                                                                                                     W                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         78  F                                                                                                     W                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         79  M                                                                                                     U                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         80  F                                                                                                     U                                                                                                     Primary                                                                                                               1000  Good                                                                                                             0                      0                     0
         81  M                                                                                                     M                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         82  F                                                                                                     M                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         83  M                                                                                                     S                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         84  F                                                                                                     S                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         85  M                                                                                                     D                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         86  F                                                                                                     D                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         87  M                                                                                                     W                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         88  F                                                                                                     W                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         89  M                                                                                                     U                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         90  F                                                                                                     U                                                                                                     Secondary                                                                                                             1000  Good                                                                                                             0                      0                     0
         91  M                                                                                                     M                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         92  F                                                                                                     M                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         93  M                                                                                                     S                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         94  F                                                                                                     S                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         95  M                                                                                                     D                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         96  F                                                                                                     D                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         97  M                                                                                                     W                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         98  F                                                                                                     W                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
         99  M                                                                                                     U                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0
        100  F                                                                                                     U                                                                                                     College                                                                                                               1000  Good                                                                                                             0                      0                     0

--- 100 row(s) selected.
>>
>>
>>
>>--unkoad 8
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demogs_4.gz' OVERWRITE
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:47.463
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:47.531
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:47.531
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:47.930
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.877
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:47.930
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:47.997
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.067

--- 20000 row(s) unloaded.
>>log;
regrhadoop.ksh fs -ls /user/trafodion/bulkload/customer_demographics_salt/merged* | grep merge | wc -l
1
>>
>>
>>--unload 9
>>-- should give error 
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demogs_2' OVERWRITE
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_demographics_salt'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;

*** ERROR[4487] Invalid Path: MERGE FILE 'merged_customer_demogs_2' must end with the characters '.gz'.

*** ERROR[8822] The statement was not prepared.

>>
>>--- unload directly to hive tables locations and then read data back by 
>>--- queru=ying hive tables
>>--unload 10
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>MERGE FILE  'merged_customer_demographics' OVERWRITE
+>--COMPRESSION GZIP
+>INTO '/user/trafodion/hive/exttables/unload_customer_demographics'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:50.956
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:50.966
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.010
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:50.966
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:51.783
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.817
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:56:51.783
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:56:51.867
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.084

--- 20000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_demographics;

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

CD_DEMO_SK   CD_GENDER                                                                                             CD_MARITAL_STATUS                                                                                     CD_EDUCATION_STATUS                                                                                   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING                                                                                      CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------  ----------------------------------------------------------------------------------------------------  ------------  ---------------------  --------------------

          1  M                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          2  F                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          3  M                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          4  F                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          5  M                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          6  F                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          7  M                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          8  F                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          9  M                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         10  F                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         11  M                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         12  F                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         13  M                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         14  F                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         15  M                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         16  F                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         17  M                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         18  F                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         19  M                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         20  F                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0

--- 20 row(s) selected.
>>
>>--unload 11
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>--MERGE FILE  'merged_customer_demographics' OVERWRITE
+>--COMPRESSION GZIP
+>INTO '/user/trafodion/hive/exttables/unload_customer_demographics'
+>select * from trafodion.hbase.customer_demographics_salt 
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:54.325
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:54.331
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.006
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:54.331
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:54.845
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.514

--- 20000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_demographics;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1502809011, failedModTS = 1502809014, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/unload_customer_demographics

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk <100  order by cd_demo_sk;

CD_DEMO_SK   CD_GENDER                                                                                             CD_MARITAL_STATUS                                                                                     CD_EDUCATION_STATUS                                                                                   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING                                                                                      CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------  ----------------------------------------------------------------------------------------------------  ------------  ---------------------  --------------------

          1  M                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          2  F                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          3  M                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          4  F                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          5  M                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          6  F                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          7  M                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          8  F                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          9  M                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         10  F                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         11  M                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         12  F                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         13  M                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         14  F                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         15  M                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         16  F                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         17  M                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         18  F                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         19  M                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         20  F                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0

--- 20 row(s) selected.
>>
>>--unload 12
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>INTO '/user/trafodion/hive/exttables/unload_customer_address'
+>select * from trafodion.hbase.customer_address ;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:56:57.715
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:56:57.722
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:56:57.722
       Rows Processed: 50000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:56:58.660
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.938

--- 50000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_address where ca_address_sk <100 order by ca_address_sk;

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table HIVE.HIVE.UNLOAD_CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

            1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            2  AAAAAAAACAAAAAAA                                                                                      362                                                                                                   Washington 6th                                                                                        RD                                                                                                    Suite 80                                                                                              Fairview                                                                                              Taos County                                                                                           NM                                                                                                    85709                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
            4  AAAAAAAAEAAAAAAA                                                                                      111                                                                                                   Smith                                                                                                 Wy                                                                                                    Suite A                                                                                               Oak Ridge                                                                                             Kit Carson County                                                                                     CO                                                                                                    88371                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
            6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
            7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      
            8  AAAAAAAAIAAAAAAA                                                                                      875                                                                                                   Lincoln                                                                                               Ct.                                                                                                   Suite Y                                                                                               Union                                                                                                 Bledsoe County                                                                                        TN                                                                                                    38721                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
            9  AAAAAAAAJAAAAAAA                                                                                      819                                                                                                   1st Laurel                                                                                            Ave                                                                                                   Suite 70                                                                                              New Hope                                                                                              Perry County                                                                                          AL                                                                                                    39431                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           10  AAAAAAAAKAAAAAAA                                                                                      851                                                                                                   Woodland Poplar                                                                                       ST                                                                                                    Suite Y                                                                                               Martinsville                                                                                          Haines Borough                                                                                        AK                                                                                                    90419                                                                                                 United States                                                                                         -9.0000000E+000  condo                                                                                               
           11  AAAAAAAALAAAAAAA                                                                                      189                                                                                                   13th 2nd                                                                                              Street                                                                                                Suite 470                                                                                             Maple Grove                                                                                           Madison County                                                                                        MT                                                                                                    68252                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
           12  AAAAAAAAMAAAAAAA                                                                                      76                                                                                                    Ash 8th                                                                                               Ct.                                                                                                   Suite O                                                                                               Edgewood                                                                                              Mifflin County                                                                                        PA                                                                                                    10069                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           13  AAAAAAAANAAAAAAA                                                                                      424                                                                                                   Main Second                                                                                           Ln                                                                                                    Suite 130                                                                                             Greenville                                                                                            Noxubee County                                                                                        MS                                                                                                    51387                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
           14  AAAAAAAAOAAAAAAA                                                                                      923                                                                                                   Pine Oak                                                                                              Dr.                                                                                                   Suite 100                                                                                                                                                                                                   Lipscomb County                                                                                       TX                                                                                                    77752                                                                                                                                                                                                       -6.0000000E+000                                                                                                      
           15  AAAAAAAAPAAAAAAA                                                                                      314                                                                                                   Spring                                                                                                Ct.                                                                                                   Suite B                                                                                               Oakland                                                                                               Washington County                                                                                     OH                                                                                                    49843                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           16  AAAAAAAAABAAAAAA                                                                                      576                                                                                                   Adams Center                                                                                          Street                                                                                                Suite J                                                                                               Valley View                                                                                           Oldham County                                                                                         TX                                                                                                    75124                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           17  AAAAAAAABBAAAAAA                                                                                      801                                                                                                   Green                                                                                                 Dr.                                                                                                   Suite 0                                                                                               Montpelier                                                                                            Richland County                                                                                       OH                                                                                                    48930                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
           18  AAAAAAAACBAAAAAA                                                                                      460                                                                                                   Maple Spruce                                                                                          Court                                                                                                 Suite 480                                                                                             Somerville                                                                                            Potter County                                                                                         SD                                                                                                    57783                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
           19  AAAAAAAADBAAAAAA                                                                                      611                                                                                                   Wilson                                                                                                Way                                                                                                   Suite O                                                                                               Oakdale                                                                                               Tangipahoa Parish                                                                                     LA                                                                                                    79584                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           
           20  AAAAAAAAEBAAAAAA                                                                                      675                                                                                                   Elm Wilson                                                                                            Street                                                                                                Suite I                                                                                               Hopewell                                                                                              Williams County                                                                                       OH                                                                                                    40587                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               

--- 20 row(s) selected.
>>
>>--unload 12-2
>>--test with numeric delimiers
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET DELIMITER 124  RECORD_SEPARATOR 10
+>INTO '/user/trafodion/hive/exttables/unload_customer_address'
+>select * from trafodion.hbase.customer_address ;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:57:02.463
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:57:02.470
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:57:02.470
       Rows Processed: 50000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:57:03.210
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.740

--- 50000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_address;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1502809018, failedModTS = 1502809022, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/unload_customer_address

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_address where ca_address_sk < 100 order by ca_address_sk;

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table HIVE.HIVE.UNLOAD_CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

            1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            2  AAAAAAAACAAAAAAA                                                                                      362                                                                                                   Washington 6th                                                                                        RD                                                                                                    Suite 80                                                                                              Fairview                                                                                              Taos County                                                                                           NM                                                                                                    85709                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
            4  AAAAAAAAEAAAAAAA                                                                                      111                                                                                                   Smith                                                                                                 Wy                                                                                                    Suite A                                                                                               Oak Ridge                                                                                             Kit Carson County                                                                                     CO                                                                                                    88371                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
            6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
            7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      
            8  AAAAAAAAIAAAAAAA                                                                                      875                                                                                                   Lincoln                                                                                               Ct.                                                                                                   Suite Y                                                                                               Union                                                                                                 Bledsoe County                                                                                        TN                                                                                                    38721                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
            9  AAAAAAAAJAAAAAAA                                                                                      819                                                                                                   1st Laurel                                                                                            Ave                                                                                                   Suite 70                                                                                              New Hope                                                                                              Perry County                                                                                          AL                                                                                                    39431                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           10  AAAAAAAAKAAAAAAA                                                                                      851                                                                                                   Woodland Poplar                                                                                       ST                                                                                                    Suite Y                                                                                               Martinsville                                                                                          Haines Borough                                                                                        AK                                                                                                    90419                                                                                                 United States                                                                                         -9.0000000E+000  condo                                                                                               
           11  AAAAAAAALAAAAAAA                                                                                      189                                                                                                   13th 2nd                                                                                              Street                                                                                                Suite 470                                                                                             Maple Grove                                                                                           Madison County                                                                                        MT                                                                                                    68252                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
           12  AAAAAAAAMAAAAAAA                                                                                      76                                                                                                    Ash 8th                                                                                               Ct.                                                                                                   Suite O                                                                                               Edgewood                                                                                              Mifflin County                                                                                        PA                                                                                                    10069                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           13  AAAAAAAANAAAAAAA                                                                                      424                                                                                                   Main Second                                                                                           Ln                                                                                                    Suite 130                                                                                             Greenville                                                                                            Noxubee County                                                                                        MS                                                                                                    51387                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
           14  AAAAAAAAOAAAAAAA                                                                                      923                                                                                                   Pine Oak                                                                                              Dr.                                                                                                   Suite 100                                                                                                                                                                                                   Lipscomb County                                                                                       TX                                                                                                    77752                                                                                                                                                                                                       -6.0000000E+000                                                                                                      
           15  AAAAAAAAPAAAAAAA                                                                                      314                                                                                                   Spring                                                                                                Ct.                                                                                                   Suite B                                                                                               Oakland                                                                                               Washington County                                                                                     OH                                                                                                    49843                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           16  AAAAAAAAABAAAAAA                                                                                      576                                                                                                   Adams Center                                                                                          Street                                                                                                Suite J                                                                                               Valley View                                                                                           Oldham County                                                                                         TX                                                                                                    75124                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           17  AAAAAAAABBAAAAAA                                                                                      801                                                                                                   Green                                                                                                 Dr.                                                                                                   Suite 0                                                                                               Montpelier                                                                                            Richland County                                                                                       OH                                                                                                    48930                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
           18  AAAAAAAACBAAAAAA                                                                                      460                                                                                                   Maple Spruce                                                                                          Court                                                                                                 Suite 480                                                                                             Somerville                                                                                            Potter County                                                                                         SD                                                                                                    57783                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
           19  AAAAAAAADBAAAAAA                                                                                      611                                                                                                   Wilson                                                                                                Way                                                                                                   Suite O                                                                                               Oakdale                                                                                               Tangipahoa Parish                                                                                     LA                                                                                                    79584                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           
           20  AAAAAAAAEBAAAAAA                                                                                      675                                                                                                   Elm Wilson                                                                                            Street                                                                                                Suite I                                                                                               Hopewell                                                                                              Williams County                                                                                       OH                                                                                                    40587                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               

--- 20 row(s) selected.
>>
>>
>>-- reduce buffer size--test work method
>>cqd HDFS_IO_BUFFERSIZE '1024';

--- SQL operation complete.
>>cqd attempt_esp_parallelism 'off';

--- SQL operation complete.
>>--unload 13
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>--MERGE FILE  'merged_customer_demographics' OVERWRITE
+>--COMPRESSION GZIP
+>INTO '/user/trafodion/hive/exttables/unload_customer'
+>select * from trafodion.hbase.customer_salt;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:57:08.695
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:57:08.701
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:57:08.701
       Rows Processed: 100000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:57:12.919
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:04.217

--- 100000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer where c_customer_sk < 100 order by c_customer_sk;

*** WARNING[6008] Statistics for column (C_CUSTOMER_SK) from table HIVE.HIVE.UNLOAD_CUSTOMER were not available. As a result, the access path chosen might not be the best possible.

C_CUSTOMER_SK  C_CUSTOMER_ID                                                                                         C_CURRENT_CDEMO_SK  C_CURRENT_HDEMO_SK  C_CURRENT_ADDR_SK  C_FIRST_SHIPTO_DATE_SK  C_FIRST_SALES_DATE_SK  C_SALUTATION                                                                                          C_FIRST_NAME                                                                                          C_LAST_NAME                                                                                           C_PREFERRED_CUST_FLAG                                                                                 C_BIRTH_DAY  C_BIRTH_MONTH  C_BIRTH_YEAR  C_BIRTH_COUNTRY                                                                                       C_LOGIN                                                                                               C_EMAIL_ADDRESS                                                                                       C_LAST_REVIEW_DATE
-------------  ----------------------------------------------------------------------------------------------------  ------------------  ------------------  -----------------  ----------------------  ---------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  -----------  -------------  ------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------

            1  AAAAAAAABAAAAAAA                                                                                                  980124                7135              32946                 2452238                2452208  Mr.                                                                                                   Javier                                                                                                Lewis                                                                                                 Y                                                                                                               9             12          1936  CHILE                                                                                                                                                                                                       Javier.Lewis@VFAxlnZEvOx.org                                                                          2452508                                                                                             
            2  AAAAAAAACAAAAAAA                                                                                                  819667                1461              31655                 2452318                2452288  Dr.                                                                                                   Amy                                                                                                   Moses                                                                                                 Y                                                                                                               9              4          1966  TOGO                                                                                                                                                                                                        Amy.Moses@Ovk9KjHH.com                                                                                2452318                                                                                             
            3  AAAAAAAADAAAAAAA                                                                                                 1473522                6247              48572                 2449130                2449100  Miss                                                                                                  Latisha                                                                                               Hamilton                                                                                              N                                                                                                              18              9          1979  NIUE                                                                                                                                                                                                        Latisha.Hamilton@V.com                                                                                2452313                                                                                             
            4  AAAAAAAAEAAAAAAA                                                                                                 1703214                3986              39558                 2450030                2450000  Dr.                                                                                                   Michael                                                                                               White                                                                                                 N                                                                                                               7              6          1983  MEXICO                                                                                                                                                                                                      Michael.White@i.org                                                                                   2452361                                                                                             
            5  AAAAAAAAFAAAAAAA                                                                                                  953372                4470              36368                 2449438                2449408  Sir                                                                                                   Robert                                                                                                Moran                                                                                                 N                                                                                                               8              5          1956  FIJI                                                                                                                                                                                                        Robert.Moran@Hh.edu                                                                                   2452469                                                                                             
            6  AAAAAAAAGAAAAAAA                                                                                                  213219                6374              27082                 2451883                2451853  Ms.                                                                                                   Brunilda                                                                                              Sharp                                                                                                 N                                                                                                               4             12          1925  SURINAME                                                                                                                                                                                                    Brunilda.Sharp@T3pylZEUQjm.org                                                                        2452430                                                                                             
            7  AAAAAAAAHAAAAAAA                                                                                                   68377                3219              44814                 2451438                2451408  Ms.                                                                                                   Fonda                                                                                                 Wiles                                                                                                 Y                                                                                                              24              4          1985  GAMBIA                                                                                                                                                                                                      Fonda.Wiles@S9KnyEtz9hv.org                                                                           2452360                                                                                             
            8  AAAAAAAAIAAAAAAA                                                                                                 1215897                2471              16598                 2449406                2449376  Sir                                                                                                   Ollie                                                                                                 Shipman                                                                                               N                                                                                                              26             12          1938  KOREA, REPUBLIC OF                                                                                                                                                                                          Ollie.Shipman@be.org                                                                                  2452334                                                                                             
            9  AAAAAAAAJAAAAAAA                                                                                                 1168667                1404              49388                 2452275                2452245  Sir                                                                                                   Karl                                                                                                  Gilbert                                                                                               N                                                                                                              26             10          1966  MONTSERRAT                                                                                                                                                                                                  Karl.Gilbert@Crg5KyP2IxX9C4d6.edu                                                                     2452454                                                                                             
           10  AAAAAAAAKAAAAAAA                                                                                                 1207553                5143              19580                 2451353                2451323  Ms.                                                                                                   Albert                                                                                                Brunson                                                                                               N                                                                                                              15             10          1973  JORDAN                                                                                                                                                                                                      Albert.Brunson@62.com                                                                                 2452641                                                                                             
           11  AAAAAAAALAAAAAAA                                                                                                 1114415                6807              47999                 2452288                2452258  Ms.                                                                                                   Betty                                                                                                 Williams                                                                                              N                                                                                                              18             12          1963  BURKINA FASO                                                                                                                                                                                                Betty.Williams@xRtDqM1eLBVQNoYAJ.com                                                                  2452398                                                                                             
           12  AAAAAAAAMAAAAAAA                                                                                                  502141                6577              47366                 2451039                2451009  Ms.                                                                                                   Margaret                                                                                              Farias                                                                                                N                                                                                                               2              6          1956  TURKMENISTAN                                                                                                                                                                                                Margaret.Farias@cb.edu                                                                                2452634                                                                                             
           13  AAAAAAAANAAAAAAA                                                                                                 1128748                2777              14006                 2449658                2449628  Mrs.                                                                                                  Rosalinda                                                                                             Grimes                                                                                                N                                                                                                               1              3          1970  UKRAINE                                                                                                                                                                                                     Rosalinda.Grimes@tC8pcU7Lt.edu                                                                        2452616                                                                                             
           14  AAAAAAAAOAAAAAAA                                                                                                  929344                 892               6440                 2450318                2450288  Mr.                                                                                                   Jack                                                                                                  Wilcox                                                                                                N                                                                                                              30              3          1937  SLOVENIA                                                                                                                                                                                                    Jack.Wilcox@Y3Etqyv3.org                                                                              2452641                                                                                             
           15  AAAAAAAAPAAAAAAA                                                                                                       ?                 134              30469                       ?                2449010  Ms.                                                                                                   Tonya                                                                                                                                                                                                                                                                                                                       ?             12          1969                                                                                                                                                                                                                                                                                                                    2452376                                                                                             
           16  AAAAAAAAABAAAAAA                                                                                                 1196373                3014              29302                 2451346                2451316  Dr.                                                                                                   Margie                                                                                                Browning                                                                                              N                                                                                                              24             12          1933  PHILIPPINES                                                                                                                                                                                                 Margie.Browning@LM674NrE2.org                                                                         2452573                                                                                             
           17  AAAAAAAABBAAAAAA                                                                                                  707524                3876               2228                 2451068                2451038  Dr.                                                                                                   Lee                                                                                                   Stovall                                                                                               N                                                                                                              23             12          1972  PHILIPPINES                                                                                                                                                                                                 Lee.Stovall@fqKC83UU0f.org                                                                            2452454                                                                                             
           18  AAAAAAAACBAAAAAA                                                                                                 1361151                6580              18456                 2450041                2450011  Sir                                                                                                   Brad                                                                                                  Lynch                                                                                                 Y                                                                                                               1              9          1950  URUGUAY                                                                                                                                                                                                     Brad.Lynch@nAbai.edu                                                                                  2452549                                                                                             
           19  AAAAAAAADBAAAAAA                                                                                                 1161742                4238              45581                 2449580                2449550  Dr.                                                                                                   Andre                                                                                                 Moore                                                                                                 N                                                                                                              20             10          1978  NICARAGUA                                                                                                                                                                                                   Andre.Moore@cTZLGYi1ZJi.org                                                                           2452576                                                                                             
           20  AAAAAAAAEBAAAAAA                                                                                                 1185612                  89              38966                 2450965                2450935  Mr.                                                                                                   Stanton                                                                                               Dallas                                                                                                Y                                                                                                              17              5          1976  SWITZERLAND                                                                                                                                                                                                 Stanton.Dallas@DBXgl18FGo.edu                                                                         2452334                                                                                             

--- 20 row(s) selected.
>>
>>--unload 14
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>--MERGE FILE  'merged_customer_demographics' OVERWRITE
+>--COMPRESSION GZIP
+>INTO '/user/trafodion/hive/exttables/unload_customer_demographics'
+>select * from trafodion.hbase.customer_demographics_salt;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:57:15.925
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:57:15.936
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.012
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:57:15.937
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:57:16.236
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.299

--- 20000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_demographics;

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

CD_DEMO_SK   CD_GENDER                                                                                             CD_MARITAL_STATUS                                                                                     CD_EDUCATION_STATUS                                                                                   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING                                                                                      CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------  ----------------------------------------------------------------------------------------------------  ------------  ---------------------  --------------------

          1  M                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          2  F                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          3  M                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          4  F                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          5  M                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          6  F                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          7  M                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          8  F                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          9  M                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         10  F                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         11  M                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         12  F                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         13  M                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         14  F                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         15  M                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         16  F                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         17  M                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         18  F                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         19  M                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         20  F                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0

--- 20 row(s) selected.
>>
>>--unload 15
>>UNLOAD  
+>WITH 
+> PURGEDATA FROM TARGET DELIMITER '|' RECORD_SEPARATOR '\n' NULL_STRING 'NULL'
+>MERGE FILE  'merged_customer_address.gz' OVERWRITE
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_address'
+>select * from trafodion.hbase.customer_address where ca_address_sk < 100;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:57:18.175
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:57:18.182
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:57:18.182
       Rows Processed: 99 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:57:18.248
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.065
Task:  MERGE FILES     Status: Started    Time: 2017-08-15 14:57:18.248
Task:  MERGE FILES     Status: Ended      Time: 2017-08-15 14:57:18.284
Task:  MERGE FILES     Status: Ended      Elapsed Time:    00:00:00.036

--- 99 row(s) unloaded.
>>
>>--unload 16
>>UNLOAD  
+>WITH 
+> PURGEDATA FROM TARGET DELIMITER '|' RECORD_SEPARATOR '\n' NULL_STRING 'NULL'
+>MERGE FILE  'merged_customer_address.gz' 
+>COMPRESSION GZIP
+>INTO '/user/trafodion/bulkload/customer_address'
+>select * from trafodion.hbase.customer_address where ca_address_sk < 100;

*** ERROR[8965] File /user/trafodion/bulkload/customer_address/merged_customer_address.gz already exists.

--- 0 row(s) unloaded.
>>
>>
>>log;
regrhadoop.ksh fs -rm /user/trafodion/hive/exttables/unload_customer_demographics/*
>>
>>
>>cqd HDFS_IO_BUFFERSIZE reset;

--- SQL operation complete.
>>cqd attempt_esp_parallelism reset;

--- SQL operation complete.
>>--unload 17
>>UNLOAD  
+>WITH 
+> PURGEDATA FROM TARGET DELIMITER '|' RECORD_SEPARATOR '\n' 
+>INTO '/user/trafodion/hive/exttables/unload_store_sales_summary'
+>select ss_sold_date_sk,ss_store_sk, sum (ss_quantity) from store_sales_salt group by  ss_sold_date_sk ,ss_store_sk;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:57:21.794
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:57:21.809
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.014
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:57:21.809
       Rows Processed: 12349 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:57:26.261
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:04.452

--- 12349 row(s) unloaded.
>>--sh sleep 10;
>>select  [first 100] * from hive.hive.unload_store_sales_summary order by  ss_sold_date_sk,ss_store_sk;

SS_SOLD_DATE_SK  SS_STORE_SK  SS_QUANTITY
---------------  -----------  -----------

        2450816            1          278
        2450816            2          459
        2450816            4          170
        2450816            7          492
        2450816            8          484
        2450816           10          480
        2450816            ?           80
        2450817            1          297
        2450817            2          156
        2450817            4          344
        2450817            7          328
        2450817            8          357
        2450817           10          312
        2450817            ?           13
        2450818            1         1129
        2450818            2          373
        2450818            4          524
        2450818            7          543
        2450818            8          242
        2450818           10         1115
        2450818            ?            ?
        2450819            1          298
        2450819            2          147
        2450819            4          590
        2450819            7          327
        2450819            8          223
        2450819           10          286
        2450819            ?           22
        2450820            1          691
        2450820            2          750
        2450820            4          602
        2450820            7          214
        2450820            8          409
        2450820           10          429
        2450820            ?           50
        2450821            1          774
        2450821            2          132
        2450821            4          567
        2450821            7          133
        2450821            8          557
        2450821           10          203
        2450822            1          252
        2450822            2          569
        2450822            4          736
        2450822            7          427
        2450822            8          392
        2450822           10          181
        2450822            ?           93
        2450823            1          208
        2450823            2          359
        2450823            4          305
        2450823            7          365
        2450823            8          441
        2450823           10          474
        2450823            ?           34
        2450824            1          258
        2450824            2          262
        2450824            4          516
        2450824            7          293
        2450824            8          172
        2450824           10          383
        2450824            ?            ?
        2450825            1          572
        2450825            2          516
        2450825            4          137
        2450825            7          730
        2450825            8          345
        2450825           10          745
        2450825            ?           72
        2450826            1          742
        2450826            2          342
        2450826            4          578
        2450826            7          431
        2450826            8          497
        2450826           10          555
        2450827            1          324
        2450827            2          209
        2450827            4          292
        2450827            7          218
        2450827            8          197
        2450827           10          314
        2450828            1          357
        2450828            2          256
        2450828            4          107
        2450828            7          177
        2450828            8          454
        2450828           10          616
        2450828            ?            ?
        2450829            1          786
        2450829            2          961
        2450829            4          355
        2450829            7          365
        2450829            8          327
        2450829           10          431
        2450829            ?           31
        2450830            1          469
        2450830            2          668
        2450830            4          753
        2450830            7          252
        2450830            8          143

--- 100 row(s) selected.
>>
>>--unload 18
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+>INTO '/user/trafodion/hive/exttables/unload_customer_and_address'
+>select * from trafodion.hbase.customer_salt c join trafodion.hbase.customer_address ca on c.c_current_addr_sk = ca.ca_address_sk ;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:57:28.396
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:57:28.406
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.010
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:57:28.406
       Rows Processed: 100000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:57:31.408
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:03.002

--- 100000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_and_address;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_and_address order by ca_address_sk,c_customer_sk;

C_CUSTOMER_SK  C_CUSTOMER_ID                                                                                         C_CURRENT_CDEMO_SK  C_CURRENT_HDEMO_SK  C_CURRENT_ADDR_SK  C_FIRST_SHIPTO_DATE_SK  C_FIRST_SALES_DATE_SK  C_SALUTATION                                                                                          C_FIRST_NAME                                                                                          C_LAST_NAME                                                                                           C_PREFERRED_CUST_FLAG                                                                                 C_BIRTH_DAY  C_BIRTH_MONTH  C_BIRTH_YEAR  C_BIRTH_COUNTRY                                                                                       C_LOGIN                                                                                               C_EMAIL_ADDRESS                                                                                       C_LAST_REVIEW_DATE                                                                                    CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ------------------  ------------------  -----------------  ----------------------  ---------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  -----------  -------------  ------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  -------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

        18001  AAAAAAAABFGEAAAA                                                                                                 1046371                1786                  1                 2449262                2449232  Sir                                                                                                   George                                                                                                Hampton                                                                                               N                                                                                                               8             11          1966  GREECE                                                                                                                                                                                                      George.Hampton@dIlBP1NI2hX.org                                                                        2452340                                                                                                           1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        57050  AAAAAAAAKNONAAAA                                                                                                  689270                 612                  1                 2451607                2451577  Mrs.                                                                                                  Toshiko                                                                                               Weaver                                                                                                N                                                                                                              22              3          1939  TRINIDAD AND TOBAGO                                                                                                                                                                                         Toshiko.Weaver@EbeGFDdHiP5jGrRVC4F.org                                                                2452370                                                                                                           1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        75885  AAAAAAAANGICBAAA                                                                                                  103496                2772                  1                 2451858                2451828  Dr.                                                                                                   Raymond                                                                                               Harms                                                                                                 Y                                                                                                              15              4          1951  MAURITIUS                                                                                                                                                                                                   Raymond.Harms@oHSA4k.com                                                                              2452328                                                                                                           1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        58325  AAAAAAAAFNDOAAAA                                                                                                 1756775                 165                  2                 2449485                2449455  Miss                                                                                                  Alice                                                                                                 Warner                                                                                                Y                                                                                                              16              1          1935  ERITREA                                                                                                                                                                                                     Alice.Warner@lHiPEB4CFp1Lb.org                                                                        2452366                                                                                                           2  AAAAAAAACAAAAAAA                                                                                      362                                                                                                   Washington 6th                                                                                        RD                                                                                                    Suite 80                                                                                              Fairview                                                                                              Taos County                                                                                           NM                                                                                                    85709                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        18725  AAAAAAAAFCJEAAAA                                                                                                  369391                 497                  3                 2449807                2449777  Mr.                                                                                                   Jimmy                                                                                                 Short                                                                                                 Y                                                                                                              13              9          1951  TIMOR-LESTE                                                                                                                                                                                                 Jimmy.Short@ryY3VmrBj3eIEYmx9.org                                                                     2452559                                                                                                           3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        32411  AAAAAAAALJOHAAAA                                                                                                 1630141                3872                  3                 2450639                2450609  Sir                                                                                                   Walter                                                                                                Sanchez                                                                                               N                                                                                                               7              5          1937  EQUATORIAL GUINEA                                                                                                                                                                                           Walter.Sanchez@2.edu                                                                                  2452540                                                                                                           3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        40969  AAAAAAAAJAAKAAAA                                                                                                       ?                3181                  3                       ?                2452575  Sir                                                                                                                                                                                                         Jackson                                                                                                                                                                                                               5              6          1928  HONG KONG                                                                                                                                                                                                   Joe.Jackson@aT7.org                                                                                                                                                                                                     3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        61034  AAAAAAAAKGOOAAAA                                                                                                 1479111                5357                  3                 2451120                2451090  Sir                                                                                                   Richard                                                                                               Reinhardt                                                                                             Y                                                                                                              15              3          1939  PARAGUAY                                                                                                                                                                                                    Richard.Reinhardt@05.org                                                                              2452610                                                                                                           3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        17028  AAAAAAAAEICEAAAA                                                                                                 1141265                 211                  4                 2451052                2451022  Dr.                                                                                                   James                                                                                                 Brown                                                                                                 N                                                                                                              25              2          1983  SOLOMON ISLANDS                                                                                                                                                                                             James.Brown@A.org                                                                                     2452473                                                                                                           4  AAAAAAAAEAAAAAAA                                                                                      111                                                                                                   Smith                                                                                                 Wy                                                                                                    Suite A                                                                                               Oak Ridge                                                                                             Kit Carson County                                                                                     CO                                                                                                    88371                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
         6856  AAAAAAAAIMKBAAAA                                                                                                  984690                4371                  5                 2449964                2449934  Dr.                                                                                                   Jorge                                                                                                 Smith                                                                                                 N                                                                                                               4              2          1935  UGANDA                                                                                                                                                                                                      Jorge.Smith@hkOEhqq7hrZHRN.edu                                                                        2452445                                                                                                           5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        10755  AAAAAAAADAKCAAAA                                                                                                  616497                6651                  5                 2450078                2450048  Mr.                                                                                                   Rodney                                                                                                Mack                                                                                                  Y                                                                                                               9             10          1959  SAN MARINO                                                                                                                                                                                                  Rodney.Mack@5.com                                                                                     2452313                                                                                                           5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        92412  AAAAAAAAMPIGBAAA                                                                                                 1455706                6679                  5                 2452065                2452035  Mr.                                                                                                   Nathaniel                                                                                             Parsons                                                                                               N                                                                                                              19              7          1990  NICARAGUA                                                                                                                                                                                                   Nathaniel.Parsons@D6vrtCokekB.org                                                                     2452633                                                                                                           5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
         4321  AAAAAAAABOABAAAA                                                                                                  414860                6233                  6                 2450208                2450178  Ms.                                                                                                   Theresa                                                                                               Overstreet                                                                                            Y                                                                                                               3             10          1969  ZIMBABWE                                                                                                                                                                                                    Theresa.Overstreet@T20uZnkNSpJ8ib0.org                                                                2452301                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
         5455  AAAAAAAAPEFBAAAA                                                                                                 1281074                1140                  6                 2450902                2450872  Mr.                                                                                                   Harvey                                                                                                Clark                                                                                                 N                                                                                                              21             10          1975  KOREA, REPUBLIC OF                                                                                                                                                                                          Harvey.Clark@bAdnGquApj4.edu                                                                          2452421                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
         5632  AAAAAAAAAAGBAAAA                                                                                                  871010                1108                  6                 2452464                2452434  Miss                                                                                                  Viva                                                                                                  Turner                                                                                                N                                                                                                               5              5          1982  NORWAY                                                                                                                                                                                                      Viva.Turner@Z.com                                                                                     2452623                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        15216  AAAAAAAAAHLDAAAA                                                                                                  509782                4894                  6                 2452116                2452086  Miss                                                                                                  Selena                                                                                                Garza                                                                                                 Y                                                                                                               4              1          1957  BELIZE                                                                                                                                                                                                      Selena.Garza@nXnNGznh4m.edu                                                                           2452638                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        27702  AAAAAAAAGDMGAAAA                                                                                                 1052985                3472                  6                 2452458                2452428  Miss                                                                                                  Hanh                                                                                                  Benson                                                                                                Y                                                                                                              10             11          1985  KENYA                                                                                                                                                                                                       Hanh.Benson@Oy8.edu                                                                                   2452602                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        69027  AAAAAAAADKNABAAA                                                                                                 1799829                2894                  6                 2449207                2449177  Mrs.                                                                                                  Holly                                                                                                 Hinkle                                                                                                Y                                                                                                               1              3          1944  POLAND                                                                                                                                                                                                      Holly.Hinkle@7NQVO3aQ7.edu                                                                            2452514                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        19703  AAAAAAAAHPMEAAAA                                                                                                  654391                4199                  7                 2450199                2450169  Dr.                                                                                                   Carrie                                                                                                Currier                                                                                               Y                                                                                                              14             11          1978  SEYCHELLES                                                                                                                                                                                                  Carrie.Currier@I.org                                                                                  2452461                                                                                                           7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      
        36673  AAAAAAAABEPIAAAA                                                                                                 1071833                1320                  7                 2450242                2450212  Dr.                                                                                                   Christopher                                                                                           Smith                                                                                                 N                                                                                                               6              3          1935  BERMUDA                                                                                                                                                                                                     Christopher.Smith@1XyfnXzQcmMv.com                                                                    2452646                                                                                                           7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      

--- 20 row(s) selected.
>>
>>--unload 19 -- unload with union 
>>UNLOAD  
+>WITH 
+> PURGEDATA FROM TARGET 
+>INTO '/user/trafodion/hive/exttables/unload_customer_address'
+>select * from customer_address where ca_address_sk < 1000 union select * from customer_address where ca_address_sk > 40000  and ca_address_sk < 41000;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 14:57:36.424
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 14:57:36.432
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.008
Task:  EXTRACT         Status: Started    Time: 2017-08-15 14:57:36.432
       Rows Processed: 1998 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 14:57:36.875
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.443

--- 1998 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_address;

(EXPR)              
--------------------

                1998

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk;

CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

            1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            2  AAAAAAAACAAAAAAA                                                                                      362                                                                                                   Washington 6th                                                                                        RD                                                                                                    Suite 80                                                                                              Fairview                                                                                              Taos County                                                                                           NM                                                                                                    85709                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
            4  AAAAAAAAEAAAAAAA                                                                                      111                                                                                                   Smith                                                                                                 Wy                                                                                                    Suite A                                                                                               Oak Ridge                                                                                             Kit Carson County                                                                                     CO                                                                                                    88371                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
            6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
            7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      
            8  AAAAAAAAIAAAAAAA                                                                                      875                                                                                                   Lincoln                                                                                               Ct.                                                                                                   Suite Y                                                                                               Union                                                                                                 Bledsoe County                                                                                        TN                                                                                                    38721                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
            9  AAAAAAAAJAAAAAAA                                                                                      819                                                                                                   1st Laurel                                                                                            Ave                                                                                                   Suite 70                                                                                              New Hope                                                                                              Perry County                                                                                          AL                                                                                                    39431                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           10  AAAAAAAAKAAAAAAA                                                                                      851                                                                                                   Woodland Poplar                                                                                       ST                                                                                                    Suite Y                                                                                               Martinsville                                                                                          Haines Borough                                                                                        AK                                                                                                    90419                                                                                                 United States                                                                                         -9.0000000E+000  condo                                                                                               
           11  AAAAAAAALAAAAAAA                                                                                      189                                                                                                   13th 2nd                                                                                              Street                                                                                                Suite 470                                                                                             Maple Grove                                                                                           Madison County                                                                                        MT                                                                                                    68252                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
           12  AAAAAAAAMAAAAAAA                                                                                      76                                                                                                    Ash 8th                                                                                               Ct.                                                                                                   Suite O                                                                                               Edgewood                                                                                              Mifflin County                                                                                        PA                                                                                                    10069                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           13  AAAAAAAANAAAAAAA                                                                                      424                                                                                                   Main Second                                                                                           Ln                                                                                                    Suite 130                                                                                             Greenville                                                                                            Noxubee County                                                                                        MS                                                                                                    51387                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
           14  AAAAAAAAOAAAAAAA                                                                                      923                                                                                                   Pine Oak                                                                                              Dr.                                                                                                   Suite 100                                                                                                                                                                                                   Lipscomb County                                                                                       TX                                                                                                    77752                                                                                                                                                                                                       -6.0000000E+000                                                                                                      
           15  AAAAAAAAPAAAAAAA                                                                                      314                                                                                                   Spring                                                                                                Ct.                                                                                                   Suite B                                                                                               Oakland                                                                                               Washington County                                                                                     OH                                                                                                    49843                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           16  AAAAAAAAABAAAAAA                                                                                      576                                                                                                   Adams Center                                                                                          Street                                                                                                Suite J                                                                                               Valley View                                                                                           Oldham County                                                                                         TX                                                                                                    75124                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           17  AAAAAAAABBAAAAAA                                                                                      801                                                                                                   Green                                                                                                 Dr.                                                                                                   Suite 0                                                                                               Montpelier                                                                                            Richland County                                                                                       OH                                                                                                    48930                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
           18  AAAAAAAACBAAAAAA                                                                                      460                                                                                                   Maple Spruce                                                                                          Court                                                                                                 Suite 480                                                                                             Somerville                                                                                            Potter County                                                                                         SD                                                                                                    57783                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
           19  AAAAAAAADBAAAAAA                                                                                      611                                                                                                   Wilson                                                                                                Way                                                                                                   Suite O                                                                                               Oakdale                                                                                               Tangipahoa Parish                                                                                     LA                                                                                                    79584                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           
           20  AAAAAAAAEBAAAAAA                                                                                      675                                                                                                   Elm Wilson                                                                                            Street                                                                                                Suite I                                                                                               Hopewell                                                                                              Williams County                                                                                       OH                                                                                                    40587                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               

--- 20 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk desc;

CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

        40999  AAAAAAAAHCAKAAAA                                                                                      491                                                                                                   Willow Hickory                                                                                        Road                                                                                                  Suite 100                                                                                             Five Forks                                                                                            Putnam County                                                                                         NY                                                                                                    12293                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
        40998  AAAAAAAAGCAKAAAA                                                                                      481                                                                                                   10th                                                                                                  Blvd                                                                                                  Suite 230                                                                                             Oakwood                                                                                               Pulaski County                                                                                        MO                                                                                                    60169                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40997  AAAAAAAAFCAKAAAA                                                                                      8                                                                                                     Jackson Cedar                                                                                         Court                                                                                                 Suite 50                                                                                              Highland Park                                                                                         Hinds County                                                                                          MS                                                                                                    56534                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40996  AAAAAAAAECAKAAAA                                                                                      616                                                                                                   Park First                                                                                            Lane                                                                                                  Suite 20                                                                                              Rossville                                                                                             Sunflower County                                                                                      MS                                                                                                    52503                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           
        40995  AAAAAAAADCAKAAAA                                                                                      242                                                                                                   Seventh                                                                                               Circle                                                                                                Suite 260                                                                                             Newport                                                                                               Clark County                                                                                          OH                                                                                                    41521                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               
        40994  AAAAAAAACCAKAAAA                                                                                      262                                                                                                   Elm Lincoln                                                                                           Court                                                                                                 Suite D                                                                                               Mount Olive                                                                                           Asotin County                                                                                         WA                                                                                                    98059                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        40993  AAAAAAAABCAKAAAA                                                                                      2                                                                                                     East                                                                                                  Parkway                                                                                               Suite 190                                                                                             Greenville                                                                                            Bay County                                                                                            FL                                                                                                    31387                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        40992  AAAAAAAAACAKAAAA                                                                                      978                                                                                                   11th River                                                                                            Cir.                                                                                                  Suite 330                                                                                             Providence                                                                                            Waseca County                                                                                         MN                                                                                                    56614                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40991  AAAAAAAAPBAKAAAA                                                                                      56                                                                                                    Central                                                                                               Ct.                                                                                                   Suite 320                                                                                             Fairview                                                                                              Musselshell County                                                                                    MT                                                                                                    65709                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
        40990  AAAAAAAAOBAKAAAA                                                                                      338                                                                                                   Second                                                                                                Circle                                                                                                Suite 40                                                                                              Maple Grove                                                                                           Marshall County                                                                                       OK                                                                                                    78252                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40989  AAAAAAAANBAKAAAA                                                                                      258                                                                                                   Chestnut Jefferson                                                                                    Ln                                                                                                    Suite 200                                                                                             Glenwood                                                                                              Lincoln County                                                                                        MS                                                                                                    53511                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40988  AAAAAAAAMBAKAAAA                                                                                      618                                                                                                   10th Dogwood                                                                                          Ln                                                                                                    Suite I                                                                                               Mount Pleasant                                                                                        Hale County                                                                                           AL                                                                                                    31933                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40987  AAAAAAAALBAKAAAA                                                                                      485                                                                                                   12th                                                                                                  Boulevard                                                                                             Suite N                                                                                               Bunker Hill                                                                                           Dade County                                                                                           GA                                                                                                    30150                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
        40986  AAAAAAAAKBAKAAAA                                                                                      483                                                                                                   Main                                                                                                  Cir.                                                                                                  Suite 120                                                                                             Stewart                                                                                               Sioux County                                                                                          ND                                                                                                    58041                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40985  AAAAAAAAJBAKAAAA                                                                                      830                                                                                                   First                                                                                                 Pkwy                                                                                                  Suite E                                                                                               Springfield                                                                                           Thayer County                                                                                         NE                                                                                                    69303                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
        40984  AAAAAAAAIBAKAAAA                                                                                      969                                                                                                   Chestnut Elm                                                                                          Ct.                                                                                                   Suite P                                                                                               Woodville                                                                                             Citrus County                                                                                         FL                                                                                                    34289                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               
        40983  AAAAAAAAHBAKAAAA                                                                                      456                                                                                                   Meadow                                                                                                Boulevard                                                                                             Suite D                                                                                               Oakland                                                                                               Coles County                                                                                          IL                                                                                                    69843                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40982  AAAAAAAAGBAKAAAA                                                                                      772                                                                                                   Park Maple                                                                                            Court                                                                                                 Suite J                                                                                               White Oak                                                                                             Stonewall County                                                                                      TX                                                                                                    76668                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40981  AAAAAAAAFBAKAAAA                                                                                      47                                                                                                    West 8th                                                                                              Drive                                                                                                 Suite 170                                                                                             Oakland                                                                                               Jefferson Davis Parish                                                                                LA                                                                                                    79843                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40980  AAAAAAAAEBAKAAAA                                                                                      676                                                                                                   Center                                                                                                Lane                                                                                                  Suite 360                                                                                             Bear Creek                                                                                            Johnson County                                                                                        MO                                                                                                    63075                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           

--- 20 row(s) selected.
>>--*****************************************************
>>--tests for snapshot scan without unload
>>--can be moved in the future to their own test 
>>--*****************************************************
>>--select 0
>>cqd TRAF_TABLE_SNAPSHOT_SCAN_TABLE_SIZE_THRESHOLD '0';

--- SQL operation complete.
>>
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';

--- SQL operation complete.
>>prepare ss from select  * from HBASE.CUSTOMER_DEMOGRAPHICS order by cd_demo_sk;

*** WARNING[4372] Snapshot scan cannot be used with table TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS because there are no snapshots associated with it.

--- SQL command prepared.
>>
>>--select 1
>>cqd parallel_num_esps '2';

--- SQL operation complete.
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';

--- SQL operation complete.
>>prepare snp from select  * from HBASE.CUSTOMER_DEMOGRAPHICS_SALT <<+ cardinality 10e10 >> order by cd_demo_sk;

--- SQL command prepared.
>>
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'NONE';

--- SQL operation complete.
>>prepare reg from select  * from HBASE.CUSTOMER_DEMOGRAPHICS_SALT <<+ cardinality 10e10 >> order by cd_demo_sk;

--- SQL command prepared.
>>log;
grep -i -e 'explain snp' -e snapshot -e full_table_name -e esp_exchange LOG018_SNAPSHOT_SCAN_PLAN.TXT | grep -v snapshot_scan_run_id 
>>--snapshot
>>explain snp;
  TRAF_TABLE_SNAPSHOT_SCA  0
  TRAF_TABLE_SNAPSHOT_SCA  LATEST
ESP_EXCHANGE ==============================  SEQ_NO 3        ONLY CHILD 2
  use_snapshot_scan ...... TRUE
  full_table_name ........ TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT
  snapshot_name .......... TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT_SNAP111
  snapshot_temp_location   /user/trafodion/bulkload/20170815145742/
grep -i -e 'explain reg' -e snapshot -e full_table_name  -e esp_exchange  LOG018_REGULAR_SCAN_PLAN.TXT | grep -v snapshot_scan_run_id
>>--no snapshot
>>explain reg;
  TRAF_TABLE_SNAPSHOT_SCA  0
  TRAF_TABLE_SNAPSHOT_SCA  NONE
ESP_EXCHANGE ==============================  SEQ_NO 3        ONLY CHILD 2
>>--no snapshot
>>execute reg ;

CD_DEMO_SK   CD_GENDER  CD_MARITAL_STATUS  CD_EDUCATION_STATUS   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING  CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ---------  -----------------  --------------------  --------------------  ----------------  ------------  ---------------------  --------------------

          1  M          M                  Primary                                500  Good                         0                      0                     0
          2  F          M                  Primary                                500  Good                         0                      0                     0
          3  M          S                  Primary                                500  Good                         0                      0                     0
          4  F          S                  Primary                                500  Good                         0                      0                     0
          5  M          D                  Primary                                500  Good                         0                      0                     0
          6  F          D                  Primary                                500  Good                         0                      0                     0
          7  M          W                  Primary                                500  Good                         0                      0                     0
          8  F          W                  Primary                                500  Good                         0                      0                     0
          9  M          U                  Primary                                500  Good                         0                      0                     0
         10  F          U                  Primary                                500  Good                         0                      0                     0
         11  M          M                  Secondary                              500  Good                         0                      0                     0
         12  F          M                  Secondary                              500  Good                         0                      0                     0
         13  M          S                  Secondary                              500  Good                         0                      0                     0
         14  F          S                  Secondary                              500  Good                         0                      0                     0
>>--snapshot
>>execute snp;

CD_DEMO_SK   CD_GENDER  CD_MARITAL_STATUS  CD_EDUCATION_STATUS   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING  CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ---------  -----------------  --------------------  --------------------  ----------------  ------------  ---------------------  --------------------

          1  M          M                  Primary                                500  Good                         0                      0                     0
          2  F          M                  Primary                                500  Good                         0                      0                     0
          3  M          S                  Primary                                500  Good                         0                      0                     0
          4  F          S                  Primary                                500  Good                         0                      0                     0
          5  M          D                  Primary                                500  Good                         0                      0                     0
          6  F          D                  Primary                                500  Good                         0                      0                     0
          7  M          W                  Primary                                500  Good                         0                      0                     0
          8  F          W                  Primary                                500  Good                         0                      0                     0
          9  M          U                  Primary                                500  Good                         0                      0                     0
         10  F          U                  Primary                                500  Good                         0                      0                     0
         11  M          M                  Secondary                              500  Good                         0                      0                     0
         12  F          M                  Secondary                              500  Good                         0                      0                     0
         13  M          S                  Secondary                              500  Good                         0                      0                     0
         14  F          S                  Secondary                              500  Good                         0                      0                     0
1,2c1,2
< >>--no snapshot
< >>execute reg ;
---
> >>--snapshot
> >>execute snp;
>>
>>--select 2
>>cqd parallel_num_esps reset;

--- SQL operation complete.
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';

--- SQL operation complete.
>>prepare snp from select  * from HBASE.CUSTOMER_ADDRESS <<+ cardinality 10e10 >> order by ca_address_sk;

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table TRAFODION.HBASE.CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'NONE';

--- SQL operation complete.
>>prepare reg from select  * from HBASE.CUSTOMER_ADDRESS <<+ cardinality 10e10 >> order by ca_address_sk;

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table TRAFODION.HBASE.CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>log;
grep -i -e 'explain snp' -e snapshot -e full_table_name -e esp_exchange LOG018_SNAPSHOT_SCAN_PLAN.TXT | grep -v snapshot_scan_run_id 
>>--snapshot
>>explain snp;
  TRAF_TABLE_SNAPSHOT_SCA  0
  TRAF_TABLE_SNAPSHOT_SCA  LATEST
  use_snapshot_scan ...... TRUE
  full_table_name ........ TRAFODION.HBASE.CUSTOMER_ADDRESS
  snapshot_name .......... TRAFODION.HBASE.CUSTOMER_ADDRESS_SNAP111
  snapshot_temp_location   /user/trafodion/bulkload/20170815145852/
grep -i -e 'explain reg' -e snapshot -e full_table_name  -e esp_exchange  LOG018_REGULAR_SCAN_PLAN.TXT | grep -v snapshot_scan_run_id
>>--no snapshot
>>explain reg;
  TRAF_TABLE_SNAPSHOT_SCA  0
  TRAF_TABLE_SNAPSHOT_SCA  NONE
>>--no snapshot
>>execute reg ;

CA_ADDRESS_SK  CA_ADDRESS_ID     CA_STREET_NUMBER  CA_STREET_NAME                                                CA_STREET_TYPE   CA_SUITE_NUMBER  CA_CITY                                                       CA_COUNTY                       CA_STATE  CA_ZIP      CA_COUNTRY                      CA_GMT_OFFSET  CA_LOCATION_TYPE
-------------  ----------------  ----------------  ------------------------------------------------------------  ---------------  ---------------  ------------------------------------------------------------  ------------------------------  --------  ----------  ------------------------------  -------------  --------------------

            1  AAAAAAAABAAAAAAA  18                Jackson                                                       Parkway          Suite 280        Fairfield                                                     Maricopa County                 AZ        86192       United States                           -7.00  condo               
            2  AAAAAAAACAAAAAAA  362               Washington 6th                                                RD               Suite 80         Fairview                                                      Taos County                     NM        85709       United States                           -7.00  condo               
            3  AAAAAAAADAAAAAAA  585               Dogwood Washington                                            Circle           Suite Q          Pleasant Valley                                               York County                     PA        12477       United States                           -5.00  single family       
            4  AAAAAAAAEAAAAAAA  111               Smith                                                         Wy               Suite A          Oak Ridge                                                     Kit Carson County               CO        88371       United States                           -7.00  condo               
            5  AAAAAAAAFAAAAAAA  31                College                                                       Blvd             Suite 180        Glendale                                                      Barry County                    MO        63951       United States                           -6.00  single family       
            6  AAAAAAAAGAAAAAAA  59                Williams Sixth                                                Parkway          Suite 100        Lakeview                                                      Chelan County                   WA        98579       United States                           -8.00  single family       
            7  AAAAAAAAHAAAAAAA                    Hill 7th                                                      Road             Suite U          Farmington                                                                                              39145       United States                               ?                      
            8  AAAAAAAAIAAAAAAA  875               Lincoln                                                       Ct.              Suite Y          Union                                                         Bledsoe County                  TN        38721       United States                           -5.00  apartment           
            9  AAAAAAAAJAAAAAAA  819               1st Laurel                                                    Ave              Suite 70         New Hope                                                      Perry County                    AL        39431       United States                           -6.00  condo               
           10  AAAAAAAAKAAAAAAA  851               Woodland Poplar                                               ST               Suite Y          Martinsville                                                  Haines Borough                  AK        90419       United States                           -9.00  condo               
           11  AAAAAAAALAAAAAAA  189               13th 2nd                                                      Street           Suite 470        Maple Grove                                                   Madison County                  MT        68252       United States                           -7.00  single family       
           12  AAAAAAAAMAAAAAAA  76                Ash 8th                                                       Ct.              Suite O          Edgewood                                                      Mifflin County                  PA        10069       United States                           -5.00  apartment           
           13  AAAAAAAANAAAAAAA  424               Main Second                                                   Ln               Suite 130        Greenville                                                    Noxubee County                  MS        51387       United States                           -6.00  single family       
           14  AAAAAAAAOAAAAAAA  923               Pine Oak                                                      Dr.              Suite 100                                                                      Lipscomb County                 TX        77752                                               -6.00                      
>>--snapshot
>>execute snp;

CA_ADDRESS_SK  CA_ADDRESS_ID     CA_STREET_NUMBER  CA_STREET_NAME                                                CA_STREET_TYPE   CA_SUITE_NUMBER  CA_CITY                                                       CA_COUNTY                       CA_STATE  CA_ZIP      CA_COUNTRY                      CA_GMT_OFFSET  CA_LOCATION_TYPE
-------------  ----------------  ----------------  ------------------------------------------------------------  ---------------  ---------------  ------------------------------------------------------------  ------------------------------  --------  ----------  ------------------------------  -------------  --------------------

            1  AAAAAAAABAAAAAAA  18                Jackson                                                       Parkway          Suite 280        Fairfield                                                     Maricopa County                 AZ        86192       United States                           -7.00  condo               
            2  AAAAAAAACAAAAAAA  362               Washington 6th                                                RD               Suite 80         Fairview                                                      Taos County                     NM        85709       United States                           -7.00  condo               
            3  AAAAAAAADAAAAAAA  585               Dogwood Washington                                            Circle           Suite Q          Pleasant Valley                                               York County                     PA        12477       United States                           -5.00  single family       
            4  AAAAAAAAEAAAAAAA  111               Smith                                                         Wy               Suite A          Oak Ridge                                                     Kit Carson County               CO        88371       United States                           -7.00  condo               
            5  AAAAAAAAFAAAAAAA  31                College                                                       Blvd             Suite 180        Glendale                                                      Barry County                    MO        63951       United States                           -6.00  single family       
            6  AAAAAAAAGAAAAAAA  59                Williams Sixth                                                Parkway          Suite 100        Lakeview                                                      Chelan County                   WA        98579       United States                           -8.00  single family       
            7  AAAAAAAAHAAAAAAA                    Hill 7th                                                      Road             Suite U          Farmington                                                                                              39145       United States                               ?                      
            8  AAAAAAAAIAAAAAAA  875               Lincoln                                                       Ct.              Suite Y          Union                                                         Bledsoe County                  TN        38721       United States                           -5.00  apartment           
            9  AAAAAAAAJAAAAAAA  819               1st Laurel                                                    Ave              Suite 70         New Hope                                                      Perry County                    AL        39431       United States                           -6.00  condo               
           10  AAAAAAAAKAAAAAAA  851               Woodland Poplar                                               ST               Suite Y          Martinsville                                                  Haines Borough                  AK        90419       United States                           -9.00  condo               
           11  AAAAAAAALAAAAAAA  189               13th 2nd                                                      Street           Suite 470        Maple Grove                                                   Madison County                  MT        68252       United States                           -7.00  single family       
           12  AAAAAAAAMAAAAAAA  76                Ash 8th                                                       Ct.              Suite O          Edgewood                                                      Mifflin County                  PA        10069       United States                           -5.00  apartment           
           13  AAAAAAAANAAAAAAA  424               Main Second                                                   Ln               Suite 130        Greenville                                                    Noxubee County                  MS        51387       United States                           -6.00  single family       
           14  AAAAAAAAOAAAAAAA  923               Pine Oak                                                      Dr.              Suite 100                                                                      Lipscomb County                 TX        77752                                               -6.00                      
1,2c1,2
< >>--no snapshot
< >>execute reg ;
---
> >>--snapshot
> >>execute snp;
>>--select 2
>>cqd parallel_num_esps reset;

--- SQL operation complete.
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';

--- SQL operation complete.
>>prepare snp from select * from trafodion.hbase.customer_salt c 
+>join trafodion.hbase.customer_address ca on c.c_current_addr_sk = ca.ca_address_sk
+>order by c.c_customer_sk, ca.ca_address_sk ;

*** WARNING[6008] Statistics for column (C_CUSTOMER_SK) from table TRAFODION.HBASE.CUSTOMER_SALT were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (C_CURRENT_ADDR_SK) from table TRAFODION.HBASE.CUSTOMER_SALT were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table TRAFODION.HBASE.CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'NONE';

--- SQL operation complete.
>>prepare reg from select * from trafodion.hbase.customer_salt c 
+>join trafodion.hbase.customer_address ca on c.c_current_addr_sk = ca.ca_address_sk
+>order by c.c_customer_sk, ca.ca_address_sk ;

*** WARNING[6008] Statistics for column (C_CUSTOMER_SK) from table TRAFODION.HBASE.CUSTOMER_SALT were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (C_CURRENT_ADDR_SK) from table TRAFODION.HBASE.CUSTOMER_SALT were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table TRAFODION.HBASE.CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>log;
grep -i -e 'explain snp' -e snapshot -e full_table_name -e esp_exchange LOG018_SNAPSHOT_SCAN_PLAN.TXT | grep -v snapshot_scan_run_id 
>>--snapshot
>>explain snp;
  TRAF_TABLE_SNAPSHOT_SCA  0
  TRAF_TABLE_SNAPSHOT_SCA  LATEST
ESP_EXCHANGE ==============================  SEQ_NO 6        ONLY CHILD 5
  use_snapshot_scan ...... TRUE
  full_table_name ........ TRAFODION.HBASE.CUSTOMER_SALT
  snapshot_name .......... TRAFODION.HBASE.CUSTOMER_SALT_SNAP111
  snapshot_temp_location   /user/trafodion/bulkload/20170815145917/
ESP_EXCHANGE ==============================  SEQ_NO 2        ONLY CHILD 1
  use_snapshot_scan ...... TRUE
  full_table_name ........ TRAFODION.HBASE.CUSTOMER_ADDRESS
  snapshot_name .......... TRAFODION.HBASE.CUSTOMER_ADDRESS_SNAP111
  snapshot_temp_location   /user/trafodion/bulkload/20170815145917/
grep -i -e 'explain reg' -e snapshot -e full_table_name  -e esp_exchange  LOG018_REGULAR_SCAN_PLAN.TXT | grep -v snapshot_scan_run_id
>>--no snapshot
>>explain reg;
  TRAF_TABLE_SNAPSHOT_SCA  0
  TRAF_TABLE_SNAPSHOT_SCA  NONE
ESP_EXCHANGE ==============================  SEQ_NO 6        ONLY CHILD 5
ESP_EXCHANGE ==============================  SEQ_NO 2        ONLY CHILD 1
>>--no snapshot
>>execute reg ;

C_CUSTOMER_SK  C_CUSTOMER_ID     C_CURRENT_CDEMO_SK  C_CURRENT_HDEMO_SK  C_CURRENT_ADDR_SK  C_FIRST_SHIPTO_DATE_SK  C_FIRST_SALES_DATE_SK  C_SALUTATION                              C_FIRST_NAME                                                                      C_LAST_NAME                                                                                                               C_PREFERRED_CUST_FLAG  C_BIRTH_DAY  C_BIRTH_MONTH  C_BIRTH_YEAR  C_BIRTH_COUNTRY                                                                   C_LOGIN                                               C_EMAIL_ADDRESS                                                                                                                                                                                           C_LAST_REVIEW_DATE                                  CA_ADDRESS_SK  CA_ADDRESS_ID     CA_STREET_NUMBER  CA_STREET_NAME                                                CA_STREET_TYPE   CA_SUITE_NUMBER  CA_CITY                                                       CA_COUNTY                       CA_STATE  CA_ZIP      CA_COUNTRY                      CA_GMT_OFFSET  CA_LOCATION_TYPE
-------------  ----------------  ------------------  ------------------  -----------------  ----------------------  ---------------------  ----------------------------------------  --------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------  ---------------------  -----------  -------------  ------------  --------------------------------------------------------------------------------  ----------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------  -------------  ----------------  ----------------  ------------------------------------------------------------  ---------------  ---------------  ------------------------------------------------------------  ------------------------------  --------  ----------  ------------------------------  -------------  --------------------

            1  AAAAAAAABAAAAAAA              980124                7135              32946                 2452238                2452208  Mr.                                       Javier                                                                            Lewis                                                                                                                     Y                                9             12          1936  CHILE                                                                                                                                   Javier.Lewis@VFAxlnZEvOx.org                                                                                                                                                                              2452508                                                     32946  AAAAAAAACLAIAAAA  325               Chestnut Main                                                 Ln               Suite I          Spring Hill                                                   Leflore County                  MS        56787       United States                           -6.00  apartment           
            2  AAAAAAAACAAAAAAA              819667                1461              31655                 2452318                2452288  Dr.                                       Amy                                                                               Moses                                                                                                                     Y                                9              4          1966  TOGO                                                                                                                                    Amy.Moses@Ovk9KjHH.com                                                                                                                                                                                    2452318                                                     31655  AAAAAAAAHKLHAAAA  614               8th Oak                                                       Boulevard        Suite 150        Antioch                                                       Gogebic County                  MI        48605       United States                           -5.00  condo               
            3  AAAAAAAADAAAAAAA             1473522                6247              48572                 2449130                2449100  Miss                                      Latisha                                                                           Hamilton                                                                                                                  N                               18              9          1979  NIUE                                                                                                                                    Latisha.Hamilton@V.com                                                                                                                                                                                    2452313                                                     48572  AAAAAAAAMLNLAAAA  649               Park Jefferson                                                Ave              Suite 300        Cedar Grove                                                   Cass County                     MO        60411       United States                           -6.00  single family       
            4  AAAAAAAAEAAAAAAA             1703214                3986              39558                 2450030                2450000  Dr.                                       Michael                                                                           White                                                                                                                     N                                7              6          1983  MEXICO                                                                                                                                  Michael.White@i.org                                                                                                                                                                                       2452361                                                     39558  AAAAAAAAGIKJAAAA  977               Cedar Sycamore                                                Road             Suite 230        Lakewood                                                      Renville County                 MN        58877       United States                           -6.00  single family       
            5  AAAAAAAAFAAAAAAA              953372                4470              36368                 2449438                2449408  Sir                                       Robert                                                                            Moran                                                                                                                     N                                8              5          1956  FIJI                                                                                                                                    Robert.Moran@Hh.edu                                                                                                                                                                                       2452469                                                     36368  AAAAAAAAABOIAAAA  604               Miller Main                                                   Blvd             Suite P          Waterloo                                                      Big Horn County                 WY        81675       United States                           -7.00  apartment           
            6  AAAAAAAAGAAAAAAA              213219                6374              27082                 2451883                2451853  Ms.                                       Brunilda                                                                          Sharp                                                                                                                     N                                4             12          1925  SURINAME                                                                                                                                Brunilda.Sharp@T3pylZEUQjm.org                                                                                                                                                                            2452430                                                     27082  AAAAAAAAKMJGAAAA  248               Jackson                                                       Cir.             Suite X          Shiloh                                                        Reno County                     KS        69275       United States                           -6.00  condo               
            7  AAAAAAAAHAAAAAAA               68377                3219              44814                 2451438                2451408  Ms.                                       Fonda                                                                             Wiles                                                                                                                     Y                               24              4          1985  GAMBIA                                                                                                                                  Fonda.Wiles@S9KnyEtz9hv.org                                                                                                                                                                               2452360                                                     44814  AAAAAAAAOAPKAAAA  704               Cedar Hickory                                                 Road             Suite V          Bethel                                                        Reynolds County                 MO        65281       United States                           -6.00  apartment           
            8  AAAAAAAAIAAAAAAA             1215897                2471              16598                 2449406                2449376  Sir                                       Ollie                                                                             Shipman                                                                                                                   N                               26             12          1938  KOREA, REPUBLIC OF                                                                                                                      Ollie.Shipman@be.org                                                                                                                                                                                      2452334                                                     16598  AAAAAAAAGNAEAAAA  463               10th                                                          Cir.             Suite 180        Plainview                                                     Grenada County                  MS        53683       United States                           -6.00  condo               
            9  AAAAAAAAJAAAAAAA             1168667                1404              49388                 2452275                2452245  Sir                                       Karl                                                                              Gilbert                                                                                                                   N                               26             10          1966  MONTSERRAT                                                                                                                              Karl.Gilbert@Crg5KyP2IxX9C4d6.edu                                                                                                                                                                         2452454                                                     49388  AAAAAAAAMOAMAAAA  416               First Tenth                                                   Pkwy             Suite K          Fairview                                                      Clinton County                  IA        55709       United States                           -6.00  apartment           
           10  AAAAAAAAKAAAAAAA             1207553                5143              19580                 2451353                2451323  Ms.                                       Albert                                                                            Brunson                                                                                                                   N                               15             10          1973  JORDAN                                                                                                                                  Albert.Brunson@62.com                                                                                                                                                                                     2452641                                                     19580  AAAAAAAAMHMEAAAA  870               Laurel Laurel                                                 Road             Suite Y          Centerville                                                   Hot Spring County               AR        70059       United States                           -6.00  condo               
           11  AAAAAAAALAAAAAAA             1114415                6807              47999                 2452288                2452258  Ms.                                       Betty                                                                             Williams                                                                                                                  N                               18             12          1963  BURKINA FASO                                                                                                                            Betty.Williams@xRtDqM1eLBVQNoYAJ.com                                                                                                                                                                      2452398                                                     47999  AAAAAAAAPHLLAAAA  267               13th                                                          Ave                                                                                                                                       54098                                                   ?  apartment           
           12  AAAAAAAAMAAAAAAA              502141                6577              47366                 2451039                2451009  Ms.                                       Margaret                                                                          Farias                                                                                                                    N                                2              6          1956  TURKMENISTAN                                                                                                                            Margaret.Farias@cb.edu                                                                                                                                                                                    2452634                                                     47366  AAAAAAAAGAJLAAAA  943               Adams                                                         Lane             Suite 170        Hartland                                                      Scott County                    IA        56594       United States                           -6.00  single family       
           13  AAAAAAAANAAAAAAA             1128748                2777              14006                 2449658                2449628  Mrs.                                      Rosalinda                                                                         Grimes                                                                                                                    N                                1              3          1970  UKRAINE                                                                                                                                 Rosalinda.Grimes@tC8pcU7Lt.edu                                                                                                                                                                            2452616                                                     14006  AAAAAAAAGLGDAAAA  450               Dogwood 9th                                                   Ln               Suite 380        Glenwood                                                      Poinsett County                 AR        73511       United States                           -6.00  single family       
           14  AAAAAAAAOAAAAAAA              929344                 892               6440                 2450318                2450288  Mr.                                       Jack                                                                              Wilcox                                                                                                                    N                               30              3          1937  SLOVENIA                                                                                                                                Jack.Wilcox@Y3Etqyv3.org                                                                                                                                                                                  2452641                                                      6440  AAAAAAAAICJBAAAA                    Valley                                                                                          Mountain View                                                 Gilchrist County                FL                                                                ?  condo               
>>--snapshot
>>execute snp;

C_CUSTOMER_SK  C_CUSTOMER_ID     C_CURRENT_CDEMO_SK  C_CURRENT_HDEMO_SK  C_CURRENT_ADDR_SK  C_FIRST_SHIPTO_DATE_SK  C_FIRST_SALES_DATE_SK  C_SALUTATION                              C_FIRST_NAME                                                                      C_LAST_NAME                                                                                                               C_PREFERRED_CUST_FLAG  C_BIRTH_DAY  C_BIRTH_MONTH  C_BIRTH_YEAR  C_BIRTH_COUNTRY                                                                   C_LOGIN                                               C_EMAIL_ADDRESS                                                                                                                                                                                           C_LAST_REVIEW_DATE                                  CA_ADDRESS_SK  CA_ADDRESS_ID     CA_STREET_NUMBER  CA_STREET_NAME                                                CA_STREET_TYPE   CA_SUITE_NUMBER  CA_CITY                                                       CA_COUNTY                       CA_STATE  CA_ZIP      CA_COUNTRY                      CA_GMT_OFFSET  CA_LOCATION_TYPE
-------------  ----------------  ------------------  ------------------  -----------------  ----------------------  ---------------------  ----------------------------------------  --------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------  ---------------------  -----------  -------------  ------------  --------------------------------------------------------------------------------  ----------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------  -------------  ----------------  ----------------  ------------------------------------------------------------  ---------------  ---------------  ------------------------------------------------------------  ------------------------------  --------  ----------  ------------------------------  -------------  --------------------

            1  AAAAAAAABAAAAAAA              980124                7135              32946                 2452238                2452208  Mr.                                       Javier                                                                            Lewis                                                                                                                     Y                                9             12          1936  CHILE                                                                                                                                   Javier.Lewis@VFAxlnZEvOx.org                                                                                                                                                                              2452508                                                     32946  AAAAAAAACLAIAAAA  325               Chestnut Main                                                 Ln               Suite I          Spring Hill                                                   Leflore County                  MS        56787       United States                           -6.00  apartment           
            2  AAAAAAAACAAAAAAA              819667                1461              31655                 2452318                2452288  Dr.                                       Amy                                                                               Moses                                                                                                                     Y                                9              4          1966  TOGO                                                                                                                                    Amy.Moses@Ovk9KjHH.com                                                                                                                                                                                    2452318                                                     31655  AAAAAAAAHKLHAAAA  614               8th Oak                                                       Boulevard        Suite 150        Antioch                                                       Gogebic County                  MI        48605       United States                           -5.00  condo               
            3  AAAAAAAADAAAAAAA             1473522                6247              48572                 2449130                2449100  Miss                                      Latisha                                                                           Hamilton                                                                                                                  N                               18              9          1979  NIUE                                                                                                                                    Latisha.Hamilton@V.com                                                                                                                                                                                    2452313                                                     48572  AAAAAAAAMLNLAAAA  649               Park Jefferson                                                Ave              Suite 300        Cedar Grove                                                   Cass County                     MO        60411       United States                           -6.00  single family       
            4  AAAAAAAAEAAAAAAA             1703214                3986              39558                 2450030                2450000  Dr.                                       Michael                                                                           White                                                                                                                     N                                7              6          1983  MEXICO                                                                                                                                  Michael.White@i.org                                                                                                                                                                                       2452361                                                     39558  AAAAAAAAGIKJAAAA  977               Cedar Sycamore                                                Road             Suite 230        Lakewood                                                      Renville County                 MN        58877       United States                           -6.00  single family       
            5  AAAAAAAAFAAAAAAA              953372                4470              36368                 2449438                2449408  Sir                                       Robert                                                                            Moran                                                                                                                     N                                8              5          1956  FIJI                                                                                                                                    Robert.Moran@Hh.edu                                                                                                                                                                                       2452469                                                     36368  AAAAAAAAABOIAAAA  604               Miller Main                                                   Blvd             Suite P          Waterloo                                                      Big Horn County                 WY        81675       United States                           -7.00  apartment           
            6  AAAAAAAAGAAAAAAA              213219                6374              27082                 2451883                2451853  Ms.                                       Brunilda                                                                          Sharp                                                                                                                     N                                4             12          1925  SURINAME                                                                                                                                Brunilda.Sharp@T3pylZEUQjm.org                                                                                                                                                                            2452430                                                     27082  AAAAAAAAKMJGAAAA  248               Jackson                                                       Cir.             Suite X          Shiloh                                                        Reno County                     KS        69275       United States                           -6.00  condo               
            7  AAAAAAAAHAAAAAAA               68377                3219              44814                 2451438                2451408  Ms.                                       Fonda                                                                             Wiles                                                                                                                     Y                               24              4          1985  GAMBIA                                                                                                                                  Fonda.Wiles@S9KnyEtz9hv.org                                                                                                                                                                               2452360                                                     44814  AAAAAAAAOAPKAAAA  704               Cedar Hickory                                                 Road             Suite V          Bethel                                                        Reynolds County                 MO        65281       United States                           -6.00  apartment           
            8  AAAAAAAAIAAAAAAA             1215897                2471              16598                 2449406                2449376  Sir                                       Ollie                                                                             Shipman                                                                                                                   N                               26             12          1938  KOREA, REPUBLIC OF                                                                                                                      Ollie.Shipman@be.org                                                                                                                                                                                      2452334                                                     16598  AAAAAAAAGNAEAAAA  463               10th                                                          Cir.             Suite 180        Plainview                                                     Grenada County                  MS        53683       United States                           -6.00  condo               
            9  AAAAAAAAJAAAAAAA             1168667                1404              49388                 2452275                2452245  Sir                                       Karl                                                                              Gilbert                                                                                                                   N                               26             10          1966  MONTSERRAT                                                                                                                              Karl.Gilbert@Crg5KyP2IxX9C4d6.edu                                                                                                                                                                         2452454                                                     49388  AAAAAAAAMOAMAAAA  416               First Tenth                                                   Pkwy             Suite K          Fairview                                                      Clinton County                  IA        55709       United States                           -6.00  apartment           
           10  AAAAAAAAKAAAAAAA             1207553                5143              19580                 2451353                2451323  Ms.                                       Albert                                                                            Brunson                                                                                                                   N                               15             10          1973  JORDAN                                                                                                                                  Albert.Brunson@62.com                                                                                                                                                                                     2452641                                                     19580  AAAAAAAAMHMEAAAA  870               Laurel Laurel                                                 Road             Suite Y          Centerville                                                   Hot Spring County               AR        70059       United States                           -6.00  condo               
           11  AAAAAAAALAAAAAAA             1114415                6807              47999                 2452288                2452258  Ms.                                       Betty                                                                             Williams                                                                                                                  N                               18             12          1963  BURKINA FASO                                                                                                                            Betty.Williams@xRtDqM1eLBVQNoYAJ.com                                                                                                                                                                      2452398                                                     47999  AAAAAAAAPHLLAAAA  267               13th                                                          Ave                                                                                                                                       54098                                                   ?  apartment           
           12  AAAAAAAAMAAAAAAA              502141                6577              47366                 2451039                2451009  Ms.                                       Margaret                                                                          Farias                                                                                                                    N                                2              6          1956  TURKMENISTAN                                                                                                                            Margaret.Farias@cb.edu                                                                                                                                                                                    2452634                                                     47366  AAAAAAAAGAJLAAAA  943               Adams                                                         Lane             Suite 170        Hartland                                                      Scott County                    IA        56594       United States                           -6.00  single family       
           13  AAAAAAAANAAAAAAA             1128748                2777              14006                 2449658                2449628  Mrs.                                      Rosalinda                                                                         Grimes                                                                                                                    N                                1              3          1970  UKRAINE                                                                                                                                 Rosalinda.Grimes@tC8pcU7Lt.edu                                                                                                                                                                            2452616                                                     14006  AAAAAAAAGLGDAAAA  450               Dogwood 9th                                                   Ln               Suite 380        Glenwood                                                      Poinsett County                 AR        73511       United States                           -6.00  single family       
           14  AAAAAAAAOAAAAAAA              929344                 892               6440                 2450318                2450288  Mr.                                       Jack                                                                              Wilcox                                                                                                                    N                               30              3          1937  SLOVENIA                                                                                                                                Jack.Wilcox@Y3Etqyv3.org                                                                                                                                                                                  2452641                                                      6440  AAAAAAAAICJBAAAA                    Valley                                                                                          Mountain View                                                 Gilchrist County                FL                                                                ?  condo               
1,2c1,2
< >>--no snapshot
< >>execute reg ;
---
> >>--snapshot
> >>execute snp;
>>
>>--select 3
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';

--- SQL operation complete.
>>
>>prepare ss from select  c_first_name, c_last_name from HBASE.customer_salt order by c_first_name, c_last_name;

*** WARNING[6008] Statistics for column (C_CUSTOMER_SK) from table TRAFODION.HBASE.CUSTOMER_SALT were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[4372] Snapshot scan cannot be used with table TRAFODION.HBASE.CUSTOMER_IDX1 because snapshot scan is not supported with index tables yet.

--- SQL command prepared.
>>
>>cqd TRAF_TABLE_SNAPSHOT_SCAN_TABLE_SIZE_THRESHOLD '1000';

--- SQL operation complete.
>>cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';

--- SQL operation complete.
>>prepare ss from select  * from trafodion.hbase.customer_address;

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table TRAFODION.HBASE.CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[4372] Snapshot scan cannot be used with table TRAFODION.HBASE.CUSTOMER_ADDRESS because its estimated size is less than the threshold of 1000 MBs.

--- SQL command prepared.
>>cqd TRAF_TABLE_SNAPSHOT_SCAN_TABLE_SIZE_THRESHOLD '0';

--- SQL operation complete.
>>
>>
>>
>>--******************************************************************************
>>--*********************BULK UNLOAD with SNAPSHOT SCAN
>>--unload 20
>>
>>cqd comp_bool_226 'on';

--- SQL operation complete.
>>  -- allow the extract syntax
>>explain options 'f' 
+>UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_address'
+>select * from trafodion.hbase.customer_address <<+ cardinality 10e10 >>;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+011
1    .    3    unload                                                1.00E+011
.    .    1    trafodion_scan                  CUSTOMER_ADDRESS      1.00E+011

--- SQL operation complete.
>>cqd comp_bool_226 reset;

--- SQL operation complete.
>>
>>UNLOAD
+>WITH PURGEDATA FROM TARGET
+> EXISTING SNAPSHOT HAVING SUFFIX 'SNAP111' 
+>INTO '/user/trafodion/hive/exttables/unload_customer_address'
+>select * from customer_address
+><<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 15:02:14.544
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 15:02:14.550
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.006
Task:  VERIFY SNAPSHO  Status: Started    Time: 2017-08-15 15:02:14.550
       Snapshots verified: 1 
Task:  VERIFY SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:15.334
Task:  VERIFY SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.784
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:02:15.334
       Rows Processed: 50000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:02:16.637
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:01.302

--- 50000 row(s) unloaded.
>>
>>select count(*) from hive.hive.unload_customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_address  where ca_address_sk < 100 order by ca_address_sk;

*** WARNING[6008] Statistics for column (CA_ADDRESS_SK) from table HIVE.HIVE.UNLOAD_CUSTOMER_ADDRESS were not available. As a result, the access path chosen might not be the best possible.

CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

            1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            2  AAAAAAAACAAAAAAA                                                                                      362                                                                                                   Washington 6th                                                                                        RD                                                                                                    Suite 80                                                                                              Fairview                                                                                              Taos County                                                                                           NM                                                                                                    85709                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
            4  AAAAAAAAEAAAAAAA                                                                                      111                                                                                                   Smith                                                                                                 Wy                                                                                                    Suite A                                                                                               Oak Ridge                                                                                             Kit Carson County                                                                                     CO                                                                                                    88371                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
            6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
            7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      
            8  AAAAAAAAIAAAAAAA                                                                                      875                                                                                                   Lincoln                                                                                               Ct.                                                                                                   Suite Y                                                                                               Union                                                                                                 Bledsoe County                                                                                        TN                                                                                                    38721                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
            9  AAAAAAAAJAAAAAAA                                                                                      819                                                                                                   1st Laurel                                                                                            Ave                                                                                                   Suite 70                                                                                              New Hope                                                                                              Perry County                                                                                          AL                                                                                                    39431                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           10  AAAAAAAAKAAAAAAA                                                                                      851                                                                                                   Woodland Poplar                                                                                       ST                                                                                                    Suite Y                                                                                               Martinsville                                                                                          Haines Borough                                                                                        AK                                                                                                    90419                                                                                                 United States                                                                                         -9.0000000E+000  condo                                                                                               
           11  AAAAAAAALAAAAAAA                                                                                      189                                                                                                   13th 2nd                                                                                              Street                                                                                                Suite 470                                                                                             Maple Grove                                                                                           Madison County                                                                                        MT                                                                                                    68252                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
           12  AAAAAAAAMAAAAAAA                                                                                      76                                                                                                    Ash 8th                                                                                               Ct.                                                                                                   Suite O                                                                                               Edgewood                                                                                              Mifflin County                                                                                        PA                                                                                                    10069                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           13  AAAAAAAANAAAAAAA                                                                                      424                                                                                                   Main Second                                                                                           Ln                                                                                                    Suite 130                                                                                             Greenville                                                                                            Noxubee County                                                                                        MS                                                                                                    51387                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
           14  AAAAAAAAOAAAAAAA                                                                                      923                                                                                                   Pine Oak                                                                                              Dr.                                                                                                   Suite 100                                                                                                                                                                                                   Lipscomb County                                                                                       TX                                                                                                    77752                                                                                                                                                                                                       -6.0000000E+000                                                                                                      
           15  AAAAAAAAPAAAAAAA                                                                                      314                                                                                                   Spring                                                                                                Ct.                                                                                                   Suite B                                                                                               Oakland                                                                                               Washington County                                                                                     OH                                                                                                    49843                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           16  AAAAAAAAABAAAAAA                                                                                      576                                                                                                   Adams Center                                                                                          Street                                                                                                Suite J                                                                                               Valley View                                                                                           Oldham County                                                                                         TX                                                                                                    75124                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           17  AAAAAAAABBAAAAAA                                                                                      801                                                                                                   Green                                                                                                 Dr.                                                                                                   Suite 0                                                                                               Montpelier                                                                                            Richland County                                                                                       OH                                                                                                    48930                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
           18  AAAAAAAACBAAAAAA                                                                                      460                                                                                                   Maple Spruce                                                                                          Court                                                                                                 Suite 480                                                                                             Somerville                                                                                            Potter County                                                                                         SD                                                                                                    57783                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
           19  AAAAAAAADBAAAAAA                                                                                      611                                                                                                   Wilson                                                                                                Way                                                                                                   Suite O                                                                                               Oakdale                                                                                               Tangipahoa Parish                                                                                     LA                                                                                                    79584                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           
           20  AAAAAAAAEBAAAAAA                                                                                      675                                                                                                   Elm Wilson                                                                                            Street                                                                                                Suite I                                                                                               Hopewell                                                                                              Williams County                                                                                       OH                                                                                                    40587                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               

--- 20 row(s) selected.
>>
>>--unload 21
>>
>>cqd comp_bool_226 'on';

--- SQL operation complete.
>>  -- allow the extract syntax
>>explain options 'f' 
+>UNLOAD EXTRACT TO '/user/trafodion/hive/exttables/unload_customer_demographics'
+>select * from trafodion.hbase.customer_demographics_salt <<+ cardinality 10e10 >>;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

4    .    5    root                                                  1.00E+011
3    .    4    esp_exchange                    1:4(hash2)            1.00E+011
1    .    3    unload                                                1.00E+011
.    .    1    trafodion_scan                  CUSTOMER_DEMOGRAPHIC  1.00E+011

--- SQL operation complete.
>>cqd comp_bool_226 reset;

--- SQL operation complete.
>>
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+> EXISTING SNAPSHOT HAVING SUFFIX 'SNAP111'
+>INTO '/user/trafodion/hive/exttables/unload_customer_demographics'
+>select * from trafodion.hbase.customer_demographics_salt <<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 15:02:20.874
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 15:02:20.878
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.003
Task:  VERIFY SNAPSHO  Status: Started    Time: 2017-08-15 15:02:20.878
       Snapshots verified: 1 
Task:  VERIFY SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:21.318
Task:  VERIFY SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.440
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:02:21.318
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:02:30.925
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:08.774

--- 20000 row(s) unloaded.
>>
>>select count(*) from hive.hive.unload_customer_demographics;

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

CD_DEMO_SK   CD_GENDER                                                                                             CD_MARITAL_STATUS                                                                                     CD_EDUCATION_STATUS                                                                                   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING                                                                                      CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------  ----------------------------------------------------------------------------------------------------  ------------  ---------------------  --------------------

          1  M                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          2  F                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          3  M                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          4  F                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          5  M                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          6  F                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          7  M                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          8  F                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          9  M                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         10  F                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         11  M                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         12  F                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         13  M                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         14  F                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         15  M                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         16  F                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         17  M                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         18  F                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         19  M                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         20  F                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0

--- 20 row(s) selected.
>>
>>--unload 22
>>UNLOAD
+>WITH PURGEDATA FROM TARGET
+> NEW SNAPSHOT HAVING  SUFFIX 'SNAP112' 
+>INTO '/user/trafodion/hive/exttables/unload_customer_demographics'
+>select * from trafodion.hbase.customer_demographics_salt <<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 15:02:32.124
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 15:02:32.137
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.014
Task:  CREATE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:32.138
       Snapshots created: 1 
Task:  CREATE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:33.364
Task:  CREATE SNAPSHO  Status: Ended      Elapsed Time:    00:00:01.227
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:02:33.364
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:02:34.688
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:01.323
Task:  DELETE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:34.688
       Snapshots deleted: 1 
Task:  DELETE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:34.720
Task:  DELETE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.032

--- 20000 row(s) unloaded.
>>
>>select count(*) from hive.hive.unload_customer_demographics;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1502809349, failedModTS = 1502809353, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/unload_customer_demographics

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

CD_DEMO_SK   CD_GENDER                                                                                             CD_MARITAL_STATUS                                                                                     CD_EDUCATION_STATUS                                                                                   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING                                                                                      CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------  ----------------------------------------------------------------------------------------------------  ------------  ---------------------  --------------------

          1  M                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          2  F                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          3  M                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          4  F                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          5  M                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          6  F                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          7  M                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          8  F                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          9  M                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         10  F                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         11  M                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         12  F                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         13  M                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         14  F                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         15  M                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         16  F                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         17  M                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         18  F                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         19  M                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         20  F                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0

--- 20 row(s) selected.
>>
>>--unload 23
>>UNLOAD
+>WITH PURGEDATA FROM TARGET
+> NEW SNAPSHOT HAVING  SUFFIX   'SNAP'  
+>INTO '/user/trafodion/hive/exttables/unload_customer_demographics'
+>select * from trafodion.hbase.customer_demographics_salt <<+ cardinality 10e10 >>;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 15:02:37.723
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 15:02:37.733
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.010
Task:  CREATE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:37.733
       Snapshots created: 1 
Task:  CREATE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:38.443
Task:  CREATE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.710
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:02:38.443
       Rows Processed: 20000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:02:40.918
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:01.648
Task:  DELETE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:40.919
       Snapshots deleted: 1 
Task:  DELETE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:40.989
Task:  DELETE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.007

--- 20000 row(s) unloaded.
>>
>>select count(*) from hive.hive.unload_customer_demographics;

(EXPR)              
--------------------

               20000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1502809354, failedModTS = 1502809358, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/unload_customer_demographics

CD_DEMO_SK   CD_GENDER                                                                                             CD_MARITAL_STATUS                                                                                     CD_EDUCATION_STATUS                                                                                   CD_PURCHASE_ESTIMATE  CD_CREDIT_RATING                                                                                      CD_DEP_COUNT  CD_DEP_EMPLOYED_COUNT  CD_DEP_COLLEGE_COUNT
-----------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------  ----------------------------------------------------------------------------------------------------  ------------  ---------------------  --------------------

          1  M                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          2  F                                                                                                     M                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          3  M                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          4  F                                                                                                     S                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          5  M                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          6  F                                                                                                     D                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          7  M                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          8  F                                                                                                     W                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
          9  M                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         10  F                                                                                                     U                                                                                                     Primary                                                                                                                500  Good                                                                                                             0                      0                     0
         11  M                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         12  F                                                                                                     M                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         13  M                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         14  F                                                                                                     S                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         15  M                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         16  F                                                                                                     D                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         17  M                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         18  F                                                                                                     W                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         19  M                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0
         20  F                                                                                                     U                                                                                                     Secondary                                                                                                              500  Good                                                                                                             0                      0                     0

--- 20 row(s) selected.
>>
>>--unload 24
>>UNLOAD  
+>WITH 
+> PURGEDATA FROM TARGET 
+> NEW SNAPSHOT HAVING  SUFFIX 'SNAP'
+>INTO '/user/trafodion/hive/exttables/unload_customer_address'
+>select * from customer_address where ca_address_sk < 1000 union select * from customer_address where ca_address_sk > 40000  and ca_address_sk < 41000;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 15:02:43.246
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 15:02:43.252
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.006
Task:  CREATE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:43.252
       Snapshots created: 1 
Task:  CREATE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:44.110
Task:  CREATE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.858
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:02:44.110
       Rows Processed: 1998 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:02:44.980
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:00.870
Task:  DELETE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:44.980
       Snapshots deleted: 1 
Task:  DELETE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:44.986
Task:  DELETE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.006

--- 1998 row(s) unloaded.
>>
>>select count(*) from hive.hive.unload_customer_address;

(EXPR)              
--------------------

                1998

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk;

CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

            1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            2  AAAAAAAACAAAAAAA                                                                                      362                                                                                                   Washington 6th                                                                                        RD                                                                                                    Suite 80                                                                                              Fairview                                                                                              Taos County                                                                                           NM                                                                                                    85709                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
            4  AAAAAAAAEAAAAAAA                                                                                      111                                                                                                   Smith                                                                                                 Wy                                                                                                    Suite A                                                                                               Oak Ridge                                                                                             Kit Carson County                                                                                     CO                                                                                                    88371                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
            5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
            6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
            7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      
            8  AAAAAAAAIAAAAAAA                                                                                      875                                                                                                   Lincoln                                                                                               Ct.                                                                                                   Suite Y                                                                                               Union                                                                                                 Bledsoe County                                                                                        TN                                                                                                    38721                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
            9  AAAAAAAAJAAAAAAA                                                                                      819                                                                                                   1st Laurel                                                                                            Ave                                                                                                   Suite 70                                                                                              New Hope                                                                                              Perry County                                                                                          AL                                                                                                    39431                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           10  AAAAAAAAKAAAAAAA                                                                                      851                                                                                                   Woodland Poplar                                                                                       ST                                                                                                    Suite Y                                                                                               Martinsville                                                                                          Haines Borough                                                                                        AK                                                                                                    90419                                                                                                 United States                                                                                         -9.0000000E+000  condo                                                                                               
           11  AAAAAAAALAAAAAAA                                                                                      189                                                                                                   13th 2nd                                                                                              Street                                                                                                Suite 470                                                                                             Maple Grove                                                                                           Madison County                                                                                        MT                                                                                                    68252                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
           12  AAAAAAAAMAAAAAAA                                                                                      76                                                                                                    Ash 8th                                                                                               Ct.                                                                                                   Suite O                                                                                               Edgewood                                                                                              Mifflin County                                                                                        PA                                                                                                    10069                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           13  AAAAAAAANAAAAAAA                                                                                      424                                                                                                   Main Second                                                                                           Ln                                                                                                    Suite 130                                                                                             Greenville                                                                                            Noxubee County                                                                                        MS                                                                                                    51387                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
           14  AAAAAAAAOAAAAAAA                                                                                      923                                                                                                   Pine Oak                                                                                              Dr.                                                                                                   Suite 100                                                                                                                                                                                                   Lipscomb County                                                                                       TX                                                                                                    77752                                                                                                                                                                                                       -6.0000000E+000                                                                                                      
           15  AAAAAAAAPAAAAAAA                                                                                      314                                                                                                   Spring                                                                                                Ct.                                                                                                   Suite B                                                                                               Oakland                                                                                               Washington County                                                                                     OH                                                                                                    49843                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
           16  AAAAAAAAABAAAAAA                                                                                      576                                                                                                   Adams Center                                                                                          Street                                                                                                Suite J                                                                                               Valley View                                                                                           Oldham County                                                                                         TX                                                                                                    75124                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
           17  AAAAAAAABBAAAAAA                                                                                      801                                                                                                   Green                                                                                                 Dr.                                                                                                   Suite 0                                                                                               Montpelier                                                                                            Richland County                                                                                       OH                                                                                                    48930                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
           18  AAAAAAAACBAAAAAA                                                                                      460                                                                                                   Maple Spruce                                                                                          Court                                                                                                 Suite 480                                                                                             Somerville                                                                                            Potter County                                                                                         SD                                                                                                    57783                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
           19  AAAAAAAADBAAAAAA                                                                                      611                                                                                                   Wilson                                                                                                Way                                                                                                   Suite O                                                                                               Oakdale                                                                                               Tangipahoa Parish                                                                                     LA                                                                                                    79584                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           
           20  AAAAAAAAEBAAAAAA                                                                                      675                                                                                                   Elm Wilson                                                                                            Street                                                                                                Suite I                                                                                               Hopewell                                                                                              Williams County                                                                                       OH                                                                                                    40587                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               

--- 20 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk desc;

CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

        40999  AAAAAAAAHCAKAAAA                                                                                      491                                                                                                   Willow Hickory                                                                                        Road                                                                                                  Suite 100                                                                                             Five Forks                                                                                            Putnam County                                                                                         NY                                                                                                    12293                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
        40998  AAAAAAAAGCAKAAAA                                                                                      481                                                                                                   10th                                                                                                  Blvd                                                                                                  Suite 230                                                                                             Oakwood                                                                                               Pulaski County                                                                                        MO                                                                                                    60169                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40997  AAAAAAAAFCAKAAAA                                                                                      8                                                                                                     Jackson Cedar                                                                                         Court                                                                                                 Suite 50                                                                                              Highland Park                                                                                         Hinds County                                                                                          MS                                                                                                    56534                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40996  AAAAAAAAECAKAAAA                                                                                      616                                                                                                   Park First                                                                                            Lane                                                                                                  Suite 20                                                                                              Rossville                                                                                             Sunflower County                                                                                      MS                                                                                                    52503                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           
        40995  AAAAAAAADCAKAAAA                                                                                      242                                                                                                   Seventh                                                                                               Circle                                                                                                Suite 260                                                                                             Newport                                                                                               Clark County                                                                                          OH                                                                                                    41521                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               
        40994  AAAAAAAACCAKAAAA                                                                                      262                                                                                                   Elm Lincoln                                                                                           Court                                                                                                 Suite D                                                                                               Mount Olive                                                                                           Asotin County                                                                                         WA                                                                                                    98059                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        40993  AAAAAAAABCAKAAAA                                                                                      2                                                                                                     East                                                                                                  Parkway                                                                                               Suite 190                                                                                             Greenville                                                                                            Bay County                                                                                            FL                                                                                                    31387                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        40992  AAAAAAAAACAKAAAA                                                                                      978                                                                                                   11th River                                                                                            Cir.                                                                                                  Suite 330                                                                                             Providence                                                                                            Waseca County                                                                                         MN                                                                                                    56614                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40991  AAAAAAAAPBAKAAAA                                                                                      56                                                                                                    Central                                                                                               Ct.                                                                                                   Suite 320                                                                                             Fairview                                                                                              Musselshell County                                                                                    MT                                                                                                    65709                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
        40990  AAAAAAAAOBAKAAAA                                                                                      338                                                                                                   Second                                                                                                Circle                                                                                                Suite 40                                                                                              Maple Grove                                                                                           Marshall County                                                                                       OK                                                                                                    78252                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40989  AAAAAAAANBAKAAAA                                                                                      258                                                                                                   Chestnut Jefferson                                                                                    Ln                                                                                                    Suite 200                                                                                             Glenwood                                                                                              Lincoln County                                                                                        MS                                                                                                    53511                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40988  AAAAAAAAMBAKAAAA                                                                                      618                                                                                                   10th Dogwood                                                                                          Ln                                                                                                    Suite I                                                                                               Mount Pleasant                                                                                        Hale County                                                                                           AL                                                                                                    31933                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40987  AAAAAAAALBAKAAAA                                                                                      485                                                                                                   12th                                                                                                  Boulevard                                                                                             Suite N                                                                                               Bunker Hill                                                                                           Dade County                                                                                           GA                                                                                                    30150                                                                                                 United States                                                                                         -5.0000000E+000  apartment                                                                                           
        40986  AAAAAAAAKBAKAAAA                                                                                      483                                                                                                   Main                                                                                                  Cir.                                                                                                  Suite 120                                                                                             Stewart                                                                                               Sioux County                                                                                          ND                                                                                                    58041                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40985  AAAAAAAAJBAKAAAA                                                                                      830                                                                                                   First                                                                                                 Pkwy                                                                                                  Suite E                                                                                               Springfield                                                                                           Thayer County                                                                                         NE                                                                                                    69303                                                                                                 United States                                                                                         -7.0000000E+000  single family                                                                                       
        40984  AAAAAAAAIBAKAAAA                                                                                      969                                                                                                   Chestnut Elm                                                                                          Ct.                                                                                                   Suite P                                                                                               Woodville                                                                                             Citrus County                                                                                         FL                                                                                                    34289                                                                                                 United States                                                                                         -5.0000000E+000  condo                                                                                               
        40983  AAAAAAAAHBAKAAAA                                                                                      456                                                                                                   Meadow                                                                                                Boulevard                                                                                             Suite D                                                                                               Oakland                                                                                               Coles County                                                                                          IL                                                                                                    69843                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40982  AAAAAAAAGBAKAAAA                                                                                      772                                                                                                   Park Maple                                                                                            Court                                                                                                 Suite J                                                                                               White Oak                                                                                             Stonewall County                                                                                      TX                                                                                                    76668                                                                                                 United States                                                                                         -6.0000000E+000  condo                                                                                               
        40981  AAAAAAAAFBAKAAAA                                                                                      47                                                                                                    West 8th                                                                                              Drive                                                                                                 Suite 170                                                                                             Oakland                                                                                               Jefferson Davis Parish                                                                                LA                                                                                                    79843                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        40980  AAAAAAAAEBAKAAAA                                                                                      676                                                                                                   Center                                                                                                Lane                                                                                                  Suite 360                                                                                             Bear Creek                                                                                            Johnson County                                                                                        MO                                                                                                    63075                                                                                                 United States                                                                                         -6.0000000E+000  apartment                                                                                           

--- 20 row(s) selected.
>>
>>--unload 25
>>UNLOAD  
+>WITH PURGEDATA FROM TARGET
+> NEW SNAPSHOT HAVING  SUFFIX 'SNAP'
+>INTO '/user/trafodion/hive/exttables/unload_customer_and_address'
+>select * from trafodion.hbase.customer_salt c join trafodion.hbase.customer_address ca on c.c_current_addr_sk = ca.ca_address_sk ;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 15:02:47.233
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 15:02:47.240
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.007
Task:  CREATE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:47.240
       Snapshots created: 2 
Task:  CREATE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:50.272
Task:  CREATE SNAPSHO  Status: Ended      Elapsed Time:    00:00:03.032
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:02:50.272
       Rows Processed: 100000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:02:54.174
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:03.902
Task:  DELETE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:54.174
       Snapshots deleted: 2 
Task:  DELETE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:54.186
Task:  DELETE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.012

--- 100000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_and_address;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_and_address order by ca_address_sk,c_customer_sk;

C_CUSTOMER_SK  C_CUSTOMER_ID                                                                                         C_CURRENT_CDEMO_SK  C_CURRENT_HDEMO_SK  C_CURRENT_ADDR_SK  C_FIRST_SHIPTO_DATE_SK  C_FIRST_SALES_DATE_SK  C_SALUTATION                                                                                          C_FIRST_NAME                                                                                          C_LAST_NAME                                                                                           C_PREFERRED_CUST_FLAG                                                                                 C_BIRTH_DAY  C_BIRTH_MONTH  C_BIRTH_YEAR  C_BIRTH_COUNTRY                                                                                       C_LOGIN                                                                                               C_EMAIL_ADDRESS                                                                                       C_LAST_REVIEW_DATE                                                                                    CA_ADDRESS_SK  CA_ADDRESS_ID                                                                                         CA_STREET_NUMBER                                                                                      CA_STREET_NAME                                                                                        CA_STREET_TYPE                                                                                        CA_SUITE_NUMBER                                                                                       CA_CITY                                                                                               CA_COUNTY                                                                                             CA_STATE                                                                                              CA_ZIP                                                                                                CA_COUNTRY                                                                                            CA_GMT_OFFSET    CA_LOCATION_TYPE
-------------  ----------------------------------------------------------------------------------------------------  ------------------  ------------------  -----------------  ----------------------  ---------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  -----------  -------------  ------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  -------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  ---------------  ----------------------------------------------------------------------------------------------------

        18001  AAAAAAAABFGEAAAA                                                                                                 1046371                1786                  1                 2449262                2449232  Sir                                                                                                   George                                                                                                Hampton                                                                                               N                                                                                                               8             11          1966  GREECE                                                                                                                                                                                                      George.Hampton@dIlBP1NI2hX.org                                                                        2452340                                                                                                           1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        57050  AAAAAAAAKNONAAAA                                                                                                  689270                 612                  1                 2451607                2451577  Mrs.                                                                                                  Toshiko                                                                                               Weaver                                                                                                N                                                                                                              22              3          1939  TRINIDAD AND TOBAGO                                                                                                                                                                                         Toshiko.Weaver@EbeGFDdHiP5jGrRVC4F.org                                                                2452370                                                                                                           1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        75885  AAAAAAAANGICBAAA                                                                                                  103496                2772                  1                 2451858                2451828  Dr.                                                                                                   Raymond                                                                                               Harms                                                                                                 Y                                                                                                              15              4          1951  MAURITIUS                                                                                                                                                                                                   Raymond.Harms@oHSA4k.com                                                                              2452328                                                                                                           1  AAAAAAAABAAAAAAA                                                                                      18                                                                                                    Jackson                                                                                               Parkway                                                                                               Suite 280                                                                                             Fairfield                                                                                             Maricopa County                                                                                       AZ                                                                                                    86192                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        58325  AAAAAAAAFNDOAAAA                                                                                                 1756775                 165                  2                 2449485                2449455  Miss                                                                                                  Alice                                                                                                 Warner                                                                                                Y                                                                                                              16              1          1935  ERITREA                                                                                                                                                                                                     Alice.Warner@lHiPEB4CFp1Lb.org                                                                        2452366                                                                                                           2  AAAAAAAACAAAAAAA                                                                                      362                                                                                                   Washington 6th                                                                                        RD                                                                                                    Suite 80                                                                                              Fairview                                                                                              Taos County                                                                                           NM                                                                                                    85709                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
        18725  AAAAAAAAFCJEAAAA                                                                                                  369391                 497                  3                 2449807                2449777  Mr.                                                                                                   Jimmy                                                                                                 Short                                                                                                 Y                                                                                                              13              9          1951  TIMOR-LESTE                                                                                                                                                                                                 Jimmy.Short@ryY3VmrBj3eIEYmx9.org                                                                     2452559                                                                                                           3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        32411  AAAAAAAALJOHAAAA                                                                                                 1630141                3872                  3                 2450639                2450609  Sir                                                                                                   Walter                                                                                                Sanchez                                                                                               N                                                                                                               7              5          1937  EQUATORIAL GUINEA                                                                                                                                                                                           Walter.Sanchez@2.edu                                                                                  2452540                                                                                                           3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        40969  AAAAAAAAJAAKAAAA                                                                                                       ?                3181                  3                       ?                2452575  Sir                                                                                                                                                                                                         Jackson                                                                                                                                                                                                               5              6          1928  HONG KONG                                                                                                                                                                                                   Joe.Jackson@aT7.org                                                                                                                                                                                                     3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        61034  AAAAAAAAKGOOAAAA                                                                                                 1479111                5357                  3                 2451120                2451090  Sir                                                                                                   Richard                                                                                               Reinhardt                                                                                             Y                                                                                                              15              3          1939  PARAGUAY                                                                                                                                                                                                    Richard.Reinhardt@05.org                                                                              2452610                                                                                                           3  AAAAAAAADAAAAAAA                                                                                      585                                                                                                   Dogwood Washington                                                                                    Circle                                                                                                Suite Q                                                                                               Pleasant Valley                                                                                       York County                                                                                           PA                                                                                                    12477                                                                                                 United States                                                                                         -5.0000000E+000  single family                                                                                       
        17028  AAAAAAAAEICEAAAA                                                                                                 1141265                 211                  4                 2451052                2451022  Dr.                                                                                                   James                                                                                                 Brown                                                                                                 N                                                                                                              25              2          1983  SOLOMON ISLANDS                                                                                                                                                                                             James.Brown@A.org                                                                                     2452473                                                                                                           4  AAAAAAAAEAAAAAAA                                                                                      111                                                                                                   Smith                                                                                                 Wy                                                                                                    Suite A                                                                                               Oak Ridge                                                                                             Kit Carson County                                                                                     CO                                                                                                    88371                                                                                                 United States                                                                                         -7.0000000E+000  condo                                                                                               
         6856  AAAAAAAAIMKBAAAA                                                                                                  984690                4371                  5                 2449964                2449934  Dr.                                                                                                   Jorge                                                                                                 Smith                                                                                                 N                                                                                                               4              2          1935  UGANDA                                                                                                                                                                                                      Jorge.Smith@hkOEhqq7hrZHRN.edu                                                                        2452445                                                                                                           5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        10755  AAAAAAAADAKCAAAA                                                                                                  616497                6651                  5                 2450078                2450048  Mr.                                                                                                   Rodney                                                                                                Mack                                                                                                  Y                                                                                                               9             10          1959  SAN MARINO                                                                                                                                                                                                  Rodney.Mack@5.com                                                                                     2452313                                                                                                           5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
        92412  AAAAAAAAMPIGBAAA                                                                                                 1455706                6679                  5                 2452065                2452035  Mr.                                                                                                   Nathaniel                                                                                             Parsons                                                                                               N                                                                                                              19              7          1990  NICARAGUA                                                                                                                                                                                                   Nathaniel.Parsons@D6vrtCokekB.org                                                                     2452633                                                                                                           5  AAAAAAAAFAAAAAAA                                                                                      31                                                                                                    College                                                                                               Blvd                                                                                                  Suite 180                                                                                             Glendale                                                                                              Barry County                                                                                          MO                                                                                                    63951                                                                                                 United States                                                                                         -6.0000000E+000  single family                                                                                       
         4321  AAAAAAAABOABAAAA                                                                                                  414860                6233                  6                 2450208                2450178  Ms.                                                                                                   Theresa                                                                                               Overstreet                                                                                            Y                                                                                                               3             10          1969  ZIMBABWE                                                                                                                                                                                                    Theresa.Overstreet@T20uZnkNSpJ8ib0.org                                                                2452301                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
         5455  AAAAAAAAPEFBAAAA                                                                                                 1281074                1140                  6                 2450902                2450872  Mr.                                                                                                   Harvey                                                                                                Clark                                                                                                 N                                                                                                              21             10          1975  KOREA, REPUBLIC OF                                                                                                                                                                                          Harvey.Clark@bAdnGquApj4.edu                                                                          2452421                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
         5632  AAAAAAAAAAGBAAAA                                                                                                  871010                1108                  6                 2452464                2452434  Miss                                                                                                  Viva                                                                                                  Turner                                                                                                N                                                                                                               5              5          1982  NORWAY                                                                                                                                                                                                      Viva.Turner@Z.com                                                                                     2452623                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        15216  AAAAAAAAAHLDAAAA                                                                                                  509782                4894                  6                 2452116                2452086  Miss                                                                                                  Selena                                                                                                Garza                                                                                                 Y                                                                                                               4              1          1957  BELIZE                                                                                                                                                                                                      Selena.Garza@nXnNGznh4m.edu                                                                           2452638                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        27702  AAAAAAAAGDMGAAAA                                                                                                 1052985                3472                  6                 2452458                2452428  Miss                                                                                                  Hanh                                                                                                  Benson                                                                                                Y                                                                                                              10             11          1985  KENYA                                                                                                                                                                                                       Hanh.Benson@Oy8.edu                                                                                   2452602                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        69027  AAAAAAAADKNABAAA                                                                                                 1799829                2894                  6                 2449207                2449177  Mrs.                                                                                                  Holly                                                                                                 Hinkle                                                                                                Y                                                                                                               1              3          1944  POLAND                                                                                                                                                                                                      Holly.Hinkle@7NQVO3aQ7.edu                                                                            2452514                                                                                                           6  AAAAAAAAGAAAAAAA                                                                                      59                                                                                                    Williams Sixth                                                                                        Parkway                                                                                               Suite 100                                                                                             Lakeview                                                                                              Chelan County                                                                                         WA                                                                                                    98579                                                                                                 United States                                                                                         -8.0000000E+000  single family                                                                                       
        19703  AAAAAAAAHPMEAAAA                                                                                                  654391                4199                  7                 2450199                2450169  Dr.                                                                                                   Carrie                                                                                                Currier                                                                                               Y                                                                                                              14             11          1978  SEYCHELLES                                                                                                                                                                                                  Carrie.Currier@I.org                                                                                  2452461                                                                                                           7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      
        36673  AAAAAAAABEPIAAAA                                                                                                 1071833                1320                  7                 2450242                2450212  Dr.                                                                                                   Christopher                                                                                           Smith                                                                                                 N                                                                                                               6              3          1935  BERMUDA                                                                                                                                                                                                     Christopher.Smith@1XyfnXzQcmMv.com                                                                    2452646                                                                                                           7  AAAAAAAAHAAAAAAA                                                                                                                                                                                            Hill 7th                                                                                              Road                                                                                                  Suite U                                                                                               Farmington                                                                                                                                                                                                                                                                                                        39145                                                                                                 United States                                                                                                       ?                                                                                                      

--- 20 row(s) selected.
>>
>>--unload 26 --test with index scan
>>cqd comp_bool_226 'on';

--- SQL operation complete.
>>  -- allow the extract syntax
>>explain options 'f' 
+>UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_name'
+>select c_first_name,c_last_name from trafodion.hbase.customer_salt;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+005
1    .    3    unload                                                1.00E+005
.    .    1    trafodion_index_scan            CUSTOMER_IDX1         1.00E+005

--- SQL operation complete.
>>cqd comp_bool_226 reset;

--- SQL operation complete.
>>
>>UNLOAD
+>WITH PURGEDATA FROM TARGET
+> NEW SNAPSHOT HAVING SUFFIX 'SNAP111' 
+>INTO '/user/trafodion/hive/exttables/unload_customer_name'
+>select c_first_name,c_last_name from trafodion.hbase.customer_salt;
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started    Time: 2017-08-15 15:02:58.169
Task:  EMPTY TARGET    Status: Ended      Time: 2017-08-15 15:02:58.257
Task:  EMPTY TARGET    Status: Ended      Elapsed Time:    00:00:00.009
Task:  CREATE SNAPSHO  Status: Started    Time: 2017-08-15 15:02:58.257
       Snapshots created: 1 
Task:  CREATE SNAPSHO  Status: Ended      Time: 2017-08-15 15:02:58.684
Task:  CREATE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.659
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:02:58.684
       Rows Processed: 100000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:03:00.257
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:01.573
Task:  DELETE SNAPSHO  Status: Started    Time: 2017-08-15 15:03:00.257
       Snapshots deleted: 1 
Task:  DELETE SNAPSHO  Status: Ended      Time: 2017-08-15 15:03:00.286
Task:  DELETE SNAPSHO  Status: Ended      Elapsed Time:    00:00:00.029

--- 100000 row(s) unloaded.
>>--sh sleep 10;
>>select count(*) from hive.hive.unload_customer_name;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>select [first 20] * from hive.hive.unload_customer_name order by c_first_name,c_last_name;

C_FIRST_NAME                                                                                          C_LAST_NAME
----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------

                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          
                                                                                                                                                                                                          

--- 20 row(s) selected.
>>
>>
>>--unload 100 --should give error [8447]
>>unload into '//\a//c' select * from CUSTOMER_ADDRESS;
Task: UNLOAD           Status: Started
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:03:02.997

*** ERROR[8447] An error occurred during hdfs access. Error Detail: Java exception in hdfsCreate(). java.io.IOException: No FileSystem for scheme: null
org.apache.hadoop.fs.FileSystem.getFileSystemClass(FileSystem.java:2584)
org.apache.hadoop.fs.FileSystem.createFileSystem(FileSystem.java:2591)
org.apache.hadoop.fs.FileSystem.access$200(FileSystem.java:91)
org.apache.hadoop.fs.FileSystem$Cache.getInternal(FileSystem.java:2630)
org.apache.hadoop.fs.FileSystem$Cache.get(FileSystem.java:2612)
org.apache.hadoop.fs.FileSystem.get(FileSystem.java:370)
org.trafodion.sql.SequenceFileWriter.hdfsCreate(SequenceFileWriter.java:156)

--- 0 row(s) unloaded.
>>
>>--unload 101 --should give syntax error
>>unload with delimiter 0 into '/user/trafodion/bulkload/test' select * from CUSTOMER_ADDRESS;

*** ERROR[15001] A syntax error occurred at or before: 
unload with delimiter 0 into '/user/trafodion/bulkload/test' select * from CUST
                      ^ (23 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>--unload 102 --should give an error
>>unload with MERGE FILE 'folder/cust_addr' into '/user/trafodion/bulkload/test' select * from customer_address;

*** ERROR[4487] Invalid Path: MERGE FILE 'folder/cust_addr' cannot contain the '/' character.

*** ERROR[8822] The statement was not prepared.

>>
>>--unload  103 -- should not give an error
>>unload with delimiter '\a' into '/user/trafodion/bulkload/test' select * from customer_address;
Task: UNLOAD           Status: Started
Task:  EXTRACT         Status: Started    Time: 2017-08-15 15:03:03.456
       Rows Processed: 50000 
Task:  EXTRACT         Status: Ended      Time: 2017-08-15 15:03:04.542
Task:  EXTRACT         Status: Ended      Elapsed Time:    00:00:01.086

--- 50000 row(s) unloaded.
>>--unload  24 -- should give an error
>>unload with delimiter 'abca' into '/user/trafodion/bulkload/test' select * from customer_address;

*** ERROR[4379] Invalid BULK UNLOAD field delimiter or record separator. A valid field delimiter or record separator must be a single character or an integer between 1 and 255.

*** ERROR[8822] The statement was not prepared.

>>
>>--unload  104 -- should give an error
>>unload with record_separator '\abca' into '/user/trafodion/bulkload/test' select * from customer_address;

*** ERROR[4374] Invalid escape sequence specified as BULK UNLOAD field delimiter or record separator. Only the following escape sequences are allowed: \a, \b, \f, \n, \r, \t, or \v.

*** ERROR[8822] The statement was not prepared.

>>--unload  105 -- should give an error
>>unload with record_separator '\z' into '/user/trafodion/bulkload/test' select * from customer_address;

*** ERROR[4374] Invalid escape sequence specified as BULK UNLOAD field delimiter or record separator. Only the following escape sequences are allowed: \a, \b, \f, \n, \r, \t, or \v.

*** ERROR[8822] The statement was not prepared.

>>--unload 106 --should give error
>>unload  into '/user/trafodion/bulkload/test' select * from customer_address order by ca_address_id;

*** ERROR[15001] A syntax error occurred at or before: 
unload  into '/user/trafodion/bulkload/test' select * from customer_address ord
er by ca_address_id;
 ^ (81 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>--unload 107  --should give error
>>cqd comp_bool_226 'on';

--- SQL operation complete.
>>unload  extract to '/user/trafodion/bulkload/test' select * from customer_address order by ca_address_id;

*** ERROR[15001] A syntax error occurred at or before: 
unload  extract to '/user/trafodion/bulkload/test' select * from customer_addre
ss order by ca_address_id;
       ^ (87 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>cqd comp_bool_226 reset;

--- SQL operation complete.
>>
>>--unload 150
>>
>>log;
regrhadoop.ksh fs -rm /user/trafodion/hive/exttables/unload_customer_demographics/*
>>
>>log;
