In BigQuery, identify when columns do not match on UNION ALL

Question

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

In Google BigQuery, this union all does not throw an error because both tables have the same number of columns (3 each). However I receive bad data output because the columns do not match. Rather than outputting a new table with 4 columns name, age, speed, strength with correct values + nulls for missing values (which would probably be preferred), the union all keeps the 3 columns from the top row.

Is there a good way to catch that the columns do not match, rather than the query silently returning bad data? Is there any way for this to return an error perhaps, as opposed to a successful table? I'm not sure how to check in SQL that the columns in the 2 tables match.

Edit: in this example it is clear to see that the columns do not match, however in our data we have 100+ columns and we want to avoid a situation where we make an error in a UNION ALL

you always need to specify the column name in case of using union all and also column order should be same for all the datasets while using union all — Fahmi
Yes i know the column order should be the same when using union all. My question is whether or not there is a way to compute that the column names + orders are, in fact, the same. — Canovice
The point is: Don't use * as projection when you do a UNION ALL but explicitly list the columns. — sticky bit
I understand the best practice. I am simply asking if it's possible to catch / handle an error in the instance that the column names do not match — Canovice
are you looking for making this in "run-time" or "design-time"? in another word - is it ok, just to first compare the schema and if all kosher than in separate query to run your union all stuff? - oh, Ok - that is exactly what you just answered — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-08-29T06:39:52

Below is for BigQuery Standard SQL and using scripting feature of 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;

when applied to sample data from your question - output is

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

In BigQuery, identify when columns do not match on UNION ALL

2 Answers