Работа с параметрами запроса
В этой статье объясняется, как работать с параметрами запроса в редакторе SQL Azure Databricks.
Параметры запроса позволяют сделать запросы более динамическими и гибкими, вставляя значения переменных во время выполнения. Вместо жесткого написания определенных значений в запросах можно определить параметры для фильтрации данных или изменения выходных данных на основе ввода пользователем. Этот подход улучшает повторное использование запросов, повышает безопасность путем предотвращения внедрения SQL и обеспечивает более эффективную обработку различных сценариев данных.
Синтаксис маркера именованных параметров
Именованные маркеры параметров являются типизированными переменными заполнителя. Используйте этот синтаксис для записи запросов в следующих частях пользовательского интерфейса Azure Databricks:
Редактор SQL
Записные книжки
Редактор набора данных панели мониторинга ИИ/BI
Пространства ИИ/BI Genie (общедоступная предварительная версия)
Вставьте параметры в запросы SQL, введя двоеточие, за которым следует имя параметра, например :parameter_name. При включении именованного маркера параметров в запрос мини-приложение отображается в пользовательском интерфейсе. Мини-приложение можно использовать для изменения типа и имени параметра.
Добавление именованного маркера параметра в запрос
В этом примере маркер параметра добавляется в следующий запрос:
SELECT
trip_distance,
fare_amount
FROM
samples.nyctaxi.trips
WHERE
fare_amount < 5
Этот запрос возвращает набор данных, включающий только суммы тарифа, которые находятся под пятью долларами. Выполните следующие действия, чтобы изменить запрос, чтобы использовать параметр вместо жестко закодированного значения (5).
- Удалите номер 5 из запроса.
- Введите двоеточие (:) за строкой
fare_parameter. Последняя строка обновленного запроса должна сказатьfare_amount < :fare_parameter. - Щелкните значок шестеренки рядом
с мини-приложением параметра. В диалоговом окне показаны следующие поля: - Ключевое слово: ключевое слово, представляющее параметр в запросе. Значение в этом поле изменить нельзя. Чтобы изменить ключевое слово, измените маркер в SQL-запросе.
- Заголовок: заголовок, отображаемый над мини-приложением. По умолчанию заголовок совпадает с ключевым словом.
- Тип: поддерживаемые типы: Text, Number, Dropdown List, Date, Date и Time, and Date and Time (с секундами). Значение по умолчанию - Text.
- В диалоговом окне измените тип на число.
- Введите число в мини-приложение параметра и нажмите кнопку "Применить изменения".
- Щелкните Сохранить, чтобы сохранить запрос.
Примеры синтаксиса именованных параметров
В следующих примерах показаны некоторые распространенные варианты использования параметров.
Вставка даты
В следующем примере содержится параметр Date, ограничивающий результаты запроса записями после определенной даты.
SELECT
o_orderdate AS Date,
o_orderpriority AS Priority,
sum(o_totalprice) AS `Total Price`
FROM
samples.tpch.orders
WHERE
o_orderdate > :date_param
GROUP BY
1,
2
Вставка числа
В следующем примере содержится параметр Number, ограничивающий результаты записей, в которых o_total_price поле больше указанного значения параметра.
SELECT
o_orderdate AS Date,
o_orderpriority AS Priority,
o_totalprice AS Price
FROM
samples.tpch.orders
WHERE
o_totalprice > :num_param
Вставка имени поля
В следующем примере field_param функция используется для IDENTIFIER предоставления порогового значения для запроса во время выполнения. Значение параметра должно быть именем столбца из таблицы, используемой в запросе.
SELECT
*
FROM
samples.tpch.orders
WHERE
IDENTIFIER(:field_param) < 10000
Вставка объектов базы данных
В следующем примере создаются три параметра: catalog, schemaи table.
SELECT
*
FROM
IDENTIFIER(:catalog || '.' || :schema || '.' || :table)
См . предложение IDENTIFIER.
Объединение нескольких параметров
Параметры можно включить в другие функции SQL. В этом примере средство просмотра позволяет выбрать название сотрудника и идентификатор номера. Запрос использует format_string функцию для объединения двух строк и фильтрации по строкам, которые соответствуют. См . format_string функцию.
SELECT
o_orderkey,
o_clerk
FROM
samples.tpch.orders
WHERE
o_clerk LIKE format_string('%s%s', :title, :emp_number)
Работа со строками JSON
Параметры можно использовать для извлечения атрибута из строки JSON. В следующем примере функция используется from_json для преобразования строки JSON в значение структуры. Подстановка строки a в качестве значения параметра (param) возвращает атрибут 1.
SELECT
from_json('{"a": 1}', 'map<string, int>') [:param]
Изменения синтаксиса
В следующей таблице показаны распространенные варианты использования параметров, исходный синтаксис усы SQL Databricks и эквивалентный синтаксис с использованием синтаксиса маркера именованных параметров.
| Вариант использования параметра | Синтаксис параметра усы | Синтаксис маркера именованных параметров |
|---|---|---|
| Загрузка только данных до указанной даты | WHERE date_field < '{{date_param}}'Необходимо включить кавычки вокруг параметра даты и фигурные скобки. |
WHERE date_field < :date_param |
| Загрузка только данных меньше указанного числового значения | WHERE price < {{max_price}} |
WHERE price < :max_price |
| Сравнивают две строки | WHERE region = {{region_param}} |
WHERE region = :region_param |
| Указание таблицы, используемой в запросе | SELECT * FROM {{table_name}} |
SELECT * FROM IDENTIFIER(:table)Когда пользователь вводит этот параметр, он должен использовать полное трехуровневое пространство имен для идентификации таблицы. |
| Независимо укажите каталог, схему и таблицу, используемую в запросе | SELECT * FROM {{catalog}}.{{schema}}.{{table}} |
SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table) |
| Использование параметров в качестве шаблона в более длинной отформатируемой строке | "({{area_code}}) {{phone_number}" Значения параметров автоматически объединяются в виде строки. |
format_string("(%d)%d, :area_code, :p hone_number) Полный пример см. в разделе "Объединение нескольких параметров ". |
Синтаксис параметра усы
Внимание
В следующих разделах применяется синтаксис запросов, который можно использовать только в редакторе SQL. Это означает, что при копировании и вставке запроса с помощью этого синтаксиса в любой другой интерфейс Azure Databricks, например в редакторе наборов данных записной книжки или панели мониторинга AI/BI, запрос необходимо вручную настроить, чтобы использовать именованные маркеры параметров, прежде чем он будет выполняться без ошибок.
В редакторе SQL любая строка между двойными фигурными скобками {{ }} рассматривается как параметр запроса. Мини-приложение отображается над панелью результатов, и в нем нужно задать значение параметра. Хотя Azure Databricks обычно рекомендует использовать именованные маркеры параметров, некоторые функции поддерживаются только с помощью синтаксиса параметров усы.
Используйте синтаксис параметра усы для следующих функций:
- Устаревшие фильтры панели мониторинга
- Несколько параметров раскрывающегося списка значений
- Диапазоны дат
- Параметры раскрывающегося списка на основе запросов
Добавление параметра усы
- Введите
Cmd + I. Параметр вставляется в текстовое поле, и открывается диалоговое окно Добавление параметра.- Ключевое слово: ключевое слово, представляющее параметр в запросе.
- Заголовок: заголовок, отображаемый над мини-приложением. По умолчанию заголовок совпадает с ключевым словом.
- Тип. поддерживаются следующие типы: текст, число, дата, дата и время, дата и время (с секундами), раскрывающийся список и раскрывающийся список на основе запроса. Значение по умолчанию - Text.
- Введите ключевое слово, при необходимости переопределите заголовок и выберите тип параметра.
- Нажмите кнопку Добавить параметр.
- В мини-приложении параметра задайте значение параметра.
- Нажмите кнопку Применить изменения.
- Нажмите кнопку Сохранить.
Кроме того, введите двойные фигурные скобки {{ }} и щелкните значок шестеренки рядом с мини-приложением параметра, чтобы изменить параметры.
Чтобы повторно выполнить запрос с другим значением параметра, введите значение в мини-приложение и нажмите кнопку Применить изменения.
Изменение параметра запроса
Чтобы изменить параметр, щелкните значок шестеренки рядом с мини-приложением параметра. Чтобы предотвратить изменение параметра пользователями, не являющимися владельцами запроса, установите флажок Показывать только результаты. Откроется диалоговое <Keyword> окно параметра.
Удаление параметра запроса
Чтобы удалить параметр запроса, удалите параметр из запроса. Мини-приложение параметров исчезает, и вы можете перезаписать запрос с помощью статических значений.
Изменение порядка отображения параметров
Чтобы изменить порядок отображения параметров, можно щелкнуть и перетащить каждый параметр в нужное место.
Типы параметров запросов
Текст
Принимает строку в качестве входных данных. Обратная косая черта, одиночная и двойная кавычки экранируются, а Azure Databricks добавляет кавычки в этот параметр. Например, строка mr's Li"s, например, принимает вид 'mr\'s Li\"s'; пример использования этого параметра:
SELECT * FROM users WHERE name={{ text_param }}
Число
Принимает число в качестве входных данных. Пример использования этого параметра:
SELECT * FROM users WHERE age={{ number_param }}
Раскрывающийся список
Чтобы ограничить область возможных значений параметров при выполнении запроса, используйте тип параметра раскрывающегося списка . Например, так: SELECT * FROM users WHERE name='{{ dropdown_param }}'. При выборе на панели параметров появится текстовое поле, в котором введите допустимые значения, каждое значение, разделенное новой строкой. Раскрывающийся список — это текстовые параметры. Чтобы использовать даты или даты и время в раскрывающемся списке, введите их в формате источника данных. Строки не пропускаются. Вы можете выбрать однозначный или многозначный раскрывающийся список.
- Одно значение: требуются одиночные кавычки вокруг параметра.
- Несколько значений: активируйте параметр Разрешить несколько значений. В раскрывающемся списке "Кавычки " выберите, следует ли оставить параметры как введенные (без кавычки) или упаковать параметры с одними или двойными кавычками. При выборе кавычки не нужно добавлять кавычки вокруг параметра.
WHERE Измените предложение, чтобы использовать ключевое IN слово в запросе.
SELECT ...
FROM ...
WHERE field IN ( {{ Multi Select Parameter }} )
Мини-приложение множественного выбора параметров позволяет передавать в базу данных несколько значений. Если выбрать параметр Double Кавычки для параметра Кавычки, запрос отражает следующий формат:WHERE IN ("value1", "value2", "value3")
Раскрывающийся список на основе запросов
Принимает результат запроса в качестве входных данных. Он имеет то же поведение, что и параметр раскрывающегося списка . Чтобы использовать его в качестве входных данных в другом запросе, необходимо сохранить раскрывающийся список Databricks SQL.
- Щелкните Раскрывающийся список на основе запроса в разделе Тип на панели параметров.
- Щелкните поле Запрос и выберите запрос. Если целевой запрос возвращает большое количество записей, производительность будет ухудшаться.
Если целевой запрос возвращает более одного столбца, то SQL Databricks использует первый из них. Если целевой запрос возвращает name и value столбцы, то SQL Databricks заполняет мини -приложение выбора параметров столбцом name, но выполняет запрос с соответствующим value.
Например, предположим, что следующий запрос возвращает данные в таблице.
SELECT user_uuid AS 'value', username AS 'name'
FROM users
| значение | name |
|---|---|
| 1001 | Dmitry Gornozhenko |
| 1002 | Jane Doe |
| 1003 | Таблицы Бобби |
Когда Azure Databricks запускает запрос, значение, переданное в базу данных, будет равно 1001, 1002 или 1003.
Дата и время
Azure Databricks имеет несколько вариантов для параметризации значений даты и метки времени, включая параметры для упрощения параметризации диапазонов времени. Выберите из трех вариантов различной точности:
| Вариант | Точность | Тип |
|---|---|---|
| Дата | дн. | DATE |
| Дата и время | мин | TIMESTAMP |
| Дата и время (с секундами) | секунда | TIMESTAMP |
При выборе параметра Диапазон создаются два параметра, назначаемых суффиксами .start и .end. Все параметры передают параметры в запрос в виде строковых литерала; Azure Databricks требует, чтобы значения даты и времени были упаковываются в одинарные кавычки ('). Например:
-- Date parameter
SELECT *
FROM usage_logs
WHERE date = '{{ date_param }}'
-- Date and Time Range parameter
SELECT *
FROM usage_logs
WHERE modified_time > '{{ date_range.start }}' and modified_time < '{{ date_range.end }}'
Параметры даты используют интерфейс выбора календаря и по умолчанию для текущей даты и времени.
Примечание.
Параметр "Диапазон дат" возвращает правильные результаты только для столбцов типа DATE. Для столбцов TIMESTAMP используйте один из вариантов диапазона даты и времени.
Динамические значения даты и диапазона дат
При добавлении параметра даты или диапазона дат в запрос в мини-приложении выбора отображается синий значок молнии. Щелкните его, чтобы указать динамические значения, например today, yesterday, this week, last week, last month или last year. Эти значения обновляются динамически.
Внимание
Динамические даты и диапазоны дат несовместимы с запланированными запросами.
Использование параметров запроса на панелях мониторинга
При необходимости запросы могут использовать параметры или статические значения. Если визуализация на основе параметризованного запроса добавляется на панель мониторинга, визуализация может быть настроена для использования любого из следующих элементов:
Параметр мини-приложения
Параметры мини-приложения относятся к одной визуализации на панели мониторинга, отображаются на панели визуализации, а значения параметров, указанные только для запроса, лежащего в основе визуализации.
Параметр панели мониторинга
Параметры панели мониторинга могут применяться к нескольким визуализациям. При добавлении визуализации на основе параметризованного запроса на панель мониторинга параметр будет добавлен в качестве параметра панели мониторинга по умолчанию. Параметры панели мониторинга настраиваются для одной или нескольких визуализаций на панели мониторинга и отображаются в верхней части панели мониторинга. Значения параметров, указанные для параметра панели мониторинга, применяются к визуализациям, повторно используемым этим параметром панели мониторинга. Панель мониторинга может иметь несколько параметров, каждый из которых может применяться к некоторым визуализациям, а не к другим.
Статическое значение
Статические значения используются вместо параметра, реагирующего на изменения. Статические значения позволяют жестко кодировать значение вместо параметра. Они делают параметр "исчезать" с панели мониторинга или мини-приложения, где она появилась ранее.
При добавлении визуализации, содержащей параметризованный запрос, можно выбрать название и источник параметра в запросе визуализации, щелкнув соответствующий значок карандаша. Вы также можете выбрать ключевое слово и значение по умолчанию. См . свойства параметра.
После добавления визуализации на панель мониторинга перейдите к интерфейсу сопоставления параметров, щелкнув меню kebab в правом верхнем углу мини-приложения панели мониторинга и выбрав пункт "Изменить параметры мини-приложения".
Свойства параметра
Заголовок: отображаемое имя, отображаемое рядом с селектором значений на панели мониторинга. По умолчанию используется ключевое слово параметра. Чтобы изменить его, щелкните значок
карандаша. Заголовки не отображаются для статических параметров панели мониторинга, так как селектор значений скрыт. Если в качестве источника значений выбрано статическое значение, поле "Заголовок" неактивно.Ключевое слово: строковый литерал для этого параметра в базовом запросе. Это полезно для отладки, если панель мониторинга не возвращает ожидаемые результаты.
Значение по умолчанию: это значение используется по умолчанию, если иное значение не установлено. Чтобы изменить это с экрана запроса, запустите запрос со значением требуемого параметра и нажмите кнопку "Сохранить ".
Источник значения— источник значения параметра. Щелкните значок
карандаша, чтобы выбрать источник.- Новый параметр панели мониторинга: создайте новый параметр на уровне панели мониторинга. Это позволяет задать значение параметра в одном месте на панели мониторинга и связать его с несколькими визуализациями.
- Существующий параметр панели мониторинга: сопоставьте параметр с существующим параметром панели мониторинга. Необходимо указать необходимый существующий параметр панели мониторинга.
- Параметр мини-приложения: отображает селектор значений в мини-приложении панели мониторинга. Это полезно для одноразовых параметров, которые не являются общими для мини-приложений.
- Статическое значение: выберите статическое значение для мини-приложения, которое будет независимым от значений, используемых в других мини-приложениях. Статически сопоставленные значения параметров не отображают селектор значений где-либо на панели мониторинга, что является более компактным. Это позволяет сделать доступными множество разнообразных параметров запросов и разгрузить пользовательский интерфейс панели мониторинга за счет некоторых параметров, которые необходимы менее часто.
Часто задаваемые вопросы
- Можно ли повторно использовать один и тот же параметр несколько раз в одном запросе?
- Можно ли использовать несколько параметров в одном запросе?
Можно ли повторно использовать один и тот же параметр несколько раз в одном запросе?
Да. Используйте один и тот же идентификатор в фигурных скобках. В этом примере параметр {{org_id}} используется дважды.
SELECT {{org_id}}, count(0)
FROM queries
WHERE org_id = {{org_id}}
Можно ли использовать несколько параметров в одном запросе?
Да. Используйте уникальное имя для каждого параметра. В этом примере используются два параметра: {{org_id}} и {{start_date}}.
SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'