Comparteix via

Trabajar con parámetros de consulta

En esta página se explica cómo trabajar con parámetros de consulta en el editor de SQL de Azure Databricks.

Los parámetros de consulta te permiten hacer tus consultas más dinámicas y flexibles insertando valores variables en tiempo de ejecución. En lugar de codificar valores específicos en sus consultas, puede definir parámetros para filtrar datos o modificar la salida en función de la entrada del usuario. Este enfoque mejora la reutilización de consultas, aumenta la seguridad al evitar la inyección de SQL y permite una administración más eficaz de diversos escenarios de datos.

Sintaxis de marcador de parámetro con nombre

Los marcadores de parámetro con nombre son variables de marcador de posición con tipo. Utilice esta sintaxis para escribir consultas en las siguientes partes de la interfaz de usuario de Azure Databricks:

  • Editor de SQL (nuevo y heredado)
  • Cuadernos
  • Editor de conjunto de datos de panel AI/BI
  • Espacios de AI/BI Genie

Nota

El editor de conjuntos de datos del panel de AI/BI y el nuevo editor de SQL admiten los siguientes tipos de datos con sintaxis de parámetros con nombre: parámetros numéricos, de fecha y hora. En todas las demás superficies de interfaz de usuario, solo se admiten parámetros de tipo cadena mediante la sintaxis de parámetros con nombre. Para emplear otros tipos de parámetros fuera de estos editores, deben usarse los parámetros mustache.

Inserte parámetros en sus consultas SQL escribiendo dos puntos seguidos de un nombre de parámetro, como :parameter_name. Cuando incluye un marcador de parámetro con nombre en una consulta, aparece un widget en la interfaz de usuario. Puede utilizar el widget para editar el tipo y el nombre del parámetro.

Se añade un parámetro con nombre a una consulta SQL. Aparece un widget debajo del editor SQL

Añadir un marcador de parámetro con nombre a una consulta

Este ejemplo añade un marcador de parámetro a la siguiente consulta:


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

Esta consulta devuelve un conjunto de datos que incluye solo importes de tarifa inferiores a cinco dólares. Siga los siguientes pasos para editar la consulta y utilizar un parámetro en lugar del valor codificado (5).

  1. Elimine el número 5 de la consulta.
  2. Escriba dos puntos (:) seguidos de la cadena fare_parameter. La última línea de la consulta actualizada debe decir fare_amount < :fare_parameter.
  3. Haga clic en el icono de engranaje. Icono de engranaje cerca del widget de parámetros. El cuadro de diálogo muestra los siguientes campos:
    • Palabra clave: palabra clave que representa el parámetro de la consulta. Este campo no se puede modificar. Para cambiar la palabra clave, edite el marcador en la consulta SQL.
    • Título: título que aparece sobre el widget. De manera predeterminada, el título es el mismo que la palabra clave.
    • Tipo: Los tipos soportados son Texto, Número, Lista desplegable, Fecha, Fecha y hora, y Fecha y hora (con segundos). El valor predeterminado es texto.
  4. En el cuadro de diálogo, cambie el Tipo a Número.
  5. Introduzca un número en el widget del parámetro y haga clic en Aplicar cambios.
  6. Haga clic en Guardar para guardar la consulta.

Ejemplos de sintaxis de parámetros con nombre

En los ejemplos siguientes se muestran algunos casos de uso comunes para los parámetros.

Inserción una fecha

En el ejemplo siguiente se incluye un parámetro Date que limita los resultados de la consulta a los registros después de una fecha específica.


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

Inserción de un número

El siguiente ejemplo incluye un parámetro Número que limita los resultados a los registros en los que el campo o_total_price es mayor que el valor del parámetro proporcionado.


SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  o_totalprice AS Price
FROM
  samples.tpch.orders
WHERE
  o_totalprice > :num_param

Inserción de un nombre de campo

En el ejemplo siguiente, field_param se usa con la función IDENTIFIER para proporcionar un valor de umbral para la consulta en tiempo de ejecución. El valor del parámetro debe ser un nombre de columna de la tabla utilizada en la consulta.


SELECT
  *
FROM
  samples.tpch.orders
