core/sql/regress/seabase/TEST022

-- tests for access to external native hbase tables.
--
-- @@@ 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 TEST022(clean_up);

log LOG022 clear;

cqd hbase_native_iud 'ON';
cqd hbase_filter_preds 'ON';

drop hbase table T022HBT1;
create hbase table T022HBT1 (column family 'cf');

drop hbase table T022HBT2;
create hbase table T022HBT2 (column family 'cf');

drop table if exists t022t1;
create table if not exists t022t1 (a int not null primary key, b int not null);

insert into hbase."_CELL_".t022hbt1 values ('1', 'cf', '1a', -1, '101');
insert into hbase."_ROW_".t022hbt1 values ('2', column_create('cf:2b', '201')),
                              ('1', column_create('cf:1b', '102'));
prepare s from insert into hbase."_ROW_".t022hbt1 values 
         (?, column_create((?, ?), ('cf:3b', '302')));
execute s using '3', 'cf:3a', '301';

select left(row_id, 10) row_id, column_display(column_details, 40) column_details
           from hbase."_ROW_".t022hbt1;
select left(row_id, 10) row_id, left(column_display(column_details), 40) 
           from hbase."_ROW_".t022hbt1;
select left(row_id, 10) row_id, left(column_display(column_details, ('cf:2b', 'cf:1b')), 40) 
           from hbase."_ROW_".t022hbt1;

-- no rows updated. where pred fails.
update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:a', 
          (cast(column_lookup(column_details, 'cf:a') as varchar(10)) || '0103'))
   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '3021';

-- no rows updated. column not found in set clause.
update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:a', 
          (cast(column_lookup(column_details, 'cf:a') as varchar(10)) || '0103'))
   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302';

begin work;
-- one row updated
update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:3a', 
          (cast(column_lookup(column_details, 'cf:3a') as varchar(10)) || '0103'))
   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302';

select left(row_id, 10) row_id, left(column_display(column_details), 50) 
           from hbase."_ROW_".t022hbt1;
rollback work;

select left(row_id, 10) row_id, left(column_display(column_details), 50) 
           from hbase."_ROW_".t022hbt1;

begin work;
-- one row updated
update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:3a', 
          (cast(column_lookup(column_details, 'cf:3a') as varchar(10)) || '0103'))
   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302';

select left(row_id, 10) row_id, left(column_display(column_details), 50) 
           from hbase."_ROW_".t022hbt1;
commit work;

select left(row_id, 10) row_id, left(column_display(column_details), 50) 
           from hbase."_ROW_".t022hbt1;

begin work;
insert into hbase."_ROW_".t022hbt1 values 
       ('4', column_create(('cf:4a', '301'), ('cf:4b', '302')));
select left(row_id, 10) row_id, left(column_display(column_details), 40) 
           from hbase."_ROW_".t022hbt1 where row_id = '4';
rollback work;

select col_family, col_name, left(col_value, 20) from hbase."_CELL_".t022hbt1;
select left(row_id, 10) row_id, left(column_display(column_details), 40) 
           from hbase."_ROW_".t022hbt1 where row_id = '4';

select left(column_lookup (column_details, 'cf:1a'), 20) from hbase."_ROW_".t022hbt1;

select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1
  where column_lookup (column_details, 'cf:3b') = '302';

select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1
  where column_lookup (column_details, 'cf:3a') = '301' 
     and column_lookup (column_details, 'cf:3b') = '302';

select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1
  where column_lookup (column_details, 'cf:3a') = '3010103' 
     and column_lookup (column_details, 'cf:3b') = '302';

select left(column_lookup (column_details, 'cf:3c'), 20) from hbase."_ROW_".t022hbt1
  where column_lookup (column_details, 'cf:3b') = '3020';

select left(column_lookup (column_details, 'cf:3c'), 20) from hbase."_ROW_".t022hbt1
  where column_lookup (column_details, 'cf:3b') = '302';

