core/sql/regress/hive/TEST005

-- -*- mode: sql; coding: utf-8 -*-
-- Tests for SQL on Hadoop PoC
-- Test simple cases of partitioned tables
-- Very basic test of data types and Unicode
-- Basic test of metadata invalidation
-- Added April 2013
--
-- @@@ 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 @@@

sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/customer_ddl;
sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/customer_temp;
sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/tbl_utf8;
sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/tbl_type;
sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/tbl_gbk;
sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/tbl_dos;
sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/tbl_dos_num;
sh regrhadoop.ksh fs -mkdir  /user/trafodion/hive/exttables/tbl_bad;
--empty folders
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/customer_ddl/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/customer_temp/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/tbl_utf8/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/tbl_type/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/tbl_gbk/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/tbl_dos/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/tbl_dos_num/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/tbl_bad/*;

--- setup Hive tables
sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_a.hive.sql;

sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_utf8.data /user/trafodion/hive/exttables/tbl_utf8;
sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_type.data /user/trafodion/hive/exttables/tbl_type;
sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_gbk.data /user/trafodion/hive/exttables/tbl_gbk;
sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_dos.data /user/trafodion/hive/exttables/tbl_dos;
sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_dos_numeric.data /user/trafodion/hive/exttables/tbl_dos_num;
sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_bad.data /user/trafodion/hive/exttables/tbl_bad;

log LOG005 clear;

set schema hive.hive;

set terminal_charset utf8;

cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '25' ;
cqd mode_seahive 'ON';
cqd HIST_ROWCOUNT_REQUIRING_STATS '50000';

------------------------------------------------------------
-- Testing query plan invalidation in the compiler, but
-- not the executor. Perform DML/DDL operations on a
-- table and try re-executing the old plan as well as
-- getting a query cache hit and updating the changed
-- Hive and HDFS metadata
------------------------------------------------------------
prepare s1 from 
  select c_preferred_cust_flag,
         count(*) 
  from customer_ddl 
  group by 1 
  order by 1
  ;
execute s1;
-- expect 0 rows

prepare s1part from 
  -- selecting part col not supported right now
  select --c_preferred_cust_flag,
         count(*) 
  from customer_bp 
  --group by 1 
  --order by 1
  ;
execute s1part;
-- expect 0 rows

-- insert some data and add one more partition
sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_b.hive.sql;

-- query cache hit, no validation at all
  select c_preferred_cust_flag,
         count(*) 
  from customer_ddl 
  group by 1 
  order by 1
  ;

-- vary query to avoid query cache hit
prepare s2 from 
  select c_preferred_cust_flag,
         count(c_customer_sk) 
  from customer_ddl 
  group by 1 
  order by 1
  ;

prepare s2part from
  -- selecting part col not supported right now
  select --c_preferred_cust_flag,
         count(*) 
  from customer_bp 
  --group by 1 
  --order by 1
  ;
execute s1;
-- because we don't invalidate in the executor,
-- this should still return 0 rows

execute s2;
-- should get an NATable cache
-- hit, we should notice the change in the table
-- and return the correct result

execute s1part;
-- because we don't invalidate in the executor,
-- this should still return 0 rows

execute s2part;
-- although this should get an NATable cache
-- hit, we should notice the change in the table
-- and return the correct result

insert into customer_temp 
select * from customer 
where c_customer_sk between 20000 and 39999;

select * from newtable;
-- no rows, but should know the new table
insert into newtable values ('abc');
select * from newtable;
-- expect to see the row, but only because query cache is off

insert into hiveregr5.newtable2 values ('xyz');
select * from hiveregr5.newtable2;

-- add a second partition to customer_bp
sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_c.hive.sql;
-- add more files to customer_ddl
sh regrhadoop.ksh dfs -cp /user/trafodion/hive/exttables/customer_temp/* /user/trafodion/hive/exttables/customer_ddl;

-- no query cache hit, but NATable cache hit
prepare s3 from 
  select count(*) 
  from customer_ddl 
  ;

-- no query cache hit, but NATable cache hit
prepare s3part from
  -- selecting part col not supported right now
  select --c_preferred_cust_flag,
         count(c_customer_id) 
  from customer_bp 
  --group by 1 
  --order by 1
  ;
execute s1;
-- s1 should still return 0 rows - for now
execute s2;
execute s3;
execute s1part;
-- s1 should still return 0 rows - for now
execute s2part;
execute s3part;

select a,b from newtable;
-- should return 0 rows

insert into newtable values (1, 'def');
select a,b from newtable;

-- overwrite the table with auto-generated partitions
sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_d.hive.sql;

prepare s4 from 
  select c_preferred_cust_flag,
         count(*) 
  from customer_ddl 
  group by 1 
  order by 1
  ;
prepare s4part from
  -- selecting part col not supported right now
  select --c_preferred_cust_flag,
         count(*) 
  from customer_bp 
  --group by 1 
  --order by 1
  ;
execute s2;
execute s4;
execute s2part;
execute s4part;
select count(*) from tbl_utf8;
select * from tbl_utf8 where id between 8 and 12;
select * from tbl_utf8 where chapter like '%三%';
select * from tbl_utf8 where chapter like '%海印_昧%';

insert into tbl_utf8_temp 
select * from tbl_utf8;

select count(*) from tbl_utf8_temp;
select * from tbl_utf8_temp where id between 8 and 12;
select * from tbl_utf8_temp where chapter like '%海印_昧%';

select count(*) from tbl_utf8p;
select * from tbl_utf8p where id between 8 and 12;
select * from tbl_utf8p where chapter like '%海印_昧%';

select * from tbl_type;
insert into tbl_type_temp select * from tbl_type;
select * from tbl_type_temp;


cqd HIVE_FILE_CHARSET 'GBK';
select c1, CONVERTTOHEX(c2) from tbl_gbk;
cqd HIVE_FILE_CHARSET reset;

cqd HIVE_SCAN_SPECIAL_MODE '1';
select * from tbl_dos;
cqd HIVE_SCAN_SPECIAL_MODE reset;
drop table if exists trafodion.seabase.tbl_dos_num;
create table trafodion.seabase.tbl_dos_num (c1 int, c2 int);
load with NO OUTPUT into trafodion.seabase.tbl_dos_num select * from tbl_dos_num;
cqd HIVE_SCAN_SPECIAL_MODE '1';
load with no output into trafodion.seabase.tbl_dos_num select * from tbl_dos_num;
select * from trafodion.seabase.tbl_dos_num;
cqd HIVE_SCAN_SPECIAL_MODE reset;
select * from tbl_bad;
cqd HIVE_SCAN_SPECIAL_MODE '2';
select * from tbl_bad;
cqd HIVE_SCAN_SPECIAL_MODE reset;
drop table if exists trafodion.seabase.traf_tbl_bad;
create table trafodion.seabase.traf_tbl_bad (
c1 int,
c2 largeint,
c3 varchar(25),
c4 real,
c5 smallint,
c6 timestamp(6),
c7 float(54),
c8 smallint
);
load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
select count(*) from trafodion.seabase.traf_tbl_bad;
load with continue on error into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
select count(*) from trafodion.seabase.traf_tbl_bad;
delete from trafodion.seabase.traf_tbl_bad ;
load with log error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
select count(*) from trafodion.seabase.traf_tbl_bad;
load with log error rows to '/user/trafodion/bulkload/logs/TEST005' into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
select count(*) from trafodion.seabase.traf_tbl_bad;
delete from trafodion.seabase.traf_tbl_bad ;
load with stop after 3 error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
select count(*) from trafodion.seabase.traf_tbl_bad;
load with log error rows, stop after 3 error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
select count(*) from trafodion.seabase.traf_tbl_bad;
insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
cqd HIVE_SCAN_SPECIAL_MODE '2';
load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
cqd HIVE_SCAN_SPECIAL_MODE reset;
drop table if exists trafodion.seabase.traf_tbl_bad;
create table trafodion.seabase.traf_tbl_bad (
c1 int not null,
c2 largeint not null,
c3 varchar(25),
c4 real,
c5 smallint,
c6 timestamp(6) not null,
c7 float(54) not null,
c8 smallint
);
cqd HIVE_SCAN_SPECIAL_MODE '2';
insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
cqd HIVE_SCAN_SPECIAL_MODE reset;

-- tests for hive timestamp mismatch check
cqd auto_query_retry_warnings 'ON';

process hive statement 'drop table thive';
process hive statement 'create table thive(a int)';

select a from hive.hive.thive;

sh echo "insert into thive values (1);" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk;

select a from hive.hive.thive;
insert into hive.hive.thive values (2);
select a from hive.hive.thive;

process hive statement 'drop table thive';
process hive statement 'create table thive(a int, b int)';

sh echo "insert into thive values (1,2);" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk;

select a from hive.hive.thive;

select * from hive.hive.thive;

-- truncate of hive data
--cqd query_cache '0';
truncate hive.hive.thive;
select * from hive.hive.thive;
insert into hive.hive.thive values (10, 20);
select * from hive.hive.thive;
truncate hive.hive.thive;
truncate hive.hive.thive;
select * from hive.hive.thive;

-- truncate of partitioned hive table
process hive statement 'drop table t005part';
process hive statement 'create table t005part(a int) partitioned by (b int, c int)';

sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk;
sh echo "select * from t005part;" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk | tee -a LOG005;

truncate hive.hive.t005part;
sh echo "select * from t005part;" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk | tee -a LOG005;

sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk;
truncate hive.hive.t005part partition ('b=10');
sh echo "select * from t005part;" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk | tee -a LOG005;


sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk;
truncate hive.hive.t005part partition ('b=10','c=11');
sh echo "select * from t005part;" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk | tee -a LOG005;

sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk;
sh echo "insert into t005part partition (b=10,c=12) values (6);" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk;
sh echo "select * from t005part;" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk | tee -a LOG005;

truncate hive.hive.t005part partition ('b=10','c=11');
sh echo "select * from t005part;" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk | tee -a LOG005;

truncate hive.hive.t005part partition ('b=10');
sh echo "select * from t005part;" > TEST005_junk;
sh regrhive.ksh -f TEST005_junk | tee -a LOG005;

-- should return error
truncate hive.hive.t005part partition ('b=12');

-- should return error
purgedata hive.hive.thive;

-- tests for hive insert error modes
invoke hive.hive.thive_insert_smallint;
showddl hive.hive.thive_insert_smallint;

truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '0';
insert into hive.hive.thive_insert_smallint select * from 
 (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;

truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '1';
insert into hive.hive.thive_insert_smallint select * from
 (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;

truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '2';
insert into hive.hive.thive_insert_smallint select * from 
 (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;

truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '3';
insert into hive.hive.thive_insert_smallint select * from
 (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;

cqd hive_max_string_length_in_bytes '2';
invoke hive.hive.thive_insert_varchar;
showddl hive.hive.thive_insert_varchar;
cqd hive_insert_error_mode '1';
truncate hive.hive.thive_insert_varchar;
insert into hive.hive.thive_insert_varchar values ('abcddcba','efghijkl');

cqd hive_max_string_length_in_bytes '20';
select * from hive.hive.thive_insert_varchar;


log;