如何查询BigQuery中视图及查询语句里的列使用情况？

一、快速获取视图的列使用情况 #

BigQuery原生提供了INFORMATION_SCHEMA.VIEW_COLUMN_USAGE视图，专门记录所有视图引用的基础表列，比COLUMN_FIELD_PATHS覆盖更全面。执行以下查询即可得到视图关联的列信息：

SELECT
  table_catalog,
  table_schema,
  table_name,
  column_name,
  referenced_table_catalog,
  referenced_table_schema,
  referenced_table_name,
  referenced_column_name
FROM
  `你的项目ID`.`你的数据集`.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

结果中referenced_*字段对应视图实际用到的基础表和列，table_*字段是视图自身的信息。

二、解析查询日志提取列使用记录 #

由于INFORMATION_SCHEMA.JOBS没有直接存储列信息，需要解析查询语句中的SQL。推荐用BigQuery的ML.PARSE_SQL函数，它能将SQL转换为结构化JSON，方便提取列引用：

WITH parsed_queries AS (
  SELECT
    job_id,
    query,
    ML.PARSE_SQL(query) AS parsed_sql
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT -- 替换为你的BigQuery地域，比如region-eu
  WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) -- 查询最近30天的记录
    AND statement_type = 'SELECT' -- 仅统计查询类语句，排除DDL/DML
)
SELECT DISTINCT
  '你的项目ID' AS table_catalog,
  JSON_EXTRACT_SCALAR(column_ref, '$.schema') AS table_schema,
  JSON_EXTRACT_SCALAR(column_ref, '$.table') AS table_name,
  JSON_EXTRACT_SCALAR(column_ref, '$.column') AS column_name
FROM
  parsed_queries,
  UNNEST(JSON_EXTRACT_ARRAY(parsed_sql, '$.statements[0].query.select.selectItems')) AS select_items,
  UNNEST(JSON_EXTRACT_ARRAY(select_items, '$.expression.references')) AS column_ref
WHERE
  JSON_EXTRACT_SCALAR(column_ref, '$.type') = 'COLUMN'

这个查询会从历史SELECT语句中提取所有被引用的列、表和Schema，用DISTINCT去重避免重复记录。

三、筛选未被使用的列 #

将所有基础表的列集合，减去上述两步得到的已使用列，即可得到未被使用的列：

WITH all_columns AS (
  SELECT
    table_catalog,
    table_schema,
    table_name,
    column_name
  FROM
    `你的项目ID`.`你的数据集`.INFORMATION_SCHEMA.COLUMNS
  WHERE
    table_type = 'BASE TABLE' -- 仅统计基础表，排除视图自身的列
),
used_columns AS (
  -- 视图用到的列
  SELECT DISTINCT
    referenced_table_catalog AS table_catalog,
    referenced_table_schema AS table_schema,
    referenced_table_name AS table_name,
    referenced_column_name AS column_name
  FROM
    `你的项目ID`.`你的数据集`.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
  UNION DISTINCT
  -- 查询日志里用到的列
  SELECT DISTINCT
    table_catalog,
    table_schema,
    table_name,
    column_name
  FROM
    -- 可直接嵌入第二步的parsed_queries逻辑，或替换为第二步的查询结果
    (上述parsed_queries的查询结果)
)
SELECT
  ac.*
FROM
  all_columns ac
LEFT JOIN
  used_columns uc
ON
  ac.table_catalog = uc.table_catalog
  AND ac.table_schema = uc.table_schema
  AND ac.table_name = uc.table_name
  AND ac.column_name = uc.column_name
WHERE
  uc.column_name IS NULL

补充说明 #

• ML.PARSE_SQL对多数复杂SQL（包括CTE、子查询）解析效果较好，但极端复杂的语句可能存在遗漏，必要时可结合正则表达式补充。
• 查询日志默认保留6个月，若需更长周期的记录，需将日志导出至Cloud Storage或其他BigQuery表。
• 操作这些视图需要对应权限：比如bigquery.jobs.list权限访问JOBS表，bigquery.tables.getData权限访问COLUMNS视图。

内容的提问来源于stack exchange，提问作者Radka Žmers