How To Union Queries In Google BigQuery

In the relational databases, we often come across various scenarios where you want to combine multiple queries having different datatypes in BigQuery.

This article will give you basic idea about union in BigQuery and would help in consolidating data from multiple tables and datasets.

In the most relational databases, there may be a situations where you need to combine the results of multiple queries into one single dataset which has different datatype.In BigQuery, this occurs when you are fetching data from multiple tables or even across datasets, and this is where the power of using a Union comes into play.

This article will give you basic idea about union in bigquery and would help in consolidating data from multiple tables and datasets.

Now, we want the Final Output In this format:-

Metrics

TY

LY

Comp
Sales $125,256 $101,253 24%
Visits 4,251 3,892 9%
Orders 1,532 1,203

27%

 

Where :

TY: This Year

LY: Last Year

Comp= (TY-LY)/LY.

Implementation:

Assuming all data sources contain different columns, we can query three different tables in the dummy dataset and combine the result set using the Standard SQL option with Google BigQuery requires a more redundant method when combining result sets.

For example:

The query is invalid since all the queries contain the same number of columns but column is of different data type (first two are integer and comp as string). So we have to convert it in to same data type.

Syntex:

CAST(expr as typename)

For Example:

Now, Suppose we want the above in specific order.

Create new dummy column.

For Example:

Now, We want to print TY,LY,comp only.

A Select * EXCEPT( Column Name) statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.

For example:

Leave a comment