Использование модели добавления для частичного обновления строк в BigQuery

#sql #google-cloud-platform #google-bigquery

#sql #google-облачная платформа #google-bigquery

Вопрос:

Предположим, у меня есть следующая запись в BQ:

 id        name           age    timestamp
1         "tom"          20     2019-01-01

Затем я выполняю два «обновления» для этой записи, используя streaming API для «добавления» дополнительных данных — https://cloud.google.com/bigquery/streaming-data-into-bigquery . В основном это делается для того, чтобы обойти квоту обновления, которую применяет BQ (и у нас есть приложение с высокой скоростью записи).

Затем я добавляю два изменения в таблицу, одно обновление, которое просто изменяет name , а затем одно обновление, которое просто изменяет age . Вот три записи после обновлений:

 id        name           age    timestamp
1         "tom"          20     2019-01-01
1         "Tom"          null   2019-02-01
1         null           21     2019-03-03

Затем я хочу запросить эту запись, чтобы получить самую «актуальную» информацию. Вот как я начал:

 SELECT id, **name**, **age**,max(timestamp)
FROM table
GROUP BY id 
-- 1,"Tom",21,2019-03-03

Как мне получить правильное имя и возраст здесь? Обратите внимание, что для записи могут быть тысячи обновлений, поэтому я не хочу писать 1000 операторов case, если это вообще возможно.

По разным другим причинам у меня обычно не будет всех данных строки за один раз, у меня будет только RowID FieldName FieldValue .

Я полагаю, что план B здесь состоит в том, чтобы выполнить запрос для получения текущих данных, а затем добавить мои изменения, чтобы вставить новую строку, но я надеюсь, что есть способ сделать это за один раз, не выполняя два запроса.

Ответ №1:

Ниже приведен стандартный SQL BigQuery

 #standardSQL
SELECT id, 
  ARRAY_AGG(name IGNORE NULLS ORDER BY ts DESC LIMIT 1)[OFFSET(0)] name,
  ARRAY_AGG(age IGNORE NULLS ORDER BY ts DESC LIMIT 1)[OFFSET(0)] age,
  MAX(ts) ts  
FROM `project.dataset.table`
GROUP BY id

Вы можете протестировать, поиграть с приведенным выше примером данных из вашего вопроса, как в примере ниже

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, "tom" name, 20 age, DATE '2019-01-01' ts UNION ALL
  SELECT 1, "Tom", NULL, '2019-02-01' UNION ALL
  SELECT 1, NULL, 21, '2019-03-03' 
)
SELECT id, 
  ARRAY_AGG(name IGNORE NULLS ORDER BY ts DESC LIMIT 1)[OFFSET(0)] name,
  ARRAY_AGG(age IGNORE NULLS ORDER BY ts DESC LIMIT 1)[OFFSET(0)] age,
  MAX(ts) ts  
FROM `project.dataset.table`
GROUP BY id

с результатом

 Row id  name    age ts   
1   1   Tom 21  2019-03-03

1. Здесь я столкнулся с одной мыслью, которой у меня не было, когда я изначально опубликовал вопрос: что, если для обновляемого значения установлено значение null? Например, если возраст меняется с 20 на null? Как бы вы предложили разобраться с подобным случаем?

2. вы можете использовать RESPECT NULLS вместо IGNORE NULLS — в этом случае имя будет удалено, NULL например

Ответ №2:

Это классический случай применения аналитических функций в стандартном SQL.

Вот как вы можете добиться своих результатов:

 select id, name, age from (
  select id, name, age, ts, rank() over (partition by id order by ts desc) rnk
  from `yourdataset.yourtable`
)
where rnk = 1

Это позволит сгруппировать ваши записи на основе id и выбрать самую последнюю ts (с указанием записи, добавленной последней для данного id ).

1. как бы производительность этого сравнивалась с приведенным выше ответом? Кроме того, я вполне уверен, что это может привести к ошибке нехватки памяти из bq при разделении по идентификатору, не так ли?