Merging tables in Google BigQuery with UNION ALL

Question

I would like to do something like this in BQ. The tables have different schemas. Any ideas how to achieve this?

SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID, Withdrawal_this_hour FROM `tb1`
UNION ALL
SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID, Refill_this_hour FROM `tb2` 
UNION ALL
SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID, changes_this_hour, net_amount, date from `tb3`

Thanks in advance..

probably will help https://stackoverflow.com/questions/10644993/support-union-function-in-bigquery-sql — Iłya Bursov, Mar 06 '19 at 23:30

score 2 · Accepted Answer · answered Mar 06 '19 at 23:47

The columns have to be the same, so something like this:

SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID,
       Withdrawal_this_hour, NULL as Refill_this_hour,
       NULL as changes_this_hour, NULL as net_amount, NULL as date 
FROM `tb1`
UNION ALL
SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID,
       NULL, Refill_this_hour, NULL, NULL, NULL
FROM `tb2` 
UNION ALL
SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID,
       NULL, NULL, changes_this_hour, net_amount, date 
FROM `tb3`

score 0 · Answer 2 · answered Mar 07 '19 at 03:15

For union all number of column and their data have to be same for all the tables big query docs you could try like below by using cte

  with cte as  (

   SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID,
   Withdrawal_this_hour, NULL as Refill_this_hour,
   NULL as changes_this_hour, NULL as net_amount, NULL as date
  UNION ALL
  SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID,
   NULL, Refill_this_hour, NULL, NULL, NULL FROM tb2
   UNION ALL
  SELECT YYYYMMDDHH, CONTAINER, Parent_Container, PROTOTYPE_ID,
   NULL, NULL, changes_this_hour, net_amount, date 
  FROM tb3
 ) select * from cte

Merging tables in Google BigQuery with UNION ALL

2 Answers2