core/sql/regress/hive/TEST009

-- ============================================================================
-- TEST009 - tests external tables for hive
--
-- @@@ START COPYRIGHT @@@
--
--  Licensed 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 @@@
--
-- This tests the following commands:
--
--   create external table
--   drop external table
--
-- When local hadoop is executed, a set of hive tables in the hive default
-- schema are created.  This test assumes that the following hive tables 
-- exist:
--     customer
--     item
--     promotion
--
-- ============================================================================

obey TEST009(clean_up);
log LOG009 clear;
obey TEST009(set_up);
obey TEST009(create_db);
obey TEST009(test_hive1);
obey TEST009(test_hive2);
obey TEST009(test_hbase);
obey TEST009(test_ext);
log;
obey TEST009(clean_up);
exit;

?section clean_up

-- remove messed up table
drop external table t009t1 for hive.sch_t009.t009t1;
drop external table t009t2 for hive.sch_t009.t009t2;

cleanup table "_HV_SCH_T009_".t009t1;
-- remove schemas from Trafodion
drop schema if exists "_HV_HIVE_" cascade;
drop schema if exists "_HV_SCH_T009_" cascade;
drop schema if exists hive_t009 cascade;

-- remove data from hdfs
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/t009t1/*;
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/t009t2/*;

-- Remove external hive tables used by this test
drop external table if exists customer for hive.hive.customer cascade;
drop external table if exists item for hive.hive.item;
drop external table if exists promotion for hive.hive.promotion;

-- drop hbase tables
drop schema if exists "_HB__ROW__" cascade;
drop schema if exists "_HB__CELL__" cascade;

-- remove table from hbase
drop hbase table "baseball";

-- remove special schemas
set parserflags 131072;
drop schema "_HBASESTATS_" cascade;
drop schema "_HIVESTATS_" cascade;
cleanup table "_HB__CELL__".t028tbl1;
drop schema "_HB__CELL__" cascade;
reset parserflags 131072;

--cqd hive_use_ext_table_attrs 'ON';
cqd hive_max_string_length_in_bytes '20';

drop table bblike1;
drop table bblike2;
drop table t009hivecust1;
drop table t009hivecust2;

process hive statement 'drop view t009cust';

?section create_db

-- The version of hive installed does not support special characters
-- TDB - when hive is upgraded to a new version, add tests where
--   the hive schema contains special characters
sh regrhive.ksh -v -f $REGRTSTDIR/TEST009_a.hive.sql &> $REGRRUNDIR/LOG009_a.hive.log;
sh regrhbase.ksh $REGRTSTDIR/TEST009_create_hbase_objects.hbase &> $REGRRUNDIR/LOG009_create_hbase_tables.log ;

-- make sure external schemas exist in Trafodion
execute get_hv_schemas;
execute get_hb_schemas;

?section set_up
prepare get_hv_schemas from
select schema_name from "_MD_".objects
where (schema_name = '_HV_HIVE_' or schema_name = '_HV_SCH_T009_')
       and object_type = 'PS';

prepare get_hb_schemas from
select schema_name from "_MD_".objects
where schema_name like '\_HB\_\_%' escape '\' and object_type = 'PS';

?section test_hive1
-- create external tables for precreated tables
create external table customer for hive.hive.customer;
create external table item for hive.hive.item;
create external table promotion for hive.hive.promotion;

-- should see a schema called "_HV_HIVE_"
execute get_hv_schemas;

-- Verify tables were created
showddl trafodion."_HV_HIVE_".customer;
showddl trafodion."_HV_HIVE_".item;
showddl trafodion."_HV_HIVE_".promotion;

-- create external tables from hive schema sch_t009
create external table t009t1 for hive.sch_t009.t009t1;
create external table t009t2 for hive.sch_t009.t009t2;

execute get_hv_schemas;
showddl trafodion."_HV_SCH_T009_".t009t1;
showddl trafodion."_HV_SCH_T009_".t009t2;

select count(*) from hive.hive.customer;
select count(*) from hive.hive.item;
select count(*) from hive.hive.promotion;

select * from hive.sch_t009.t009t1;
select * from hive.sch_t009.t009t2;

-- fails since accesses to the external name are prohibited
select * from trafodion."_HV_SCH_T009_".t009t1;

set schema "_HV_HIVE_";

-- all these creates should fail, they are not supported yet
create table hive_customer like hive.hive.customer;
create table newtable1 like hive.hive.customer;
create table newtable2 like customer;
create table newtable3 (a int);

-- test creates with a different default schema
create schema hive_t009;
set schema hive_t009;

-- create like on hive tables/views
create table t009hivecust1 like hive.hive.customer;
invoke t009hivecust1;
create table newtable1 like hive.hive.customer;
process hive statement 'drop view t009cust';
process hive statement 'create view t009cust as select * from customer';
create table t009hivecust2 like hive.hive.t009cust;
invoke t009hivecust2;

-- these creates fail
create external table seabase.customer like hive.hive.customer;
create external table customer1 like hive.hive.customer;
create table t009t2 as select * from "_HV_SCH_T009_".t009t2;

-- this create succeeds
create table t009t1 like "_HV_SCH_T009_".t009t1;
drop table t009t1;
create table t009t1 as select * from hive.sch_t009.t009t1;

drop table t009t1;

drop external table "_HV_HIVE_".customer;
drop external table item for hive.hive.item;

?section test_hive2
-- drop data from the hive table and recreate with 4 columns
-- this causes the external table to be invalid

-- cleanup data from the old table, and create/load data with additional column
sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/t009t1/*;
sh regrhive.ksh -v -f $REGRTSTDIR/TEST009_b.hive.sql &> $REGRRUNDIR/LOG009_b.hive.log;;

-- should fail - column mismatch
select count(*) from hive.sch_t009.t009t1;
sh sqlci -i "TEST009(select_stmts)";

-- drop and recreate external table
select catalog_name, schema_name, object_name from table(natablecacheentries('user','local')) order by 1,2,3;
drop external table t009t1 for hive.sch_t009.t009t1;
select catalog_name, schema_name, object_name from table(natablecacheentries('user','local')) order by 1,2,3;
create external table t009t1 for hive.sch_t009.t009t1;
get tables in schema "_HV_SCH_T009_";
invoke hive.sch_t009.t009t1;
showddl trafodion."_HV_SCH_T009_".t009t1;

-- select should now succeed
select count(*) from hive.sch_t009.t009t1;
select * from hive.sch_t009.t009t1;

?section select_stmts
log LOG009;
select * from hive.sch_t009.t009t1;
select * from hive.sch_t009.t009t2;

?section test_hbase
select count(*) from hbase."_ROW_"."baseball";
select count(*) from hbase."_CELL_"."baseball";

select left(row_id, 10) as row_id, left(column_display(column_details, ('teams:team_number', 'games:visitor_team', 'games:game_time')), 100) as cols from hbase."_ROW_"."baseball";

-- create like on native hbase tables
create table bblike1 like hbase."_ROW_"."baseball";
invoke bblike1;
create table bblike2 like hbase."_CELL_"."baseball";
invoke bblike2;

-- error. Cannot create a non-hbase-mapped table
create external table "baseball" for hbase."_ROW_"."baseball";

?section test_ext
-- test external table attributes
set schema trafodion.sch;
drop external table if exists store_sales for hive.hive.store_sales;
create external table store_sales 
  for hive.hive.store_sales;
invoke hive.hive.store_sales;

set schema hive.hive;
prepare s from select * from store_sales where ss_item_sk = 1;
explain options 'c' s;

-- join with nested join
control query shape nested_join(scan(path 'CUSTOMER'), 
      scan(path 'STORE_SALES'));
prepare s from select * from customer, store_sales 
    where store_sales.ss_item_sk = customer.c_customer_sk;
explain options 'fc' s;
explain options 'p' s;

control query shape cut;
set schema trafodion.sch;
drop external table if exists date_dim for hive.hive.date_dim;
cqd volatile_table_find_suitable_key 'SYSTEM';
create external table date_dim 
  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date, 
   d_month_seq int, d_week_seq int, d_quarter_seq int, d_year int, d_dow int,
   d_moy int, d_dom int, d_qoy int, d_fy_year int, d_fy_quarter_seq int,
   d_fy_week_seq int,
   d_day_name varchar(120 bytes) character set utf8, d_quarter_name varchar(200 bytes) character set utf8, d_holiday varchar(100 bytes) character set utf8,
   d_weekend varchar(100 bytes) character set utf8, d_following_holiday varchar(100 bytes) character set utf8, 
   d_first_dom int, d_last_dom int, d_same_day_ly int, d_same_day_lq int,
   d_current_day varchar(100 bytes) character set utf8, d_current_week varchar(111 bytes) character set utf8,
   d_current_month varchar(200 bytes) character set utf8, d_current_quarter varchar(100 bytes) character set utf8, 
   d_current_year varchar(100 bytes) character set utf8)
  for hive.hive.date_dim;
invoke hive.hive.date_dim;
showddl hive.hive.date_dim;
prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';
explain options 'c' s;

drop external table if exists date_dim for hive.hive.date_dim;
create external table date_dim 
  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
  for hive.hive.date_dim;
invoke hive.hive.date_dim;
showddl hive.hive.date_dim;
prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';
explain options 'p' s;


-- error cases
drop external table if exists date_dim for hive.hive.date_dim;

-- column d_date_skk doesn't exist in native hive table
create external table date_dim 
  (d_date_skk int)
  for hive.hive.date_dim;

-- cannot have primary key on hive/text tables
drop external table if exists store_sales for hive.hive.store_sales;
create external table store_sales 
  for hive.hive.store_sales primary key (ss_item_sk);

-- column d_date_sk has incompatible type
create external table date_dim 
  (d_date_sk date)
  for hive.hive.date_dim;