WHERE
  IDENTIFIER(:field_param) < 10000

Inserción de objetos de base de datos

En el ejemplo siguiente se crean tres parámetros: catalog, schema y table.


SELECT
  *
FROM
  IDENTIFIER(:catalog || '.' || :schema || '.' || :table)

Consulte la cláusula IDENTIFIER.

Concatenación de varios parámetros

Puede incluir parámetros en otras funciones SQL. Este ejemplo permite al visor seleccionar un título de empleado y un identificador numérico. La consulta usa la función format_string para concatenar las dos cadenas y filtrar por las filas que coinciden. Consulte la format_string función.


SELECT
  o_orderkey,
  o_clerk
FROM
  samples.tpch.orders
WHERE
  o_clerk LIKE format_string('%s%s', :title, :emp_number)

Operaciones con cadenas JSON

Puede usar parámetros para extraer un atributo de una cadena JSON. En el ejemplo siguiente se utiliza la función from_json para convertir una cadena JSON en un valor struct. Sustituyendo la cadena a como valor del parámetro (param) se obtiene el atributo 1.

SELECT
  from_json('{"a": 1}', 'map<string, int>') [:param]

Crear un intervalo

El tipo INTERVAL representa un intervalo de tiempo y permite realizar operaciones y operaciones aritméticas basadas en el tiempo. En el ejemplo siguiente se usa una CAST función para convertir el parámetro como tipo de intervalo. El valor INTERVAL resultante puede utilizarse para cálculos basados en el tiempo o para filtrar en la consulta.

Consulte INTERVAL type (Tipo ) para obtener detalles completos y sintaxis.

SELECT CAST(:param AS INTERVAL MINUTE)

Añadir un intervalo de fechas

En los ejemplos siguientes se muestra cómo filtrar los registros dentro de un intervalo de fechas. Los parámetros de fecha pueden usar .min atributos y .max para especificar un intervalo. También puede especificar un intervalo mediante dos parámetros de fecha diferentes.

Uso de atributos min y max

  1. Elija un nombre de parámetro. Use .min para acceder al valor de intervalo mínimo y .max para acceder al valor de intervalo máximo, como en la consulta siguiente:

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

  2. Establezca el tipo de parámetro en Date.

  3. Establezca el tipo de widget en Rango.

Uso de dos parámetros de fecha

En el ejemplo siguiente se usan dos parámetros y start_dateend_date, para establecer un intervalo de fechas.

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

Parametrizar acumulaciones por día, mes o año

El siguiente ejemplo agrega datos de viajes en taxi a un nivel de granularidad parametrizado. La función DATE_TRUNC trunca el valor tpep_pickup_datetime basándose en el valor del parámetro :date_granularity, como , DAY, MONTH o YEAR. La fecha truncada se aliasa como date_rollup y se utiliza en la cláusula 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

Utilizar varios valores en una sola consulta

El siguiente ejemplo utiliza la función ARRAY_CONTAINS para filtrar una lista de valores. Las funciones TRANSFORM y SPLIT permiten pasar varios valores separados por comas como parámetro de cadena.

El valor :list_parameter toma una lista de valores separados por comas. La función SPLIT analiza la lista y divide los valores separados por comas en una matriz. La función TRANSFORM transforma cada elemento de la matriz eliminando los espacios en blanco. La función ARRAY_CONTAINS comprueba si el valor dropoff_zip de la tabla trips está contenido en la matriz de valores pasada como list_parameter.


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

Nota

Este ejemplo funciona para valores de cadena. Para modificar la consulta para otros tipos de datos, como una lista de enteros, envuelva la operación TRANSFORM con una operación CAST para convertir los valores de cadena en el tipo de datos deseado.

Cambios en la sintaxis

La siguiente tabla muestra casos de uso comunes para parámetros, la sintaxis de bigote SQL original de Databricks y la sintaxis equivalente utilizando la sintaxis de marcador de parámetro con nombre.

