首页 文章

按最新日期过滤BigQuery行的最有效方法

提问于
浏览
2

我目前正在使用一个使用BigQuery存储登台数据的ETL管道,然后使用Dataprep转换数据并将其存储在新的BigQuery表中以进行 生产环境 .

我们遇到的问题是找到最经济有效的方法,将这些转换应用于少量数据,通常只是登台数据表中当前最大日期的最后X天数 . 例如,我们需要计算登台数据中的最大可用日期,然后检索此日期过去3天内的所有行 . 遗憾的是,我们不能依赖临时数据中的“最大日期”始终是最新的(此数据来自不同质量和可靠性的第三方API) .

首先,我尝试通过获取最大日期直接在Dataprep中应用这些变换,使用DATEDIFF创建比较列,然后丢弃超过此“最大日期”3天的行 . 事实证明,这在成本方面非常耗时且效率低 .

我们接下来尝试的是在BigQuery视图中过滤掉数据,然后将其用作Dataprep流的初始数据集(数据将在Dataprep应用任何变换之前进行预过滤) . 我们首先尝试在BigQuery中动态执行此操作,如下所示:

WITH latest_partitiontime AS (SELECT _PARTITIONTIME as pt FROM 
`{project}.{dataset}.{table}`
GROUP BY _PARTITIONTIME
ORDER BY _PARTITIONTIME DESC
LIMIT 1)

SELECT {columns}
FROM `{project}.{dataset}.{table}`
WHERE _PARTITIONTIME >= (SELECT pt FROM latest_partitiontime)

但是在预览GB /估计的查询成本时,它似乎非常低效且昂贵 .

我们接下来要做的就是对日期进行硬编码,由于某种原因这个日期更便宜/更快:

SELECT {columns}
FROM `{project}.{dataset}.{table}`
WHERE _PARTITIONTIME >= '2018-08-08'

因此,我们当前的计划是维护每个表的视图,并在每次登台数据成功完成时通过Python SDK更新视图SQL中的硬编码日期(https://cloud.google.com/bigquery/docs/managing-views) .

感觉我们可能错过了一个更容易/更有效的解决方案来解决这个问题 . 所以我想问:

  • 在Dataprep或BigQuery中按日期执行此初始过滤是否更具成本效益?

  • 过滤所选产品中数据的最具成本效益的方法是什么?

1 回答

  • 3

    您是否熟悉标准SQL的MERGE语句和clustering feature已发布?这实际上可以合并您的数据,您可以进一步customize it只读取一些分区 .

    手册示例:

    MERGE dataset.DetailedInventory T
    USING dataset.Inventory S
    ON T.product = S.product
    WHEN NOT MATCHED AND quantity < 20 THEN
      INSERT(product, quantity, supply_constrained, comments)
      VALUES(product, quantity, true, ARRAY<STRUCT<created DATE, comment STRING>>[(DATE('2016-01-01'), 'comment1')])
    WHEN NOT MATCHED THEN
      INSERT(product, quantity, supply_constrained)
      VALUES(product, quantity, false)
    

    提示:您可以按 null 进行分区,并仅使用'clustering level'

相关问题