Мощные основополагающие методы, помогающие раскрыть возможности сценариев и автоматизации BigQuery
Работа со строками в BigQuery стала намного мощнее с появлением сценариев и, в частности, оператора EXECUTE IMMEDIATE. Это позволяет создавать SQL-запросы в виде строк, а затем выполнять созданные SQL-запросы в специальных или запланированных сценариях или внутри вызываемой ПРОЦЕДУРЫ или ФУНКЦИИ.
Это означает, что с помощью небольшой хитрости на основе строк вы можете внезапно автоматизировать действия, которые ранее требовали доступа к GCloud или API, оптимизировав свой рабочий процесс так, чтобы вам никогда не приходилось покидать консоль.
Однако, как и в большинстве других вещей в жизни и разработке программного обеспечения, существует множество способов достижения одного и того же результата, каждый со своими плюсами и минусами.
1. CONCAT(ENATION)
Первый способ будет знаком любому, кто работает с электронными таблицами (т. е. Практически всем в мире, кто когда-либо работал с данными): CONCAT (сокращение от Concatenate). В простейшей форме это принимает несколько строк, разделенных запятыми, в качестве аргументов, объединяет их и возвращает в виде одной строки:
SELECT
CONCAT("This ", "is ", "a ", "useless ", "example.") AS example
Это приводит к выходу:
This is a useless example.
И это правильно.
Давайте рассмотрим более полезный пример, в котором синтаксис функции CONCAT выделен жирным шрифтом:
WITH
example_project_metadata AS (
SELECT
"flowfunctions" AS project_id,
"examples" AS dataset_name,
"a_more_useful_example" AS table_name
)
SELECT
CONCAT("`", project_id, ".", dataset_name, ".", table_name, "`")
AS table_ref
FROM example_project_metadata
Если вы не распознаете структуру, начинающуюся с “WITH“, тогда это очень важный день для вас, возможно, даже более важный, чем тот день, когда вы изучили VLOOKUP в Excel. Что, надеемся, вам сейчас никогда не понадобится, так как вы поняли, что электронные таблицы-это подверженные ошибкам инструменты злоупотребления человеческими данными.
Это общее табличное выражение (CTE), простая, но замечательная конструкция, которая позволяет выполнять последовательные атомарные операции с данными, писать код, который на самом деле может быть понят при чтении сверху вниз (в отличие от запутанных вложенных запросов) и который заставляет вас выдавать псевдонимы каждой операции. Это означает, что вы можете объяснить, что вы делаете на каждом шаге — без комментариев — с помощью имени CTE, а также повторно ссылаться на результаты этого конкретного CTE в любой последующей точке запроса.
На самом деле, надо немного переписать этот запрос, поскольку это помогает с точки зрения рабочего процесса, позвольте объяснить, почему ниже:
WITH
example_project_metadata AS (
SELECT
"flowfunctions" AS project_id,
"examples" AS dataset_name,
"a_more_useful_example" AS table_name
),
build_table_ref AS (
SELECT
CONCAT("`", project_id, ".", dataset_name, ".", table_name, "`")
AS table_ref
FROM example_project_metadata
)
SELECT *
FROM build_table_ref
Это означает, что при разработке в редакторе запросов SQL вы можете визуально проверять данные на любом этапе, изменив ссылку final SELECT на другое имя CTE (i.e. SELECT * FROM example_project_metadata) и выполнив запрос. Не то чтобы визуальный контроль данных был особенно полезен, как только набор данных выходит за рамки определенного (удивительно малого) размера, но все мы люди и любим смотреть на вещи. И иногда это бывает полезно.
Приведенный выше код вернет следующее значение в столбце table_ref:
`flowfunctions.examples.a_more_useful_example`
Что само по себе не особенно полезно, но, надеемся, вы сможете увидеть, как это начинает иметь немного больший потенциал для целей автоматизации.
2. Pipe Concatenation
Это почти то же самое, но с немного другим синтаксисом (выделено жирным шрифтом), использующим concatenation operator вместо функции CONCAT для достижения точно такого же результата:
WITH example_project_metadata AS ( SELECT "flowfunctions" AS project_id, "examples" AS dataset_name, "a_more_useful_example" AS table_name ), build_table_ref AS ( SELECT "`"||project_id||"."||dataset_name||"."||table_name||"`" AS table_ref FROM example_project_metadata ) SELECT * FROM build_table_ref
На наш взгляд, это очень много, но в зависимости от вашего варианта использования, личных предпочтений или зрения это может быть немного более читабельным.
3. FORMAT()
Функция FORMAT-это чрезвычайно мощный и универсальный механизм для построения строк с жестким контролем над вводимыми элементами. В этом случае мы просто вводим строку в другую строку (используя спецификатор формата “%s”), но у вас есть точный контроль над форматом различных типов данных, которые вы можете вводить — ознакомьтесь с документацией здесь. В любом случае, в этом случае SQL выглядит так:
WITH
example_project_metadata AS (
SELECT
"flowfunctions" AS project_id,
"examples" AS dataset_name,
"a_more_useful_example" AS table_name
),
build_table_ref AS (
SELECT
FORMAT("`%s.%s.%s`",
project_id, dataset_name, table_name)
AS table_ref
FROM example_project_metadata
)
SELECT *
FROM build_table_ref
Каждый идентификатор “%s ” последовательно заменяется каждым значением строковой переменной, и запрос приведет к ошибке, если количество переменных или любой тип данных переменной не совпадут. Для более сложных конструкций можно использовать многострочную строку с тройными кавычками (см. Следующий пример аналогичной реализации), а в более длинных случаях может быть полезно разделить введенные переменные на разные строки для удобства чтения и отслеживания.
Здесь одно предупреждение: Для длинных операторов может быть трудно увидеть, какая переменная сопоставлена с каким идентификатором, поэтому альтернативный подход может быть проще для записи и чтения.
4. Pipe Injection
Окончательный вариант очень похож на объединение каналов и очень полезен при введении переменных в более длинную многострочную инструкцию SQL. Обратите внимание, что тройные кавычки используются для многострочных строк (точно так же, как в Python), однако они также полезны в однострочных строках, где они могут содержать дополнительные символы кавычек. Примером кода, использующего этот метод, является:
WITH example_project_metadata AS ( SELECT "flowfunctions" AS project_id, "examples" AS dataset_name, "a_more_useful_example" AS table_name, "name" AS username_field, "Jim" AS my_name ), build_sql_query AS ( SELECT """SELECT * FROM `"""||project_id||"""."""||dataset_name||"""."""||table_name||"""` WHERE """||username_field||""" = '"""||my_name||"""' """ AS sql_query FROM example_project_metadata ) SELECT * FROM build_sql_query
Не самый синтаксически красивый код, но когда он упакован, например, в пользовательскую функцию (UDF), его можно написать, протестировать и никогда больше не просматривать. Здесь мы фактически создаем SQL-запрос для выполнения, который гораздо лучше использует шаблоны строк, чем создание случайных бесполезных предложений или ссылок на таблицы. На самом деле результатом этого запроса будет другой запрос:
SELECT * FROM `flowfunctions.examples.a_more_useful_example` WHERE name = 'Jim'
Хотя существуют гораздо более элегантные способы структурирования вашего кода (многие предпочитают использовать UDFs для построения SQL и возвращать его в виде строки, а затем использовать PROCEDURE для выполнения SQL), однако для выполнения этого запроса самым простым способом вам просто нужно завернуть его в инструкцию EXECUTE IMMEDIATE:
EXECUTE IMMEDIATE ( WITH example_project_metadata AS ( SELECT "flowfunctions" AS project_id, "examples" AS dataset_name, "a_more_useful_example" AS table_name, "name" AS username_field, "Jim" AS my_name ), build_sql_query AS ( SELECT """SELECT * FROM `"""||project_id||"""."""||dataset_name||"""."""||table_name||"""` WHERE """||username_field||""" = '"""||my_name||"""' """ AS sql_query FROM example_project_metadata ) SELECT * FROM build_sql_query );
И как по волшебству, вы написали какой-то SQL, который пишет и выполняет SQL!
