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

-- Tests to retrieve region/cluster space and access stats
-- Tests to retrieve metadata info about hive tables

obey TEST002(clean_up);

log LOG002 clear;
obey TEST002(setup);
obey TEST002(region_stats);
obey TEST002(cluster_stats);
obey TEST002(hive_md);
log;
exit;

?section setup
create schema t002sch;
set schema t002sch;
create table t002t1(a int not null primary key, 
     b int not null, c int not null);
create index t002t1i1 on t002t1(b);       
create index t002t1i2 on t002t1(c);       
insert into t002t1 values (1,2, 3);

sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1';
sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1I1';
sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1I2';

select * from t002t1;

create table t002t2(a int not null primary key, b int not null)
  salt using 4 partitions;
create index t002t2i1 on t002t2(b);       
insert into t002t2 values (1,2);

sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2';
sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2I1';

select * from t002t2;


?section region_stats
set schema t002sch;
invoke table(region stats ());
invoke table(region stats (t002t1));

select left(trim(schema_name) || '.' || trim(object_name), 14),
  region_num, region_name, num_stores, num_store_files,
  store_file_uncomp_size, store_file_size, mem_store_size,
  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
 from table(region stats(t002t1));

select left(trim(schema_name) || '.' || trim(object_name), 14),
  region_num, region_name, num_stores, num_store_files,
  store_file_uncomp_size, store_file_size, mem_store_size,
  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
 from table(region stats(index t002t1i1));

select left(trim(schema_name) || '.' || trim(object_name), 14),
  region_num, region_name, num_stores, num_store_files,
  store_file_uncomp_size, store_file_size, mem_store_size,
  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
 from table(region stats(t002t2));

select left(trim(schema_name) || '.' || trim(object_name), 14),
  region_num, region_name, num_stores, num_store_files,
  store_file_uncomp_size, store_file_size, mem_store_size,
  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
 from table(region stats(index t002t2i1));

select left(trim(schema_name) || '.' || trim(object_name), 14),
 sum(store_file_size), 
 'ReadRequestsCount: '  || cast(sum(read_requests_count) as varchar(10)),
 'WriteRequestsCount: ' || cast(sum(write_requests_count) as varchar(10))
 from table(region stats (using (select * from (
   get tables in schema t002sch, no header, return full names) x(a) ))) 
 group by 1 order by 2 desc;

select left(trim(schema_name) || '.' || trim(object_name), 14),
 sum(store_file_size), 
 'ReadRequestsCount: '  || cast(sum(read_requests_count) as varchar(10)),
 'WriteRequestsCount: ' || cast(sum(write_requests_count) as varchar(10))
 from table(region statistics (using (select * from (
   get tables in schema t002sch, no header, return full names) x(a) ))) 
 group by 1 order by 2 desc;

get region stats for table t002t1;
get region stats for index t002t1i1;
get region stats for table t002t2;
get region stats for index t002t2i1;

get region statistics for table t002t1;

get region stats for 
    (select * from 
      (get tables in schema t002sch, no header, return full names)x(a));

-- stats for all indexes on a table
select left(trim(schema_name) || '.' || trim(object_name), 14),
  region_num, region_name, num_stores, num_store_files,
  store_file_uncomp_size, store_file_size, mem_store_size,
  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
 from table(region stats (using (select * from (
   get indexes on table t002t1, no header, return full names) x(a) )));
get region stats for 
  (select * from 
    (get indexes on table t002t1, no header, return full names)x(a));

-- return summary stats only
get region stats for table t002t1, summary;
get region stats for index t002t1i1, summary;
get region stats for table t002t2, summary;
get region stats for index t002t2i1, summary;

get region stats for 
  (select * from 
    (get tables in schema t002sch, no header, return full names)x(a)), summary;

-- where preds
select cast(trim(schema_name) || '.' || trim(object_name) as char(30) character set iso88591),
 region_num, region_name, num_stores, num_store_files
 from table(region stats(t002t1))
 where object_name = 'T002T1';

select  cast(trim(object_name) as char(30) character set iso88591),
 sum(store_file_size)
 from table(region statistics (using (select * from (
   get tables in schema t002sch, no header, return full names) x(a) ))) 
 where object_name like 'T002T1%'
 group by 1 order by 2 desc;

?section cluster_stats
invoke table(cluster stats());

select cast(trim(schema_name) || '.' || trim(object_name) as char(30) character set iso88591),
 sum(store_file_size)
 from table(cluster stats())
 where schema_name = 'T002SCH'
 group by 1 order by 2 desc;

select cast('RegionName: ' || trim(region_name) as char(30) character set iso88591),
 sum(store_file_size)
 from table(cluster stats())
 where schema_name = 'T002SCH'
 group by 1 order by 2 desc;

?section hive_md
cqd hive_max_string_length_in_bytes '20';
invoke table(hivemd(tables));
invoke table(hivemd(columns));
select cast(table_name as char(30) character set iso88591), 
       file_format, num_cols, num_part_cols, num_bucket_cols, num_sort_cols,
       field_delimiter, record_terminator, hive_table_type
  from table(hivemd(tables, "hive", "warehouse"));
select cast(table_name as char(30) character set iso88591), 
       file_format, num_cols, num_part_cols, num_bucket_cols, num_sort_cols,
       field_delimiter, record_terminator, hive_table_type
  from table(hivemd(tables)) where table_name = 'warehouse';
select cast(column_name as char(30) character set iso88591), 
       sql_data_type, fs_data_type, hive_data_type,
       column_size, column_scale, 
       column_number, part_col_number, bucket_col_number, sort_col_number
  from table(hivemd(columns, "hive", "warehouse"))
  order by column_number;
select cast(column_name as char(30) character set iso88591), 
       sql_data_type, fs_data_type, hive_data_type,
       column_size, column_scale, 
       column_number, part_col_number, bucket_col_number, sort_col_number
  from table(hivemd(columns))
  where table_name = 'warehouse'
  order by column_number;

create table createOptions (a int) HBASE_OPTIONS 
(max_versions = '3',
 min_versions = '2',
 time_to_live = '100',
 blockcache = 'false',
 in_memory = 'true',
 compression = 'GZ',
 bloomfilter = 'ROWCOL',
 blocksize = '10000',
 data_block_encoding = 'DIFF',
 cache_blooms_on_write = 'false',
cache_data_on_write = 'false',
cache_indexes_on_write = 'false',
compact_compression = 'GZ',
prefix_length_key = '10',
evict_blocks_on_close = 'true',
keep_deleted_cells = 'false',
replication_scope = '0',
max_filesize = '4000000' ,
compact = 'true',
durability = 'async_wal',
memstore_flush_size = '2000000',
split_policy = 'org.apache.hadoop.hbase.regionserver.KeyPrefixRegionSplitPolicy',
CACHE_DATA_IN_L1 = 'false',
prefetch_blocks_on_open = 'false'
);

showddl createOptions ;

?section clean_up
set schema t002sch;
drop table t002t1 cascade;
drop table t002t2 cascade;
drop table createOptions ;
drop schema t002sch cascade;

