时间戳

首页 > 解决方案 > 如何合并 BigQuery 数据集中的一千个表？

sql - 如何合并 BigQuery 数据集中的一千个表？

问题描述

我在 BigQuery 中有一个数据集，其中包含大约 1000 个表，每个变量一个表。每个表包含两列：observation_number、variable_name。请注意，variable_name 列假定实际的变量名称。每个表至少包含 20000 行。在观察数上合并这些表的最佳方法是什么？

我开发了一个 Python 代码，它将在 Cloud Function 上运行，它会生成 SQL 查询来合并表。它通过连接到数据集并遍历表以获取所有 table_id 来实现这一点。但是，查询最终会太大，性能也不是很好。

这是生成查询的 Python 代码示例（请注意，它仍在本地运行，尚未在云函数中运行）。

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set project_id and dataset_id.
project_id = 'project-id-gcp'
dataset_name = 'sample_dataset'
dataset_id = project_id+'.'+dataset_name

dataset = client.get_dataset(dataset_id)

# View tables in dataset
tables = list(client.list_tables(dataset))  # API request(s)
table_names = []

if tables:
    for table in tables:
        table_names.append(table.table_id)
else:
    print("\tThis dataset does not contain any tables.")

query_start = "select "+table_names[0]+".observation"+","+table_names[0]+"."+table_names[0]
query_select = ""
query_from_select = "(select observation,"+table_names[0]+" from `"+dataset_name+"."+table_names[0]+"`) "+table_names[0]
for table_name in table_names:
    if table_name != table_names[0]:
        query_select = query_select + "," + table_name+"."+table_name
        query_from_select = query_from_select + " FULL OUTER JOIN (select observation," + table_name + " from " + "`"+dataset_name+"."+table_name+"`) "+table_name+" on "+table_names[0]+".observation="+table_name+".observation"
query_from_select = " from ("+query_from_select + ")"
query_where = " where " + table_names[0] + ".observation IS NOT NULL"
query_order_by = " order by observation"

query_full = query_start+query_select+query_from_select+query_where+query_order_by

with open("query.sql","w") as f:
    f.write(query_full)

这是为两个表生成的查询示例：

select 
  VARIABLE1.observation, 
  VARIABLE1.VARIABLE1, 
  VARIABLE2.VARIABLE2 
from 
  (
    (
      select 
        observation, 
        VARIABLE1 
      from 
        `sample_dataset.VARIABLE1`
    ) VARIABLE1 FULL 
    OUTER JOIN (
      select 
        observation, 
        VARIABLE2 
      from 
        `sample_dataset.VARIABLE2`
    ) VARIABLE2 on VARIABLE1.observation = VARIABLE2.observation
  ) 
where 
  VARIABLE1.observation IS NOT NULL 
order by 
  observation

随着表数量的增长，这个查询变得越来越大。有关如何提高此操作的性能的任何建议？还有其他方法可以解决这个问题吗？

标签： sqlgoogle-cloud-platformgoogle-bigquerygoogle-cloud-functions

解决方案

我不知道这个问题是否有很好的技术答案。似乎您正试图在单个查询中进行大量的连接，而 BQ 的优势并没有通过许多连接来实现。

虽然我在下面概述了一个潜在的解决方案，但您是否考虑过是否/为什么真的需要一个包含 1000 多个潜在列的表？并不是说您没有，但可能有其他方法可以解决您的问题，而无需创建如此复杂的表。

一种可能的解决方案是将您的联接/表子集为更易于管理的块。例如，如果您有 1000 个表，请针对较小的表子集（2/5/10/等）运行脚本并将这些结果写入中间表。然后加入您的中间表。这可能需要几层中间表，具体取决于子表的大小。基本上，您希望最小化（或合理）每个查询中的连接数。完成后删除中间表以帮助减少不必要的存储成本。

推荐阅读