Ответ 1
from Pavans answer: Обратите внимание, что этот подход будет взимать с вас стоимость сканирования исходной таблицы для запроса столько раз, сколько вы запрашиваете его.
из комментариев Pentium10: Итак, предположим, что у меня есть несколько лет данных, мне нужно подготовить разные запросы для каждого дня и запустить все это, и предположим, что у меня 1000 дней в истории, мне нужно заплатить в 1000 раз больше полной цены запроса от источника таблица?
Как мы видим, основная проблема здесь заключается в том, что вы выполняете полное сканирование каждый день. Остальная проблема не является проблемой и может быть легко записана в любом клиенте по выбору
Итак, ниже - Как разделять таблицу, избегая полного сканирования таблицы каждый день?
Ниже, шаг за шагом, показан подход
Достаточно общего для того, чтобы распространяться/применяться к любому реальному случаю использования - тем временем я использую bigquery-public-data.noaa_gsod.gsod2017, и я ограничиваю "упражнение" всего 10 дней, чтобы сохранить его читабельным
Шаг 1 - Создайте сводную таблицу
На этом шаге мы
а) сжать содержимое каждой строки в запись/массив
и
б) поместить их в соответствующую "ежедневную" колонку
#standardSQL
SELECT
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170101' THEN r END) AS day20170101,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170102' THEN r END) AS day20170102,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170103' THEN r END) AS day20170103,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170104' THEN r END) AS day20170104,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170105' THEN r END) AS day20170105,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170106' THEN r END) AS day20170106,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170107' THEN r END) AS day20170107,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170108' THEN r END) AS day20170108,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170109' THEN r END) AS day20170109,
ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170110' THEN r END) AS day20170110
FROM (
SELECT d, r, ROW_NUMBER() OVER(PARTITION BY d) AS line
FROM (
SELECT
stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r
FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t
GROUP BY stn, d
)
)
GROUP BY line
Запустить выше запрос в веб-интерфейсе с помощью pivot_table (или любого другого имени) в качестве адресата
Как мы можем видеть - здесь мы получим таблицу с 10 столбцами - по одному столбцу на один день, а схема каждого столбца - копия схемы исходной таблицы:
Шаг 2 - обработка разделов один за другим ТОЛЬКО сканирование соответствующего столбца (без полного сканирования таблицы) - вставка в соответствующий раздел
#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170101) AS r
Выполнить над запросом из веб-интерфейса пользователя с таблицей назначения с именем mytable $20160101
Вы можете запускать его на следующий день
#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170102) AS r
Теперь у вас должна быть таблица назначения как mytable $20160102 и т.д.
Вы должны иметь возможность автоматизировать / script этот шаг с любым клиентом по вашему выбору
Существует много вариантов того, как вы можете использовать вышеприведенный подход - это зависит от вашего творчества
Примечание. BigQuery разрешает до 10000 столбцов в таблице, поэтому 365 столбцов для соответствующих дней в году, определенно, не проблема: o) Если не существует ограничения на то, как далеко назад вы можете пойти с новыми разделами - я слышал (но у меня еще не было возможности проверить), теперь осталось не более 90 дней назад
Обновить
Обратите внимание: В приведенной выше версии есть небольшая дополнительная логика упаковки всех агрегированных ячеек в максимально возможное количество конечных чисел.
ROW_NUMBER() OVER(PARTITION BY d) AS line
и затем
GROUP BY line
наряду с
ARRAY_CONCAT_AGG(…)
делает это
Это хорошо работает, когда размер строки в исходной таблице не такой большой, поэтому окончательный смешанный размер строки по-прежнему будет находиться в пределах размера строки, который имеет BigQuery (который, по моему мнению, составляет 10 МБ на данный момент)
Если ваша исходная таблица уже имеет размер строки, близкий к этому пределу, используйте ниже скорректированную версию
В этой версии - группировка удаляется так, что каждая строка имеет только значение для одного столбца
#standardSQL
SELECT
CASE WHEN d = 'day20170101' THEN r END AS day20170101,
CASE WHEN d = 'day20170102' THEN r END AS day20170102,
CASE WHEN d = 'day20170103' THEN r END AS day20170103,
CASE WHEN d = 'day20170104' THEN r END AS day20170104,
CASE WHEN d = 'day20170105' THEN r END AS day20170105,
CASE WHEN d = 'day20170106' THEN r END AS day20170106,
CASE WHEN d = 'day20170107' THEN r END AS day20170107,
CASE WHEN d = 'day20170108' THEN r END AS day20170108,
CASE WHEN d = 'day20170109' THEN r END AS day20170109,
CASE WHEN d = 'day20170110' THEN r END AS day20170110
FROM (
SELECT
stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r
FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t
GROUP BY stn, d
)
WHERE d BETWEEN 'day20170101' AND 'day20170110'
Как вы теперь видите - сводная таблица (sparce_pivot_table) достаточно разрежена (то же самое 21,5 МБ, но теперь 114 089 строк против 11 584 строки в pivot_table), поэтому она имеет средний размер строки 190В против 1,9 КБ в начальной версии. Это, очевидно, примерно в 10 раз меньше числа столбцов в примере.
Поэтому, прежде чем использовать этот подход, необходимо выполнить некоторую математику для оценки/оценки того, что и как можно сделать!
Тем не менее: каждая ячейка в сводной таблице представляет собой представление JSON всей строки в исходной таблице. Он таков, что он содержит не только значения, как и для строк в исходной таблице, но также имеет в себе схему
Как таковой он довольно многословный - таким образом, размер ячейки может быть в несколько раз больше, чем оригинальный размер [который ограничивает использование этого подхода... если вы не получите еще более творческий: o)... который еще много областей здесь, чтобы применить: o)]