Skip to content
Advertisement

Merging tables in Google BigQuery with UNION ALL

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..

Advertisement

Answer

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`
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement