Перейти к основному содержимому Перейти к дополнительному содержимому
Ресурсы Qlik

Добавление преобразований SQL

В задачи преобразования можно включать таблицы на основе SQL. Преобразование SQL позволяет ввести запрос SQL SELECT (ВЫБРАТЬ) в конвейер для определения сложных или простых преобразований. Также можно использовать макросы для адаптации запроса, чтобы он запускался в случае выполнения начальной загрузки или инкрементной загрузки.

Для получения дополнительной информации о создании задачи преобразования см. раздел Преобразование данных.

Добавление преобразования SQL

Чтобы добавить преобразование SQL в задачу данных Преобразование, выполните следующие действия.

  1. В окне Преобразование выберите наборы данных, которые нужно включить в запрос, затем нажмите Добавить преобразование SQL.

    Параметр Добавить преобразование SQL отображается в окне настройки преобразования.

  2. В поле Имя введите соответствующее имя целевого набора данных.

    Также можно добавить более длинное описание в поле Описание.

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

    • Значение Вкл будет создавать таблицы и осуществлять связанную обработку ELT.

    • Значение Выкл будет создавать виды, которые выполняют преобразования на ходу.

  4. Параметр Инкрементная загрузка позволяет корректировать запрос для инкрементной загрузки данных путем применения фильтров или других условий, чтобы уменьшить набор данных, обрабатываемый с помощью макросов. Параметр Инкрементная загрузка доступен, только если данные материализованы как таблицы.

    • Когда параметр Инкрементная загрузка имеет значение Вкл

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

    • Когда параметр Инкрементная загрузка имеет значение Выкл

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

    Примечание к информацииЕсли запрос выбирает все записи, которые должны существовать в цели, установите для параметра Инкрементная загрузка значение «Выкл». Записи, которые не выбраны, будут удалены из цели.

  5. Щелкните Добавить, когда будете готовы к созданию преобразования SQL.

    Откроется окно Изменение преобразования SQL.

  6. В SELECT query введите запрос, чтобы вернуть таблицу на основе SQL.

    Запрос может считывать данные только из выбранных исходных наборов данных, но можно добавить другие исходные наборы данных с помощью кнопки Добавить.

    Можно выбрать из полей Наборы данных и Столбцы.

  7. Когда применяется инкрементная загрузка, можно использовать макросы для адаптации запроса, чтобы он запускался в случае выполнения начальной загрузки или инкрементной загрузки.

    Для получения дополнительной информации см. раздел Использование макросов для адаптации запроса в соответствии с типом загрузки.

  8. Нажмите Извлечь параметры, чтобы выполнить синтаксический анализ параметров в запросе SELECT.

    Параметры необходимо сопоставить с исходными или целевыми объектами. Можно выполнить сопоставление со следующими объектами:

    • Исторические структуры типа 2 (_history)

    • Текущие структуры

    • Целевые объекты

      Можно сопоставлять целевые объекты, только когда параметр определен внутри инкрементного макроса.

    Примечание к подсказкеМожно заменить параметр ссылкой, чтобы сделать запрос более удобным для чтения. При изменении значения в поле Параметр ссылка в SELECT query тоже изменится.

  9. Нажмите Описать таблицу, чтобы проверить выходные метаданные запроса.

    Также можно предварительно просмотреть результаты, используя образец данных.

    Предварительный просмотр данных

  10. Убедитесь, что таблица содержит первичный ключ.

    Также можно изменить настройки метаданных.

    • Задайте первичные ключи в столбце Ключ.

    • Задайте, допускает ли столбец нулевое значение, в столбце Допускает значение NULL.

    • Выберите столбец и щелкните Изменить, чтобы задать имя столбца, ключ, допустимость значения NULL и тип данных.

  11. Если запрос возвращает удовлетворительные результаты, нажмите OK. Набор данных добавлен в список целей и доступен для предварительного просмотра.

Предварительный просмотр данных

Можно предварительно просмотреть результаты, используя образец данных.

Примечание к информацииПредварительный просмотр должен быть включен на уровне клиента в Консоль управления Qlik. Параметр Просмотр данных в Интеграция данных находится на экране Параметры в разделе Контроль функции.

Для предварительного просмотра данных требуются следующие роли:

  • Может просматривать данные в пространстве, где находится подключение к данным.

  • Может просматривать в пространстве, где находится проект данных.

Для предварительного просмотра результатов выполните следующие действия.

  • Щелкните Просмотр данных.

Отображаются результаты с использованием образца данных. Параметр Количество строк позволяет настроить, сколько строк требуется включить в образец.

Можно фильтровать данные образца двумя способами:

  • Используйте фильтр для ограничения объема извлекаемых данных образца.

    Например, если используется фильтр ${OrderYear}>2023 и параметру Количество строк задано значение 10, будет отображен образец, содержащий 10 заказов за 2024 год.

  • Фильтруйте данные образца по конкретному столбцу.

    Это затронет только существующие данные образца. Если использовать фильтр, чтобы включить только заказы за 2024 год, а фильтр столбцов настроен для отображения заказов за 2022 год, будет возвращен пустой образец.