Caso de uso de parámetros Sintaxis de parámetro de bigote Sintaxis de marcador de parámetro con nombre
Cargar solo datos anteriores a una fecha especificada WHERE date_field < '{{date_param}}'
Debe incluir comillas alrededor del parámetro de fecha y las llaves.		 WHERE date_field < :date_param
Cargar solo datos inferiores a un valor numérico especificado WHERE price < {{max_price}} WHERE price < :max_price
Compara dos cadenas WHERE region = {{region_param}} WHERE region = :region_param
Especificar la tabla utilizada en una consulta SELECT * FROM {{table_name}} SELECT * FROM IDENTIFIER(:table)
Cuando un usuario introduce este parámetro, debe utilizar el espacio de nombres completo de tres niveles para identificar la tabla.
Especificar de forma independiente el catálogo, el esquema y la tabla utilizados en una consulta SELECT * FROM {{catalog}}.{{schema}}.{{table}} SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Utilizar parámetros como plantilla en una cadena más larga y formateada "({{area_code}}) {{phone_number}}"
Los valores de los parámetros se concatenan automáticamente como una cadena.		 format_string("(%d)%d, :area_code, :phone_number)
Consulte Concatenar varios parámetros para ver un ejemplo completo.
Crear un intervalo SELECT INTERVAL {{p}} MINUTE SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Sintaxis del parámetro Mustache

Importante

Las siguientes secciones se aplican a la sintaxis de consulta que puede utilizar únicamente en el editor de SQL. Esto significa que si copia y pega una consulta con esta sintaxis en cualquier otra interfaz de Azure Databricks, como un bloc de notas o un editor de conjuntos de datos de cuadros de mando AI/BI, la consulta debe ajustarse manualmente para utilizar marcadores de parámetros con nombre antes de que se ejecute sin errores.

En el editor SQL, cualquier cadena entre llaves dobles {{ }} se trata como un parámetro de consulta. Aparece un widget encima del panel de resultados donde se establece el valor del parámetro. Aunque Azure Databricks suele recomendar el uso de marcadores de parámetros con nombre, algunas funciones solo se admiten mediante la sintaxis de parámetros de bigote.

Utilice la sintaxis de parámetro de bigote para las siguientes funciones:

Añadir un parámetro de bigote

  1. Tipo Cmd + I. El parámetro se inserta en el símbolo de inserción de texto y aparece el cuadro de diálogo Agregar parámetro.
    • Palabra clave: palabra clave que representa el parámetro de la consulta.
    • Título: título que aparece sobre el widget. De manera predeterminada, el título es el mismo que la palabra clave.
    • Tipo: los tipos admitidos son Texto, Número, Fecha, Fecha y hora, Fecha y hora (con segundos), Lista desplegable y Lista desplegable basada en consulta. El valor predeterminado es texto.
  2. Escriba la palabra clave, invalide opcionalmente el título y seleccione el tipo de parámetro.
  3. Haga clic en Agregar parámetro.
  4. En el widget de parámetro, establezca el valor del parámetro.
  5. Haga clic en Aplicar cambios.
  6. Haga clic en Guardar.

Como alternativa, escriba llaves dobles {{ }} y haga clic en el icono de engranaje cerca del widget de parámetros para editar la configuración.

Para volver a ejecutar la consulta con otro valor de parámetro, escriba el valor en el widget y haga clic en Aplicar cambios.

Edición de un parámetro de consulta

Para editar un parámetro, haga clic en el icono de engranaje situado junto al widget de parámetro. Para evitar que los usuarios que no poseen la consulta cambien el parámetro, haga clic en Mostrar solo resultados. Aparece el cuadro de diálogo del parámetro <Keyword>.

Eliminación de un parámetro de consulta

Para quitar un parámetro de consulta, elimine el parámetro de la consulta. El widget de parámetros desaparece y puede volver a escribir la consulta mediante valores estáticos.

Cambio del orden de los parámetros

Para cambiar el orden en que se muestran los parámetros, puede hacer clic y arrastrar cada parámetro a la posición deseada.

Tipos de parámetros de consulta

Text

Toma una cadena como entrada. Las barras diagonales inversas, las comillas simples y dobles se escapan, y Azure Databricks agrega comillas a este parámetro. Por ejemplo, una cadena como mr's Li"s se transforma en 'mr\'s Li\"s' Un ejemplo de uso de esto podría ser

SELECT * FROM users WHERE name={{ text_param }}

Número

Toma un número como entrada. Un ejemplo de uso de esto podría ser

SELECT * FROM users WHERE age={{ number_param }}

Para restringir el ámbito de los posibles valores de parámetro al ejecutar una consulta, usa el tipo de parámetro Lista desplegable. Un ejemplo sería SELECT * FROM users WHERE name='{{ dropdown_param }}'. Cuando se selecciona en el panel de configuración de parámetros, aparece un cuadro de texto donde se especifican los valores permitidos, cada valor separado por una nueva línea. Las listas desplegables son parámetros de texto. Para usar fechas o fechas y horas en la lista desplegable, escríbelas en el formato que requiere el origen de datos. No se aplica escape a las cadenas. Puede elegir entre un único valor o una lista desplegable de varios valores.

  • Valor único: se requieren comillas simples alrededor del parámetro.
  • Varios valores: alterne la opción Permitir varios valores. En la lista desplegable Comillas, elija si desea dejar los parámetros como especificados (sin comillas) o ajustar los parámetros con comillas simples o dobles. No es necesario agregar comillas alrededor del parámetro si elige comillas.

Cambie la cláusula WHERE para usar la palabra clave IN en la consulta.

SELECT ...
FROM   ...
WHERE field IN ( {{ Multi Select Parameter }} )

El widget de selección múltiple de parámetros permite pasar varios valores a la base de datos. Si seleccionas la opción Comillas dobles para el parámetro Cotización, la consulta reflejará el siguiente formato: WHERE IN ("value1", "value2", "value3")

Lista desplegable basada en consulta

Toma el resultado de una consulta como entrada. Tiene el mismo comportamiento que el parámetro Lista desplegable. Debe guardar la consulta de lista desplegable de Databricks SQL para usarla como entrada en otra consulta.

  1. Haga clic en Lista desplegable basada en consulta en Tipo en el panel de configuración.
  2. Haga clic en el campo Consulta y seleccione una consulta. Si la consulta de destino devuelve un gran número de registros, el rendimiento se resiente.

Si la consulta de destino devuelve más de una columna, Databricks SQL usa la primera. Si la consulta de destino devuelve columnas name y value, Databricks SQL rellena el widget de selección de parámetros con la columna name, pero ejecuta la consulta con el value asociado.

Por ejemplo, supongamos que la consulta siguiente devuelve los datos de la tabla.

SELECT user_uuid AS 'value', username AS 'name'
FROM users
valor nombre
1001 John Smith
1002 Jane Doe
1003 Bobby Tables

Cuando Azure Databricks ejecuta la consulta, el valor pasado a la base de datos sería 1001, 1002 o 1003.

Fecha y hora

Azure Databricks tiene varias opciones para parametrizar los valores de fecha y marca de tiempo, incluidas las opciones para simplificar la parametrización de intervalos de tiempo. Selecciona entre tres opciones de precisión variable:

Opción Precisión Tipo
Fecha Día DATE
Fecha y hora minute TIMESTAMP
Fecha y hora (con segundos) second TIMESTAMP

Al elegir una opción del parámetro Range, se crean dos parámetros designados por los sufijos .start y .end. Todas las opciones pasan parámetros a la consulta como literales de cadena; Azure Databricks requiere que ajuste los valores de fecha y hora entre comillas simples ('). Por ejemplo:

-- 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 }}'

