具有不同架构的联合表 [英] 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

运行良好... 我已经将Test_a和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)

它也可以正常运行

但是当我尝试

 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.

非常感谢您的帮助.

询问原因: 我们将数据保存在每日表格"中，因此在查询时，我们仅支付感兴趣的时间段. 由于BQ不支持动态SQL"，因此我们创建了一个脱机流程，该流程使用查询模板并生成所需时间段的查询.就像是: 输入:

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>] 

输出:

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?

推荐答案

我不认为最后一个查询应该有效.您要从两个表中请求列a，b和c，但是其中一个表没有该名称的列.在我看来，这似乎是一个查询错误，因为您明确要求的是表中不存在的列.

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?

这篇关于具有不同架构的联合表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！