google-bigquery - BigQuery全表分区

Question

我在一张表中有340 GB的数据（价值270天的数据）。现在计划将这些数据移动到分区表。

这意味着我将有270个分区。将此数据移到分区表的最佳方法是什么。

我不想运行270个查询，这是非常昂贵的操作。因此，正在寻找优化的解决方案。

我有多个这样的表。我需要将所有这些表迁移到分区表。

谢谢，

Answer 1

我看到三个选择


直接从原始表中提取：
操作（要运行的查询数）= [提取的天数] = 270
完全扫描（在原始表的完全扫描中测得的扫描数据量）=天= 270
成本，$ = $ 5 x表格大小，TB x完整扫描= $ 5 x 0.34 x 270 = $ 459.00
分层（递归）提取：（在Mosha的答案中进行了描述）
动作= 2 ^ log2（天）– 2 = 510
完整扫描= 2 * log2（天）= 18
成本$ = $ 5 x表大小，TB x完整扫描= $ 5 x 0.34 x 18 = $ 30.60
聚类提取：（我将在几秒钟内进行描述）
动作=天+1 = 271
完整扫描= [总是] 2 = 2
成本$ = $ 5 x表大小，TB x完整扫描= $ 5 x 0.34 x 2 = $ 3.40


摘要

Method                              Actions Total Full Scans    Total Cost  
Direct Extraction                       270              270       $459.00
Hierarchical(recursive) Extraction      510               18        $30.60
Clustered Extraction                    271                2         $3.40  

绝对，对于大多数实际目的，Mosha的解决方案是可行的方法（我在大多数情况下都使用了该方法）
它相对简单明了

即使您需要运行510次查询-查询“相对”简单，并且编排逻辑也很容易在通常使用的客户端上实现
节省成本是显而易见的！
从460美元降至31美元！
下降了将近15倍！

如果您-
a）希望再降低9倍的成本（因此总共降低了135倍）
b）喜欢玩乐和更多挑战
-看一下第三种选择


“群集提取”说明


想法/目标：
第1步
我们希望将原始表转换为另一个具有270列的[单]表–一天一列
每列将在原始表的相应日期保留一个序列化行
此新表中的总行数将等于大多数“繁重”的一天的行数
这将只需要一个查询（请参见下面的示例）并进行一次完整扫描

第2步
准备好该新表之后，我们将仅提取每天的查询，然后写入最终的日表（日表的模式与原始表的架构完全相同，并且所有这些表都可以预先创建）
这将需要运行270个查询，而扫描的大小大约等于原始表的一个完整大小（这实际上取决于架构的复杂程度，因此可能会有所不同）。
查询列时-我们将需要反序列化行的值并将其解析回原始方案

非常简化的示例：（在此处使用BigQuery标准SQL）

这个例子的目的只是在您发现有趣的想法时提供指导
序列化/反序列化得到了极大简化，以使您专注于思想，而较少关注特定实现，这可能因情况而异（主要取决于模式）

因此，假设原始表（theTable）看起来如下所示

  SELECT  1 AS id, "101" AS x, 1 AS ts UNION ALL
  SELECT  2 AS id, "102" AS x, 1 AS ts UNION ALL
  SELECT  3 AS id, "103" AS x, 1 AS ts UNION ALL
  SELECT  4 AS id, "104" AS x, 1 AS ts UNION ALL
  SELECT  5 AS id, "105" AS x, 1 AS ts UNION ALL
  SELECT  6 AS id, "106" AS x, 2 AS ts UNION ALL
  SELECT  7 AS id, "107" AS x, 2 AS ts UNION ALL
  SELECT  8 AS id, "108" AS x, 2 AS ts UNION ALL
  SELECT  9 AS id, "109" AS x, 2 AS ts UNION ALL
  SELECT 10 AS id, "110" AS x, 3 AS ts UNION ALL
  SELECT 11 AS id, "111" AS x, 3 AS ts UNION ALL
  SELECT 12 AS id, "112" AS x, 3 AS ts UNION ALL
  SELECT 13 AS id, "113" AS x, 3 AS ts UNION ALL
  SELECT 14 AS id, "114" AS x, 3 AS ts UNION ALL
  SELECT 15 AS id, "115" AS x, 3 AS ts UNION ALL
  SELECT 16 AS id, "116" AS x, 3 AS ts UNION ALL
  SELECT 17 AS id, "117" AS x, 3 AS ts UNION ALL
  SELECT 18 AS id, "118" AS x, 3 AS ts UNION ALL
  SELECT 19 AS id, "119" AS x, 4 AS ts UNION ALL
  SELECT 20 AS id, "120" AS x, 4 AS ts

步骤1 –转换表并将结果写入tempTable

SELECT 
  num, 
  MAX(IF(ts=1, ser, NULL)) AS ts_1, 
  MAX(IF(ts=2, ser, NULL)) AS ts_2, 
  MAX(IF(ts=3, ser, NULL)) AS ts_3, 
  MAX(IF(ts=4, ser, NULL)) AS ts_4
FROM (
  SELECT 
    ts, 
    CONCAT(CAST(id AS STRING), "|", x, "|", CAST(ts AS STRING)) AS ser, 
    ROW_NUMBER() OVER(PARTITION BY ts ORDER BY id) num
  FROM theTable
)
GROUP BY num

tempTable将如下所示：

num    ts_1        ts_2         ts_3        ts_4     
1   1|101|1     6|106|2     10|110|3    19|119|4     
2   2|102|1     7|107|2     11|111|3    20|120|4     
3   3|103|1     8|108|2     12|112|3        null     
4   4|104|1     9|109|2     13|113|3        null     
5   5|105|1        null     14|114|3        null     
6      null        null     15|115|3        null     
7      null        null     16|116|3        null     
8      null        null     17|117|3        null     
9      null        null     18|118|3        null    

在这里，我使用简单的串联进行序列化

步骤2 –提取特定日期的行并将输出写入相应的每日表
请注意：在下面的示例中-我们提取ts = 2的行：这对应于ts_2列

SELECT
  r[OFFSET(0)] AS id,
  r[OFFSET(1)] AS x,
  r[OFFSET(2)] AS ts
FROM (
  SELECT SPLIT(ts_2, "|") AS r 
  FROM tempTable 
  WHERE NOT ts_2 IS NULL
)

结果将如下所示（预期）：

id    x    ts    
6   106     2    
7   107     2    
8   108     2    
9   109     2   

我希望我有更多的时间写下来，所以如果缺少某些内容，不要判断太多-这是更具方向性的答案-但同时，示例是非常合理的，并且如果您具有简单的模式-几乎没有多余的思考是必须的。当然，对于记录，模式中的嵌套内容（最具挑战性的部分是序列化/反序列化）–但这就是乐趣所在–节省了额外的$