如何在BigQuery中查询各表大小及其占数据集总大小的比例？

我在Google BigQuery中针对名为test的数据集，通过两个临时内联表分别计算各表大小、数据集总大小，再通过笛卡尔积连接（因dataset_size仅包含DB_size一列，此连接方式可行），最终得到各表大小占数据集总大小的比例。

原SQL代码 #

WITH table_sizes AS ( 
 SELECT
    table_id
  , row_count
  , ROUND(SUM(size_bytes) / (1024*1024), 2) AS size_in_mb
FROM test.__TABLES__
GROUP BY
  table_id,
  row_count
), dataset_size AS
(
SELECT
ROUND(SUM(size_bytes)/1024/1024, 2) AS DB_size
FROM test.__TABLES__
)
SELECT
    t.table_id
  , t.row_count
  , t.size_in_mb
  , ROUND(t.size_in_mb*100/d.DB_size,1) AS percent
  FROM
  table_sizes t,
  dataset_size d
  ORDER BY 
  t.size_in_mb DESC
  LIMIT 5
  ;

执行结果 #

[
  {
    "table_id": "randomdata",
    "row_count": "11100330",
    "size_in_mb": "1651.42",
    "percent": "97.6"
  }, 
  {
    "table_id": "ocod_full",
    "row_count": "95535",
    "size_in_mb": "39.98",
    "percent": "2.4"
  }, 
  {
    "table_id": "DUMMY",
    "row_count": "10000",
    "size_in_mb": "0.99",
    "percent": "0.1"
  }, 
  {
    "table_id": "abcd",
    "row_count": "2",
    "size_in_mb": "0.0",
    "percent": "0.0"
  }, 
  {
    "table_id": "json",
    "row_count": "0",
    "size_in_mb": "0.0",
    "percent": "0.0"
  }
]

优化建议 #

1. 用窗口函数替代笛卡尔积，减少表扫描次数

原代码两次扫描test.__TABLES__表，通过窗口函数可实现一次扫描完成计算，提升执行效率的同时简化逻辑：

SELECT
  table_id,
  row_count,
  ROUND(SUM(size_bytes) / (1024*1024), 2) AS size_in_mb,
  ROUND(
    SUM(size_bytes) * 100 / SUM(SUM(size_bytes)) OVER(),
    1
  ) AS percent
FROM test.__TABLES__
GROUP BY table_id, row_count
ORDER BY size_in_mb DESC
LIMIT 5;

2. 处理除数为0的异常情况

如果数据集为空（总大小为0），原代码计算占比会触发除以0的错误，可通过CASE语句规避：

SELECT
  table_id,
  row_count,
  ROUND(SUM(size_bytes) / (1024*1024), 2) AS size_in_mb,
  CASE
    WHEN SUM(SUM(size_bytes)) OVER() = 0 THEN 0.0
    ELSE ROUND(SUM(size_bytes) * 100 / SUM(SUM(size_bytes)) OVER(), 1)
  END AS percent
FROM test.__TABLES__
GROUP BY table_id, row_count
ORDER BY size_in_mb DESC
LIMIT 5;

3. 简化单位转换写法

可以用1 << 20替代1024*1024（因为2^20 = 1048576，即1MB对应的字节数），写法更简洁：

ROUND(SUM(size_bytes) / (1 << 20), 2) AS size_in_mb

内容的提问来源于stack exchange，提问作者Mich Talebzadeh