Как объединить и сгруппировать строки в большой таблице BigQuery с проблемами "Превышения ресурсов"

ProgramBox

Как объединить и сгруппировать строки в большой таблице BigQuery с проблемами «Превышения ресурсов»

Post author:admin
Запись опубликована:10 апреля, 2023
Post category:Вопросы по программированию

#sql #google-bigquery

Вопрос:

У меня есть таблица с одним полем с 1,1 миллиардом строк в BigQuery.

Свойства таблицы:

Одно поле, где имя поля — идентификатор и тип поля — строка
Общий размер таблицы — 8,3 ГБ

Я хотел бы создать новый следующим образом:

Первый столбец — это поле UUID, использующее GENERATE_UUID()
Второй столбец, id_str, который представляет собой 25 000 записей идентификаторов, объединенных в этот столбец с разделенными запятыми значениями идентификаторов

Я пробовал разные решения, но продолжаю сталкиваться

«Превышение ресурсов»

Есть ли разумный способ обойти это ограничение? Есть какой-нибудь другой подход к решению моей проблемы внутри BigQuery?

На данный момент у меня есть код, который генерирует вышеупомянутую ошибку

 SELECT
  GENERATE_UUID() as batch_id,
  STRING_AGG(id) as ids_str
from
  (
  WITH vars AS (
  SELECT 25000 as rec_count
)
  SELECT
    cast(ceiling(ROW_NUMBER() OVER ()  / 25000) as int64) as batch_count,
    25000 as rec_count,
    cast(id as string) as id
  FROM
    tbl_profile
)
group by rec_count

1. Почему вы хотите сохранить такую большую строку? Почему бы не создать таблицу поиска для каждой таблицы с идентификатором guid?

2. Чтобы добавить к вопросу @GordonLinoff, отображается ли сообщение об ошибке только «Превышение ресурсов»? или есть другие подробности. Также в вашем SQL вы предоставляете только SELECT часть — это та часть, у которой проблема? Что произойдет, если вы ограничите внутренний SQL WHERE после FROM tbl_profile

3. Цель таблицы — создать 40 тыс. пакетных заданий с 25 тыс. идентификаторами в каждой записи задания и уникальным полем UUID. Я хочу создать таблицу с одной строкой для каждого пакетного задания со строкой из 25 тыс. идентификаторов, чтобы их можно было быстро обслуживать. Таблица результатов BigQuery будет экспортирована в CloudSQL, которая будет распределять задания одно за другим среди рабочих. Я просто хочу разделить строки 1B на куски по 25k, но продолжаю сталкиваться с «Превышением ресурсов», поскольку вы не можете сортировать такие большие наборы данных. Я не знаю, как создать 25k блоков без использования сортировки, я хочу сохранить всю свою логику обработки данных SQL в BQ

4. Я имел в виду «без использования логики номера строки», а не сортировки в ответе выше.

Ответ №1:

Любой другой подход к решению моей проблемы внутри BigQuery?

Если ваш вариант использования позволяет вам немного ослабить требования, поэтому вместо

 The second column to be 25,000 id concatenated into one column

это было бы

 The second column to be about (close to) 25,000 id concatenated into one column

В этом случае ниже (для стандартного SQL BigQuery) может / должно хорошо работать для вас

 #standardSQL
SELECT
  GENERATE_UUID() AS batch_id,
  COUNT(1) batch_size,
  STRING_AGG(id) AS ids_str
FROM (
  SELECT
    CAST((cnt * RAND()) / 25000   0.5 AS INT64) AS batch_count,
    CAST(id AS STRING) AS id
  FROM `project.dataset.table`
  CROSS JOIN (SELECT COUNT(1) cnt FROM `project.dataset.table`)
)
GROUP BY batch_count

это должно привести к результату, как показано ниже

Как вы можете видеть здесь, количество идентификаторов в каждой строке не точно 25 000, но достаточно близко к нему

Надеюсь, это может быть вариантом для вас

1. Ваше предположение верно, и размер пакета около 25 КБ вполне подходит, как объяснено немного подробнее в моем ответе выше. Это решение идеально и поможет мне группировать и объединять без использования логики номера строки. Еще раз спасибо за ваш вклад в это сообщество. Надеюсь, у вас отличный день.

2. Рад, что это помогло. Пожалуйста, также рассмотрите возможность голосования за ответ o)