我想要做的事情应该很简单：给定一个BigQuery模式，我想选择除了几个之外的所有表（包括嵌套的表）。棘手的是，BigQuery有一个嵌套结构，我想排除的少数嵌套在其他记录中。
我发现SELECT *一个href =cloud.google/bigquery/docs/reference/standard-sql/query-syntax#select-modifiers =nofollow noreferrer> BigQuery文档，这看起来很有前途。例如，使用公共github_nested数据集，我们可以编写一个查询，比如
$（$） b $ b #standardSQL SELECT *除（有效载荷）FROM`bigquery-public-data.samples.github_nested` LIMIT 1000通过从结果中删除有效负载记录，这就是我们所期望的。现在让我们想象我们只想移除payloadment，从而在响应中保留有效负载记录内容的其余部分。我试过
#standardSQL SELECT * except（payloadment）FROM`bigquery-public-data.samples。 github_nested` LIMIT 1000然而，这是失败的。
想到这个问题的方法是，您仍然希望结果中有一个有效内容列，但您希望它有不同的结构，即排除评论。在这种情况下，您可以使用 SELECT * REPLACE 进行修改。例如，
#standardSQL SELECT * REPLACE（（SELECT AS STRUCT payload。* EXCEPT（comment））AS有效载荷）从`bigquery-public-data.samples.github_nested` LIMIT 1000;
What I want to do should be simple: Given a BigQuery schema, I want to select all tables (including nested ones) apart from a few. The tricky thing is that BigQuery has a nested structure and the few I want to exclude are nested within other records.
I've found the SELECT * except clause in the BigQuery documentation which seems very promising. The problem is that it doesn't seem to support the nested structure exclusion.
For example, using the public github_nested dataset we can write a query like
#standardSQL SELECT * except (payload) FROM `bigquery-public-data.samples.github_nested` LIMIT 1000This does what we expect successfully by removing the payload record from the results. Let's imagine now that we only want to remove payloadment, thereby preserving the rest of the payload record contents in the response. I tried
#standardSQL SELECT * except (payloadment) FROM `bigquery-public-data.samples.github_nested` LIMIT 1000However, this fails.
Anyone know of a way to accomplish this?
Thanks!
解决方案The way to think of the problem is that you still want a payload column in the result, but you want it to have a different structure, namely to exclude comment. In this case, you can use SELECT * REPLACE to make the modification. For example,
#standardSQL SELECT * REPLACE ((SELECT AS STRUCT payload.* EXCEPT (comment)) AS payload) FROM `bigquery-public-data.samples.github_nested` LIMIT 1000;
更多推荐
BigQuery select *除了嵌套列
发布评论