Самый эффективный способ фильтровать строки bigquery по последней дате

В настоящее время я работаю над конвейером ETL, который использует BigQuery для хранения промежуточных данных, а затем использует 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)

Но при предварительном просмотре ГБ / оценочной стоимости запроса это кажется очень неэффективным и дорогим.

Следующее, что мы попробовали, - это жестко запрограммировать дату, что по какой-то причине было намного дешевле / быстрее:

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

Итак, наш текущий план состоит в том, чтобы поддерживать представление для каждой таблицы и обновлять жестко запрограммированную дату в представлении SQL через Python SDK каждый раз, когда промежуточные данные успешно завершаются (https://cloud.google.com/bigquery/docs/managing-views).

Похоже, что мы потенциально упускаем более простое и эффективное решение этой проблемы. Поэтому я хотел спросить:

  • Что более рентабельно - выполнить эту первоначальную фильтрацию по дате в Dataprep или BigQuery?
  • Каков наиболее экономичный способ фильтрации данных в выбранном продукте?

Почему вы использовали (SELECT _PARTITIONTIME as pt FROM {project}.{dataset}.{table} GROUP BY _PARTITIONTIME ORDER BY _PARTITIONTIME DESC LIMIT 1) вместо MAX(_partitiontime)? GROUP BY и особенно ORDER BY чрезвычайно ресурсоемки ...

Martin Weitzmann 10.08.2018 15:54

Привет, Мартин. Мы пробовали MAX (_PARTITIONTIME) задолго до этого, и это не повлияло на эффективность или стоимость запроса (и то, и другое все еще было очень высоким).

James Thompson 10.08.2018 16:39
2
2
1 552
1

Ответы 1

Вы знакомы с оператором ОБЪЕДИНЕНИЕ стандартного SQL и выпущенным функция кластеризации? это может фактически объединить ваши данные, и вы можете дополнительно настроить это читать только некоторые разделы.

Пример из руководства:

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 и использовать только «уровень кластеризации»

Другие вопросы по теме