core/sql/regress/hive/TEST018

-- Tests for Hbase - Load/Extract
-- Added April 2014
--
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@

create schema trafodion.hbase;
set schema trafodion.hbase;
cqd comp_bool_226 'on';
cqd TRAF_TABLE_SNAPSHOT_SCAN_TABLE_SIZE_THRESHOLD  '0';
cqd hive_max_string_length_in_bytes '60';
cqd HIST_ROWCOUNT_REQUIRING_STATS '50000';
cqd AUTO_QUERY_RETRY_WARNINGS 'ON';

obey TEST018(clean_up);

log LOG018 clear;

sh regrhive.ksh -v -f $REGRTSTDIR/TEST018_create_hive_tables.hive &> $REGRRUNDIR/LOG018_create_hive_tables.log ;

obey TEST018(setup);
obey TEST018(test_bulk_unload_simple);

log;
obey TEST018(clean_up);
exit;

?section log_results

log LOG018_SNAPSHOT_SCAN_PLAN.TXT clear;
--snapshot
explain snp;
log;
sh echo "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 " >> LOG018 ;
sh 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 2>&1 >> LOG018 ;
log LOG018_REGULAR_SCAN_PLAN.TXT clear;
--no snapshot
explain reg;
log;
sh echo "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" >> LOG018 ;
sh 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 2>&1 >> LOG018 ;

log LOG018_REGULAR_SCAN.DAT clear;
--no snapshot
execute reg ;
log;
log LOG018_SNAPSHOT_SCAN.DAT clear;
--snapshot
execute snp;
log;

sh head -n 20 LOG018_REGULAR_SCAN.DAT  2>&1 >> LOG018;
sh head -n 20 LOG018_SNAPSHOT_SCAN.DAT  2>&1 >> LOG018;

sh diff  LOG018_REGULAR_SCAN.DAT LOG018_SNAPSHOT_SCAN.DAT 2>&1 >> LOG018;

?section clean_up
drop index customer_idx1;
drop table customer_demographics cascade;
drop table customer_demographics_salt cascade;
drop table customer_address cascade;
drop table customer_address_NOPK cascade;
drop table customer_salt;
drop table store_sales_salt;
drop table nulls;
drop table null_format_src;

sh regrhadoop.ksh fs -rm   /user/trafodion/bulkload/merged_customer_address.gz ;
sh regrhadoop.ksh fs -rm   /user/trafodion/bulkload/merged_customer_demogs.gz ;
sh regrhadoop.ksh fs -rm   /user/trafodion/bulkload/merged_customer_demogs_3;
sh regrhadoop.ksh fs -rm   /user/trafodion/bulkload/merged_customer_demogs_4.gz ;
sh regrhadoop.ksh fs -rm   /user/trafodion/bulkload/merged_customer_demogs_2.gz ;
sh  regrhbase.ksh $REGRTSTDIR/TEST018_drop_hbase_objects.hbase &> $REGRRUNDIR/TEST018_drop_hbase_objects.log ;
?section setup
--------------------------------------------------------------------------
create table nulls (a char(5), b char(10));
create table null_format_src (a varchar(5), b varchar(5));

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)
); 

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); 


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)
);



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); 


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 
;



?section test_bulk_unload_simple
--------------------------------------------------------------------------
--nulls
insert into nulls values ('aaa1','bbbbb1'),(null,'bbbbb2'),
                         ('aaa3',null),(null,null),
                          ('aaa5',null),(null,'bbbbb6');

insert into null_format_src values 
   ('a', 'b'), ('a', null), (null, 'b'), ('a', ''), ('', 'b'),
   (null, ''), (':', null), ('', ''), (':', ':'), (null, null);

--load1
load with no recovery into customer_address 
select * from hive.hive.customer_address;    
--
select count(*) from hive.hive.customer_address;                                                                                          
select count(*) from customer_address;

--load2
load  with no recovery  into customer_demographics 
select * from hive.hive.customer_demographics  where cd_demo_sk <= 20000;
--
select count(*) from hive.hive.customer_demographics   where cd_demo_sk <= 20000;
select count(*) from customer_demographics;
--load3
load  with no recovery into customer_demographics_salt 
select * from hive.hive.customer_demographics  where cd_demo_sk <= 20000;      
--                                                                              
select count(*) from customer_demographics_salt;
--load4
load  with no recovery  into customer_salt 
select * from hive.hive.customer;
--
select count(*) from hive.hive.customer;
select count(*) from customer_salt;

