Современное построение конвейера данных с помощью BigQuery Dataform

Поскольку ELT становится все более популярным, BigQuery Dataform позволяет выполнять современные преобразования на основе SQL в среде BigQuery.

Отказ от ответственности. Я инженер по работе с клиентами Google Cloud. Мнения являются моими собственными и не обязательно мнением Google.

Это первая часть серии статей о Google Cloud Dataform.

Недавно я начал использовать BigQuery Dataform. Dataform — это современный инструмент конвейера данных и приобретение Google Cloud, которое теперь интегрировано в среду BigQuery; как с точки зрения пользовательского интерфейса, так и с точки зрения исполнения. И теперь это общедоступно (GA).

В этой статье мы рассмотрим некоторые основные возможности Dataform и то, как вы можете их использовать. Мы делаем это, реализуя очень простое преобразование, которое мы изменяем шаг за шагом, чтобы повысить гибкость и возможность повторного использования преобразования. В этих изменениях используется определенная механика Dataform, которая поможет вам понять, как все работает.

Dataform как продукт очень хорошо подходит для подхода извлечения-загрузки-преобразования (ELT) для перемещения данных в BigQuery. Как только данные находятся в BigQuery (или доступны через внешние или BigLake таблицы), Dataform берет на себя часть преобразования (T) в этом подходе. Одним из основных преимуществ ELT (и использования Dataform) является тот факт, что вы можете писать свои конвейеры данных на основе SQL. Это означает, что вы можете получить информацию от большого числа аналитиков данных и инженеров данных, уже знакомых с SQL. Зависимость Dataform от SQL также упрощает быстрый запуск и упрощает обслуживание в целом.

Есть немало других интересных аспектов Dataform, таких как поддержка Git для CI/CD, возможности тестирования, поддержка планирования, бессерверный механизм выполнения и внешний запуск, которые я не буду здесь рассматривать.

Базовое преобразование

Если вы хотите следовать приведенному ниже примеру, обязательно следуйте инструкциям, изложенным здесь, чтобы создать репозиторий и рабочую область, а также назначить соответствующие роли для использования наборов данных Dataform и BigQuery. Образцы данных для загрузки в таблицу customer_data можно найти здесь.

Начнем с базового преобразования, которое создает новую таблицу на основе существующей таблицы клиентов. Таблица клиентов содержит столбец с именем value. В новой таблице должен быть дополнительный столбец с именем segment со значениями «Platinum», «Gold», «Silver» или «Bronze», которые основаны на столбце value. стола клиентов.

Dataform использует файловую структуру SQLX для хранения кода своих конвейеров, а также метаданных об источниках данных и целях, с которыми взаимодействуют конвейеры. Содержимое этих файлов SQLX обычно сосредоточено вокруг оператора SQL, сопровождаемого некоторыми метаданными.

Ниже приведено определение нашего простого преобразования для создания новой таблицы с содержимым таблицы customer_data, расширенной столбцом segment:

config {
    type: "table"
}

SELECT *,
    CASE
        WHEN value > 800000 THEN "platinum"
        WHEN value > 600000 THEN "gold"
        WHEN value > 300000 THEN "silver"
    ELSE
        "bronze"
    END AS segment
FROM `dataform.customer_data`

/definitions/01_start.sqlx

Вы заметили, что контент состоит из двух частей:

Блок конфигурации, который сообщает Dataform, что делать, а именно создавать в этом случае новую таблицу.
Н.Б. Имя таблицы будет именем файла SQLX, но его также можно указать вручную с помощью параметра name.
Стандартный оператор SQL SELECT для определения содержимого новой таблицы.

Выполнение этого «конвейера» создаст таблицу 01_start в наборе данных, настроенном как схема по умолчанию в файле /dataform.json.

Ссылки на источники данных

Преобразования редко находятся в конвейере сами по себе. Обычно они берут данные с предыдущего шага и материализуют их в другом месте. Dataform понимает зависимости между этими преобразованиями, кроме исходных данных. Dataform также должен знать данные уже существующего источника данных, на который затем можно ссылаться при первом преобразовании. Это позволяет Dataform строить и визуализировать полный граф выполнения для управления порядком выполнения всех преобразований.

Поэтому в нашем примере мы сообщим Dataform о таблице customer_data и будем использовать ссылку на эту таблицу в нашем обновленном операторе SQL. Во-первых, давайте определим таблицу customer_data.

config {
  type:   "declaration",
  schema: "dataform",               // BigQuery dataset
  name:   "customer_data"           // BigQuery table
}

/definitions/00_customer_data.sqlx

В этом определении мы видим наш второй тип конфигурации, declaration. Как только это определение существует, мы можем просто сослаться на таблицу customer_data, используя функцию Dataform ref("table_name").

Использование функции во второй версии нашего преобразования выглядит так:

