core/sql/regress/hive/TEST007

-- Tests for trafodion views on hive tables and native hive views
--
-- @@@ 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 @@@

obey TEST007(clean_up_traf);
obey TEST007(clean_up_hive);

initialize authorization;
register user sql_user3 as sql_user3;

log LOG007 clear;
obey TEST007(setup_traf);
obey TEST007(tests_traf);
obey TEST007(error_tests_traf);

obey TEST007(setup_hive);
obey TEST007(tests_hive);
obey TEST007(error_tests_hive);

log;
obey TEST007(clean_up_traf);
obey TEST007(clean_up_hive);

exit;

?section clean_up_traf
drop view trafodion.sch007.vhive1 cascade;
drop view trafodion.sch007.vhive11 cascade;
drop view trafodion.sch007.vhive2 cascade;
drop view trafodion.sch007.vhive3 cascade;
drop view trafodion.sch007.vhive4 cascade;
drop view trafodion.sch007.vhive5 cascade;
drop view trafodion.sch007.vhive6 cascade;
drop external table thive1 for hive.hive.thive1 cascade;
cleanup table trafodion."_HV_HIVE_".thive1;
drop external table thive2 for hive.hive.thive2 cascade;
process hive statement 'drop table thive1';
process hive statement 'drop table thive2';
drop external table thive3 for hive.hive.thive3;
process hive statement 'drop table thive3';
drop table if exists trafodion.sch007.thbase1 cascade;

?section setup_traf
set schema hive.hive;
cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '20' ;
create schema if not exists trafodion.sch007;

?section tests_traf
-- tests for views on hive tables
process hive statement 'drop table thive1';
process hive statement 'create table thive1(a int, b int)';
sh echo "insert into thive1 values (1, 2);" > TEST007_junk;
sh regrhive.ksh -f TEST007_junk;

process hive statement 'drop table thive2';
process hive statement 'create table thive2(a int, b int)';
sh echo "insert into thive2 values (1, 2);" > TEST007_junk;
sh regrhive.ksh -f TEST007_junk;

create external table thive2 for hive.hive.thive2;

create view trafodion.sch007.vhive1 as select * from thive1;
showddl hive.hive.thive1;

create view trafodion.sch007.vhive11 as select * from thive2;
create view trafodion.sch007.vhive2 as select x.a, y.b from thive1 x, thive2 y
  where x.a < 2 and x.b = y.b;
create view trafodion.sch007.vhive3 as select * from trafodion.sch007.vhive2;

-- view on hive and traf tables
drop table if exists trafodion.sch007.thbase1;
create table trafodion.sch007.thbase1 (aa int, bb int);
create view trafodion.sch007.vhivehbase as select * from 
        trafodion.sch007.thbase1, thive2 where a = aa;
insert into trafodion.sch007.thbase1 values (1,2);
invoke trafodion.sch007.vhivehbase;
select * from trafodion.sch007.vhivehbase;

select * from thive1;
insert into trafodion.sch007.vhive1 values (3,4);
select * from thive1;

prepare s from select * from trafodion.sch007.vhive1;
explain options 'f' s;
execute s;

prepare s from select * from trafodion.sch007.vhive2;
explain options 'f' s;
execute s;

prepare s from select * from trafodion.sch007.vhive3;
explain options 'f' s;
execute s;

get tables in view trafodion.sch007.vhive1;
get tables in view trafodion.sch007.vhive11;

get all tables in view trafodion.sch007.vhive3;
get views in view trafodion.sch007.vhive3;
get views on table hive.hive.thive1;
get all views on table hive.hive.thive2;

drop external table thive2 for hive.hive.thive2 cascade;
showddl hive.hive.thive2;
showddl trafodion.sch007.vhive3;
get all views on table hive.hive.thive2;

?section error_tests_traf

create view trafodion.sch007.vhive4 as select x.a, y.b from thive1 x, thive1 y;

-- insert not allowed
insert into trafodion.sch007.vhive4 values (3,4);

