CODEWITHSUNDEEP
sqlgoogle-bigquery
Shahin Ghannadian
Shahin Ghannadian

Reputation: 295

union tables with different schema in BigQuery Standard SQL

What is the best way to append table with different Schema?

Table 0 Schema

Table0 Schema

Table0 Preview

Table 1 Schema

Table1 Schema

Table1 Preview

this works fine

SELECT img1, NULL as img2 FROM `xxx.staging.table0` 
UNION ALL 
SELECT img1, img2 FROM `xxx.staging.table1`

but is there a way to append tables without having to explicitly declare each column?

and also is it good practice to save table 0 as

SELECT img1, CAST(NULL as string) as img2 FROM `modemutti-8d8a6.staging.table0`

making sure img2 is a string:

enter image description here

and then afterwards appending the 2 tables with the exact same schema?

SELECT * FROM `xxx.staging.table1ab` 
UNION ALL 
SELECT * FROM `xxx.staging.table1`

Upvotes: 3

Views: 10386

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

Below is for BigQuery Standard SQL 

#standardSQL
SELECT * FROM `project.dataset.table1`
UNION ALL
SELECT * FROM `project.dataset.table0`
LEFT JOIN (SELECT '', '') ON FALSE

Note: in (SELECT '', '') you need to have as many '' as number of extra fields you have in table1 - so total number of fields in both tables are the same

You can test, play with above using dummy data as below 

#standardSQL
WITH `project.dataset.table0` AS (
  SELECT 'aaa' img1 UNION ALL
  SELECT 'xxx' UNION ALL
  SELECT 'yyy' UNION ALL
  SELECT 'zzz' 
),  `project.dataset.table1` AS (
  SELECT 'bbb' img1, 'ccc' img2, 'ddd' img3 UNION ALL
  SELECT 'eee', 'fff', 'ggg'
)
SELECT * FROM `project.dataset.table1`
UNION ALL
SELECT * FROM `project.dataset.table0`
LEFT JOIN (SELECT '', '') ON FALSE

with result 

Row     img1    img2    img3     
1       bbb     ccc     ddd  
2       eee     fff     ggg  
3       aaa     null    null     
4       xxx     null    null     
5       yyy     null    null     
6       zzz     null    null

Upvotes: 5

Related Questions