Работа с параметрами запроса

На этой странице объясняется, как работать с параметрами запроса в редакторе SQL Azure Databricks.

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

Синтаксис маркера именованных параметров

Именованные параметры-маркеры являются типизированными подстановочными переменными. Используйте этот синтаксис для записи запросов в следующих частях пользовательского интерфейса Azure Databricks:

• Редактор SQL (новые и устаревшие версии)
• Записные книжки
• Редактор набора данных панели мониторинга ИИ/BI
• ИИ/BI Genie spaces

Примечание.

Редактор набора данных панели мониторинга ИИ/BI и новый редактор SQL поддерживают следующие типы данных с именованным синтаксисом параметров: числовые, дата и параметры даты и времени. Во всех остальных поверхностях пользовательского интерфейса поддерживаются только параметры строкового типа с помощью именованного синтаксиса параметров. Чтобы использовать другие типы параметров за пределами этих редакторов, используйте параметры усы.

Вставьте параметры в запросы SQL, введя двоеточие, за которым следует имя параметра, например :parameter_name. При включении именованного маркера параметров в запрос мини-приложение отображается в пользовательском интерфейсе. Мини-приложение можно использовать для изменения типа и имени параметра.

Добавление именованного маркера параметра в запрос

В этом примере маркер параметра добавляется в следующий запрос:

SELECT
  trip_distance,
  fare_amount
FROM
  samples.nyctaxi.trips
WHERE
  fare_amount < 5

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

1. Удалите номер 5 из запроса.
2. Введите двоеточие (:) перед строкой fare_parameter. Последняя строка обновленного запроса должна сказать fare_amount < :fare_parameter.
3. Щелкните рядом с мини-приложением параметра. В диалоговом окне показаны следующие поля:
   • Ключевое слово: ключевое слово, представляющее параметр в запросе. Значение в этом поле изменить нельзя. Чтобы изменить ключевое слово, измените маркер в SQL-запросе.
   • Заголовок: заголовок, отображаемый над мини-приложением. По умолчанию заголовок совпадает с ключевым словом.
   • Тип: поддерживаемые типы: Текст, Число, Выпадающий список, Дата, Дата и время, и Дата и время (с секундами). Значение по умолчанию - Text.
4. В диалоговом окне измените тип на число.
5. Введите число в мини-приложение параметра и нажмите кнопку "Применить изменения".
6. Щелкните Сохранить, чтобы сохранить запрос.

Примеры синтаксиса именованных параметров

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

Вставка даты

В следующем примере содержится параметр 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)

См. пункт.

Объединение нескольких параметров

Параметры можно включить в другие функции 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]

Создание интервала

Тип INTERVAL представляет интервал времени и позволяет выполнять арифметические и операции на основе времени. В следующем примере функция CAST используется для приведения параметра в качестве типа интервала. Полученное INTERVAL значение можно использовать для вычислений на основе времени или фильтрации в запросе.

см. раздел INTERVAL тип для получения полной информации и синтаксиса.

SELECT CAST(:param AS INTERVAL MINUTE)

Добавление диапазона дат

В следующих примерах показано, как фильтровать записи в диапазоне дат. Параметры даты могут использовать .min и .max атрибуты для указания диапазона. Кроме того, можно указать диапазон с помощью двух разных параметров даты.

Использование минимальных и максимальных атрибутов

1. Выберите имя параметра. Используйте .min для доступа к минимальному значению диапазона и .max для доступа к максимальному значению диапазона, как показано в следующем запросе:

   SELECT * FROM samples.nyctaxi.trips
   WHERE tpep_pickup_datetime
   BETWEEN :date_range.min AND :date_range.max
   

2. Задайте для параметра тип Date.

3. Установите для типа виджета значение Range.

Использование двух параметров даты

В следующем примере используются два параметра и start_dateend_dateдля задания диапазона дат.

SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN :start_date AND :end_date

Параметризация агрегирования по дням, месяцам или годам

В следующем примере объединяются данные о поездках на такси на параметризованном уровне детализации. Функция DATE_TRUNC усекает значение tpep_pickup_datetime на основе параметра :date_granularity, такого как DAY, MONTH или YEAR. Усеченная дата псевдонимируется как date_rollup и используется в предложении GROUP BY .

SELECT DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS
  date_rollup,
  COUNT(*) AS total_trips
FROM samples.nyctaxi.trips
GROUP BY date_rollup

Использование нескольких значений в одном запросе

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

Значение :list_parameter принимает список разделенных запятыми значений. Функция SPLIT анализирует этот список, разделяя разделенные запятыми значения на массив. Функция TRANSFORM преобразует каждый элемент в массиве путем удаления любого пробела. Функция ARRAY_CONTAINS проверяет, содержится ли значение dropoff_zip из таблицы trips в массиве значений, переданных как list_parameter.

SELECT * FROM samples.nyctaxi.trips WHERE
  array_contains(
    TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)),
    dropoff_zip
  )

Примечание.

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

