core/sql/regress/seabase/TEST002 - trafodion - Git at Google

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

 -- purgedata should preserve regions
 get region stats for table t002t2;
 purgedata t002t2;
 get region stats for table t002t2;
 select * from t002t2;

 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));

 process hive statement 'drop table thive002';
 process hive statement 'create table thive002 (a int, b string)';
 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", "thive002"))
    where file_format = 'TEXTFILE';
 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 = 'thive002'
   and file_format = 'TEXTFILE';

 cqd nested_joins 'OFF';
 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", "thive002")) C,
        table(hivemd(tables, "hive", "thive002")) T
    where C.table_name = T.table_name
     and T.file_format = 'TEXTFILE'
   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(tables, "hive", "thive002")) T,
        table(hivemd(columns, "hive", "thive002")) C
   where C.table_name = T.table_name
     and T.file_format = 'TEXTFILE'
     and T.table_name = 'thive002'
     and C.table_name = 'thive002'
   order by column_number;
 cqd nested_joins reset;

 prepare s from select schema_name from table(hivemd(tables)) where table_name = 'thive002' and file_format = 'TEXTFILE';
 -- should return one row
 execute s;
 -- should return one row
 execute s;

 process hive statement 'drop table thivearr';
 process hive statement 'create table thivearr (a array<string>)';
 -- should return warning 8742
 select table_name from table(hivemd(columns)) where table_name = 'thivearr';

 process hive ddl 'drop table thivealldt';
 process hive ddl 'create table thivealldt(a smallint, b int, c bigint, d decimal(3,1), e string, f char(10), g varchar(7), h date, i timestamp)';
 select fs_data_type, left(sql_data_type, 20) sql_data_type, left(display_data_type, 60) display_data_type, hive_data_type, column_precision, column_scale, column_size  from table(hivemd(columns, "hive","thivealldt"  ));

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

	-- purgedata should preserve regions
	get region stats for table t002t2;
	purgedata t002t2;
	get region stats for table t002t2;
	select * from t002t2;

	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));

	process hive statement 'drop table thive002';
	process hive statement 'create table thive002 (a int, b string)';
	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", "thive002"))
	where file_format = 'TEXTFILE';
	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 = 'thive002'
	and file_format = 'TEXTFILE';

	cqd nested_joins 'OFF';
	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", "thive002")) C,
	table(hivemd(tables, "hive", "thive002")) T
	where C.table_name = T.table_name
	and T.file_format = 'TEXTFILE'
	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(tables, "hive", "thive002")) T,
	table(hivemd(columns, "hive", "thive002")) C
	where C.table_name = T.table_name
	and T.file_format = 'TEXTFILE'
	and T.table_name = 'thive002'
	and C.table_name = 'thive002'
	order by column_number;
	cqd nested_joins reset;

	prepare s from select schema_name from table(hivemd(tables)) where table_name = 'thive002' and file_format = 'TEXTFILE';
	-- should return one row
	execute s;
	-- should return one row
	execute s;

	process hive statement 'drop table thivearr';
	process hive statement 'create table thivearr (a array<string>)';
	-- should return warning 8742
	select table_name from table(hivemd(columns)) where table_name = 'thivearr';

	process hive ddl 'drop table thivealldt';
	process hive ddl 'create table thivealldt(a smallint, b int, c bigint, d decimal(3,1), e string, f char(10), g varchar(7), h date, i timestamp)';
	select fs_data_type, left(sql_data_type, 20) sql_data_type, left(display_data_type, 60) display_data_type, hive_data_type, column_precision, column_scale, column_size from table(hivemd(columns, "hive","thivealldt" ));

	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;