-- del/upd not allowed
delete from trafodion.sch007.vhive1;
update trafodion.sch007.vhive1 set b = 1;

--  traf view is not updatable
delete from trafodion.sch007.vhive4;

-- traf view must be in traf cat/sch
create view vhive5 as select * from thive1;

-- if underlying hive table is dropped, an error is returned
prepare s from select * from trafodion.sch007.vhive1;
execute s;
process hive statement 'drop table thive1';
execute s;
select * from trafodion.sch007.vhive1;
unregister hive table if exists hive.hive.thive1;
select * from hive.hive.thive1;
cleanup hive table hive.hive.thive1;
select * from hive.hive.thive1;

?section clean_up_hive
unregister hive table hive.hive.thive1 cleanup;

unregister hive view hive.hivesch007.vhive1 cascade cleanup;
unregister hive view hive.hivesch007.vhive11 cascade cleanup;

process hive statement 'drop table hivesch007.thive1';
process hive statement 'drop view hivesch007.vhive11';
process hive statement 'drop view hivesch007.vhive1';
process hive statement 'drop database hivesch007 cascade';
process hive statement 'drop table thive007';
process hive statement 'drop view vhive007';

cleanup hive view hive.hivesch007.vhive11;

drop view trafodion.sch007.vtrafonhive;

drop schema trafodion.sch007 cascade;

process hive statement 'drop table hivesch0071.h1';
process hive statement 'drop view hivesch0071.vh1';
process hive statement 'drop view hivesch0071.vh11';

process hive statement 'drop table hivesch007.thive9';
drop view trafodion.sch007.vhive9;
drop external table thive9 for hive.hivesch007.thive9;

process hive statement 'drop database hivesch0071 cascade';

?section setup_hive
process hive statement 'create database hivesch007';

process hive statement 'create table hivesch007.thive1 (a int)';

sh echo "insert into hivesch007.thive1 values (1);" > TEST007_junk;
sh regrhive.ksh -f TEST007_junk;

process hive statement 'create view hivesch007.vhive1 as select * from hivesch007.thive1 where thive1.a > 0';

process hive statement 'create view hivesch007.vhive11 as select * from hivesch007.vhive1 where vhive1.a > 0';

process hive statement 'create table thive007 (a int)';

process hive statement 'create view vhive007 as select * from thive007 where a > 0';

insert into hive.hive.thive007 values (1);

?section tests_hive
invoke hive.hivesch007.vhive1;
showddl hive.hivesch007.vhive1;

prepare s from select * from hive.hivesch007.vhive1;
explain options 'f' s;
execute s;

invoke hive.hivesch007.vhive11;
showddl hive.hivesch007.vhive11;

prepare s from select * from hive.hivesch007.vhive11;
explain options 'f' s;
execute s;

prepare s from select * from hive.hive.vhive007;
explain options 'f' s;
execute s;

get tables in schema hive.hivesch007;
get views in schema hive.hivesch007;

get tables in schema hive.hive, match '%007%';
get views in schema hive.hive, match '%007%';

get tables in catalog hive, match 'hivesch007%';
get views in catalog hive, match 'hivesch007%';

set schema hive.hive;
prepare s from select * from vhive007 x, hive.hivesch007.vhive1 y
  where x.a = y.a;
explain options 'f' s;
execute s;

-- create traf view on native hive view
create schema if not exists trafodion.sch007;
create view trafodion.sch007.vtrafonhive as select * from hive.hivesch007.vhive11;
showddl trafodion.sch007.vtrafonhive;
prepare s from select * from trafodion.sch007.vtrafonhive;
explain options 'f' s;
execute s;
get tables in view trafodion.sch007.vtrafonhive;
get views in view trafodion.sch007.vtrafonhive;
get views on view hive.hivesch007.vhive11;

unregister hive view hive.hivesch007.vhive11 cascade;
get tables in view hive.hivesch007.vhive11;
get views on table hive.hivesch007.thive1;