select column_lookup (column_details, 'cf:3a', cast as int),
       column_lookup (column_details, 'cf:3a', cast as int) + 1 
  from hbase."_ROW_".t022hbt1;

select column_lookup (column_details, 'cf:3a', cast as int),
       column_lookup (column_details, 'cf:3a', cast as int) + 1 
  from hbase."_ROW_".t022hbt1
   where 
       column_lookup (column_details, 'cf:3b', cast as int) = 302;

begin work;
delete from hbase."_ROW_".t022hbt1 where row_id = '3';
select left(row_id, 10) from hbase."_ROW_".t022hbt1;
commit work;
select left(row_id, 10) from hbase."_ROW_".t022hbt1;

delete columns ('cf:1a') from hbase."_ROW_".t022hbt1  where row_id  = '1';
select left(row_id, 10) row_id, column_display(column_details, 40) column_details
           from hbase."_ROW_".t022hbt1;

delete from hbase."_ROW_".t022hbt1;
select count(*) from hbase."_ROW_".t022hbt1;

-- operations between trafodion and hbase tables
begin work;
insert into t022t1 values (1, 100);
insert into hbase."_ROW_".t022hbt1 values ('1', column_create('cf:a', '100'));
select count(*) from t022t1;
select count(*) from hbase."_ROW_".t022hbt1;
rollback work;
select count(*) from t022t1;
select count(*) from hbase."_ROW_".t022hbt1;

insert into hbase."_ROW_".t022hbt1 values 
       ('3', column_create(('cf:3a', '301'), ('cf:3b', '302')));
update hbase."_ROW_".t022hbt1 set column_details = column_create('cf:3b', '3022')
  where row_id = '3' and column_lookup(column_details, 'cf:3a', cast as int) = 3011;
update hbase."_ROW_".t022hbt1 set column_details = column_create('cf:3b', '3022')
  where row_id = '3' and column_lookup(column_details, 'cf:3a', cast as int) = 301;
select left(row_id, 10) row_id, left(column_display(column_details), 40) 
           from hbase."_ROW_".t022hbt1;

-- negative tests
delete from hbase."_CELL_".t022hbt1;
insert into hbase."_ROW_".t022hbt1 values ('2', column_create(':b', '201'));
insert into hbase."_ROW_".t022hbt1 values ('2', '100');
insert into hbase."_ROW_".t022hbt1 select * from hbase."_ROW_".t022hbt2;

-- tests with large rows
invoke hbase."_CELL_".t022hbt2;
invoke hbase."_ROW_".t022hbt2;
cqd hbase_max_column_val_length '100000';
cqd hbase_max_column_info_length '100000';
invoke hbase."_CELL_".t022hbt2;
invoke hbase."_ROW_".t022hbt2;
insert into hbase."_CELL_".t022hbt2 values ('1', 'cf', 'a', -1, repeat('a', 100000));
insert into hbase."_ROW_".t022hbt2 values 
                              ('2', column_create(
                                       ('cf:1a', repeat('a', 40000)),
                                       ('cf:1b', repeat('z', 40000))));
select count(*) from hbase."_CELL_".t022hbt2;
select char_length(col_value) from hbase."_CELL_".t022hbt2 order by 1;
select count(*) from hbase."_CELL_".t022hbt2;
select char_length(col_value) from hbase."_CELL_".t022hbt2 order by 1;
select count(*) from hbase."_ROW_".t022hbt2;
select char_length(column_details) from hbase."_ROW_".t022hbt2 order by 1;
select left(row_id, 10) row_id, left(column_display(column_details), 40) 
           from hbase."_ROW_".t022hbt2;


-- tests to map hbase tables to relational traf tables
cqd traf_hbase_mapped_tables 'ON';

drop hbase table t022hbm1;
create hbase table t022hbm1 (column family 'cf');
insert into hbase."_ROW_".t022hbm1 values ('a1', 
                 column_create(('cf:B', '100 ')));
insert into hbase."_ROW_".t022hbm1 values ('a2', column_create(('cf:A', 'a2')));

