CODEWITHSUNDEEP
google-bigquerymultiple-databases
Ilja
Ilja

Reputation: 631

BigQuery - combine tables

I have monthly Data in BigQuery but I want to create a yearly database, meaning combine the 12 subdatabase to 1.

How can it be done?

The structure is identical for all 12 databases in form:

Date, Name, Amount, Value, Type_of_Good

I thought JOIN might help me, but it is not the case.

Thanks

Upvotes: 8

Views: 10661

Answers (3)

TrentWoodbury
TrentWoodbury

Reputation: 911

You can also use a UNION ALL to concatenate tables with identical columns.

SELECT 
    Date, Name, Amount, Value, Type_of_Good
FROM
    january
UNION ALL
SELECT
    Date, Name, Amount, Value, Type_of_Good
FROM
    february

And you can UNION ALL for all the months you need.

Upvotes: 0

Jordan Tigani
Jordan Tigani

Reputation: 26617

Pentium10's suggestion works, but you might also consider two other options:

  1. Use TABLE_QUERY() (described here) which will allow you to construct a query that selects from multiple tables.
  2. Use a view (described here). Note that views can't currently be used with TABLE_QUERY or TABLE_DATE_RANGE (although that functionality should be coming soon!). But a view would allow you to take the query suggested by Pentium10 and save it so that it looks like a single table.
  3. Use table copy with a write disposition of append to copy the individual tables into your year summary table. While this would mean that you would get charged for storage for the new table, it would also let you delete the old tables if you don't need them any more, and be the most flexible option since you then have a real table with the combined data.

Upvotes: 7

Pentium10
Pentium10

Reputation: 207912

you can use the following syntax

SELECT Date, Name, Amount, Value, Type_of_Good
FROM
(select Date, Name, Amount, Value, Type_of_Good from january ...),
(select Date, Name, Amount, Value, Type_of_Good from february ...),
...
(select Date, Name, Amount, Value, Type_of_Good from december ...)

Upvotes: 8

Related Questions