Google Cloud｜高效赋能 BigQuery：Partitioned and Clustered Table
伴随您的业务拓展，越来越多资料将会被储存在 BigQuery 内。在运算上 BigQuery 会需要扫描更多资料，造成了更长的等待时间和更多的成本支出。为了 帮助企业解决这个时间成本的问题及花费 ，Google 也提供了一系列的方法来帮助企业最佳化 BigQuery 的使用。
BigQuery 知识点
BigQuery 是 Google 提供完全托管、企业级的资料仓储（Dataware house）服务。当您使用 BigQuery 一段时间后，会发现随着您的业务拓展，越来越多资料将会被储存在 BigQuery 內。而无可避免地，由于在运算上 BigQuery 会需要扫描更多资料，造成了更长的等待时间和更多的成本支出。
01 如何有效地将 BigQuery 使用到最佳化？
为了帮助企业解决这个时间成本的问题及花费，Google 也提供了一系列的方法来帮助企业最佳化 BigQuery 的使用，其中包含了使用 preview 功能取代 Query 来检视资料表、避免使用 selcet *、使用 Caching results、设定 Query 资料量上限等等的解決方法。
但是若您发现已经 最佳化您的 Query 但仍然 无法有效降低 BigQuery 的等待时间并控制成本的支出 ，不仿可以重新设计您的资料表，也就是使用 BigQuery 的 Partitioned Table 和 Clustered Table。
这篇文章将会带您认识这两种特殊的资料表，并比较两者间的不同，并且在最后通过实际测试展示其带来的效果。
02 BigQuery 特殊资料表 - Partitioned Table
Partitioned Table 是在 BigQuery 中 一种将资料进行分区的特殊资料表 。将大型资料表切割成不同分区不仅可以增进 Query 的效能，同时可以减少每次 Query 读取的资料量来减少相关成本支出。而当新资料写入分区资料表时也会自动根据分区规划来写入适合的分区。
在 BigQuery 中可以根据以下类别将资料表进行分区：
1、时间栏位
2、资料导入时间
3、整数栏位
一、以时间栏位进行分区
资料表根据 TIMESTAMP, DATE, or DATETIME 栏位来进行分区。对于 TIMESTAMP ，DATE 的栏位可以使用年、月、日、小时的方式来将资料表进行分区，而 DATETIME 栏位可以使用年、月、日的方式来将资料表进行分区。下图利用 DATETIME 栏位和 Monthly 的方式来切割资料表：
此外，BigQuery 也会自动创建两个额外的分区：
1、__NULL__：包含了时间栏位为 null 的资料。
2、__UNPARTITIONED__：包含了栏位时间点在 1960-01-01 之前或是 2159-12-31 之后的资料。
二、以资料导入时间进行分区
资料表根据资料导入 BigQuery 的 timestamp 来进行分区。同样，可以根据年、月、日、小时的方式来将资料表进行分区。
对于以资料导入时间为标准进行分区的资料表，BigQuery 会自动新增一个 pseudocolumn：_PARTITIONTIME 来将导入时间点转换为分区时间点，如下图所示：
由于这个 partition table 是根据每小时来进行分区，所以在 _PARTITIONTIM E栏位显示的是 每小时的分区时间点 。
对于时间单位分区表或是导入时间分区表该选择用什么样的时间单位（小时、日、月、年）进行切割，可以参考以下建议：
1、以 天为单位 （Daily Partitioning）是预设的切割方法，适用于当资料时间点横跨许多不同日期。
2、以 小时为单位 （Hourly Partitioning）的切割适合在短期的时间内（e.g., 6个月内）有大量的资料。但必须注意的是一个分区资料表最多只能有4000个分区。
3、以 月或是年为单位 （Monthly or Yearly Partitioning）的切割适合资料时间点范围很广（e.g., 超过500天）但每日的时间点相对少的资料表。
三、以整数栏位进行分区
资料表根据整数栏位来进行分区。以这种方式进行分区时，必须提供以下资讯：
1、整数列的名称
2、开始数值
3、结束数值
4、间隔
举例来说，若给定 user_id 为整数列、开始数值为1、结束数值为100、间隔为10，则第一个分区为 user_id 1-10、第二个分区为 user_id 11-20、第三个分区为 user_id 21-30，以此类推。若某个资料点的 user_id 不在 1-100 的区间内，则其会被分派到 __UNPARTITIONED__ 的分区；若某资料点 user_id 为 null，则其会被分派到 __NULL__ 的分区。
03 如何在 BigQuery 中建立 Partitioned Table?
在 BigQuery 中 Partitioned Table 必须从一开始就建立，建立 Partitioned Table 和一般 Table 在语法上大同小异。Partitioned Table 必须在 PARTITION BY 加入以哪个 column 为基础来进行分类 ，并且在 OPTIONS 选择是否设定分区失效时间和是否设定 partition filter requirements（也就是对 table 进行查询都必须使用 “where” 来过滤 partition 的栏位）。
1、以 时间栏位 进行分区：
CREATE TABLE
mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
transaction_date
OPTIONS
(partition_expiration_days=3,
require_partition_filter=true)
2、以 导入时间 进行分区：
CREATE TABLE
mydataset.newtable (transaction_id INT64)
PARTITION BY
_PARTITIONDATE
OPTIONS(
partition_expiration_days=3,
require_partition_filter=true)
3、以 整数栏位 进行分区：
CREATE TABLE
mydataset.newtable (customer_id INT64, date1 DATE)
PARTITION BY
RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))
OPTIONS(
require_partition_filter=true)
此外，BigQuery 并不支持 将一般 Table 转换为 Partitioned Table，必须重新建立一个新的Partitioned Table：
CREATE TABLE
mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
transaction_date
AS
SELECT
transaction_id, transaction_date
FROM
mydataset.mytable
04 BigQuery 特殊资料表- Clustered Table
除了 Partitioned Table 之外，BigQuery 中的 Clustered Table 同样可以提升查询速度并减少成本花费 。Clustered table 中资料会自动根据一个或多个指定栏位中的值来进行重新排序。
当查询中的过滤条件或聚合运算包含了指定栏位时，BigQuery 可以避免扫描不必要的数据。若使用多個指定栏位來创建分群资料表时指定栏位的顺序决定了数据的排序顺序，因此 必须特別注意指定栏位的顺序 。若有新的资料写入Clustered Table，BigQuery 将会自动根据其指定栏位的值进行重新排序 (Auto re-clustering)。
一般來说，高基数(High Cardinality)或非时间栏位适合当作 Clustered Table 的指定栏位。请参考下图，除了使用时间栏位进行分区也同时使用 Tags 栏位來进行 cluster。可以看到资料表在进行 cluster 后，在 Tags 栏位中相近的值会被排序再一起。
此外，若查询一个小于 1GB 资料表或分区时，Clustered Table 在效能上并不会有太显著的进步。
05 如何在 Big Query 中建立 Clustered Table?
BigQuery 支持不同方法来建立 Clustered Table，基本 SQL 语法可以参考以下：
CREATE TABLE mydataset.myclusteredtable
(
customer_id STRING,
transaction_amount NUMERIC
)
CLUSTER BY
customer_id
OPTIONS (
description=”a table clustered by customer_id”)
06 比较 Partitioned Table 与 Clustered Table
一般来说，不论是 Partitioned Table 或是 Clustered Table 都可以增加资料查询的效率并减少相对应成本费用的方法。但 Partitioned Table 和 Clustered Table 分別有其适用的情境：
Partitioned Table：
1、您想在查询运行之前了解查询成本。Partitioned Table 在进行 Query 前会先有 Dry Run 来进行 Partition pruning，因此可以事先知道查询成本。而 Clustered pruning 则是在查询运行当下才会进行，所以无法事前掌握相关成本。
2、您需要 partition-level 的管理，如设定分区失效时间、将资料写入特定分区等。
3、您想要选择资料切割方法，如以时间栏位或是整数栏位进行分区。
Clustered Table：
1、沒有严格的成本限制，可以接受再查询前的成本预算误差。
2、您需要比单独使用 partitioned table 更细致的进行切割资料。在同一栏位可以同时进行 partitioning 和 clustering。
3、您的查询常常会使用某些特定的栏位。
4、指定的栏位拥有 High Cardinality ，也就是在指定栏位中的值基本上不重复。
除了以上各自适用的情境，您也可以在同一张资料表中同时使用 partitioning 和 clustering 来达到更精细的资料排序。资料首先会进行 partitioning ，然后在每一区中进行 clustering。另一方面，由于在查询前只会显示在 partition pruning 之后的成本，所以 实际查询成本可能会比预先显示的更低 。
07 BigQuery 实战测试
我们利用 BigQuery 公开资料集中的 iowa_liquor_sales 来进行测试。这是一个酒商的销售资料，其中包含了销售日期（YYYY/MM/DD）、店家id、店家名称、城市、商品名称、数量等栏位。Table 大小为 6.28GB。
在衡量的指标方面，主要会以 Elpased time 和 Slot time 作为比较指标。
- Elapsed Time： BigQuery 执行查询所花费的总时间。
- Slot Time：vCPU 执行查询所用的总时间。
BigQuery 是基于一个分布式平行运算的架构，所以基本上来说 slot time 会大于 elapsed time 。但对于规模较小的查询，偶尔 elapsed time 会大于 slot time，是因为 BigQuery 需要一些时间来整合运算结果。
不论是 Elpased time 或是 Slot time， 值越大就代表查询需要更多时间 。
请注意： 在进行测试前，请先在Query Settings中将快取的功能关闭才能争取衡量效能的差异。
首先，我们针对 iowa_liquor_sales 进行一个简单的 Query，查看在 2014-01–01 至 2020-01-01 间且城市栏位为 “Dubuque” 的资料：
可以看到在 Execution Details 中看到 Elapsed time 为4秒、slot time 为3秒，且需要扫描整个 table（6.28GB）。
接着我们建立一个新的 table：iowaliquor.sales_partitioned_clustered 并针对原本 table 中的 date 栏位进行 partitioning 和 city 栏位进行 clustering：
建立完成后，针对新的 table 在进行相同的查询：
可以看到 Elapsed time 和 slot time 已经降至为2秒，且只需扫描 3.54GB 的资料。
实践证明 table partitioning 和 clustering 可以加速 query 的速度，并減少 BigQuery 扫描的数据量。 若使用资料量更大的 table，其中差距将会更加明显。
08 思想科技 Master Concept 能做什么？
看完这篇文章，相信您已经了解了 Partitioned table 和 Clustered table 带来的优势。想要开启 BigQuery 优化之旅- 欢迎直接 联系我们 ，让我们知道您的想法。我们的团队渴望看到这些功能为您的出海业务开辟了哪些新的可能性。