最近有用户问到Trafodion如何查看表的容量（即size），类似于Oracle这样，
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
其实在很久之前，我就写过一篇文章，是关于“Trafodion get region stats查看表对应的region详情”，https://blog.csdn.net/Post_Yuan/article/details/72771795
Trafodion提供了"get region stat"这样的方法可以查看Trafodion表的region情况，包括region数、store文件数、store文件size等信息。因此，我们可以通过这种方式获取表的容量信息，除此之外，Trafodion还支持一种通过SQL UDF的方式来查询表的容量信息。下面我们来具体通过例子来介绍如何查看表的容量信息吧~
1 get region stats查看表容量
如之前的博客中所介绍，使用以下命令查看表的region情况，通常以下输出，可以知道当前表总共有8个region，16个store文件，store文件的总size为126877696 bytes，因此可以得到表的容量为121MB。
SQL>get region stats for table DMA_ENTTYPE_STAT;
Stats Summary
=============
ObjectName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT
NumRegions: 8
RegionsLocation: /hbase/data/default
TotalNumStores: 16
TotalNumStoreFiles: 8
TotalUncompressedSize: 837812224
TotalStoreFileSize: 126877696
TotalMemStoreSize: 0
TotalReadRequestsCount: 20249624
TotalWriteRequestsCount: 0
Stats Details
=============
RegionServer: esgzb-del-n005:60020
RegionNum: 1
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 104857600 Bytes
StoreFileSize: 16777216 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2531260
WriteRequestsCount: 0
RegionServer: esgzb-del-n007:60020
RegionNum: 2
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 104857600 Bytes
StoreFileSize: 15728640 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2530316
WriteRequestsCount: 0
RegionServer: esgzb-del-n007:60020
RegionNum: 3
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 104857600 Bytes
StoreFileSize: 15728640 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2532848
WriteRequestsCount: 0
RegionServer: esgzb-del-n007:60020
RegionNum: 4
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 104857600 Bytes
StoreFileSize: 15728640 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2530920
WriteRequestsCount: 0
RegionServer: esgzb-del-n005:60020
RegionNum: 5
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 104857600 Bytes
StoreFileSize: 15728640 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2532544
WriteRequestsCount: 0
RegionServer: esgzb-del-n005:60020
RegionNum: 6
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 103809024 Bytes
StoreFileSize: 15728640 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2527668
WriteRequestsCount: 0
RegionServer: esgzb-del-n006:60020
RegionNum: 7
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 104857600 Bytes
StoreFileSize: 15728640 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2534576
WriteRequestsCount: 0
RegionServer: esgzb-del-n007:60020
RegionNum: 8
RegionName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967
NumStores: 2
NumStoreFiles: 1
UncompressedSize: 104857600 Bytes
StoreFileSize: 15728640 Bytes
MemStoreSize: 0 (less than 1MB)
ReadRequestsCount: 2529492
WriteRequestsCount: 0
--- SQL operation complete.
以上列出了每个region的详细，如果我们只想看到一个总的情况，可以使用summary方式展示结果如下，
SQL>get region stats for table DMA_ENTTYPE_STAT,summary;
Stats Summary
=============
ObjectName: TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT
NumRegions: 8
RegionsLocation: /hbase/data/default
TotalNumStores: 16
TotalNumStoreFiles: 8
TotalUncompressedSize: 837812224
TotalStoreFileSize: 126877696
TotalMemStoreSize: 0
TotalReadRequestsCount: 20249624
TotalWriteRequestsCount: 0
--- SQL operation complete.
2 table(region stats()) 查看表容量
上述方法1使用get方式查看表的region信息及容量信息，不属于标准的SQL方式，这里我们提供另外一套SQL的方式来查看表的容量等相关信息，以下给出示例，
SQL>select * from table(region stats(DMA_ENTTYPE_STAT));
CATALOG_NAME SCHEMA_NAME OBJECT_NAME REGION_SERVER REGION_NUM REGION_NAME NUM_STORES NUM_STORE_FILES STORE_FILE_UNCOMP_SIZE STORE_FILE_SIZE MEM_STORE_SIZE READ_REQUESTS_COUNT WRITE_REQUESTS_COUNT
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- ---------------------- -------------------- -------------------- -------------------- --------------------
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n005:60020 1 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 104857600 16777216 0 2531260 0
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n007:60020 2 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 104857600 15728640 0 2530316 0
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n007:60020 3 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 104857600 15728640 0 2532848 0
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n007:60020 4 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 104857600 15728640 0 2530920 0
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n005:60020 5 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 104857600 15728640 0 2532544 0
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n005:60020 6 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 103809024 15728640 0 2527668 0
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n006:60020 7 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 104857600 15728640 0 2534576 0
TRAFODION DAAS_GX DMA_ENTTYPE_STAT esgzb-del-n007:60020 8 TRAF_1500000:TRAFODION.DAAS_GX.DMA_ENTTYPE_STAT/1533691518967 2 1 104857600 15728640 0 2529492 0
--- 8 row(s) selected.
以下结果显示了UDF所有的输出信息，包括region的详细信息，如果我们只是希望获取表的总容量，可以使用以下语句来实现，其中输出列STORE_SIZE即表的真实容量大小。
SQL>select sum(store_file_uncomp_size)/1024/1024 AS UNCOMPRESS_SIZE, sum(store_file_size)/1024/1024 AS STORE_SIZE from table(region stats(DAAS_GX.DMA_ENTTYPE_STAT));
UNCOMPRESS_SIZE STORE_SIZE
-------------------- --------------------
799 121
--- 1 row(s) selected.
3 查看固定Schema所有表容量列表
以上两种方法均是针对单个表的查看容量方法，如果希望像前面Oracle那种方式可以直接得到某个Schema下所有表的容量的列表，应该如何实现呢？
首先我们肯定是需要通过SQL的方式获取到某个Schema下所有的表列表，这可以通过以下SQL实现，
SQL>select * from (get tables in schema DAAS_GX, return full names) x(a);
A
--------------------------------------------------------------------------------------------------------------------------------
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_LIST"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT_30W_2"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT_30W_P8"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT_NOPRAT"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT_NOPRAT2"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT_P1"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT_P8"
"TRAFODION"."DAAS_GX"."DMA_ENTTYPE_STAT_UPDATE"
"TRAFODION"."DAAS_GX"."SB_HISTOGRAMS"
"TRAFODION"."DAAS_GX"."SB_HISTOGRAM_INTERVALS"
"TRAFODION"."DAAS_GX"."SB_PERSISTENT_SAMPLES"
--- 12 row(s) selected.
其次，结合方法2的UDF与上述语句，我们得到以下语句可以列出固定Schema下所有表的容量列表，
select trim(schema_name) || '.' || trim(object_name),
sum(store_file_size+mem_store_size)/1024/1024 as store_size
from table(region statistics (using (select * from (
get tables in schema daas_gx, no header, return full names, with namespace) x(a) )))
group by 1 order by 2 desc;
示例输出如下，
SQL>select trim(schema_name) || '.' || trim(object_name),
+> sum(store_file_size+mem_store_size)/1024/1024 as store_size
+>from table(region statistics (using (select * from (
get tables in schema daas_gx, no header, return full names, with namespace) x(a) )))
group by 1 order by 2 desc;+>+>
(EXPR) STORE_SIZE
-------------------------------------------------------------------------------------------------------------------------------- --------------------
DAAS_GX.DMA_ENTTYPE_STAT_NOPRAT2 179
DAAS_GX.DMA_ENTTYPE_STAT_UPDATE 152
DAAS_GX.DMA_ENTTYPE_STAT_NOPRAT 128
DAAS_GX.DMA_ENTTYPE_STAT_P1 126
DAAS_GX.DMA_ENTTYPE_STAT 121
DAAS_GX.DMA_ENTTYPE_STAT_P8 115
DAAS_GX.DMA_ENTTYPE_STAT_30W_2 8
DAAS_GX.DMA_ENTTYPE_STAT_30W_P8 8
DAAS_GX.SB_HISTOGRAM_INTERVALS 1
DAAS_GX.SB_PERSISTENT_SAMPLES 0
DAAS_GX.DMA_ENTTYPE_LIST 0
DAAS_GX.SB_HISTOGRAMS 0
--- 12 row(s) selected.
4 table(cluster stats())查看全库所有表容量
上述3可以获取一个Schema下面所有表的容量信息，如果要获取束个库的所有表容量信息，我们可以使用下面这个语句，
select trim(schema_name) || '.' || trim(object_name),
sum(store_file_size+mem_store_size)/1024/1024 as store_size
from table(cluster stats())
group by 1 order by 2 desc;
当然，有了此方式之后，我们可以通过添加筛选条件进一步得到某个Schema或某个表的信息，此种方式比以上方法都更简单化。
如获取某个schema的容量信息如下，
select trim(schema_name) || '.' || trim(object_name),
sum(store_file_size+mem_store_size)/1024/1024 as store_size
from table(cluster stats())
where schema_name='DAAS_GX'
group by 1 order by 2 desc;
如获取某个table的容量信息如下，
select trim(schema_name) || '.' || trim(object_name),
sum(store_file_size+mem_store_size)/1024/1024 as store_size
from table(cluster stats())
where schema_name='DAAS_GX' and object_name='DMA_ENTTYPE_STAT_NOPRAT'
group by 1 order by 2 desc;
扫一扫
893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
