Google Git
Sign in
apache/trafodion/default/./core/sql/regress/hive/EXPECTED018
blob: 6f100f10b23655cef7c174ef2bf6e8fd5d5a40ce [file]
>>
>>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: 2018-02-15 18:03:08.179883
Task: CLEANUP Status: Ended Time: 2018-02-15 18:03:08.204652
Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.025
Task: LOADING DATA Status: Started Time: 2018-02-15 18:03:08.204741
Rows Processed: 50000
Error Rows: 0
Task: LOADING DATA Status: Ended Time: 2018-02-15 18:03:20.138170
Task: LOADING DATA Status: Ended Elapsed Time: 00:00:11.933
Task: COMPLETION Status: Started Time: 2018-02-15 18:03:20.138306
Rows Loaded: 50000
Task: COMPLETION Status: Ended Time: 2018-02-15 18:03:20.614389
Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.476
--- 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: 2018-02-15 18:03:25.520129
Task: CLEANUP Status: Ended Time: 2018-02-15 18:03:25.533927
Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.014
Task: LOADING DATA Status: Started Time: 2018-02-15 18:03:25.533977
Rows Processed: 20000
Error Rows: 0
Task: LOADING DATA Status: Ended Time: 2018-02-15 18:03:40.466857
Task: LOADING DATA Status: Ended Elapsed Time: 00:00:14.933
Task: COMPLETION Status: Started Time: 2018-02-15 18:03:40.466943
Rows Loaded: 20000
Task: COMPLETION Status: Ended Time: 2018-02-15 18:03:40.964115
Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.497
--- 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: 2018-02-15 18:03:45.603941
Task: CLEANUP Status: Ended Time: 2018-02-15 18:03:45.623723
Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.020
Task: LOADING DATA Status: Started Time: 2018-02-15 18:03:45.624021
Rows Processed: 20000
Error Rows: 0
Task: LOADING DATA Status: Ended Time: 2018-02-15 18:03:56.104059
Task: LOADING DATA Status: Ended Elapsed Time: 00:00:10.480
Task: COMPLETION Status: Started Time: 2018-02-15 18:03:56.104160
Rows Loaded: 20000
Task: COMPLETION Status: Ended Time: 2018-02-15 18:03:56.594604
Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.490
--- 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: 2018-02-15 18:03:59.171940
Task: CLEANUP Status: Ended Time: 2018-02-15 18:03:59.185363
Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.013
Task: LOADING DATA Status: Started Time: 2018-02-15 18:03:59.185428
Rows Processed: 100000
Error Rows: 0
Task: LOADING DATA Status: Ended Time: 2018-02-15 18:04:16.244797
Task: LOADING DATA Status: Ended Elapsed Time: 00:00:17.059
Task: COMPLETION Status: Started Time: 2018-02-15 18:04:16.244907
Rows Loaded: 100000
Task: COMPLETION Status: Ended Time: 2018-02-15 18:04:16.754047
Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.509
--- 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: 2018-02-15 18:04:22.571403
Task: CLEANUP Status: Ended Time: 2018-02-15 18:04:22.593772
Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.022
Task: LOADING DATA Status: Started Time: 2018-02-15 18:04:22.593835
Rows Processed: 160756
Error Rows: 0
Task: LOADING DATA Status: Ended Time: 2018-02-15 18:04:41.379079
Task: LOADING DATA Status: Ended Elapsed Time: 00:00:18.785
Task: COMPLETION Status: Started Time: 2018-02-15 18:04:41.379174
Rows Loaded: 160756
Task: COMPLETION Status: Ended Time: 2018-02-15 18:04:41.864053
Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.485
--- 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: 2018-02-15 18:04:54.535223
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:04:54.545332
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.010
Task: EXTRACT Status: Started Time: 2018-02-15 18:04:54.545432
Rows Processed: 10
Task: EXTRACT Status: Ended Time: 2018-02-15 18:04:54.907669
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.362
--- 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: 2018-02-15 18:04:55.644505
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:04:55.653487
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.009
Task: EXTRACT Status: Started Time: 2018-02-15 18:04:55.653576
Rows Processed: 10
Task: EXTRACT Status: Ended Time: 2018-02-15 18:04:55.793745
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.140
--- 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: 2018-02-15 18:04:58.173365
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:04:58.181232
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.008
Task: EXTRACT Status: Started Time: 2018-02-15 18:04:58.181338
Rows Processed: 10
Task: EXTRACT Status: Ended Time: 2018-02-15 18:04:59.59341
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.878
--- 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: 2018-02-15 18:06:11.944426
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:06:11.951622
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.007
Task: EXTRACT Status: Started Time: 2018-02-15 18:06:11.951715
Rows Processed: 50000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:06:13.577030
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.625
Task: MERGE FILES Status: Started Time: 2018-02-15 18:06:13.577106
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:06:13.649524
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.072
--- 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: 2018-02-15 18:06:18.315264
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:06:18.332814
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.018
Task: EXTRACT Status: Started Time: 2018-02-15 18:06:18.332913
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:06:19.299182
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.966
Task: MERGE FILES Status: Started Time: 2018-02-15 18:06:19.299258
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:06:19.381584
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.082
--- 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: 2018-02-15 18:06:23.294544
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:06:23.313697
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.019
Task: EXTRACT Status: Started Time: 2018-02-15 18:06:23.313776
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:06:24.191928
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.878
Task: MERGE FILES Status: Started Time: 2018-02-15 18:06:24.191983
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:06:24.241983
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.050
--- 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: 2018-02-15 18:06:32.546089
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:06:32.554718
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.009
Task: EXTRACT Status: Started Time: 2018-02-15 18:06:32.554786
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:06:33.724947
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.170
Task: MERGE FILES Status: Started Time: 2018-02-15 18:06:33.724996
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:06:33.823547
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.099
--- 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: 2018-02-15 18:06:38.56371
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:06:38.67413
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.011
Task: EXTRACT Status: Started Time: 2018-02-15 18:06:38.67479
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:06:39.90158
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.023
Task: MERGE FILES Status: Started Time: 2018-02-15 18:06:39.90224
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:06:39.187512
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.097
--- 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: 2018-02-15 18:06:43.445112
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:06:43.453703
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.009
Task: EXTRACT Status: Started Time: 2018-02-15 18:06:43.453783
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:06:44.340742
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.887
--- 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: 2018-02-15 18:06:53.855555
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:06:53.907871
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.052
Task: EXTRACT Status: Started Time: 2018-02-15 18:06:53.907963
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:06:55.94643
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.187
Task: MERGE FILES Status: Started Time: 2018-02-15 18:06:55.94708
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:06:55.204791
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.110
--- 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: 2018-02-15 18:07:08.188150
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:08.202625
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.014
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:08.202732
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:09.582936
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.380
Task: MERGE FILES Status: Started Time: 2018-02-15 18:07:09.582998
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:07:09.681563
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.099
--- 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: 2018-02-15 18:07:13.656228
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:13.669280
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.013
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:13.669356
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:14.794127
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.125
Task: MERGE FILES Status: Started Time: 2018-02-15 18:07:14.794177
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:07:14.872595
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.078
--- 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: 2018-02-15 18:07:17.114815
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:17.123541
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.009
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:17.123619
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:18.534503
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.411
--- 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 = 1518718034, failedModTS = 1518718037, failedLoc = hdfs://localhost:30200/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: 2018-02-15 18:07:21.834979
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:21.842607
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.008
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:21.842684
Rows Processed: 50000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:22.846533
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.004
--- 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: 2018-02-15 18:07:27.223840
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:27.230172
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.006
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:27.230271
Rows Processed: 50000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:28.276265
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.046
--- 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 = 1518718042, failedModTS = 1518718047, failedLoc = hdfs://localhost:30200/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: 2018-02-15 18:07:35.116218
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:35.138360
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.022
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:35.138511
Rows Processed: 100000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:39.493885
Task: EXTRACT Status: Ended Elapsed Time: 00:00:04.355
--- 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: 2018-02-15 18:07:42.584392
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:42.606491
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.022
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:42.606575
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:42.938953
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.332
--- 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: 2018-02-15 18:07:45.134272
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:45.144199
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.010
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:45.144292
Rows Processed: 99
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:45.206585
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.062
Task: MERGE FILES Status: Started Time: 2018-02-15 18:07:45.206623
Task: MERGE FILES Status: Ended Time: 2018-02-15 18:07:45.249409
Task: MERGE FILES Status: Ended Elapsed Time: 00:00:00.043
--- 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: 2018-02-15 18:07:50.933942
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:50.947531
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.014
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:50.947646
Rows Processed: 12349
Task: EXTRACT Status: Ended Time: 2018-02-15 18:07:57.267172
Task: EXTRACT Status: Ended Elapsed Time: 00:00:06.320
--- 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: 2018-02-15 18:07:59.625343
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:07:59.634853
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.010
Task: EXTRACT Status: Started Time: 2018-02-15 18:07:59.634938
Rows Processed: 100000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:08:03.958036
Task: EXTRACT Status: Ended Elapsed Time: 00:00:04.323
--- 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: 2018-02-15 18:08:09.455473
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:08:09.466952
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.011
Task: EXTRACT Status: Started Time: 2018-02-15 18:08:09.467039
Rows Processed: 1998
Task: EXTRACT Status: Ended Time: 2018-02-15 18:08:10.742385
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.275
--- 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/20180215180816/
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/20180215180828/
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/20180215180857/
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/20180215180857/
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: 2018-02-15 18:11:30.124408
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:11:30.134892
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.010
Task: VERIFY SNAPSHO Status: Started Time: 2018-02-15 18:11:30.135007
Snapshots verified: 1
Task: VERIFY SNAPSHO Status: Ended Time: 2018-02-15 18:11:30.786874
Task: VERIFY SNAPSHO Status: Ended Elapsed Time: 00:00:00.652
Task: EXTRACT Status: Started Time: 2018-02-15 18:11:30.786980
Rows Processed: 50000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:11:33.589088
Task: EXTRACT Status: Ended Elapsed Time: 00:00:02.802
--- 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: 2018-02-15 18:11:37.695020
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:11:37.697994
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.003
Task: VERIFY SNAPSHO Status: Started Time: 2018-02-15 18:11:37.698032
Snapshots verified: 1
Task: VERIFY SNAPSHO Status: Ended Time: 2018-02-15 18:11:38.91730
Task: VERIFY SNAPSHO Status: Ended Elapsed Time: 00:00:00.394
Task: EXTRACT Status: Started Time: 2018-02-15 18:11:38.91793
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:11:54.815317
Task: EXTRACT Status: Ended Elapsed Time: 00:00:16.724
--- 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: 2018-02-15 18:11:59.514403
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:11:59.568789
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.054
Task: CREATE SNAPSHO Status: Started Time: 2018-02-15 18:11:59.569162
Snapshots created: 1
Task: CREATE SNAPSHO Status: Ended Time: 2018-02-15 18:12:02.196246
Task: CREATE SNAPSHO Status: Ended Elapsed Time: 00:00:02.627
Task: EXTRACT Status: Started Time: 2018-02-15 18:12:02.196322
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:12:04.853940
Task: EXTRACT Status: Ended Elapsed Time: 00:00:02.658
Task: DELETE SNAPSHO Status: Started Time: 2018-02-15 18:12:04.854023
Snapshots deleted: 1
Task: DELETE SNAPSHO Status: Ended Time: 2018-02-15 18:12:04.970459
Task: DELETE SNAPSHO Status: Ended Elapsed Time: 00:00:00.116
--- 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 = 1518718314, failedModTS = 1518718322, failedLoc = hdfs://localhost:30200/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: 2018-02-15 18:12:09.434906
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:12:09.447361
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.012
Task: CREATE SNAPSHO Status: Started Time: 2018-02-15 18:12:09.447441
Snapshots created: 1
Task: CREATE SNAPSHO Status: Ended Time: 2018-02-15 18:12:11.216278
Task: CREATE SNAPSHO Status: Ended Elapsed Time: 00:00:01.769
Task: EXTRACT Status: Started Time: 2018-02-15 18:12:11.216735
Rows Processed: 20000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:12:13.303549
Task: EXTRACT Status: Ended Elapsed Time: 00:00:02.087
Task: DELETE SNAPSHO Status: Started Time: 2018-02-15 18:12:13.303627
Snapshots deleted: 1
Task: DELETE SNAPSHO Status: Ended Time: 2018-02-15 18:12:13.312214
Task: DELETE SNAPSHO Status: Ended Elapsed Time: 00:00:00.009
--- 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 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: 2018-02-15 18:12:15.664213
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:12:15.671583
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.007
Task: CREATE SNAPSHO Status: Started Time: 2018-02-15 18:12:15.671688
Snapshots created: 1
Task: CREATE SNAPSHO Status: Ended Time: 2018-02-15 18:12:16.657657
Task: CREATE SNAPSHO Status: Ended Elapsed Time: 00:00:00.986
Task: EXTRACT Status: Started Time: 2018-02-15 18:12:16.657770
Rows Processed: 1998
Task: EXTRACT Status: Ended Time: 2018-02-15 18:12:17.470194
Task: EXTRACT Status: Ended Elapsed Time: 00:00:00.812
Task: DELETE SNAPSHO Status: Started Time: 2018-02-15 18:12:17.470315
Snapshots deleted: 1
Task: DELETE SNAPSHO Status: Ended Time: 2018-02-15 18:12:17.478106
Task: DELETE SNAPSHO Status: Ended Elapsed Time: 00:00:00.008
--- 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: 2018-02-15 18:12:19.884968
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:12:19.897491
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.013
Task: CREATE SNAPSHO Status: Started Time: 2018-02-15 18:12:19.897613
Snapshots created: 2
Task: CREATE SNAPSHO Status: Ended Time: 2018-02-15 18:12:23.452227
Task: CREATE SNAPSHO Status: Ended Elapsed Time: 00:00:03.555
Task: EXTRACT Status: Started Time: 2018-02-15 18:12:23.452312
Rows Processed: 100000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:12:29.936799
Task: EXTRACT Status: Ended Elapsed Time: 00:00:06.484
Task: DELETE SNAPSHO Status: Started Time: 2018-02-15 18:12:29.937310
Snapshots deleted: 2
Task: DELETE SNAPSHO Status: Ended Time: 2018-02-15 18:12:29.957784
Task: DELETE SNAPSHO Status: Ended Elapsed Time: 00:00:00.020
--- 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: 2018-02-15 18:12:33.805157
Task: EMPTY TARGET Status: Ended Time: 2018-02-15 18:12:33.812573
Task: EMPTY TARGET Status: Ended Elapsed Time: 00:00:00.007
Task: CREATE SNAPSHO Status: Started Time: 2018-02-15 18:12:33.812685
Snapshots created: 1
Task: CREATE SNAPSHO Status: Ended Time: 2018-02-15 18:12:34.536950
Task: CREATE SNAPSHO Status: Ended Elapsed Time: 00:00:00.724
Task: EXTRACT Status: Started Time: 2018-02-15 18:12:34.537031
Rows Processed: 100000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:12:37.722517
Task: EXTRACT Status: Ended Elapsed Time: 00:00:03.185
Task: DELETE SNAPSHO Status: Started Time: 2018-02-15 18:12:37.722612
Snapshots deleted: 1
Task: DELETE SNAPSHO Status: Ended Time: 2018-02-15 18:12:37.731824
Task: DELETE SNAPSHO Status: Ended Elapsed Time: 00:00:00.009
--- 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: 2018-02-15 18:12:42.125129
*** ERROR[8447] An error occurred during hdfs access. Error Detail: Java exception in HdfsClient::hdfsOpen(). 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: 2018-02-15 18:12:42.706958
Rows Processed: 50000
Task: EXTRACT Status: Ended Time: 2018-02-15 18:12:44.128709
Task: EXTRACT Status: Ended Elapsed Time: 00:00:01.422
--- 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;