google-bigquery - 从非分区表迁移到分区表

Question

6月，BQ团队announced support for date-partitioned tables。但是该指南缺少如何将旧的未分区表迁移到新样式中。

我正在寻找一种方法来将几个表（如果不是全部）更新为新样式。

在DAY类型之外，还有其他可用的选项吗？ BQ UI是否显示此信息，因为我无法从BQ Web UI创建这样的新分区表。

Answer 1

来自Pavan的答案：请注意，这种方法将向您收取查询源表扫描次数的次数。

来自Pentium10的评论：因此，假设我有数年的数据，我需要每天准备不同的查询并运行所有查询，并且假设我有1000天的历史，就需要从源头支付完整查询价格的1000倍表？




正如我们所看到的-这里的主要问题是每天进行全面扫描。其余的问题不大，可以在任何client of the choice中轻松编写脚本

因此，下面是-如何对表进行分区，同时避免每天进行全表扫描？

下面分步展示了该方法

它足够通用，可以扩展/适用于任何实际用例-同时我正在使用bigquery-public-data.noaa_gsod.gsod2017，并且我将“运动”限制为仅10天以保持可读性

第1步-创建数据透视表
在这一步中，我们
a）将每一行的内容压缩到记录/数组中
和
b）将它们全部放入相应的“每日”列

#standardSQL
SELECT
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170101' THEN r END) AS day20170101,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170102' THEN r END) AS day20170102,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170103' THEN r END) AS day20170103,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170104' THEN r END) AS day20170104,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170105' THEN r END) AS day20170105,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170106' THEN r END) AS day20170106,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170107' THEN r END) AS day20170107,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170108' THEN r END) AS day20170108,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170109' THEN r END) AS day20170109,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170110' THEN r END) AS day20170110
FROM (
  SELECT d, r, ROW_NUMBER() OVER(PARTITION BY d) AS line
  FROM (
    SELECT 
      stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r
    FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t 
    GROUP BY stn, d
  ) 
)
GROUP BY line  

在Web UI中以ivot_table（或任何首选名称）作为目标在查询上方运行

如我们所见-在这里，我们将获得包含10列的表格-一天的一列，每列的架构是原始表架构的副本：



第2步–一对一地处理分区，仅扫描相应的列（不进行全表扫描）–插入相应的分区

#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170101) AS r

从Web UI运行以上查询，并使用名为mytable $ 20160101的目标表

你第二天可以跑

#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170102) AS r

现在您应该将目标表设置为mytable $ 20160102，依此类推



您应该能够与所选的任何客户端自动执行/编写脚本

使用上述方法的方式有多种-由您的创造力决定

注意：BigQuery在表格中最多允许10000列，因此一年中各天的365列绝对不是问题：o）
除非对使用新分区的距离有限制，否则我听说（但还没有机会检查）现在距现在还不到90天


更新资料


请注意：
上面的版本具有一些额外的逻辑，即将所有聚合的单元打包到尽可能少的最终行数中。

ROW_NUMBER() OVER(PARTITION BY d) AS line
然后
GROUP BY line
随着
ARRAY_CONCAT_AGG(…)
做这个

当原始表格中的行大小不那么大时，此方法效果很好，因此最终合并的行大小仍将在BigQuery所具有的行大小限制之内（我相信目前为10 MB）

如果您的源表的行大小已接近该限制，请使用低于调整后的版本

在此版本中–分组被删除，因此每一行只有一个列的值

#standardSQL
SELECT
    CASE WHEN d = 'day20170101' THEN r END AS day20170101,
    CASE WHEN d = 'day20170102' THEN r END AS day20170102,
    CASE WHEN d = 'day20170103' THEN r END AS day20170103,
    CASE WHEN d = 'day20170104' THEN r END AS day20170104,
    CASE WHEN d = 'day20170105' THEN r END AS day20170105,
    CASE WHEN d = 'day20170106' THEN r END AS day20170106,
    CASE WHEN d = 'day20170107' THEN r END AS day20170107,
    CASE WHEN d = 'day20170108' THEN r END AS day20170108,
    CASE WHEN d = 'day20170109' THEN r END AS day20170109,
    CASE WHEN d = 'day20170110' THEN r END AS day20170110
FROM (
    SELECT 
        stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r
    FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t 
    GROUP BY stn, d
)
WHERE d BETWEEN 'day20170101' AND 'day20170110'

就像您现在看到的那样-数据透视表（sparce_pivot_table）足够稀疏（相同的21.5 MB，但现在是114,089行，而数据透视表中的11,584行），因此平均行大小为190B，而初始版本为1.9KB。显然，与示例中的列数相比，这大约少了10倍。
因此，在使用这种方法之前，需要做一些数学运算来计划/估计什么以及如何完成！



仍然：数据透视表中的每个单元格都是原始表中整行的JSON表示形式。这样，它不仅保存原始表中行的值，而且还具有模式



因此，它非常冗长-因此单元格的大小可能是原始大小的几倍[这限制了此方法的使用...除非您更具创造力：o）...此处仍然有很多区域申请：o）]