时间戳

首页 > 解决方案 > 将数据加载到 BigQuery 中并根据时间对数据进行分区以及按另一个变量拆分

google-bigquery - 将数据加载到 BigQuery 中并根据时间对数据进行分区以及按另一个变量拆分

问题描述

（我对数据库很陌生，所以请告诉我如何才能更好地解决这个问题。）

我正在尝试将多个列式数据文件从 AWS S3 存储桶加载到 BigQuery

以下是相关列的快照： 

timestamp_info_nginx_ms site_code action
          1.539168e+12    site_1  event1
          1.539168e+12    site_2  event2
          1.539168e+12    site_3  event1
          1.539168e+12    site_1  event1
          1.539168e+12    site_2  event2

数据大小为每周 200+GB，我希望能够加载 12 周的数据。

我的目标是尽量减少每月查询成本。

一些背景：我的主要用例是我将一次分析一个网站（或一组网站）的数据。在 150 个网站中，我将主要关注 10-15 个网站。我们称它们为主要网站。我希望定期（每天）分析主要网站，偶尔（每月 1-3 次）或很少（2 个月内 1-3 次）分析其他网站。

我知道我需要按天对数据表进行分区。通过 BigQuery GUI 看起来相对简单。

但是，我的问题是，是否可以将这些数据加载到我的主要网站的单独表中（每个主要网站一个表）并为其余的单独的表加载？

标签： google-bigquery

解决方案

查看 BigQuery 最近发布的称为集群的功能后，这正是我所寻找的。以下代码行将解决我关于示例数据集的问题。

对于这个用例，我假设数据存储在 GCS 中，并且是 ndjson 和压缩文件。

在发布此答案时，无法通过 Web UI 创建聚集表，因此我正在通过安装 gcloud sdk 查看命令行选项。

虽然可以在加载数据时创建分区表（如在表创建和将数据加载到其中可以同时完成），但不可能（截至目前）同时创建集群表。因此，这是一个两步过程，其中第 1 步是创建一个空表；第 2 步是将数据加载到其中。

鉴于我的示例数据集，我的架构将如下所示： 

 [
   {"type": "TIMESTAMP", "name": "timestamp_info_nginx_ms", "mode": "NULLABLE"},
   {"type": "STRING", "name": "site_code", "mode": "NULLABLE"},
   {"type": "STRING", "name": "action", "mode": "NULLABLE"}
  ]

将上述 json 作为 myschema.json 存储在当前工作目录中。
请注意，我的分区字段将是时间戳，而我的集群字段将是 site_code 和 action。完成聚类的顺序很重要。当您对此运行查询时，请记住聚类顺序

在 BigQuery 中创建一个名为 my-dataset 的数据集。

现在在终端中调用 gcloud sdk 的 bq 命令来创建一个表。 

bq mk -t --schema ./myschema.json --time_partitioning_type=DAY --time_partitioning_field timestamp_info_nginx_ms --require_partition_filter=TRUE --clustering_fields='site_code,action' my-dataset.my-clustered-table

这应该在名为 my-dataset 的现有数据集中创建一个名为 my-clustered-table 的新表。

现在在终端中使用 gcloud sdk 的 bq 命令将数据加载到表中。 

bq load --source_format=NEWLINE_DELIMITED_JSON --max_bad_records=1000 my-dataset.my-clustered-table gs://my-bucket/my-json-files/*

这应该有效。

推荐阅读