| >>obey TEST002(setup); |
| >>create schema t002sch; |
| |
| --- SQL operation complete. |
| >>set schema t002sch; |
| |
| --- SQL operation complete. |
| >>create table t002t1(a int not null primary key, |
| +> b int not null, c int not null); |
| |
| --- SQL operation complete. |
| >>create index t002t1i1 on t002t1(b); |
| |
| --- SQL operation complete. |
| >>create index t002t1i2 on t002t1(c); |
| |
| --- SQL operation complete. |
| >>insert into t002t1 values (1,2, 3); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1'; |
| >>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1I1'; |
| >>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1I2'; |
| >> |
| >>select * from t002t1; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| |
| --- 1 row(s) selected. |
| >> |
| >>create table t002t2(a int not null primary key, b int not null) |
| +> salt using 4 partitions; |
| |
| --- SQL operation complete. |
| >>create index t002t2i1 on t002t2(b); |
| |
| --- SQL operation complete. |
| >>insert into t002t2 values (1,2); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2'; |
| >>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2I1'; |
| >> |
| >>select * from t002t2; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- purgedata should preserve regions |
| >>get region stats for table t002t2; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2 |
| NumRegions: 4 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 4 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 1 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 2 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 1 |
| WriteRequestsCount: 1 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 3 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 4 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| |
| --- SQL operation complete. |
| >>purgedata t002t2; |
| |
| --- SQL operation complete. |
| >>get region stats for table t002t2; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2 |
| NumRegions: 4 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 4 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 0 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 2 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 3 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 4 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| |
| --- SQL operation complete. |
| >>select * from t002t2; |
| |
| --- 0 row(s) selected. |
| >> |
| >>insert into t002t2 values (1,2); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2'; |
| >>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2I1'; |
| >> |
| >>select * from t002t2; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>obey TEST002(region_stats); |
| >>set schema t002sch; |
| |
| --- SQL operation complete. |
| >>invoke table(region stats ()); |
| |
| -- Definition of Trafodion table TRAFODION.T002SCH.EXE_UTIL_REGION_STATS__ |
| -- Definition current Tue Jun 5 00:29:30 2018 |
| |
| ( |
| CATALOG_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SCHEMA_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , OBJECT_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , REGION_SERVER CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , REGION_NUM LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , REGION_NAME CHAR(512 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_STORES INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_STORE_FILES INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , STORE_FILE_UNCOMP_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , STORE_FILE_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , MEM_STORE_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , READ_REQUESTS_COUNT LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , WRITE_REQUESTS_COUNT LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| |
| --- SQL operation complete. |
| >>invoke table(region stats (t002t1)); |
| |
| -- Definition of Trafodion table TRAFODION.T002SCH.EXE_UTIL_REGION_STATS__ |
| -- Definition current Tue Jun 5 00:29:31 2018 |
| |
| ( |
| CATALOG_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SCHEMA_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , OBJECT_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , REGION_SERVER CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , REGION_NUM LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , REGION_NAME CHAR(512 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_STORES INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_STORE_FILES INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , STORE_FILE_UNCOMP_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , STORE_FILE_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , MEM_STORE_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , READ_REQUESTS_COUNT LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , WRITE_REQUESTS_COUNT LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>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)); |
| |
| (EXPR) REGION_NUM REGION_NAME NUM_STORES NUM_STORE_FILES STORE_FILE_UNCOMP_SIZE STORE_FILE_SIZE MEM_STORE_SIZE (EXPR) (EXPR) |
| -------------------------------------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- ---------------------- -------------------- -------------------- ----------------------------- ------------------------------ |
| |
| T002SCH.T002T1 1 TRAFODION.T002SCH.T002T1/1528158469344 1 0 0 0 0 ReadRequestsCount: 2 WriteRequestsCount: 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>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)); |
| |
| (EXPR) REGION_NUM REGION_NAME NUM_STORES NUM_STORE_FILES STORE_FILE_UNCOMP_SIZE STORE_FILE_SIZE MEM_STORE_SIZE (EXPR) (EXPR) |
| -------------------------------------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- ---------------------- -------------------- -------------------- ----------------------------- ------------------------------ |
| |
| T002SCH.T002T1 1 TRAFODION.T002SCH.T002T1I1/1528158479307 1 0 0 0 0 ReadRequestsCount: 0 WriteRequestsCount: 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>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)); |
| |
| (EXPR) REGION_NUM REGION_NAME NUM_STORES NUM_STORE_FILES STORE_FILE_UNCOMP_SIZE STORE_FILE_SIZE MEM_STORE_SIZE (EXPR) (EXPR) |
| -------------------------------------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- ---------------------- -------------------- -------------------- ----------------------------- ------------------------------ |
| |
| T002SCH.T002T2 1 TRAFODION.T002SCH.T002T2/1528158523869 1 0 0 0 0 ReadRequestsCount: 0 WriteRequestsCount: 0 |
| T002SCH.T002T2 2 TRAFODION.T002SCH.T002T2/1528158523869 1 0 0 0 0 ReadRequestsCount: 1 WriteRequestsCount: 1 |
| T002SCH.T002T2 3 TRAFODION.T002SCH.T002T2/1528158523869 1 0 0 0 0 ReadRequestsCount: 0 WriteRequestsCount: 0 |
| T002SCH.T002T2 4 TRAFODION.T002SCH.T002T2/1528158523869 1 0 0 0 0 ReadRequestsCount: 0 WriteRequestsCount: 0 |
| |
| --- 4 row(s) selected. |
| >> |
| >>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)); |
| |
| (EXPR) REGION_NUM REGION_NAME NUM_STORES NUM_STORE_FILES STORE_FILE_UNCOMP_SIZE STORE_FILE_SIZE MEM_STORE_SIZE (EXPR) (EXPR) |
| -------------------------------------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- ---------------------- -------------------- -------------------- ----------------------------- ------------------------------ |
| |
| T002SCH.T002T2 1 TRAFODION.T002SCH.T002T2I1/1528158529016 1 0 0 0 0 ReadRequestsCount: 1 WriteRequestsCount: 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>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; |
| |
| (EXPR) (EXPR) (EXPR) (EXPR) |
| -------------------------------------------------------- -------------------- ----------------------------- ------------------------------ |
| |
| T002SCH.T002T2 0 ReadRequestsCount: 1 WriteRequestsCount: 1 |
| T002SCH.SB_HIS 0 ReadRequestsCount: 0 WriteRequestsCount: 0 |
| T002SCH.T002T1 0 ReadRequestsCount: 2 WriteRequestsCount: 1 |
| T002SCH.SB_PER 0 ReadRequestsCount: 0 WriteRequestsCount: 0 |
| |
| --- 4 row(s) selected. |
| >> |
| >>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; |
| |
| (EXPR) (EXPR) (EXPR) (EXPR) |
| -------------------------------------------------------- -------------------- ----------------------------- ------------------------------ |
| |
| T002SCH.T002T2 0 ReadRequestsCount: 1 WriteRequestsCount: 1 |
| T002SCH.SB_HIS 0 ReadRequestsCount: 0 WriteRequestsCount: 0 |
| T002SCH.T002T1 0 ReadRequestsCount: 2 WriteRequestsCount: 1 |
| T002SCH.SB_PER 0 ReadRequestsCount: 0 WriteRequestsCount: 0 |
| |
| --- 4 row(s) selected. |
| >> |
| >>get region stats for table t002t1; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 2 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T1/1528158469344 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 2 |
| WriteRequestsCount: 1 |
| |
| |
| --- SQL operation complete. |
| >>get region stats for index t002t1i1; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1I1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T1I1/1528158479307 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 1 |
| |
| |
| --- SQL operation complete. |
| >>get region stats for table t002t2; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2 |
| NumRegions: 4 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 4 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 1 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 2 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 1 |
| WriteRequestsCount: 1 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 3 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 4 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| |
| --- SQL operation complete. |
| >>get region stats for index t002t2i1; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2I1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 1 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T2I1/1528158529016 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 1 |
| WriteRequestsCount: 1 |
| |
| |
| --- SQL operation complete. |
| >> |
| >>get region statistics for table t002t1; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 2 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T1/1528158469344 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 2 |
| WriteRequestsCount: 1 |
| |
| |
| --- SQL operation complete. |
| >> |
| >>get region stats for |
| +> (select * from |
| +> (get tables in schema t002sch, no header, return full names)x(a)); |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.SB_HISTOGRAMS |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 0 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.SB_HISTOGRAMS/1528158446488 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.SB_HISTOGRAM_INTERVALS |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 0 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.SB_HISTOGRAM_INTERVALS/1528158454615 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.SB_PERSISTENT_SAMPLES |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 0 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.SB_PERSISTENT_SAMPLES/1528158460979 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 2 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T1/1528158469344 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 2 |
| WriteRequestsCount: 1 |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2 |
| NumRegions: 4 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 4 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 1 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 2 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 1 |
| WriteRequestsCount: 1 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 3 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| RegionServer: edev06:35100 |
| RegionNum: 4 |
| RegionName: TRAFODION.T002SCH.T002T2/1528158523869 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 0 |
| |
| |
| --- SQL operation complete. |
| >> |
| >>-- 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) ))); |
| |
| (EXPR) REGION_NUM REGION_NAME NUM_STORES NUM_STORE_FILES STORE_FILE_UNCOMP_SIZE STORE_FILE_SIZE MEM_STORE_SIZE (EXPR) (EXPR) |
| -------------------------------------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- ---------------------- -------------------- -------------------- ----------------------------- ------------------------------ |
| |
| T002SCH.T002T1 1 TRAFODION.T002SCH.T002T1I1/1528158479307 1 0 0 0 0 ReadRequestsCount: 0 WriteRequestsCount: 1 |
| T002SCH.T002T1 1 TRAFODION.T002SCH.T002T1I2/1528158488210 1 0 0 0 0 ReadRequestsCount: 0 WriteRequestsCount: 1 |
| |
| --- 2 row(s) selected. |
| >>get region stats for |
| +> (select * from |
| +> (get indexes on table t002t1, no header, return full names)x(a)); |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1I1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T1I1/1528158479307 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 1 |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1I2 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Details |
| ============= |
| |
| RegionServer: edev06:35100 |
| RegionNum: 1 |
| RegionName: TRAFODION.T002SCH.T002T1I2/1528158488210 |
| NumStores: 1 |
| NumStoreFiles: 0 |
| UncompressedSize: 0 (less than 1MB) |
| StoreFileSize: 0 (less than 1MB) |
| MemStoreSize: 0 (less than 1MB) |
| ReadRequestsCount: 0 |
| WriteRequestsCount: 1 |
| |
| |
| --- SQL operation complete. |
| >> |
| >>-- return summary stats only |
| >>get region stats for table t002t1, summary; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 2 |
| TotalWriteRequestsCount: 1 |
| |
| --- SQL operation complete. |
| >>get region stats for index t002t1i1, summary; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1I1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 1 |
| |
| --- SQL operation complete. |
| >>get region stats for table t002t2, summary; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2 |
| NumRegions: 4 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 4 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 1 |
| TotalWriteRequestsCount: 1 |
| |
| --- SQL operation complete. |
| >>get region stats for index t002t2i1, summary; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2I1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 1 |
| TotalWriteRequestsCount: 1 |
| |
| --- SQL operation complete. |
| >> |
| >>get region stats for |
| +> (select * from |
| +> (get tables in schema t002sch, no header, return full names)x(a)), summary; |
| |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.SB_HISTOGRAMS |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 0 |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.SB_HISTOGRAM_INTERVALS |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 0 |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.SB_PERSISTENT_SAMPLES |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 0 |
| TotalWriteRequestsCount: 0 |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T1 |
| NumRegions: 1 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 1 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 2 |
| TotalWriteRequestsCount: 1 |
| |
| Stats Summary |
| ============= |
| |
| ObjectName: TRAFODION.T002SCH.T002T2 |
| NumRegions: 4 |
| RegionsLocation: /hbase/data/default |
| TotalNumStores: 4 |
| TotalNumStoreFiles: 0 |
| TotalUncompressedSize: 0 |
| TotalStoreFileSize: 0 |
| TotalMemStoreSize: 0 |
| TotalReadRequestsCount: 1 |
| TotalWriteRequestsCount: 1 |
| |
| --- SQL operation complete. |
| >> |
| >>-- 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'; |
| |
| (EXPR) REGION_NUM REGION_NAME NUM_STORES NUM_STORE_FILES |
| ------------------------------ -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- |
| |
| T002SCH.T002T1 1 TRAFODION.T002SCH.T002T1/1528158469344 1 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>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; |
| |
| (EXPR) (EXPR) |
| ------------------------------ -------------------- |
| |
| T002T1 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>obey TEST002(cluster_stats); |
| >>invoke table(cluster stats()); |
| |
| -- Definition of Trafodion table TRAFODION.T002SCH.EXE_UTIL_CLUSTER_STATS__ |
| -- Definition current Tue Jun 5 00:29:37 2018 |
| |
| ( |
| REGION_SERVER CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , REGION_NAME CHAR(512 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , CATALOG_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SCHEMA_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , OBJECT_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_STORES INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_STORE_FILES INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , STORE_FILE_UNCOMP_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , STORE_FILE_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , MEM_STORE_SIZE LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , READ_REQUESTS_COUNT LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , WRITE_REQUESTS_COUNT LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>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; |
| |
| (EXPR) (EXPR) |
| ------------------------------ -------------------- |
| |
| T002SCH.SB_HISTOGRAM_INTERVALS 0 |
| T002SCH.T002T1I2 0 |
| T002SCH.T002T1 0 |
| T002SCH.SB_PERSISTENT_SAMPLES 0 |
| T002SCH.T002T1I1 0 |
| T002SCH.T002T2 0 |
| T002SCH.SB_HISTOGRAMS 0 |
| T002SCH.T002T2I1 0 |
| |
| --- 8 row(s) selected. |
| >> |
| >>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; |
| |
| (EXPR) (EXPR) |
| ------------------------------ -------------------- |
| |
| RegionName: 1528158446488 0 |
| RegionName: 1528158523869 0 |
| RegionName: 1528158488210 0 |
| RegionName: 1528158479307 0 |
| RegionName: 1528158469344 0 |
| RegionName: 1528158460979 0 |
| RegionName: 1528158454615 0 |
| RegionName: 1528158529016 0 |
| |
| --- 8 row(s) selected. |
| >> |
| >>obey TEST002(hive_md); |
| >>cqd hive_max_string_length_in_bytes '20'; |
| |
| --- SQL operation complete. |
| >>invoke table(hivemd(tables)); |
| |
| -- Definition of Trafodion table TRAFODION.T002SCH.HIVEMD_TABLES__ |
| -- Definition current Tue Jun 5 00:29:38 2018 |
| |
| ( |
| CATALOG_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SCHEMA_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , TABLE_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , FILE_FORMAT CHAR(24) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , CREATE_TIME LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_COLS INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_PART_COLS INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_BUCKET_COLS INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NUM_SORT_COLS INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , HIVE_OWNER CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , FIELD_DELIMITER SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , RECORD_TERMINATOR SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NULL_FORMAT CHAR(8) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , LOCATION CHAR(1024 BYTES) CHARACTER SET UTF8 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , HIVE_TABLE_TYPE CHAR(128 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| |
| --- SQL operation complete. |
| >>invoke table(hivemd(columns)); |
| |
| -- Definition of Trafodion table TRAFODION.T002SCH.HIVEMD_COLUMNS__ |
| -- Definition current Tue Jun 5 00:29:38 2018 |
| |
| ( |
| CATALOG_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SCHEMA_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , TABLE_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SQL_DATA_TYPE CHAR(32) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , FS_DATA_TYPE INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , DISPLAY_DATA_TYPE CHAR(96) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , HIVE_DATA_TYPE CHAR(32) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_SIZE INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , CHARACTER_SET CHAR(40) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_PRECISION INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_SCALE INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , DT_CODE INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , NULLABLE INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PART_COL_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , BUCKET_COL_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SORT_COL_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , DATETIME_QUALIFIER CHAR(28) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , DATETIME_START_FIELD INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , DATETIME_END_FIELD INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , DEFAULT_VALUE CHAR(240 BYTES) CHARACTER SET UTF8 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY (CATALOG_NAME ASC, SCHEMA_NAME ASC, TABLE_NAME ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>process hive statement 'drop table thive002'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table thive002 (a int, b string)'; |
| |
| --- SQL operation complete. |
| >>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'; |
| |
| (EXPR) FILE_FORMAT NUM_COLS NUM_PART_COLS NUM_BUCKET_COLS NUM_SORT_COLS FIELD_DELIMITER RECORD_TERMINATOR HIVE_TABLE_TYPE |
| ------------------------------ ------------------------ ----------- ------------- --------------- ------------- --------------- ----------------- -------------------------------------------------------------------------------------------------------------------------------- |
| |
| thive002 TEXTFILE 2 0 0 0 1 10 MANAGED_TABLE |
| |
| --- 1 row(s) selected. |
| >>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'; |
| |
| (EXPR) FILE_FORMAT NUM_COLS NUM_PART_COLS NUM_BUCKET_COLS NUM_SORT_COLS FIELD_DELIMITER RECORD_TERMINATOR HIVE_TABLE_TYPE |
| ------------------------------ ------------------------ ----------- ------------- --------------- ------------- --------------- ----------------- -------------------------------------------------------------------------------------------------------------------------------- |
| |
| thive002 TEXTFILE 2 0 0 0 1 10 MANAGED_TABLE |
| |
| --- 1 row(s) selected. |
| >> |
| >>cqd nested_joins 'OFF'; |
| |
| --- SQL operation complete. |
| >>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; |
| |
| (EXPR) SQL_DATA_TYPE FS_DATA_TYPE HIVE_DATA_TYPE COLUMN_SIZE COLUMN_SCALE COLUMN_NUMBER PART_COL_NUMBER BUCKET_COL_NUMBER SORT_COL_NUMBER |
| ------------------------------ -------------------------------- ------------ -------------------------------- ----------- ------------ ------------- --------------- ----------------- --------------- |
| |
| a SIGNED INTEGER 132 int 4 0 0 -1 -1 -1 |
| b VARCHAR 64 string 20 -1 1 -1 -1 -1 |
| |
| --- 2 row(s) selected. |
| >> |
| >>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; |
| |
| (EXPR) SQL_DATA_TYPE FS_DATA_TYPE HIVE_DATA_TYPE COLUMN_SIZE COLUMN_SCALE COLUMN_NUMBER PART_COL_NUMBER BUCKET_COL_NUMBER SORT_COL_NUMBER |
| ------------------------------ -------------------------------- ------------ -------------------------------- ----------- ------------ ------------- --------------- ----------------- --------------- |
| |
| a SIGNED INTEGER 132 int 4 0 0 -1 -1 -1 |
| b VARCHAR 64 string 20 -1 1 -1 -1 -1 |
| |
| --- 2 row(s) selected. |
| >>cqd nested_joins reset; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare s from select schema_name from table(hivemd(tables)) where table_name = 'thive002' and file_format = 'TEXTFILE'; |
| |
| --- SQL command prepared. |
| >>-- should return one row |
| >>execute s; |
| |
| SCHEMA_NAME |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| hive |
| |
| --- 1 row(s) selected. |
| >>-- should return one row |
| >>execute s; |
| |
| SCHEMA_NAME |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| hive |
| |
| --- 1 row(s) selected. |
| >> |
| >>process hive statement 'drop table thivearr'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table thivearr (a array<string>)'; |
| |
| --- SQL operation complete. |
| >>-- should return warning 8742 |
| >>select table_name from table(hivemd(columns)) where table_name = 'thivearr'; |
| |
| *** WARNING[8742] GetMetaDataInfo operation could not be completed. Reason: Datatype array<string> for column 'a' in table hive.hive.thivearr is not supported. This table will be ignored. |
| |
| --- 0 row(s) selected. |
| >>process hive ddl 'drop table thivearr'; |
| |
| --- SQL operation complete. |
| >> |
| >>process hive ddl 'drop table thivealldt'; |
| |
| --- SQL operation complete. |
| >>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)'; |
| |
| --- SQL operation complete. |
| >>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" )); |
| |
| FS_DATA_TYPE SQL_DATA_TYPE DISPLAY_DATA_TYPE HIVE_DATA_TYPE COLUMN_PRECISION COLUMN_SCALE COLUMN_SIZE |
| ------------ -------------------- ------------------------------------------------------------ -------------------------------- ---------------- ------------ ----------- |
| |
| 130 SIGNED SMALLINT SMALLINT smallint 15 0 2 |
| 132 SIGNED INTEGER INT int 31 0 4 |
| 134 SIGNED LARGEINT LARGEINT bigint 63 0 8 |
| 130 SIGNED SMALLINT NUMERIC(3, 1) decimal(3,1) 3 1 2 |
| 64 VARCHAR VARCHAR(20 BYTES) CHARACTER SET UTF8 string -1 -1 20 |
| 0 CHARACTER CHAR(10 CHARS) CHARACTER SET UTF8 char(10) -1 -1 40 |
| 64 VARCHAR VARCHAR(7 CHARS) CHARACTER SET UTF8 varchar(7) -1 -1 28 |
| 192 DATETIME DATE date 1 0 4 |
| 192 DATETIME TIMESTAMP(9) timestamp 3 6 11 |
| |
| --- 9 row(s) selected. |
| >> |
| >>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', |
| +>HDFS_STORAGE_POLICY = 'hot' , |
| +>prefetch_blocks_on_open = 'false' |
| +>); |
| |
| --- SQL operation complete. |
| >> |
| >>showddl createOptions ; |
| |
| CREATE TABLE TRAFODION.T002SCH.CREATEOPTIONS |
| ( |
| A INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| 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', |
| HDFS_STORAGE_POLICY = 'hot', |
| PREFETCH_BLOCKS_ON_OPEN = 'false' |
| ) |
| ; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