Изменения синтаксиса

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

Вариант использования параметра	Синтаксис параметра Mustache	Синтаксис маркера именованных параметров
Загрузка только данных до указанной даты	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, :phone_number) Полный пример см. в разделе "Объединение нескольких параметров ".
Создание интервала	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Синтаксис параметра Mustache

Внимание

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

В редакторе SQL любая строка между двойными фигурными скобками {{ }} рассматривается как параметр запроса. Мини-приложение отображается над панелью результатов, и в нем нужно задать значение параметра. Хотя Azure Databricks обычно рекомендует использовать именованные маркеры параметров, некоторые функции поддерживаются только с помощью mustache-синтаксиса параметров.

Используйте синтаксис параметров Mustache для следующей функциональности:

• Несколько параметров раскрывающегося списка значений
• Параметры раскрывающегося списка на основе запросов

Добавьте параметр усов

1. Введите Cmd + I. Параметр вставляется в текстовое поле, и открывается диалоговое окно Добавление параметра.
   • Ключевое слово: ключевое слово, представляющее параметр в запросе.
   • Заголовок: заголовок, отображаемый над мини-приложением. По умолчанию заголовок совпадает с ключевым словом.
   • Тип. поддерживаются следующие типы: текст, число, дата, дата и время, дата и время (с секундами), раскрывающийся список и раскрывающийся список на основе запроса. Значение по умолчанию - Text.
2. Введите ключевое слово, при необходимости переопределите заголовок и выберите тип параметра.
3. Нажмите кнопку Добавить параметр.
4. В виджете параметра установите значение параметра.
5. Нажмите кнопку Применить изменения.
6. Нажмите кнопку Сохранить.

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

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

Изменение параметра запроса

Чтобы изменить параметр, щелкните значок шестеренки рядом с виджетом параметра. Чтобы запретить пользователям, которым не принадлежит запрос, изменять параметр, нажмите Показывать только результаты. Откроется диалоговое окно параметра <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 }} )

Мини-приложение множественного выбора параметров позволяет передавать в базу данных несколько значений. Если для параметра Кавычки выбрать вариант Двойные кавычки, запрос отобразится в следующем формате:WHERE IN ("value1", "value2", "value3")

Выпадающий список на основе запросов

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

1. Нажмите Раскрывающийся список на основе запроса в разделе Тип на панели настроек.
2. Щелкните поле Запрос и выберите запрос. Если целевой запрос возвращает большое количество записей, производительность будет ухудшаться.

Если целевой запрос возвращает более одного столбца, то SQL Databricks использует первый из них. Если целевой запрос возвращает name и value столбцы, то SQL Databricks заполняет мини -приложение выбора параметров столбцом name, но выполняет запрос с соответствующим value.

Например, предположим, что следующий запрос возвращает данные в таблице.

SELECT user_uuid AS 'value', username AS 'name'
FROM users

значение	имя
1001	Джон Смит
1002	Джейн Доу
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 в правом верхнем углу мини-приложения панели мониторинга и выбрав пункт "Изменить параметры мини-приложения".

Свойства параметра

• Заголовок: имя, видимое рядом с выбором значений на панели мониторинга. По умолчанию используется параметр Keyword. Чтобы изменить, щелкните значок . Заголовки не отображаются для статических параметров панели мониторинга, так как селектор значений скрыт. Если выбрать статическое значение в качестве источника значений, поле Заголовок станет неактивным.

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

• Значение по умолчанию: это значение используется по умолчанию, если иное значение не установлено. Чтобы изменить это с экрана запроса, запустите запрос со значением требуемого параметра и нажмите кнопку "Сохранить ".

• Источник значения— источник значения параметра. Щелкните , чтобы выбрать источник.

  • Новый параметр панели мониторинга: создайте новый параметр на уровне панели мониторинга. Это позволяет задать значение параметра в одном месте на панели мониторинга и связать его с несколькими визуализациями.
  • Существующий параметр панели мониторинга: сопоставьте параметр с существующим параметром панели мониторинга. Необходимо указать параметр существующей панели мониторинга.
  • Параметр мини-приложения: отображает селектор значений в мини-приложении панели мониторинга. Это полезно для одноразовых параметров, которые не являются общими для мини-приложений.
  • Статическое значение: выберите статическое значение для мини-приложения, которое будет независимым от значений, используемых в других мини-приложениях. Статически сопоставленные значения параметров не отображают селектор значений где-либо на панели мониторинга, что является более компактным. Это позволяет воспользоваться гибкостью параметров запроса, не загромождая пользовательский интерфейс на панели мониторинга, когда определенные параметры не ожидается часто изменять.

  

Часто задаваемые вопросы

• Можно ли повторно использовать один и тот же параметр несколько раз в одном запросе?
• Можно ли использовать несколько параметров в одном запросе?

Можно ли повторно использовать один и тот же параметр несколько раз в одном запросе?

Да. Используйте один и тот же идентификатор в фигурных скобках. В этом примере параметр {{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}}'