bigquery merging same columns from different tables

Question

I have 2 tables in BigQuery and I want to merge their columns together and stack the data to get 1 big table with all the data. Effectively, the tables contain same data, but few columns have different names, while few have same names.

Below is an example of how data exists in these tables:

Table1:

Date     | BU  | Campaign | Impressions | Clicks
01/01/15 | XYZ |  C1      |    500      |   20

Table2:

Date     | BU  | Campaign | Total_Impressions | Total_Clicks
01/01/16 | ABC |  C2      |    600            | 30

Expected output:

Table3:

Date     | BU  | Campaign | Impressions | Clicks
01/01/15 | XYZ |  C1      |    500      |   20
01/01/16 | ABC |  C2      |    600      |   30

How can I do this in BigQuery?

Answer 1

You are looking for union all:

select bu, campaign, impressions, clicks
from table1
union all
select bu, campaign, total_impressions, total_clicks
from table2;

Answer 2

By default BigQuery still uses its own legacy SQL dialect. There you can UNION multiple tables with a comma as explained in the reference.

For it to work the columns from each table first need to get the same name.

So the query then becomes:

SELECT *
FROM 
  (select bu, campaign, impressions, clicks from table1),
  (select bu, campaign, total_impressions AS impressions, total_clicks AS clicks from table2)

When using the new standard SQL instead of the legacy dialect, you can use a union all statement.