如何让原用于MySQL的元数据查询SQL代码在Google BigQuery中正常运行

报错原因 #

你使用的是MySQL环境下的INFORMATION_SCHEMA查询语法，和BigQuery的元数据查询规则不兼容：

• BigQuery的INFORMATION_SCHEMA不属于全局资源，必须绑定项目+区域（查询全项目）或者项目+数据集（查询单数据集）前缀才能访问，未加前缀且未设置默认数据集时就会触发找不到表的报错
• 两个数据库的INFORMATION_SCHEMA字段枚举值、过滤规则存在差异，直接复制运行会返回空结果或者报错

调整步骤 #

1. 确定查询范围，补全表前缀 #

两种可选的前缀规则，根据你的查询需求选：

• 单数据集查询：前缀格式为你的项目ID.你的数据集ID，比如my-project.sales_data.INFORMATION_SCHEMA.TABLES，仅返回该数据集下的表、字段元数据
• 全项目查询：前缀格式为你的项目ID.你的资源所在区域，比如my-project.region-us-central1.INFORMATION_SCHEMA.TABLES，返回该区域下所有数据集的元数据

2. 调整SQL语法适配BigQuery规则 #

• 将WHERE条件里的t.TABLE_TYPE='BASE TABLE'改为t.TABLE_TYPE='TABLE'，BigQuery内普通表的类型标识为TABLE
• 删除过滤条件里的'mysql','performance_schema'，BigQuery不存在这两个系统数据集，无需额外过滤

调整后示例代码 #

以下为查询全项目元数据的示例，将my-gcp-project替换为你自己的GCP项目ID，region-us-central1替换为你的资源实际所属区域即可：

SELECT 
  'bigquery' AS dbms,
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  c.COLUMN_NAME,
  c.ORDINAL_POSITION,
  c.DATA_TYPE,
  c.CHARACTER_MAXIMUM_LENGTH,
  n.CONSTRAINT_TYPE,
  k.REFERENCED_TABLE_SCHEMA,
  k.REFERENCED_TABLE_NAME,
  k.REFERENCED_COLUMN_NAME 
FROM 
  `my-gcp-project.region-us-central1.INFORMATION_SCHEMA.TABLES` t 
LEFT JOIN 
  `my-gcp-project.region-us-central1.INFORMATION_SCHEMA.COLUMNS` c 
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
  AND t.TABLE_NAME = c.TABLE_NAME 
LEFT JOIN 
  `my-gcp-project.region-us-central1.INFORMATION_SCHEMA.KEY_COLUMN_USAGE` k 
  ON c.TABLE_SCHEMA = k.TABLE_SCHEMA 
  AND c.TABLE_NAME = k.TABLE_NAME 
  AND c.COLUMN_NAME = k.COLUMN_NAME 
LEFT JOIN 
  `my-gcp-project.region-us-central1.INFORMATION_SCHEMA.TABLE_CONSTRAINTS` n 
  ON k.CONSTRAINT_SCHEMA = n.CONSTRAINT_SCHEMA 
  AND k.CONSTRAINT_NAME = n.CONSTRAINT_NAME 
  AND k.TABLE_SCHEMA = n.TABLE_SCHEMA 
  AND k.TABLE_NAME = n.TABLE_NAME 
WHERE 
  t.TABLE_TYPE = 'TABLE' 
  AND t.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA');

如果是在BigQuery控制台运行单数据集查询，也可以先在控制台顶部选择默认数据集，此时不需要给INFORMATION_SCHEMA加前缀，仅修改WHERE条件即可正常运行。

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