排查 BigQuery 连接器错误和限制

将数据洞察关联到 BigQuery 时，您可能会遇到超时、SQL 语法限制、配额限制或 VPC Service Controls 错误。本指南介绍了 BigQuery 连接器中的常见问题。展开解决步骤，调查并解决问题。

查询和 SQL 语法错误

数据洞察中的自定义 SQL 查询具有特定限制。如果您的查询违反了这些限制，则可能会发生错误。

Field is ambiguous 个联接错误

如果您的自定义查询包含重复的列名称，图表会显示以下错误：

User Configuration Error: Field is ambiguous

出错提示文本：用户配置错误

原因：联接表 (JOIN) 不能共享重复的列名称。例如，如果您在 Criteria_ID 字段上联接两个具有相同架构的表，则生成的虚拟表将具有重复的列（Criteria_ID、Parent_ID、Name），这会触发歧义错误。

解决步骤

使用 AS 关键字或 EXCEPT 子句，使所有列名称具有唯一性：

选项 1：使用别名明确重命名重复字段

SELECT *
FROM (
  SELECT
    Criteria_ID AS Criteria_ID_1,
    Parent_ID AS Parent_ID_1,
    Name AS NAME_1
  FROM
    `project.dataset.table_1` ) AS table_1
LEFT JOIN (
  SELECT
    Criteria_ID AS Criteria_ID_2,
    Parent_ID AS Parent_ID_2,
    Name AS NAME_2
  FROM
    `project.dataset.table_2` ) AS table_2
ON
  table_1.Criteria_ID_1 = table_2.Criteria_ID_2;

方法 2：使用 EXCEPT 排除和重命名特定字段

如果您只需要重命名少量字段，同时保留其余字段，请使用 EXCEPT：

SELECT * EXCEPT (city), city AS city_1 FROM `project.dataset.table_1`

自定义 SQL 查询语法错误（多条语句）

如果自定义 SQL 查询包含变量或多条语句（DECLARE、SET），则会失败。

原因：数据洞察会在外部 SELECT 查询 (SELECT * FROM (<your_custom_sql>)) 中运行您的 SQL。因此，您的查询必须是单个 SELECT 语句。

例如，以下查询会失败，因为它在选择之前声明了一个变量：

DECLARE cost_per_tb_in_dollar FLOAT64 DEFAULT 4.2;
SELECT total_bytes_billed / (1024 * 1024) * cost_per_tb_in_dollar / (1024 * 1024) FROM `billing_table`;

解决步骤

使用通用表表达式（CTE 或 WITH 子句）将计算合并到一个 SELECT 语句中：

WITH constants AS (
  SELECT 4.2 AS cost_per_tb_in_dollar
)
SELECT
  total_bytes_billed / (1024 * 1024) * c.cost_per_tb_in_dollar / (1024 * 1024) AS cost
FROM `billing_table`, constants AS c;

性能和查询超时

如果查询运行时间超过 3 到 5 分钟，数据洞察会在收到结果之前超时，并返回：HTTP 504 Gateway timeout。

HTTP 504 Gateway timeout 或长时间运行的查询错误

数据洞察中的自定义查询或复杂图表汇总可能会在 3 到 5 分钟后超时，从而返回 HTTP 504 Gateway timeout 错误。

解决步骤

如果您的查询持续超时，请使用以下优化措施：

  • 启用 BigQuery Storage Read API：启用 BigQuery Storage Read API 以提高数据吞吐量。
  • 简化查询：移除不必要的 `JOIN` 命令，按更宽泛的时间段对数据进行分组，并仅选择所需的列。
  • 使用 BigQuery BI Engine：通过 BigQuery BI Engine 预留容量，实现亚秒级性能。
  • 使用数据库视图：将自定义 SQL 保存为 BigQuery 视图或物化视图，然后将数据洞察直接连接到该视图。
  • 预先汇总到报告表格中：使用 BigQuery 中的预定查询将摘要记录写入单独的表中，然后查询摘要表。

配额和表限制

如果数据集包含数千个表或返回数百万条记录，则可能会出现限制错误：

包含超过 5,000 个表的数据集界面响应冻结

当您尝试使用数据洞察中的表选择列表连接到 BigQuery 数据集时，界面会冻结或无响应。

原因：连接器最多支持每个数据集 5,000 个表。如果数据集包含超过 5,000 个表或视图，表选择列表会超时并冻结。

解决步骤

请使用以下任一替代方法进行连接，而无需加载表列表：

  • 使用自定义查询进行连接：选择自定义查询，然后编写一个不太复杂的 `SELECT` 语句： 
    SELECT * FROM `your_project.your_dataset.your_table`
  • 直接从 BigQuery 连接：在 BigQuery 控制台中，找到您的表，点击导出或探索数据，然后选择使用 Looker Studio 打开。
  • 拆分或重新整理数据集：将报告表格移至包含少于 5,000 个表格的较小专用报告数据集。

最多可返回 200 万行

当您直观呈现大型数据集时，图表可能会显示数据截断警告，或省略超过 200 万条记录的行。

原因：连接器每次图表查询最多返回 200 万行。如果查询结果超过 200 万条记录，图表会截断数据并显示警告。

解决步骤

为避免数据截断，请执行以下操作：

  • 应用报告级日期过滤条件，以缩小查询量。
  • 查询需要分区过滤条件的日期分区表（“DATE”“DATETIME”“TIMESTAMP”）（了解详情）。
  • 在数据洞察中直观呈现之前，先在 BigQuery 中对高基数维度进行分组。

MEDIAN 和 PERCENTILE 差异

在连接到 BigQuery 的图表中计算精确中位数 (MEDIAN) 或百分位数 (PERCENTILE) 时，输出结果可能与在其他 SQL 数据库或 CSV 导出文件中执行的相同计算略有不同。