drop table if exists t022hbm1 cascade;
drop external table if exists t022hbm1;
create external table t022hbm1 (a varchar(4) not null, b char(4))
        primary key (a)
        attribute default column family 'cf'
        map to hbase table t022hbm1;
invoke t022hbm1;

-- if no schema is specified, table is looked in regular and then mapped schema
invoke t022hbm1;
create table t022hbm1 (a int);
invoke t022hbm1;

-- join between traf and hbase table with the same name.
prepare s from select * from t022hbm1, hbase."_MAP_".t022hbm1;
explain options 'f' s;

drop table t022hbm1;
invoke t022hbm1;

-- should return error 4056
prepare s from select * from t022hbm1, hbase."_MAP_".t022hbm1;

prepare s from select * from t022hbm1 x, hbase."_MAP_".t022hbm1 y;

prepare s from select * from t022hbm1;
execute s;
select * from t022hbm1 where a = 'a1';
select a, cast(b as int) from t022hbm1;

alter table t022hbm1 add column "cf".c int;
invoke t022hbm1;

-- create a traf table like a mapped table
cqd schema reset;
create table t022hbm1_like like t022hbm1;
invoke t022hbm1_like;

insert into t022hbm1_like select * from t022hbm1;
select * from t022hbm1_like;

create table t022hbm1_ctas as select * from t022hbm1;
select * from t022hbm1_ctas;

-- create view on mapped table
create view t022v1 as select * from t022hbm1;

drop external table t022hbm1 cascade;
invoke t022hbm1;
create external table t022hbm1 ("cf".a varchar(4) not null,
            b int)
        primary key (a)
        map to hbase table t022hbm1;
invoke t022hbm1;

alter table t022hbm1 drop column b;
invoke t022hbm1;

alter table t022hbm1 add column "cf".b int;
invoke t022hbm1;

-- IUD operations on mapped tables
cqd traf_hbase_mapped_tables_iud 'ON';

cqd schema reset;
delete from hbase."_ROW_".t022hbm1;
drop external table t022hbm1;
create external table t022hbm1 (a varchar(4) not null, b int) 
        primary key (a)
        attribute default column family 'cf'
        map to hbase table t022hbm1
        data format native;
--This part of the test is being commented out due to non deterministic results
--It needs to be renabled after this JIRA is fixed : TRAFODION-2613
--insert into t022hbm1 values ('a', 1);
--select * from t022hbm1;
--update t022hbm1 set b = b + 1;
--select * from t022hbm1;
--insert into t022hbm1 values ('a', 1); -- should fail
--insert into t022hbm1 values ('b', null);
--select * from t022hbm1;
--delete from t022hbm1 where a = 'a';
--select * from t022hbm1;
--update t022hbm1 set b = 10;
--select * from t022hbm1;
--update t022hbm1 set b = null;
--select * from t022hbm1;
--delete from t022hbm1;
--select * from t022hbm1;

drop external table t022hbm1;
create external table t022hbm1 (a int not null, b int not null, c int) 
        primary key (a, b)
        attribute default column family 'cf'
        map to hbase table t022hbm1
        data format native;
invoke t022hbm1;
insert into t022hbm1 values (1,2,3);
insert into t022hbm1 values (1,2,3);
insert into t022hbm1 values (1, 1, 1);
insert into t022hbm1 values (-1, -2, -3);
select * from t022hbm1 order by 1;
upsert into t022hbm1 values (1,2,4);
select * from t022hbm1 order by 1;

-- various serialization options
drop external table t022hbm1;
create external table t022hbm1 (a varchar(4) not null, primary key not serialized (a), b int) 
        attribute default column family 'cf'
        map to hbase table t022hbm1;
invoke t022hbm1;

drop external table t022hbm1;
create external table t022hbm1 (a char(4) not null, primary key serialized (a), b int) 
        attribute default column family 'cf'
        map to hbase table t022hbm1
        data format native;
invoke t022hbm1;

