Как удалить STRUCT из ARRAY в поле NESTED
Есть ли простой способ удалить STRUCT из вложенного и повторяющегося поля в BigQuery (тип столбца таблицы BQ: RECORD, Mode: REPATED).
Допустим, у меня есть следующие таблицы:
- список желаний
name toy.id toy.priority
Alice 1 high
2 medium
3 low
Kazik 3 high
1 medium
- игрушки
id name available
1 car 0
2 doll 1
3 bike 1
Я хочу УДАЛИТЬ из списка желаний игрушки, которых нет в наличии (toys.available==0). В данном случае это toy.id==1.
В итоге список желаний будет выглядеть так:
name toy.id toy.priority
Alice 2 medium
3 low
Kazik 3 high
Я знаю, как его выбрать:
WITH `project.dataset.wishlist` AS
(
SELECT 'Alice' name, [STRUCT<id INT64, priority STRING>(1, 'high'), (2, 'medium'), (3, 'low')] toy UNION ALL
SELECT 'Kazik' name, [STRUCT<id INT64, priority STRING>(3, 'high'), (1, 'medium')]
), toys AS (
SELECT 1 id, 'car' name, 0 available UNION ALL
SELECT 2 id, 'doll' name, 1 available UNION ALL
SELECT 3 id, 'bike' name, 1 available
)
SELECT wl.name, ARRAY_AGG(STRUCT(unnested_toy.id, unnested_toy.priority)) as toy
FROM `project.dataset.wishlist` wl, UNNEST (toy) as unnested_toy
LEFT JOIN toys t ON unnested_toy.id=t.id
WHERE t.available != 0
GROUP BY name
Но я не знаю, как удалить структуры <toy.id, toy.priority> из списка желаний, когда toys.available==0.
Есть очень похожие вопросы типа Как удалить/обновить вложенные данные в bigquery или Как удалить строки из структуры в bigquery, но ответы либо мне непонятны в плане удаления, либо предлагают скопировать весь список желаний в новую таблицу с помощью оператора выбора. Мой «список желаний» огромен, а «toys.availabilty» часто меняется. Копирование мне кажется очень неэффективным.
Не могли бы вы предложить решение, соответствующее лучшим практикам BQ?
Спасибо!
запрос в вопросе делает именно то, о чем вы просите - в чем проблема? просьба уточнить!! Если вы имели в виду физически удалить эти структуры в исходной таблице - то нет особого смысла, а вдруг в следующий раз эта удаленная из вишлиста игрушка станет доступной?! в любом случае - пожалуйста, уточните, что именно вы хотите выполнить
@MikhailBerlyant, пожалуйста, не храните данные так сильно. Знание того, как удалять определенные структуры, очень помогло бы мне. Это потому, что в реальном сценарии в wishlist у меня есть прогнозы, основанные на разных моделях машинного обучения. Структуры содержат ссылки на группы прогнозов/описания (которые находятся в списке toys), которым принадлежит выборка. Но некоторые модели обновляются при определенных условиях, и в результате некоторые из назначений этих идентификаторов недействительны. Поэтому определенный идентификатор должен быть заранее удален из структур. Они неверны, замедляют запросы и имеют ненулевой углеродный след :) Спасибо.
Спасибо @SergeyGeron. Я боялся, что это будет вывод, так как это действительно один из принципов дизайна BQ. Однако, поскольку удаление строк было реализовано в BQ, я подумал, что удаление STRUCT внутри строки также возможно.
Ответы 2
Вы можете UNNEST() и объединить:
SELECT wl.name,
(SELECT ARRAY_AGG(t)
FROM UNNEST(wl.toy) t JOIN
toys
ON toys.id = t.id
WHERE toys.available <> 0
) as available_toys
FROM `project.dataset.wishlist` wl;
Спасибо, Гордон, но это не операция удаления. В моем сценарии я хотел бы удалить некоторые STRUCT, потому что в определенный момент они могут стать ошибочными. Если УДАЛЕНИЕ невозможно, наиболее логичным решением для меня, вероятно, является ОБНОВЛЕНИЕ этих строк с измененным МАССИВОМ СТРУКТОВ. Что ты думаешь?
@Лукиз. . . Я не понимаю вашего комментария. Это ничего не "удаляет". Он возвращает новый набор результатов, содержащий только доступные игрушки.
Да, это правда. Но, пожалуйста, прочитайте заголовок и сам вопрос.
... так как удаление строк было реализовано в BQ, я подумал, что удаление STRUCT внутри строки также возможно.
Для этого вы можете использовать UPDATE DML (не DELETE, так как он используется для удаления целых строк, а UPDATE можно использовать для изменения строки)
update `project.dataset.wishlist` wl
set toy = ((
select array_agg(struct(unnested_toy.id, unnested_toy.priority))
from unnest(toy) as unnested_toy
left join `project.dataset.toys` t on unnested_toy.id=t.id
where t.available != 0
))
where true;
Я думаю, что это ответ, который я искал, но позвольте мне проверить его завтра. Спасибо!
конечно. не торопись :о)
BigQuery не очень эффективен при частом изменении данных. Он в основном предназначен для таблиц данных только для добавления. Возможно, лучше подойдет Bigtable.