Join tables with different schema

Question

I am referring to Jordan’s response regarding “solving evolving schemes”. Working with changing schemes .

Since I have a similar problem, I tried to query tables with a 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 

works fine ... I put Test_a and Test_b in the physical tables (all fields are NULL) 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 works great

but when i tried

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

Failed ... Is there an error, am I doing something wrong? the last sample is the one that I received after it allows me to “develop” my circuit over time. I would like to avoid changing the schema of all existing tables whenever I add a column to support a new business need.

Thank you very much for your help.

Reason: We keep our data in the "Daily Tables", therefore, upon request, we pay only for the period of interest to us. Since BQ does not support Dynamic SQL, we have created an autonomous process that accepts a query template and generates a query for the required period. Something like: Input:

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

Exit:

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

Our process does not know the logic of the request. Sometimes we add fields to support changing business needs. Using dynamic subsamples will complicate staff work, and changing the schema for all hundreds of existing tables is expensive and error prone. Any suggestions?

Answer 1

I don't think the last request should work. You request columns a, b, and c from two tables, but one of these tables does not have a column with that name. It looks like a query error for me, since you are explicitly querying for a column that does not exist in the table.

There is a workaround - use a subheading - which you notice if you know that the field may not be in the same schema. Another workaround, of course, is to upgrade the circuit.

It seems to be working as intended. If you do not agree, can you tell me why?

Answer 2

You can choose from combining tables with different schemas. A simple trick is to use a subquery with an asterisk, as Jordan suggested. No need to change the circuit.

In your case, this will work

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