drop external table t022hbm1;
create external table t022hbm1 (a varchar(4) not null, primary key(a), b int) 
        attribute default column family 'cf'
        map to hbase table t022hbm1;
invoke t022hbm1;

drop external table t022hbm1;
create external table t022hbm1 (a varchar(4) not null primary key, b int) 
        attribute default column family 'cf'
        map to hbase table t022hbm1;
invoke t022hbm1;

drop external table t022hbm1;
create external table t022hbm1 (a varchar(4) not null, b int) 
        primary key not serialized (a)
        attribute default column family 'cf'
        map to hbase table t022hbm1;
invoke t022hbm1;

drop external table t022hbm1;
create external table t022hbm1 (a char(4) not null, b int) 
        primary key serialized (a) 
        attribute default column family 'cf'
        map to hbase table t022hbm1
        data format native;
invoke t022hbm1;

drop external table t022hbm1;
create external table t022hbm1 (a varchar(4) not null, b int) 
        primary key (a)
        attribute default column family 'cf'
        map to hbase table t022hbm1;
invoke t022hbm1;

drop hbase table t022hbm1;
create hbase table t022hbm1 (column family 'cf');
drop external table if exists t022hbm1;
create external table t022hbm1 (a varchar(4) not null, b int) 
        primary key (a) 
        attribute default column family 'cf'
        map to hbase table t022hbm1;
insert into hbase."_ROW_".t022hbm1 values ('a1', column_create(('cf:B', '10')));

-- multi column families in mapped table
drop external table if exists t022hbm2;
drop hbase table t022hbm2;
create hbase table t022hbm2 (column family 'cf1', column family 'cf2');
create external table t022hbm2 ("cf1".A int, "cf2".B int, 
                           "cf1".Z varchar(4) not null primary key) 
     map to hbase table t022hbm2;
insert into hbase."_ROW_".t022hbm2 values ('a11', 
           column_create(('cf1:A', '10'), ('cf2:B', '20')));
invoke t022hbm2;
select * from t022hbm2;
cleanup hbase table t022hbm2;

-- registration of external hbase tables
drop external table if exists t022hbm2;
drop hbase table t022hbm2;
create hbase table t022hbm2 (column family 'cf1', column family 'cf2');
create external table t022hbm2 ("cf1".A int, "cf2".B int, 
                           "cf1".Z varchar(4) not null primary key) 
     map to hbase table t022hbm2;

insert into hbase."_ROW_".t022hbm2 values ('a11', 
           column_create(('cf1:A', '10'), ('cf2:B', '20')));
invoke t022hbm2;
select * from t022hbm2;

showddl hbase."_CELL_".t022hbm2;
get hbase registered tables in catalog trafodion, match '%T022HBM2%';
unregister hbase table t022hbm2;
register hbase table t022hbm2;
get hbase registered tables in catalog trafodion, match '%T022HBM2%';
showddl hbase."_CELL_".t022hbm2;
showddl hbase."_ROW_".t022hbm2;
unregister hbase table t022hbm2;
get hbase registered tables in catalog trafodion, match '%T022HBM2%';
showddl hbase."_CELL_".t022hbm2;

--showstats for table hbase."_CELL_".t022hbm2 on every column;
update statistics for table hbase."_CELL_".t022hbm2 on every column;
showstats for table hbase."_CELL_".t022hbm2 on every column;
get hbase registered tables in catalog trafodion, match '%T022HBM2%';
showddl hbase."_CELL_".t022hbm2;

showstats for table hbase."_ROW_".t022hbm2 on every column;
update statistics for table hbase."_CELL_".t022hbm2 on every column;
showstats for table hbase."_CELL_".t022hbm2 on every column;
get hbase registered tables in catalog trafodion, match '%T022HBM2%';
showddl hbase."_CELL_".t022hbm2;

showstats for table hbase."_MAP_".t022hbm2 on every column;
update statistics for table hbase."_MAP_".t022hbm2 on every column;
showstats for table hbase."_MAP_".t022hbm2 on every column;
showddl hbase."_MAP_".t022hbm2;

