时间戳

首页 > 解决方案 > 在 BigQuery 中，确定 UNION ALL 上的列何时不匹配

sql - 在 BigQuery 中，确定 UNION ALL 上的列何时不匹配

问题描述

with
  table1 as (
    select 'joe' as name, 17 as age, 25 as speed
  ),
  table2 as (
    select 'nick' as name, 21 as speed, 23 as strength
  )

select * from table1
union all
select * from table2

在 Google BigQuery 中，这union all不会引发错误，因为两个表的列数相同（每列 3 个）。但是我收到错误的数据输出，因为列不匹配。而不是输出一个具有 4 列的新表name, age, speed,strength具有正确的值 + 缺失值的空值（这可能是首选），而是union all保留顶行的 3 列。

有没有一种很好的方法来捕获列不匹配，而不是查询默默地返回错误数据？与成功的表相反，有什么方法可以返回错误吗？我不确定如何在 SQL 中检查 2 个表中的列是否匹配。

编辑：在此示例中，很明显可以看到列不匹配，但是在我们的数据中，我们有 100 多列，我们希望避免在 UNION ALL 中出错的情况

标签： sqlgoogle-bigqueryunion-all

解决方案

以下是 BigQuery 标准 SQL 并使用 BQ 的脚本功能

DECLARE statement STRING;
SET statement = (
  WITH  table1_columns AS (
    SELECT column FROM (SELECT * FROM `project.dataset.table1` LIMIT 1) t,
    UNNEST(REGEXP_EXTRACT_ALL(TRIM(TO_JSON_STRING(t), '{}'), r'"([^"]*)":')) column
  ), table2_columns AS (
    SELECT column FROM (SELECT * FROM `project.dataset.table2` LIMIT 1) t,
    UNNEST(REGEXP_EXTRACT_ALL(TRIM(TO_JSON_STRING(t), '{}'), r'"([^"]*)":')) column
  ), all_columns AS (
    SELECT column FROM table1_columns UNION DISTINCT SELECT column FROM table2_columns
  )
  SELECT (
      SELECT 'SELECT ' || STRING_AGG(IF(t.column IS NULL, 'NULL as ', '') || a.column, ', ') || ' FROM `project.dataset.table1` UNION ALL '
      FROM all_columns a LEFT JOIN table1_columns t USING(column)
    ) || (
      SELECT 'SELECT ' || STRING_AGG(IF(t.column IS NULL, 'NULL as ', '') || a.column, ', ') || ' FROM `project.dataset.table2`'
      FROM all_columns a LEFT JOIN table2_columns t USING(column)
    ) 
);
EXECUTE IMMEDIATE statement;

当应用于您问题的样本数据时 - 输出是

Row name    age     speed   strength     
1   joe     17      25      null     
2   nick    null    21      23

推荐阅读