原因：在 BigQuery 查询中，MEDIAN 和 PERCENTILE 使用 APPROX_QUANTILES 近似聚合函数。这可以快速处理 PB 级数据集，但近似结果可能与对 CSV 导出数据或其他 SQL 数据库执行的精确计算结果略有不同。

数据类型和加密错误

了解如何处理不受支持的列类型和组织强制执行的密钥加密政策。

CONDITION_NOT_MET 加密错误 (CMEK)

查询数据集时，图表失败并返回以下错误：

User Configuration Error: CONDITION_NOT_MET

原因：当数据洞察运行查询作业时，BigQuery 会将查询结果写入结算项目中的临时表。如果针对结算项目中的 BigQuery 强制执行 constraints/gcp.restrictNonCmekServices 组织政策限制条件，则 BigQuery 会要求为所有新表（包括临时查询结果表）使用客户管理的加密密钥 (CMEK)。由于连接器不会在查询作业请求中传递 Cloud Key Management Service 密钥，因此除非配置了项目默认 Cloud KMS 密钥，否则查询会失败并显示 User Configuration Error: CONDITION_NOT_MET。

解决步骤

在 BigQuery 中配置项目默认 Cloud KMS 密钥，以便 BigQuery 自动加密临时查询结果表：

  1. 向结算项目的 BigQuery 加密服务账号授予 Cloud KMS 密钥的 Cloud KMS CryptoKey Encrypter/Decrypter (roles/cloudkms.cryptoKeyEncrypterDecrypter) 角色。
  2. 在您要查询的数据集所在位置的结算项目中设置项目默认 Cloud KMS 密钥 (region-LOCATION.default_kms_key_name)。例如：
    ALTER PROJECT PROJECT_ID
SET OPTIONS (
  `region-LOCATION.default_kms_key_name` = 'projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY'
);

或者，与您的组织管理员合作，在 constraints/gcp.restrictNonCmekServices 政策中豁免结算项目。

TIME 种数据类型不受支持

当您连接到包含 TIME 数据类型（例如 23:59:59）列的 BigQuery 表时，数据洞察会将该字段转换为 TEXT，从而阻止基于时间的排序或汇总。

原因：数据洞察不原生支持 BigQuery 的 TIME 数据类型（例如 23:59:59）。连接器会在提取时将 TIME 列转换为 TEXT 字符串，从而导致无法按时间排序。

解决步骤

使用以下任一解决方法将 `TIME` 列转换为 DATETIME 对象：

解决方法 1：使用自定义 SQL 查询

直接在 SQL 中将 `TIME` 字段与基准日期 (`1970-01-01`) 组合：

SELECT
  *,
  -- Combine a dummy date (1970-01-01) with your TIME field
  DATETIME(DATE "1970-01-01", your_time_field) AS time_as_datetime
FROM
  `your_project.your_dataset.your_table`
  • 结果：数据洞察会将 `time_as_datetime` 提取为 **日期和时间** 字段。
  • 格式设置：在报告图表属性中，将字段的 **显示格式** 更改为 **小时**、**分钟**或自定义时间格式 (`h:mm:ss`)，以便仅显示时间部分（了解详情）。

解决方法 2：在数据洞察中创建计算字段

如果您不修改 SQL 查询，请在数据源中创建计算字段：

PARSE_DATETIME("%H:%M:%S", CAST(your_time_field AS TEXT))
  • 结果：`PARSE_DATETIME` 函数会将文本字符串解析为 **日期和时间** 对象，并将日历日期默认设置为 1970 年 1 月 1 日（了解详情）。

VPC Service Controls 错误

排查在服务边界内工作时出现的访问遭拒错误和后台进程限制。

Service Control Failure（在未连接 VPN 的情况下查看报告时）

当您在组织 VPN 或公司网络之外查看报告时，部分或所有图表会失败，并显示以下错误：

Service Control Failure

原因：连接器会将报告查看者的 IP 地址传递给 BigQuery，以验证基于 IP 的 VPC Service Controls 访问权限级别。复制报告时，副本中的旧版或“幽灵”自定义 SQL 数据源可能会引用受服务边界保护的结算项目，即使您的主要数据集位于边界之外也是如此。

解决步骤

在报告中找出并移除或重新分配任何隐藏的边界内结算项目：

  1. 复制受影响的报告，以便安全地排查问题。
  2. 在副本的报告编辑器中，依次前往资源 > 管理添加的数据源。
  3. 检查附加到报告的每个嵌入式 **BigQuery** 或 **自定义 SQL** 数据源。
  4. 修改每个自定义 SQL 连接，以验证其配置的 **结算项目**。如果任何数据源指向受 VPC Service Controls 边界保护的结算项目，请将其更新为使用不受保护的结算项目，或者删除不再使用的数据源。

VPC Service Controls 保护下的定时发送的邮件或图表提醒失败

如果自动后台功能（例如定时发送的邮件或图表提醒）在连接到受 VPC Service Controls 保护的 BigQuery 数据集的图表上执行，则定时发送的邮件将不包含报告内容或附件，或者提醒无法触发 (VPC Service Controls unexpected field in error map)。

原因：由于自动化后台功能（例如定时发送的邮件或图表提醒）作为后台任务运行，没有最终用户 IP 地址，因此 VPC Service Controls (VPC-SC) 在评估基于 IP 的访问权限级别 (VPC Service Controls unexpected field in error map) 时会阻止这些功能。

解决步骤

如需在 VPC Service Controls 边界内使用自动化后台功能，请将数据源配置为使用服务账号凭证，或创建基于身份的访问权限级别：