Los parámetros de fecha usan una interfaz de selección de calendario y pueden tener como valor predeterminado la fecha y hora actuales.

Nota

El parámetro Date Range solo devuelve los resultados correctos para las columnas de tipo DATE. Para las columnas TIMESTAMP, use una de las opciones de Intervalo de fecha y hora.

Valores dinámicos de fecha e intervalo de fechas

Al agregar un parámetro de fecha o intervalo de fechas a la consulta, el widget de selección muestra un icono de rayo azul. Haga clic en él para mostrar valores dinámicos como today, yesterday, this week, last week, last month o last year. Estos valores se actualizan de forma dinámica.

Importante

Las fechas dinámicas y los intervalos de fechas no son compatibles con las consultas programadas.

Uso de parámetros de consulta en paneles

Opcionalmente, las consultas pueden usar parámetros o valores estáticos. Cuando se agrega una visualización basada en una consulta con parámetros a un panel, la visualización se puede configurar para usar una de las siguientes opciones:

  • Parámetro de widget

    Los parámetros de widget son específicos de una única visualización en un panel, aparecen en el panel de visualización y los valores de los parámetros especificados solo se aplican a la consulta subyacente a la visualización.

  • Parámetro de panel

    Los parámetros del panel se pueden aplicar a varias visualizaciones. Al agregar una visualización basada en una consulta con parámetros a un panel, el parámetro se agregará como parámetro de panel de forma predeterminada. Los parámetros del panel se configuran para una o varias visualizaciones en un panel y aparecen en la parte superior del panel. Los valores de parámetro especificados para un parámetro de panel se aplican a las visualizaciones que reutilizan ese parámetro de panel determinado. Un panel puede tener varios parámetros, cada uno de los cuales puede aplicarse a algunas visualizaciones y no a otras.

  • Valor estático

    Los valores estáticos se usan en lugar de un parámetro que responde a los cambios. Los valores estáticos permiten codificar un valor en lugar de un parámetro. Hacen que el parámetro "desaparezca" del panel de control o del widget en el que aparecía anteriormente.