config { type: "table" }

SELECT *,
    CASE
        WHEN value > 800000 THEN "platinum"
        WHEN value > 600000 THEN "gold"
        WHEN value > 300000 THEN "silver"
    ELSE
        "bronze"
    END AS segment
FROM ${ref("customer_data")}

/definitions/02a_table_ref.sqlx

Обратите внимание, что функция ref(…) заключена в блок ${…}. ref(…) function — это пример возможности Dataform использовать встроенный JavaScript в любом месте оператора SQL для динамического изменения запроса. Встроенный JavaScript внедряется с помощью этой оболочки ${…}.

Вот еще один пример. Мы создаем представление на основе только что созданной таблицы, в которой отсутствует столбец value, но есть столбец сегмент клиента.

config { type: "view" }

SELECT * EXCEPT(value)
FROM ${ref("02a_table_ref")}

/definitions/02b_view.sqlx

Если мы посмотрим на график исполнения/зависимости, то увидим:

В 01_start жестко запрограммировано имя таблицы customer_data. Dataform не знает о зависимости этой таблицы.
customer_data определяется и известна Dataform и включена в граф зависимостей.
02a_table_ref зависит от таблицы customer_data.
02b_view зависит от таблицы 02a_table_ref

Использование JavaScript

Мы также можем использовать наш собственный код JavaScript и внедрить его в оператор SQL. Это позволяет нам делать некоторые действительно интересные вещи, такие как создание генератора параметризованных запросов, массовое создание таблиц или разработка повторно используемых фрагментов SQL. Вы даже можете группировать наборы кода JavaScript в пакеты и использовать их в нескольких репозиториях Dataform, чтобы упростить разработку рабочих процессов SQL.

Константы

Начнем с простого. Мы должны следовать рекомендациям по написанию кода и перенести жестко закодированные значения оператора CASE в более централизованное и лучше управляемое место. Почему? Эти значения можно использовать в нескольких местах, и когда их нужно изменить, мы не хотим выполнять все преобразования и обновлять эти значения. Вместо этого мы хотим изменить значения один раз в нашем центральном расположении, и все преобразования будут автоматически использовать обновленные значения.

В Dataform этим центральным расположением является папка includes/, где мы можем создавать несколько файлов (модулей) JavaScript, которые экспортируют элементы, такие как константы и функции, для использования в других местах наших файлов SQLX.

const platinum_threshold = 800000;
const gold_threshold = 600000;
const silver_threshold = 300000;

module.exports = {platinum_threshold, gold_threshold, silver_threshold};

/includes/constants.js

Этот файл требует очень небольшого пояснения. Мы определяем значения как константы и экспортируем их, чтобы мы могли использовать их в наших файлах SQLX, как показано ниже:

config { type: "table" }

SELECT *,
    CASE
        WHEN value > ${constants.platinum_threshold} THEN "platinum"
        WHEN value > ${constants.gold_threshold} THEN "gold"
        WHEN value > ${constants.silver_threshold} THEN "silver"
    ELSE
        "bronze"
    END AS segment
FROM ${ref("customer_data")}

/definitions/03_constants.sqlx

Обратите внимание, что часть WHEN оператора CASE теперь ссылается на константы, которые мы только что определили. Синтаксис для ссылки на константу (или что-то еще экспортируемое) использует ${module_name.exported_object}.

Функции

Теперь представьте, что оператор CASE используется во многих запросах/преобразованиях SQL. Когда-нибудь бизнес решит, что мы переходим от 4 сегментов только к 3. Опять же, мы хотим применить такое изменение только один раз. Как и с константами, мы делаем то же самое с функциями фрагмента SQL; определите их централизованно в папке includes/. Вот пример повторно используемого фрагмента кода для оператора CASE:

// Using values provided through constants (includes/constants.js)
function customerSegment(column) {
    return `
        CASE
            WHEN ${column} > ${constants.platinum_threshold} THEN "platinum"
            WHEN ${column} > ${constants.gold_threshold} THEN "gold"
            WHEN ${column} > ${constants.silver_threshold} THEN "silver"
        ELSE
            "bronze"
        END`;
}

module.exports = { customerSegment };

/includes/functions.js

Эта функция возвращает текстовую строку с оператором CASE и может использоваться во многих операторах SQL с возможностью передачи имени столбца в качестве параметра. Вы можете видеть, что мы также можем использовать ссылки на наши константы в нашей возвращаемой строке.

Чтобы использовать этот фрагмент SQL, мы пишем это:

config { type: "table" }

SELECT *,
    ${functions.customerSegment("value")} AS segment
FROM ${ref("customer_data")}

/definitions/04_function.sqlx

Возможно, вы видите, насколько мощным может быть использование JavaScript в качестве механизма шаблонов, и какие возможности это может принести.

Документация: