如何用Databricks SQL按目录、架构、表统计行数制作仪表盘？

核心思路 #

利用Databricks内置的system.information_schema.tables获取全工作区的物理表清单，通过动态SQL拼接每个表的行数统计语句，最终批量执行得到按目录、架构、表维度汇总的结果。

实现代码 #

直接执行以下SQL即可生成符合需求的汇总结果：

EXECUTE IMMEDIATE (
  WITH table_list AS (
    SELECT
      catalog_name,
      schema_name,
      table_name,
      -- 拼接单个表的行数统计SQL
      CONCAT(
        'SELECT ''', catalog_name, ''' AS 目录, ',
        '''', schema_name, ''' AS 架构, ',
        '''', table_name, ''' AS 表, ',
        'COUNT(*) AS 行数 FROM ', catalog_name, '.', schema_name, '.', table_name
      ) AS count_sql
    FROM system.information_schema.tables
    -- 过滤条件：只统计物理表，排除系统目录
    WHERE table_type = 'BASE TABLE'
      AND catalog_name != 'system'
  )
  -- 将所有表的统计SQL拼接为UNION ALL形式
  SELECT STRING_AGG(count_sql, ' UNION ALL ') FROM table_list
)

关键说明 #

• 权限要求：需要拥有目标Catalog、Schema及表的SELECT权限，否则会触发权限报错。
• 性能优化：若工作区表数量多或单表数据量极大，建议在业务低峰时段执行；对于分区表，也可考虑读取表的元数据统计信息（如spark.sql.statistics.sizeInBytes）替代实时COUNT(*)，但元数据可能存在更新延迟。
• 自定义过滤：可在WHERE子句中添加额外条件，比如指定特定Catalog：AND catalog_name = 'example_catalog_1'，或特定Schema：AND schema_name = 'Finance'。
• 视图统计：若需统计视图行数，移除table_type = 'BASE TABLE'条件即可，但视图的COUNT(*)会触发全量数据扫描，性能开销更大。

内容的提问来源于stack exchange，提问作者Arnold Souza