我目前正在使用一个使用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 回答
您是否熟悉标准SQL的MERGE语句和clustering feature已发布?这实际上可以合并您的数据,您可以进一步customize it只读取一些分区 .
手册示例:
提示:您可以按
null
进行分区,并仅使用'clustering level'