Clever Geek Handbook

BigQuery - combine tables

I have monthly data in BigQuery, but I want to create an annual database, i.e. combine 12 subtasks into 1.

How can I do that?

The structure is identical for all 12 databases in the form:

Date, name, amount, value, type_good

I thought JOIN might help me, but it doesn't.

thank

+3


source to share


2 answers


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 ...)
+3


source


The Pentium10 proposal works, but you can also consider two other options:



  • Use TABLE_QUERY()

    (described here) which will allow you to build a query that selects from multiple tables.
  • Use the view ( described here ). Please note that views cannot be used with TABLE_QUERY

    or TABLE_DATE_RANGE

    at this time (although this feature should be coming soon!). But the view will allow you to take the query suggested by the Pentium10 and save it so that it looks like a separate table.
  • Use a copy of a record-for-record table to copy individual tables into the PivotTable of the year. While this will mean that you will receive storage fees for the new table, it will also allow you to drop the old tables if you no longer need them, and be the most flexible option since then you have a real table with combined data.
+4


source






More articles:


All Articles