В июне команда BQ объявила о поддержке таблиц с разбиением по дате. Но в руководстве отсутствует способ переноса старых неразделенных таблиц в новый стиль.
Я ищу способ обновить несколько или не все таблицы до нового стиля.
Кроме того, вне DAY-типа разделены какие-то другие варианты? Отображает ли пользовательский интерфейс BQ это, поскольку я не смог создать такую новую секционированную таблицу из веб-интерфейса BQ.
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)]
Пока новая функция не развернута в BigQuery, существует другой (гораздо более дешевый) способ разделения таблиц с помощью облачного потока данных. Мы использовали этот подход вместо сотен SELECT *, которые стоили бы нам тысячи долларов.
partitionBigQuery.IO.Read для чтения таблицы.BigQuery.IO.Write для каждого дня/осколка, который будет записывать в соответствующий раздел с использованием синтаксиса декоратора раздела - "$YYYYMMDD"Вот пример на Github, чтобы вы начали.
Вы все еще должны заплатить за конвейер потока данных, но это часть стоимости использования нескольких SELECT * в BigQuery.
На сегодняшний день вы можете теперь создать многораздельную таблицу из однораздельной таблицы, запросив ее и указав столбец разделения. Вы заплатите за одно полное сканирование таблицы на исходной (не разбитой на части) таблице. Примечание: это в настоящее время в бета-версии.
Чтобы создать секционированную таблицу из результата запроса, запишите результаты в новую таблицу назначения. Вы можете создать многораздельную таблицу, запросив либо многораздельную таблицу, либо однораздельную таблицу. Вы не можете изменить существующую стандартную таблицу на многораздельную таблицу, используя результаты запроса.
Если у вас есть таблицы с закрытыми данными сегодня, вы можете использовать этот подход:
Если у вас есть одна несегментированная таблица, которая должна быть преобразована в секционированную таблицу, вы можете попробовать подход к выполнению запроса SELECT *, чтобы разрешить большие результаты и использовать раздел таблицы в качестве адресата (аналогично тому, как вы повторите данные для раздела):
https://cloud.google.com/bigquery/docs/creating-partitioned-tables#restating_data_in_a_partition
Обратите внимание, что этот подход будет взимать с вас стоимость сканирования исходной таблицы для запроса столько раз, сколько вы запрашиваете.
Мы работаем над тем, чтобы сделать этот сценарий значительно лучше в ближайшие несколько месяцев.
Для меня работает следующий набор запросов, применяемых непосредственно в большом запросе (большой запрос создает новый запрос).
CREATE TABLE (new?)dataset.new_table PARTITION BY DATE(date_column) AS SELECT * FROM dataset.table_to_copy;
Затем в качестве следующего шага я опускаю таблицу:
DROP TABLE dataset.table_to_copy;
Я получил это решение с https://fivetran.com/docs/warehouses/bigquery/partition-table, используя только шаг 2