为了账号安全,请及时绑定邮箱和手机立即绑定

从非分区表迁移到分区表

/ 猿问

从非分区表迁移到分区表

慕丝7291255 2019-10-19 16:17:26

6月,BQ团队宣布支持日期分区表。但是该指南缺少如何将旧的未分区表迁移到新样式中。


我正在寻找一种方法来将一些表(如果不是全部)更新为新样式。


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


查看完整描述

3 回答

?
缥缈止盈

来自Pavan的答案:请注意,此方法将向您收取查询源表的扫描费用,其查询次数与查询次数相同。

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

正如我们所看到的-这里的主要问题是每天进行全面扫描。剩下的问题就不那么多了,可以轻松地在任何选择的客户端中编写脚本


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


下面分步展示了该方法


它足够通用,可以扩展/适用于任何实际用例-同时我正在使用,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  

使用pivot_table(或任何首选名称)作为目标在Web UI中运行以上查询


如我们所见-在这里,我们将获得具有10列的表-一天的一列,每列的模式是原始表的模式的副本:

//img3.mukewang.com/5daac6ca0001a0a412711078.jpg

步骤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,依此类推

//img.mukewang.com/5daac6d40001d3a412741079.jpg

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


使用上述方法的方式有很多-取决于您的创造力


注意: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倍。

因此,在使用这种方法之前,需要做一些数学运算以计划/估算什么以及如何完成!

//img.mukewang.com/5daac6e00001a84a10820910.jpg

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

//img1.mukewang.com/5daac6ee0001e03a06690942.jpg

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


查看完整回答
反对 回复 2019-10-19
?
芜湖不芜

在BigQuery中推出新功能之前,还有另一种(便宜得多)使用Cloud Dataflow划分表的方法。我们使用这种方法而不是运行数百条SELECT *语句,这将使我们损失数千美元。

  1. 使用常规partition命令在BigQuery中创建分区表

  2. 创建数据流管道并使用接收BigQuery.IO.Read器读取表

  3. 使用分区转换对每一行进行分区

  4. 一次最多使用200个分片/接收器(不超过此数量,并且达到API限制),BigQuery.IO.Write为每天/分片创建一个接收器,使用分区装饰器语法将其写入相应的分区-"$YYYYMMDD"

  5. 重复N次,直到处理完所有数据。

这是Github上的示例,可以帮助您入门。

您仍然需要为Dataflow管道付费,但这只是SELECT *在BigQuery 中使用倍数的成本的一小部分。


查看完整回答
反对 回复 2019-10-19
?
哆啦的时光机

从今天开始,您现在可以通过查询非分区表并指定分区列来创建分区表。您需要为原始(未分区)表进行一次全表扫描。注意:此功能目前处于测试阶段。


https://cloud.google.com/bigquery/docs/creating-column-partitions#creating_a_partitioned_table_from_a_query_result


要根据查询结果创建分区表,请将结果写入新的目标表。您可以通过查询分区表或非分区表来创建分区表。您不能使用查询结果将现有标准表更改为分区表。


查看完整回答
反对 回复 2019-10-19

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信