-- error cases

-- primary key cannot be missing
select * from t022hbm1;

-- operations not allowed
alter table t022hbm1 alter column "cf".b largeint;
invoke t022hbm1;

set schema trafodion."_HB_MAP_";

-- cannot invoke using map schema name
invoke "_HB_MAP_".t022hbm1;

-- cannot use "_HB_MAP_" in a table name
prepare s from select * from t022hbm1;
prepare s from select * from "_HB_MAP_".t022hbm1;

drop table "_HB_MAP_".t022hbm1;
alter table trafodion."_HB_MAP_".t022hbm1 drop column b;

set schema trafodion.sch;

drop external table if exists t022hbm1;

-- cannot specify serialized option
create external table t022hbm1 (a varchar(4) not null, b int) 
        primary key serialized (a) 
        attribute default column family 'cf'
        map to hbase table t022hbm1;

-- cannot be aligned format
create external table t022hbm1 (a varchar(4) not null primary key) 
  attribute aligned format map to hbase table t022hbm1;

-- must specify pkey
create external table t022hbm1 (a char(4)) map to hbase table t022hbm1;

-- external and hbase table names must be the same
create external table t022hbm11 (a char(4) not null primary key) 
        map to hbase table t022hbm1;

-- all non-pkey columns must be nullable
create external table t022hbm1 (a varchar(4) not null primary key,
              b int not null)
              map to hbase table t022hbm1;

-- all non-pkey columns must have default value of null
create external table t022hbm1 (a varchar(4) not null primary key,
              b int default 10)
              map to hbase table t022hbm1;

-- mapped table already exist
create external table t022hbm1 (a varchar(4) not null primary key) 
              map to hbase table t022hbm1;
create external table t022hbm1 (a varchar(4) not null primary key) 
              map to hbase table t022hbm1;

-- hbase table doesn't exist
create external table t022hbm11 (a char(4) not null primary key) 
                map to hbase table t022hbm11;

-- cannot create view in HB_MAP schema
create view "_HB_MAP_".v as select * from t022hbm1;

-- cannot create index on an hbase external table
create index ti on t022hbm1 (a);

drop external table if exists t022hbm1;
drop hbase table t022hbm1;
create hbase table t022hbm1 (column family 'cf');
create external table t022hbm1 (a varchar(4) not null, b int) 
        primary key (a) 
        attribute default column family 'cf'
        map to hbase table t022hbm1;
insert into hbase."_ROW_".t022hbm1 values ('a1', 
                               column_create(('cf:A', '10')));
-- rowID must match pkey col contents
select * from t022hbm1;

drop hbase table t022hbm1;
create hbase table t022hbm1 (column family 'cf');
create external table t022hbm1 (a varchar(4) not null, b int)
        primary key (a)
        attribute default column family 'cf'
        map to hbase table t022hbm1;
insert into hbase."_ROW_".t022hbm1 values ('a1234567', 
                               column_create(('cf:B', '10')));
-- primary key col length must be big enough to hold rowID
select * from t022hbm1;

drop hbase table t022hbm1;
create hbase table t022hbm1 (column family 'cf');
create external table t022hbm1 (a varchar(4) not null, b int)
        primary key (a)
        attribute default column family 'cf'
        map to hbase table t022hbm1;
insert into hbase."_ROW_".t022hbm1 values ('a1', 
                               column_create(('cf:B', '1000000')));
-- buffer to retrieve column value must be big enough
cqd hbase_max_column_val_length '5';
select * from t022hbm1;

log;

?section clean_up
cqd schema reset;
drop view t022v1;
drop hbase table t022hbt1;
drop hbase table t022hbt2;
drop hbase table t022hbm1;
drop hbase table t022hbm11;
drop hbase table t022hbm2;
drop table t022hbm1_like;
drop table t022hbm1_ctas;
drop table t022t1;
drop table t022hbm1 cascade;
drop table t022hbm2;