Trabajar con parámetros de consulta

En este artículo se explica cómo trabajar con parámetros de consulta en el editor de SQL de Azure Databricks.

Los parámetros de consulta permiten que las consultas sean más dinámicas y flexibles insertando valores de variable en tiempo de ejecución. En lugar de codificar valores específicos de forma rígida en las consultas, puede definir parámetros para filtrar los datos o modificar la salida en función de la entrada del usuario. Este enfoque mejora la reutilización de consultas, mejora la seguridad mediante la prevención de la inyección de SQL y permite un control más eficaz de diversos escenarios de datos.

Sintaxis de marcador de parámetros con nombre

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

• Editor SQL

• Cuaderno

• Editor de conjuntos de datos del panel de AI/BI

• Espacios de AI/BI Genie (versión preliminar pública)

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

Adición de un marcador de parámetro con nombre a una consulta

En este ejemplo se agrega un marcador de parámetro a la consulta siguiente:

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 tarifas inferiores a cinco dólares. Siga estos pasos para editar la consulta a fin de usar un parámetro en lugar del valor codificado de forma rígida (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 indicar fare_amount < :fare_parameter.
3. Haga clic en el 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 admitidos 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. Escriba un número en el widget de parámetros 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 de 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

En el ejemplo siguiente se incluye un parámetro Numérico que limita los resultados a los registros en los que el campo o_total_price sea 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 Cláusula IDENTIFICADOR.

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 Función format_string.

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

Trabajo con cadenas JSON

Puede usar parámetros para extraer un atributo de una cadena JSON. En el ejemplo siguiente, se usa la función from_json para convertir la cadena JSON un valor de estructura. La sustitución de la cadena a como valor del parámetro (param) devuelve el atributo 1.

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

Crear un intervalo

El INTERVAL tipo representa un intervalo de tiempo y permite realizar operaciones aritméticas y basadas en el tiempo. En el ejemplo siguiente se incluye el parámetro dentro de una format_string función que, a continuación, se convierte como un tipo de intervalo. El valor resultante INTERVAL se puede usar para los cálculos basados en el tiempo o el filtrado en la consulta.

Consulte TIPO INTERVAL para obtener detalles completos y sintaxis.

SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Agregar un intervalo de fechas

En el ejemplo siguiente se muestra cómo agregar un intervalo de fechas con parámetros para seleccionar registros en un período de tiempo específico.

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

Parametrizar paquetes acumulativos por día, mes o año

En el ejemplo siguiente se agregan datos de carreras de taxi en un nivel con parámetros de granularidad. La DATE_TRUNC función trunca el tpep_pickup_datetime valor en función del valor del :date_granularity parámetro, como DAY, MONTHo YEAR. La fecha truncada se alias como date_rollup y se usa en la GROUP BY cláusula .

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

Uso de varios valores en una sola consulta

En el ejemplo siguiente se usa la ARRAY_CONTAINS función para filtrar una lista de valores. Las TRANSFORMfunciones , y SPLIT permiten pasar varios valores separados por comas como parámetro de cadena.

El :list_parameter valor toma una lista de valores separados por comas. La SPLIT función analiza esa lista, dividiendo los valores separados por comas en una matriz. La TRANSFORM función transforma cada elemento de la matriz quitando cualquier espacio en blanco. La ARRAY_CONTAINS función comprueba si el dropoff_zip valor de la trips tabla está contenido en la matriz de valores pasados como list_parameter.

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

Cambios de sintaxis

La siguiente tabla muestra casos de uso comunes para los parámetros, la sintaxis Mustache SQL original de Databricks y la sintaxis equivalente mediante la sintaxis de marcadores de parámetros con nombre.

Sintaxis del parámetro Mustache

En el editor de 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 funcionalidades solo se admiten mediante la sintaxis del parámetro mustache.

Use la sintaxis del parámetro mustache para la siguiente funcionalidad:

• Filtros de panel heredados
• Parámetros desplegables de varios valores
• Intervalos de fechas
• Parámetros desplegables basados en consultas

Adición de un parámetro mustache

1. Escriba 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 Save(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 en cada parámetro y arrastrarlo a la posición deseada.

Tipos de parámetros de consulta

• Texto
• Number
• Lista desplegable
• Lista desplegable basada en consultas
• Fecha y hora

Texto

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

Number

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

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

Lista desplegable

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 consultas

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

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:

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.

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.

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 de forma rígida un valor en lugar de un parámetro. Hacen que el parámetro "desaparezca" del panel o widget donde apareció 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 . 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 de lápiz para elegir un origen.

  • 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.

  

Preguntas más frecuentes (P+F)

• ¿Puedo reutilizar el mismo parámetro varias veces en una sola consulta?
• ¿Puedo usar varios parámetros en una sola consulta?

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