BigQuery select * 除了嵌套列 [英] BigQuery select * except nested column

问题描述

我想做的应该很简单:给定一个 BigQuery 架构，我想选择除少数表之外的所有表(包括嵌套表).棘手的是 BigQuery 具有嵌套结构，而我想排除的少数则嵌套在其他记录中.

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.

我在 BigQuery 文档 看起来很有前途.问题是它似乎不支持嵌套结构排除.

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.

例如，使用公共 github_nested 数据集，我们可以编写一个查询，如

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 1000

通过从结果中删除有效负载记录，这成功地完成了我们的预期.现在让我们想象一下，我们只想删除 payload.comment，从而在响应中保留其余的有效负载记录内容.我试过了

This does what we expect successfully by removing the payload record from the results. Let's imagine now that we only want to remove payload.comment, thereby preserving the rest of the payload record contents in the response. I tried

#standardSQL
SELECT * except (payload.comment) FROM `bigquery-public-data.samples.github_nested` LIMIT 1000

然而，这失败了.

有谁知道实现这一目标的方法吗?

Anyone know of a way to accomplish this?

谢谢！

推荐答案

这个问题的思路是你还是希望结果中有一个payload列，但是你希望它有一个不同的结构，即排除comment.在这种情况下，您可以使用 SELECT * REPLACE 进行修改.例如，

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 * 除了嵌套列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！