--load5
load  with no recovery into store_sales_salt 
select * from hive.hive.store_sales where ss_item_sk <= 1000;
--
--select count(*) from hive.hive.store_sales  where ss_item_sk <= 1000;
select count(*) from store_sales_salt;

------------------------------------------
-- handling of various null formats

-- using insert
insert overwrite table hive.hive.null_format_default select * from null_format_src;
select * from hive.hive.null_format_default;

insert overwrite table hive.hive.null_format_empty select * from null_format_src;
select * from hive.hive.null_format_empty;

insert overwrite table hive.hive.null_format_colon select * from null_format_src;
select * from hive.hive.null_format_colon;


-- using unload
unload with purgedata from target
   into '/user/trafodion/hive/exttables/null_format_default'
   select * from null_format_src;
select * from hive.hive.null_format_default;

unload with purgedata from target
   into '/user/trafodion/hive/exttables/null_format_empty'
   select * from null_format_src;
select * from hive.hive.null_format_empty;

unload with purgedata from target
   into '/user/trafodion/hive/exttables/null_format_colon'
   select * from null_format_src;
select * from hive.hive.null_format_colon;

---index 0
create index customer_idx1 on customer_salt(c_first_name, c_last_name);

sh  regrhbase.ksh $REGRTSTDIR/TEST018_create_hbase_objects.hbase &> $REGRRUNDIR/LOG018_create_hbase_tables.log ;

alter table hbase.customer_address generate stored descriptor;
alter table hbase.customer_demographics_salt generate stored descriptor;
alter table hbase.customer_salt generate stored descriptor;

--exp1
explain options 'f' 
UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_address'
select * from trafodion.hbase.customer_address 
<<+ cardinality 10e10 >>;
--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 
;
log;
sh echo "regrhadoop.ksh fs -copyToLocal  /user/trafodion/bulkload/customer_address/merged_customer_address.gz /tmp " >> LOG018 ;;
sh regrhadoop.ksh fs -copyToLocal  /user/trafodion/bulkload/customer_address/merged_customer_address.gz /tmp ;
sh echo "gunzip -f /tmp/merged_customer_address.gz" >> LOG018 ; ;
sh gunzip -f /tmp/merged_customer_address.gz  ;
sh echo "cat /tmp/merged_customer_address | wc -l" >> LOG018 ;
sh cat /tmp/merged_customer_address | wc -l >> LOG018 ;
log LOG018;
--------------------------
--exp2
explain options 'f' 
UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_demographics'
select * from trafodion.hbase.customer_demographics 
<<+ cardinality 10e10 >>;
--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 >>;
log;
sh echo "regrhadoop.ksh fs -copyToLocal  /user/trafodion/bulkload/customer_demographics/merged_customer_demogs.gz /tmp " >> LOG018 ;
sh regrhadoop.ksh fs -copyToLocal  /user/trafodion/bulkload/customer_demographics/merged_customer_demogs.gz /tmp   ;
sh echo "gunzip -f /tmp/merged_customer_demogs.gz" >> LOG018 ;
sh gunzip -f /tmp/merged_customer_demogs.gz  ;
sh echo "cat /tmp/merged_customer_demogs | wc -l" >> LOG018 ;
sh cat /tmp/merged_customer_demogs | wc -l >> LOG018 ;
log LOG018;
-----------
--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 >>;
log;
sh echo "regrhadoop.ksh fs -cat  /user/trafodion/bulkload/customer_demographics/merged_customer_demogs_2 | wc -l " >> LOG018 ;
sh regrhadoop.ksh fs -cat  /user/trafodion/bulkload/customer_demographics/merged_customer_demogs_2 | wc -l >> LOG018  ;
log LOG018;
----------------------------------
--exp 3
explain options 'f' 
UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_demographics_salt'
select * from trafodion.hbase.customer_demographics_salt 
<<+ cardinality 10e10 >>;
--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 >>;