register hive view hive.hivesch007.vhive11 cascade;
showddl hive.hivesch007.vhive11;
get tables in view hive.hivesch007.vhive11;
get views in view hive.hivesch007.vhive11;
get views on table hive.hivesch007.thive1;
get views on view hive.hivesch007.vhive1;

-- tests for recursive hive view/table register/unregister operations
process hive statement 'create database hivesch0071';
process hive statement 'create table hivesch0071.h1 (a int)';
process hive statement 'create view hivesch0071.vh1 as select * from hivesch0071.h1';
process hive statement 'create view hivesch0071.vh11 as select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1';

set schema hive.hivesch0071;
showddl hive.hivesch0071.vh11;
get tables in view vh11;
get views in view vh11;
get all objects in view vh11;

register hive view vh11 cascade;
get tables in view vh11;
get views in view vh11;
get all objects in view vh11;

get hive registered tables in catalog trafodion, match '%hivesch0071%';
get hive registered views in catalog trafodion, match '%hivesch0071%';
get hive registered objects in catalog trafodion, match '%hivesch0071%';

select object_name from trafodion."_MD_".objects where schema_name = 'HIVESCH0071';

unregister hive view vh11 cascade;
get hive registered objects in catalog trafodion, match '%hivesch0071%';

showddl vh11;

select object_name from trafodion."_MD_".objects where schema_name = 'HIVESCH0071';

-- register hive schemas
get schemas in catalog hive, match '%hivesch0078%';
get hive registered schemas in catalog trafodion, match '%hivesch0078%';
showddl schema hive.hivesch0078;
get tables in schema hive.hivesch0078;
process hive statement 'create database hivesch0078';
showddl schema hive.hivesch0078;
register hive schema hive.hivesch0078;
showddl schema hive.hivesch0078;
get hive registered schemas in catalog trafodion, match '%hivesch0078%';
unregister hive schema hive.hivesch0078;
showddl schema hive.hivesch0078;
get hive registered schemas in catalog trafodion, match '%hivesch0078%';
process hive statement 'drop database hivesch0078';
showddl schema hive.hivesch0078;

?section error_tests_hive
insert into hive.hivesch007.vhive1 values (1);
delete from hive.hivesch007.vhive1;

-- drop underlying hive table
cqd auto_query_retry_warnings 'ON';

process hive statement 'drop table hivesch007.thive1';

prepare s from select * from hive.hivesch007.vhive11;
execute s;

process hive statement 'create table hivesch007.thive1 (a int)';

prepare s from select * from hive.hivesch007.vhive11;
execute s;

process hive statement 'drop table hivesch007.thive1';

execute s;

-- test compatibility with hive tables created prior to registration change)
set schema hive.hivesch007;
process hive statement 'drop table hivesch007.thive9';
process hive statement 'create table hivesch007.thive9 (a int)';
unregister hive table if exists hive.hivesch007.thive9;
cqd hive_no_register_objects 'ON';
drop external table if exists thive9 for hive.hivesch007.thive9;
create external table thive9 for hive.hivesch007.thive9;
showddl thive9;
grant select on hive.hivesch007.thive9 to sql_user3;
get privileges for user sql_user3, match '%HIVESCH007%';
insert into hive.hivesch007.thive9 values (1), (2), (3), (4), (5), (6);
update statistics for table hive.hivesch007.thive9 on every column;
showstats for table hive.hivesch007.thive9 on every column;
create view trafodion.sch007.vhive9 as select * from hive.hivesch007.thive9;
showddl hive.hivesch007.thive9;

register hive table hive.hivesch007.thive9;
showddl thive9;
showstats for table hive.hivesch007.thive9 on every column;
unregister hive table hive.hivesch007.thive9;
showddl thive9;
showstats for table hive.hivesch007.thive9 on every column;
drop view trafodion.sch007.vhive9;
drop external table thive9 for hive.hivesch007.thive9;

showddl thive9;
showstats for table hive.hivesch007.thive9 on every column;