BigQuery中UNNEST数组时行数增减异常问题咨询

场景说明 #

通过Azure Synapse无服务器SQL池的复制数据活动，将Google Cloud Platform（GCP）账单数据从BigQuery导出至Azure Synapse Analytics，使用查询UNNEST结构体数组列时，出现行数异常减少的情况，需分析原因。

表结构DDL #

billing_account_id STRING,
project STRUCT<id STRING, number STRING, name STRING, labels ARRAY<STRUCT<key STRING, value STRING>>, ancestry_numbers STRING, ancestors ARRAY<STRUCT<resource_name STRING, display_name STRING>>>,
labels ARRAY<STRUCT<key STRING, value STRING>>,
system_labels ARRAY<STRUCT<key STRING, value STRING>>,
resource STRUCT<name STRING, global_name STRING>,
usage STRUCT<amount FLOAT64, unit STRING, amount_in_pricing_units FLOAT64, pricing_unit STRING>,
credits ARRAY<STRUCT<name STRING, amount FLOAT64, full_name STRING, id STRING, type STRING>>,
invoice STRUCT<month STRING>,
cost_type STRING,
adjustment_info STRUCT<id STRING, description STRING, mode STRING, type STRING>

行数统计查询及结果 #

-- 原表行数
SELECT count(1) FROM `export.gcp_billing_export` tbl; -- 488,861 rows

-- UNNEST project.labels后行数
SELECT count(1) FROM `export.gcp_billing_export` tbl, UNNEST(project.labels) AS ar_proj_labels; -- 236,567 rows（行数减少）

-- UNNEST project.ancestors后行数
SELECT count(1) FROM `export.gcp_billing_export` tbl, UNNEST(project.ancestors) AS ar_proj_ancestors; -- 1,241,985 rows（符合预期增加）

-- UNNEST labels后行数
SELECT count(1) FROM `export.gcp_billing_export` tbl, UNNEST(labels) AS ar_labels; -- 2,077,164 rows（符合预期增加）

-- UNNEST system_labels后行数
SELECT count(1) FROM `export.gcp_billing_export` tbl, UNNEST(system_labels) AS ar_system_labels; -- 3,639,408 rows（符合预期增加）

-- UNNEST credits后行数
SELECT count(1) FROM `export.gcp_billing_export` tbl, UNNEST(credits) AS tbl_credits; -- 4,752 rows（行数大幅下降）

原因分析 #

BigQuery中用逗号分隔的UNNEST属于CROSS JOIN（交叉连接），这种连接逻辑会过滤掉数组为NULL或空数组的原表行，仅当数组包含有效元素时，才会将原表行与数组元素展开连接。

1. project.labels行数减少的原因
   原表中多数行的project.labels是NULL或空数组，仅约236,567行的project.labels包含有效元素。交叉连接后，空数组的行被排除，导致总行数低于原表。可通过以下查询验证：

   SELECT count(1)
   FROM `export.gcp_billing_export`
   WHERE project.labels IS NOT NULL AND ARRAY_LENGTH(project.labels) > 0;
   

   结果应接近236,567。

2. credits行数大幅下降的原因
   原表中仅有极少部分行的credits是非空数组，绝大多数行的credits为NULL或空数组。交叉连接后，这些行被全部过滤，最终行数仅保留有有效credits的行展开后的数量。验证查询：

   SELECT count(1)
   FROM `export.gcp_billing_export`
   WHERE credits IS NOT NULL AND ARRAY_LENGTH(credits) > 0;
   

   结果应等于4,752左右。

解决方案（如需保留所有原表行） #

若需保留原表所有行，无论数组是否为空，应使用LEFT JOIN UNNEST替代默认的交叉连接：

SELECT count(1)
FROM `export.gcp_billing_export` tbl
LEFT JOIN UNNEST(project.labels) AS ar_proj_labels;

此时行数将等于原表行数，空数组对应的ar_proj_labels字段值为NULL。

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