Можно сортировать образец данных по конкретному столбцу. Сортировка затронет только существующие данные образца. Если использовать фильтр, чтобы включить только заказы за 2024 год, и инвертировать порядок сортировки, данные образца будут содержать только заказы за 2024 год.

Использование макросов для адаптации запроса в соответствии с типом загрузки

Когда применяется инкрементная загрузка, можно использовать макросы для адаптации запроса, чтобы он запускался в случае выполнения начальной загрузки или инкрементной загрузки.

Выберите макрос и добавьте запрос внутри соответствующего предложения.

  • Q_RUN_INCREMENTAL добавляет приложение для размещения запроса, который выполняется только в случае инкрементной загрузки.

  • Q_RUN_INITIAL_LOAD добавляет приложение для размещения запроса, который выполняется только в случае начальной загрузки.

Не требуется указывать запрос полностью в каждом макросе. Можно корректировать или добавлять код с учетом конкретного сценария применения.

Пример использования: инкрементный фильтр с использованием диапазона дат.

В этом случае набор данных о заказах необходимо преобразовать с использованием пользовательского кода SQL. Так как это большой набор данных, необходимо включить инкрементную фильтрацию, чтобы при инкрементной загрузке обрабатывались только заказы за последние 12 часов.

Это базовый запрос:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM “mydb”.”myStorageTask”.”ORDER” O JOIN “mydb”.”myStorageTask”.”ORDER_DETAIL” OD ON O.ORDER_ID = OD.ORDER_ID

Это фильтр, который будет применяться во время инкрементной загрузки, чтобы учитывались только заказы, добавленные за последние 12 часов:

WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP())
Примечание к подсказкеПри внедрении таблицы на основе SQL рекомендуется заменять имена таблиц на нотацию ${TABLE}. Это предотвращает использование жестко закодированных имен баз данных и схем, которые могут меняться в зависимости от среды, например, при переносе из среды разработки в производственную среду.

Это полный запрос, в котором используется макрос фильтра и имена таблиц заменяются с использованием нотации ${TABLE}:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP() ) {{/QLIK_FUNC_IF}}

Таким образом создается запрос на начальную загрузку:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID

И запрос на инкрементную загрузку:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP() )

Пример использования: инкрементный фильтр с использованием данных в цели.

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

Примечание к информацииКак видно на этом примере, с помощью макросов можно корректировать любую часть запроса для начальной или инкрементной загрузки.

Это базовый запрос:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM “mydb”.”myStorageTask”.”ORDER” O JOIN “mydb”.”myStorageTask”.”ORDER_DETAIL” OD ON O.ORDER_ID = OD.ORDER_ID

Это фильтр, применяемый во время инкрементной загрузки. "ORDERS_DERIVED" — это имя целевой базы данных.

WHERE OD.UPDATE_DTM > = (SELECT MAX(LAST_UPDATE_DTM) FROM “mydb”,”myTrasformTask”.”ORDERS_DERIVED” )
Примечание к подсказкеИнкрементная загрузка также может сопоставлять нотацию ${TABLE} с целевыми объектами, созданными при выполнении задачи преобразования.

Это полный запрос, в котором используется макрос фильтра и имена таблиц заменяются с использованием нотации ${TABLE}. LOADED_BY имеет значение INIT, если загрузка начальная, и значение INCR, если загрузка инкрементная:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, {{#QLIK_FUNC_IF Q_RUN_INITIAL_LOAD}} ‘INIT’ as LOADED_BY {{/QLIK_FUNC_IF}} {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} ‘INCR’ as LOADED_BY {{/QLIK_FUNC_IF}} FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} WHERE OD.UPDATE_DTM >= (SELECT MAX(LAST_UPDATE_DTM) FROM ${TARGET_TABLE} ) {{/QLIK_FUNC_IF}}

Таким образом создается запрос на начальную загрузку:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, ‘INIT’ as LOADED_BY FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID

И запрос на инкрементную загрузку:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, ‘INCR’ as LOADED_BY FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID WHERE OD.UPDATE_DTM >= (SELECT MAX(LAST_UPDATE_DTM) FROM ${TARGET_TABLE} )

Рекомендации

  • При добавлении преобразования SQL первичный ключ не определяется автоматически. Ключ следует добавить к набору данных во вкладке Набор данных.

  • Не создавайте вручную заголовки столбцов в преобразованных выходных данных.

  • Не рекомендуется использовать SELECT вместе с *, так как в результате этого при каждом запросе могут возвращаться разные столбцы. Если схема изменена, внесите соответствующие изменения в запрос и выполните команду Описать таблицу еще раз. После этого можно изменять столбцы и настраивать таблицы.

Помогла ли вам эта страница?

Если вы обнаружили какую-либо проблему на этой странице и с ее содержанием — будь то опечатка, пропущенный шаг или техническая ошибка, сообщите нам об этом, чтобы мы смогли ее исправить!

Оставьте свой отзыв здесь