时间戳

首页 > 解决方案 > 查询 Bigquery 重复字段

google-cloud-platform - 查询 Bigquery 重复字段

问题描述

下面是我的 BigQuery 表的架构。我选择了 sentence_id、store 和 BU_model 并将数据插入到 BigQuery 中的另一个表中。生成的新表的数据类型分别为整数、重复和重复。我想展平/取消嵌套重复的字段，以便在我的第二个表中将它们创建为 STRING 字段。使用标准sql如何实现这一点？

+- sentences: record (repeated)
|  |- sentence_id: integer                                                                                                                             
|  |- autodetected_language: string                                                                                                                    
|  |- processed_language: string 
|  +- attributes: record
|  |  |- agent_rating: integer
|  |  |- store: string (repeated)
|  +- classifications: record
|  |  |- BU_Model: string (repeated)

我用来创建第二个表的查询如下。我想将 BU_Model 作为 STRING 列进行查询。

SELECT sentence_id ,a.attributes.store,a.classifications.BU_Model
FROM staging_table , unnest(sentences) a

预期输出应如下所示：

暂存表：

41783851    regions     Apparel
            district    Footwear
12864656    regions
            district

最终目标表：

41783851    regions     Apparel
41783851    regions     Footwear            
41783851    district    Apparel
41783851    district    Footwear
12864656    regions
12864656    district

我尝试了以下查询，它似乎按预期工作，但这意味着我必须取消嵌套每个预期的重复字段。我在 Bigquery 中的表有 50 多个重复的列。有没有更简单的方法解决这个问题？

SELECT
sentence_id,
flattened_stores,
flattened_Model
FROM `staging`  
left join unnest(sentences) a
left join unnest(a.attributes.store) as flattened_stores
left join unnest(a.classifications.BU_Model) as flattened_Model

标签： google-cloud-platformgoogle-bigquery

解决方案

假设您还需要输出中的三列 - 数组被展平为字符串

SELECT sentence_id , 
  ARRAY_TO_STRING(a.attributes.store, ',') store,
  ARRAY_TO_STRING(a.classifications.BU_Model, ',') BU_Model
FROM staging_table , unnest(sentences) a

更新以解决最近的问题变化

在 BigQuery 标准 SQL 中 - 使用LEFT JOIN UNNEST()（就像您在上一个查询中所做的那样）是执行您想要获得的结果的最合理方式

在 BigQuery Legacy SQL 中 - 您可以使用FLATTEN语法 - 但它具有相同的缺点，即需要对所有 50 多列重复相同的内容

非常简化的例子： 

#legacySQL
SELECT sentence_id, store, BU_Model
FROM (FLATTEN([project:dataset.stage], BU_Model))

结论：我会采用LEFT JOIN UNNEST()方法

推荐阅读