Al agregar una visualización que contiene una consulta parametrizada, puede elegir el título y el origen del parámetro en la consulta de visualización si hace clic en el icono de lápiz adecuado. También puede seleccionar la palabra clave y un valor predeterminado. Consulte Propiedades de parámetro.

Después de agregar una visualización a un panel, acceda a la interfaz de asignación de parámetros haciendo clic en el menú kebab de la parte superior derecha de un widget de panel y, a continuación, haciendo clic en Cambiar configuración del widget.

Propiedades del parámetro

  • Título: nombre para mostrar que aparece junto al selector de valores en el panel. El valor predeterminado es el parámetro Palabra clave. Para editarlo, haga clic en el icono de lápiz Icono de lápiz. Los títulos de los parámetros estáticos del panel no aparecen porque el selector de valores está oculto. Si selecciona Valor estático como Origen del valor, el campo Título aparece atenuado.

  • Palabra clave: literal de cadena de este parámetro en la consulta subyacente. Esto resulta útil para depurar si el panel no devuelve los resultados esperados.

  • Valor predeterminado: valor usado si no se especifica ningún otro. Para cambiar esto desde la pantalla de consulta, ejecute la consulta con el valor de parámetro deseado y haga clic en el botón Guardar.

  • Origen del valor: origen del valor del parámetro. Haga clic en el icono del lápiz Icono de lápiz para elegir una fuente.

    • Nuevo parámetro de panel: cree un nuevo parámetro de nivel de panel. Esto le permite establecer un valor de parámetro en un solo lugar en el panel y asignarlo a varias visualizaciones.
    • Parámetro de panel existente: asigne el parámetro a un parámetro de panel existente. Debe especificar qué parámetro de panel ya existente.
    • Parámetro de widget: muestra un selector de valores dentro del widget de panel. Esto es útil para parámetros únicos que no se comparten entre widgets.
    • Valor estático: elija un valor estático para el widget, independientemente de los valores usados en otros widgets. Los valores de parámetro asignados estáticamente no muestran un selector de valores en ningún lugar del panel, que es más compacto. Esto le permite aprovechar la flexibilidad de los parámetros de consulta sin abarrotar la interfaz de usuario en un panel si no se espera que determinados parámetros cambien con frecuencia.

    Cambiar la asignación de parámetros

Preguntas más frecuentes (P+F)

¿Puedo reutilizar el mismo parámetro varias veces en una sola consulta?

Sí. Use el mismo identificador entre llaves. En este ejemplo se usa el parámetro {{org_id}} dos veces.

SELECT {{org_id}}, count(0)
FROM queries
WHERE org_id = {{org_id}}

¿Puedo usar varios parámetros en una sola consulta?

Sí. Use un nombre único para cada parámetro. En este ejemplo se usan dos parámetros: {{org_id}} y {{start_date}}.

SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'
  • Last updated on