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`