log;
sh echo "regrhadoop.ksh fs -du -s /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_3" >> LOG018 ;
sh regrhadoop.ksh fs -du -s /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_3 >> LOG018 ;
log LOG018;
-------------------
--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 >>;

log;
sh echo "regrhadoop.ksh fs -du -s /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_4.gz" >> LOG018 ;
sh regrhadoop.ksh fs -du -s /user/trafodion/bulkload/customer_demographics_salt/merged_customer_demogs_4.gz >> LOG018 ;
log LOG018;

--exp4
explain options 'f' 
UNLOAD EXTRACT TO '/user/trafodion/bulkload/customer_demographics_salt'
select * from trafodion.hbase.customer_demographics_salt 
<<+ cardinality 10e10 >>;
--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 >>;

log;
sh echo "regrhadoop.ksh fs -cat /user/trafodion/bulkload/customer_demographics_salt/file* | wc -l" >> LOG018 ;
sh regrhadoop.ksh fs -cat /user/trafodion/bulkload/customer_demographics_salt/file* | wc -l >> LOG018 ;
sh echo "regrhadoop.ksh fs -ls /user/trafodion/bulkload/customer_demographics_salt/file* |  grep file | wc -l" >> LOG018 ;
sh regrhadoop.ksh fs -ls /user/trafodion/bulkload/customer_demographics_salt/file* |  grep file |  wc -l >> LOG018 ;
log LOG018;

--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 >>;

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';
select [first 100] * from hive.hive.unload_customer_demographics where cd_demo_sk <200 order by cd_demo_sk;



--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 >>;
log;
sh echo "regrhadoop.ksh fs -ls /user/trafodion/bulkload/customer_demographics_salt/merged* | grep merge | wc -l" >> LOG018 ;
sh regrhadoop.ksh fs -ls /user/trafodion/bulkload/customer_demographics_salt/merged* |  grep merge | wc -l >> LOG018 ;
log LOG018;


--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 >>;

--- 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 >>;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_demographics;
select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

--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 >>;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_demographics;
select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk <100  order by cd_demo_sk;

--unload 12
UNLOAD  
WITH PURGEDATA FROM TARGET
INTO '/user/trafodion/hive/exttables/unload_customer_address'
select * from trafodion.hbase.customer_address ;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_address;
select [first 20] * from hive.hive.unload_customer_address where ca_address_sk <100 order by ca_address_sk;

--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 ;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_address;
select [first 20] * from hive.hive.unload_customer_address where ca_address_sk < 100 order by ca_address_sk;


-- reduce buffer size--test work method
cqd HDFS_IO_BUFFERSIZE '1024';
cqd attempt_esp_parallelism 'off';
--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;
--sh sleep 10;
select count(*) from hive.hive.unload_customer;
select [first 20] * from hive.hive.unload_customer where c_customer_sk < 100 order by c_customer_sk;

--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;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_demographics;
select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

--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;

--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;


log;
sh echo "regrhadoop.ksh fs -rm /user/trafodion/hive/exttables/unload_customer_demographics/*" >> LOG018 ;
sh regrhadoop.ksh fs -rm /user/trafodion/hive/exttables/unload_customer_demographics/* ;
log LOG018;


cqd HDFS_IO_BUFFERSIZE reset;
cqd attempt_esp_parallelism reset;
--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; 
--sh sleep 10;
select  [first 100] * from hive.hive.unload_store_sales_summary order by  ss_sold_date_sk,ss_store_sk; 

--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 ;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_and_address;
select [first 20] * from hive.hive.unload_customer_and_address order by ca_address_sk,c_customer_sk;

--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;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_address;
select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk;
select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk desc;
--*****************************************************
--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';

cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';
prepare ss from select  * from HBASE.CUSTOMER_DEMOGRAPHICS order by cd_demo_sk;

--select 1
cqd parallel_num_esps '2';
cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';
prepare snp from select  * from HBASE.CUSTOMER_DEMOGRAPHICS_SALT <<+ cardinality 10e10 >> order by cd_demo_sk;

cqd TRAF_TABLE_SNAPSHOT_SCAN 'NONE';
prepare reg from select  * from HBASE.CUSTOMER_DEMOGRAPHICS_SALT <<+ cardinality 10e10 >> order by cd_demo_sk;
log;
obey TEST018(log_results);
log LOG018;

--select 2
cqd parallel_num_esps reset;
cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';
prepare snp from select  * from HBASE.CUSTOMER_ADDRESS <<+ cardinality 10e10 >> order by ca_address_sk;

cqd TRAF_TABLE_SNAPSHOT_SCAN 'NONE';
prepare reg from select  * from HBASE.CUSTOMER_ADDRESS <<+ cardinality 10e10 >> order by ca_address_sk;
log;
obey TEST018(log_results);
log LOG018;
--select 2
cqd parallel_num_esps reset;
cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';
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 ;

cqd TRAF_TABLE_SNAPSHOT_SCAN 'NONE';
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 ;
log;
obey TEST018(log_results);
log LOG018;

--select 3
cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';

prepare ss from select  c_first_name, c_last_name from HBASE.customer_salt order by c_first_name, c_last_name;

cqd TRAF_TABLE_SNAPSHOT_SCAN_TABLE_SIZE_THRESHOLD '1000';
cqd TRAF_TABLE_SNAPSHOT_SCAN 'LATEST';
prepare ss from select  * from trafodion.hbase.customer_address;
cqd TRAF_TABLE_SNAPSHOT_SCAN_TABLE_SIZE_THRESHOLD '0';



--******************************************************************************
--*********************BULK UNLOAD with SNAPSHOT SCAN
--unload 20

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

UNLOAD
WITH PURGEDATA FROM TARGET
 EXISTING SNAPSHOT HAVING SUFFIX 'SNAP111' 
INTO '/user/trafodion/hive/exttables/unload_customer_address'
select * from customer_address
<<+ cardinality 10e10 >>;

select count(*) from hive.hive.unload_customer_address;
select [first 20] * from hive.hive.unload_customer_address  where ca_address_sk < 100 order by ca_address_sk;

--unload 21

cqd comp_bool_226 'on';  -- 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 >>;
cqd comp_bool_226 reset;

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 >>;

select count(*) from hive.hive.unload_customer_demographics;
select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

--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 >>;

select count(*) from hive.hive.unload_customer_demographics;
select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

--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 >>;

select count(*) from hive.hive.unload_customer_demographics;
select [first 20] * from hive.hive.unload_customer_demographics where cd_demo_sk < 100 order by cd_demo_sk;

--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;

select count(*) from hive.hive.unload_customer_address;
select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk;
select [first 20] * from hive.hive.unload_customer_address order by ca_address_sk desc;

--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 ;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_and_address;
select [first 20] * from hive.hive.unload_customer_and_address order by ca_address_sk,c_customer_sk;

--unload 26 --test with index scan
cqd comp_bool_226 'on';  -- 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;
cqd comp_bool_226 reset;

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;
--sh sleep 10;
select count(*) from hive.hive.unload_customer_name;
select [first 20] * from hive.hive.unload_customer_name order by c_first_name,c_last_name;


--unload 100 --should give error [8447]
unload into '//\a//c' select * from CUSTOMER_ADDRESS;

--unload 101 --should give syntax error
unload with delimiter 0 into '/user/trafodion/bulkload/test' select * from CUSTOMER_ADDRESS;

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

--unload  103 -- should not give an error
unload with delimiter '\a' into '/user/trafodion/bulkload/test' select * from customer_address;
--unload  24 -- should give an error
unload with delimiter 'abca' into '/user/trafodion/bulkload/test' select * from customer_address;

--unload  104 -- should give an error
unload with record_separator '\abca' into '/user/trafodion/bulkload/test' select * from customer_address;
--unload  105 -- should give an error
unload with record_separator '\z' into '/user/trafodion/bulkload/test' select * from customer_address;
--unload 106 --should give error
unload  into '/user/trafodion/bulkload/test' select * from customer_address order by ca_address_id;
--unload 107  --should give error
cqd comp_bool_226 'on';
unload  extract to '/user/trafodion/bulkload/test' select * from customer_address order by ca_address_id;
cqd comp_bool_226 reset;

--unload 150

log;
sh echo "regrhadoop.ksh fs -rm /user/trafodion/hive/exttables/unload_customer_demographics/*" >> LOG018 ;
sh regrhadoop.ksh fs -rm /user/trafodion/hive/exttables/unload_customer_demographics/* ;
log LOG018;

log;