CODEWITHSUNDEEP
google-bigquery
N.N.
N.N.

Reputation: 3172

Union tables with different schema

I am referring to Jordan's answer regarding "dealing with evolving schemas "Dealing with evolving schemas.

As I share similar problem I have tried to query tables with different schema and got the following results:

Select a,b,c 
FROM (Select 1 as a, 2 as b), --Test_a
     (Select 1 as a, 2 as b, 3 as c), --Test_b

runs fine... I have put Test_a and Test_b into physical tables (all fields are nullable) and tried:

 Select a,b,c 
 FROM (Select a,b, from BI_WORKSPACE.Test_a), 
      (Select a,b,c from BI_WORKSPACE.Test_b)

It also runs fine

but when i tried 

 Select a,b,c 
FROM BI_WORKSPACE.Test_a,
     BI_WORKSPACE.Test_b

It failed... Is there a bug, something i do wrong? the last sample is the one i am after as it allows me to "evolve" my schema over time. i would like to avoid altering schema of all existing tables whenever i add a column to support a new business need.

Many thanks for your help.

The reason for asking: We hold our data in "Daily tables" so when querying we pay only for the period we are interested in. As BQ doesn’t support "Dynamic SQL", we have created an offline process that takes a query template and generates a query for desired period. Something like: Input: 

Select a,b,c FROM [<Source>]

Output: 

Select a,b,c FROM [MYDATASET.TABLE20140201], [MYDATASET.TABLE20140202], [MYDATASET.TABLE20140203], [MYDATASET.TABLE20140204] , [MYDATASET.TABLE20140205] , [MYDATASET.TABLE20140206] , [MYDATASET.TABLE20140207]

Our process is unaware of the query logic. Sometimes we add fields to support evolving business needs. Using dynamic sub selects will complicate staff a lot, and altering the schema for all hundreds of existing tables is expensive and prone to mistakes. Any suggestions?

Upvotes: 3

Views: 6784

Answers (2)

tworec
tworec

Reputation: 4747

It's possible to select from union of tables with different schemas. Simple trick is to use subquery with asterisk as Jordan proposed. There's no need to alter schema.

In your case this will work (legacy SQL dialect)

SELECT a,b,c 
FROM ( SELECT * FROM BI_WORKSPACE.Test_a ),
     ( SELECT * FROM BI_WORKSPACE.Test_b )

Upvotes: 1

Jordan Tigani
Jordan Tigani

Reputation: 26637

I don't think the last query should work. You're asking for columns a,b, and c from two tables, but one of those tables doesn't have a column with that name. That looks like a query error to me, since you are explicitly asking for a column that doesn't exist on the table.

There is a workaround -- to use a subselect -- which you noticed, if you know that a field may be missing from one schema. The other workaround, of course, is to update the schema.

This seems like it is working as intended. If you don't agree, can you let me know why?

Upvotes: 2

Related Questions