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

问题描述

我想要做的事情应该很简单：给定一个BigQuery模式，我想选择除了几个之外的所有表（包括嵌套的表）。棘手的是，BigQuery有一个嵌套结构，我想排除的少数嵌套在其他记录中。

我发现SELECT *一个href =https://cloud.google.com/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 
  

通过从结果中删除有效负载记录，这就是我们所期望的。现在让我们想象我们只想移除payload.comment，从而在响应中保留有效负载记录内容的其余部分。我试过

  #standardSQL 
 SELECT * except（payload.comment）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 1000

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

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