Trabalhar com parâmetros de consulta

Esta página explica como trabalhar com parâmetros de consulta no editor do SQL do Azure Databricks.

Os parâmetros de consulta podem deixar suas consultas mais dinâmicas e flexíveis inserindo valores variáveis no runtime. Em vez de codificar valores específicos nas consultas, você pode definir parâmetros para filtrar dados ou modificar a saída com base na entrada de usuário. Essa abordagem melhora a reutilização de consultas, aumenta a segurança ao impedir a injeção de SQL e possibilita um tratamento mais eficiente de diversos cenários de dados.

Sintaxe do marcador de parâmetro nomeado

Marcadores de parâmetro nomeados são variáveis de espaço reservado digitadas. Use esta sintaxe para escrever consultas nas seguintes partes da interface do usuário do Azure Databricks:

• Editor do SQL (novo e herdado)
• Notebooks
• Editor de conjunto de dados do painel de IA/BI
• Espaços de IA/BI Genie

Insira parâmetros em consultas SQL digitando o sinal de dois-pontos seguido por um nome de parâmetro, como :parameter_name. Quando você inclui um marcador de parâmetro nomeado em uma consulta, um widget é exibido na interface do usuário. É possível usar o widget para editar o tipo e o nome do parâmetro.

Adicionar um marcador de parâmetro nomeado a uma consulta

Este exemplo adiciona um marcador de parâmetro à seguinte consulta:

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

Essa consulta retorna um conjunto de dados que inclui apenas valores de tarifa inferiores a cinco dólares. Use as etapas a seguir para editar a consulta para usar um parâmetro, em vez do valor embutido em código (5).

1. Exclua o número 5 da consulta.
2. Digite um sinal de dois-pontos (:) seguido pela cadeia de caracteres fare_parameter. A última linha da consulta atualizada deve ser fare_amount < :fare_parameter.
3. Clique no ícone de engrenagem próximo ao widget de parâmetro. A caixa de diálogo mostra os seguintes campos:
   • Palavra-chave: a palavra-chave que representa o parâmetro na consulta. Não é possível editar esse campo. Para alterar a palavra-chave, edite o marcador na consulta SQL.
   • Título: o título que aparece acima do widget. Por padrão, o título é o mesmo que a palavra-chave.
   • Tipo: os tipos com suporte são Texto, Número, Lista Suspensa, Data, Data e Hora, Data e Hora (com Segundos). O padrão é Texto.
4. Na caixa de diálogo, altere o Tipo para Número.
5. Insira um número no widget de parâmetro e clique em Aplicar alterações.
6. Clique em Salvar para salvar a consulta.

Exemplos de sintaxe de parâmetro nomeado

Os exemplos a seguir demonstram alguns casos de uso comuns para parâmetros.

Inserir uma data

O exemplo a seguir inclui um parâmetro de data que limita os resultados da consulta a registros após uma data 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

Inserir um número

O exemplo a seguir inclui um parâmetro Number que limita os resultados a registros em que o campo o_total_price é maior que o valor de parâmetro fornecido.

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

Inserir um nome de campo

No exemplo a seguir, field_param é usado com a função IDENTIFIER para fornecer um valor de limite para a consulta em runtime. O valor do parâmetro deve ser um nome de coluna da tabela usada na consulta.

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

Inserir objetos de banco de dados

O exemplo a seguir cria três parâmetros: catalog, schema e table.

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

Consulte a cláusula IDENTIFIER.

Concatenar vários parâmetros

Você pode incluir parâmetros em outras funções SQL. Este exemplo permite que o espectador selecione um título de funcionário e uma ID de número. A consulta usa a função format_string para concatenar as duas cadeias de caracteres e filtrar as linhas que correspondem. Veja a format_string função.

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

Trabalhar com cadeias de caracteres JSON

Você pode usar parâmetros para extrair um atributo de uma cadeia de caracteres JSON. O seguinte exemplo usa a função from_json para converter a cadeia de caracteres JSON em um valor de struct. Substituir a cadeia de caracteres a pelo valor do parâmetro (param) retorna o atributo 1.

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

Criar um intervalo

O tipo INTERVAL representa um intervalo de tempo e permite a execução de aritmética e operações baseadas em tempo. O exemplo a seguir usa uma função CAST para converter o parâmetro como tipo de intervalo. O valor de INTERVAL resultante pode ser usado para cálculos baseados em tempo ou filtragem na consulta.

Consulte o tipo INTERVAL para obter detalhes e a sintaxe completa.

SELECT CAST(:param AS INTERVAL MINUTE)

Adicionar um intervalo de datas

Os exemplos a seguir demonstram como filtrar registros dentro de um intervalo de datas. Os parâmetros de data podem usar os atributos .min e .max para especificar um intervalo. Você também pode especificar um intervalo usando dois parâmetros de data diferentes.

Usar atributos mínimos e máximos

1. Escolha um nome de parâmetro. Use .min para acessar o valor mínimo do intervalo e .max acessar o valor máximo do intervalo, como na seguinte consulta:

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

2. Defina o tipo de parâmetro como Date.

3. Defina o tipo de Widget como Range.

Usar dois parâmetros de data

O exemplo a seguir usa dois parâmetros start_date e end_date, para definir um intervalo de datas.

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

Parametrizar rollups por dia, mês ou ano

O exemplo a seguir agrega dados de corrida de táxi em um nível parametrizado de granularidade. A função DATE_TRUNC trunca o valor tpep_pickup_datetime com base no valor do parâmetro :date_granularity, como DAY, MONTH ou YEAR. A data truncada tem um alias date_rollup e é usada na 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

Usar vários valores em uma única consulta

O exemplo a seguir usa a função ARRAY_CONTAINS para filtrar uma lista de valores. As funções TRANSFORM e SPLIT permitem que vários valores separados por vírgula sejam passados como um parâmetro de cadeia de caracteres.

O valor :list_parameter usa uma lista de valores separados por vírgula. A função SPLIT analisa essa lista, dividindo os valores separados por vírgula em uma matriz. A função TRANSFORM transforma cada elemento na matriz removendo qualquer espaço em branco. A função ARRAY_CONTAINS verifica se o valor dropoff_zip da tabela trips está contido na matriz de valores passados como list_parameter.

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

Alterações de sintaxe

A tabela a seguir mostra casos de uso comuns para parâmetros, a sintaxe mustache original do Databricks SQL e a sintaxe equivalente usando marcador de parâmetros nomeados.

Sintaxe do parâmetro Mustache

No editor de SQL, qualquer cadeia de caracteres entre chaves duplas {{ }} é tratada como um parâmetro de consulta. Um widget aparece acima do painel de resultados onde você definiu o valor do parâmetro. Embora o Azure Databricks geralmente recomenda usar marcadores de parâmetro nomeado, algumas funcionalidades só têm suporte usando a sintaxe de parâmetro mustache.

Use a sintaxe de parâmetro mustache para a seguinte funcionalidade:

• Filtros de painel herdados
• Parâmetros de lista suspensa com vários valores
• Parâmetros de lista suspensa baseados em consulta

Adicionar um parâmetro mustache

1. Digite Cmd + I. O parâmetro é inserido no cursor de texto e a caixa de diálogo Adicionar Parâmetro é exibida.
   • Palavra-chave: a palavra-chave que representa o parâmetro na consulta.
   • Título: o título que aparece acima do widget. Por padrão, o título é o mesmo que a palavra-chave.
   • Tipo: os tipos com suporte são Texto, Número, Data, Data e Hora, Data e Hora (com Segundos), Lista suspensa e Lista suspensa baseada em consulta. O padrão é Texto.
2. Insira a palavra-chave; opcionalmente, substitua o título e selecione o tipo de parâmetro.
3. Clique em Adicionar Parâmetro.
4. No widget de parâmetro, defina o valor do parâmetro.
5. Clique em Aplicar alterações.
6. Clique em Salvar.

Como alternativa, digite chaves duplas {{ }} e clicar no ícone de engrenagem próximo ao widget do parâmetro para editar as configurações.

Para executar a consulta com um valor de parâmetro diferente, insira o valor no widget e clique em Aplicar Alterações.

Editar um parâmetro de consulta

Para editar um parâmetro, clique no ícone de engrenagem ao lado do widget de parâmetro. Para impedir que os usuários que não possuem a consulta alterem o parâmetro, clique em Mostrar Somente Resultados. A caixa de diálogo de parâmetro <Keyword> é exibida.

Remover um parâmetro de consulta

Para remover um parâmetro de consulta, exclua o parâmetro da consulta. O widget de parâmetro desaparece e você pode reescrever sua consulta usando valores estáticos.

Alterar a ordem dos parâmetros

Para alterar a ordem em que os parâmetros são mostrados, você pode clicar e arrastar cada parâmetro para a posição desejada.

Tipos de parâmetro de consulta

• Texto
• Número
• Lista suspensa
• Lista suspensa baseada em consulta
• Data e hora

Texto

Aceita uma cadeia de caracteres como entrada. A barra invertida, as aspas simples e aspas duplas têm escapes, e o Azure Databricks adiciona aspas a esse parâmetro. Por exemplo, uma cadeia de caracteres como mr's Li"s é transformada em 'mr\'s Li\"s' Um exemplo de como usar isso pode ser

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

Número

Aceita um número como entrada. Um exemplo de como usar isso pode ser

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

Lista suspensa

Para restringir o escopo dos valores de parâmetro possíveis ao executar uma consulta, use o tipo de parâmetro Lista suspensa. Um exemplo seria SELECT * FROM users WHERE name='{{ dropdown_param }}'. Quando selecionada no painel de configurações de parâmetro, é exibida uma caixa de texto na qual você insere os valores permitidos, cada valor separado por uma nova linha. Listas suspensas são parâmetros de texto. Para usar datas ou datas e horas em sua Lista Suspensa, insira-as no formato exigido pela fonte de dados. As cadeias de caracteres não têm escape. Você pode escolher uma lista de menu suspenso de valor único ou de vários valores.

• Valor único: é obrigatório usar aspas simples em torno do parâmetro.
• Vários valores: ative a opção Permitir vários valores. Na lista suspensa Aspas, escolha se deseja deixar os parâmetros como inseridos (sem aspas) ou encapsular os parâmetros com aspas simples ou duplas. Você não precisará adicionar aspas ao redor do parâmetro se escolher aspas.

Altere sua cláusula WHERE para usar a palavra-chave IN em sua consulta.

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

O widget de seleção múltipla do parâmetro permite que você passe vários valores para o banco de dados. Se você selecionar a opção Aspas Duplas para o parâmetro Quotation, sua consulta refletirá o seguinte formato: WHERE IN ("value1", "value2", "value3")

Lista suspensa baseada em consulta

Aceita o resultado de uma consulta como entrada. Ela tem o mesmo comportamento do parâmetro Lista Suspensa. Você precisa salvar a consulta da lista suspensa de SQL do Databricks para usá-la como entrada em outra consulta.

1. Clique na Lista suspensa baseada em consulta em Tipo no painel de configurações.
2. Clique no campo Consulta e selecione uma consulta. Se a consulta de destino retornar um grande número de registros, o desempenho será prejudicado.

Se a consulta de destino retornar mais de uma coluna, o SQL do Databricks usará a primeira. Se a consulta de destino retornar as colunas name e value, o SQL do Databricks preencherá o widget de seleção de parâmetro com a coluna name, mas executará a consulta com ovalue associado.

Por exemplo, suponha que a consulta a seguir retorne os dados na tabela.

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

Quando o Azure Databricks executa a consulta, o valor passado para o banco de dados será 1001, 1002 ou 1003.

Data e hora

O Azure Databricks oferece várias opções para parametrizar valores de data e de carimbo de data/hora, incluindo opções para simplificar a parametrização de intervalos de tempo. Selecione entre três opções de precisão variável:

Ao escolher uma opção de parâmetro de intervalo, você cria dois parâmetros designados pelos sufixos .start e .end. Todas as opções passam parâmetros à consulta como literais de cadeia de caracteres. O Azure Databricks exige que você coloque os valores de data e hora entre aspas simples ('). Por exemplo:

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

Os parâmetros de data usam uma interface de seleção de calendário e o padrão usado é a data e a hora atuais.

Valores dinâmicos de data e intervalo de datas

Quando você adiciona um parâmetro de data ou de intervalo de datas à consulta, o widget de seleção mostra um ícone azul de raio. Clique nele para exibir valores dinâmicos como today, yesterday, this week, last week, last month ou last year. Esses valores são atualizados dinamicamente.

Usar parâmetros de consulta em painéis

Opcionalmente, as consultas podem aproveitar parâmetros ou valores estáticos. Quando uma visualização baseada em uma consulta parametrizada é adicionada a um painel, a visualização pode ser configurada para usar um:

• Parâmetro de widget

  Os parâmetros de widget são específicos para apenas uma visualização em um dashboard, aparecem dentro do painel de visualização e os valores de parâmetro especificados se aplicam somente à consulta subjacente à visualização.

• Parâmetro de dashboard

  Os parâmetros de dashboard podem ser aplicados a várias visualizações. Quando você adiciona uma visualização com base em uma consulta parametrizada a um dashboard, o parâmetro será adicionado por padrão como um parâmetro de dashboard. Os parâmetros de dashboard são configurados para uma ou mais visualizações em um painel e aparecem na parte superior do painel. Os valores de parâmetro especificados para um parâmetro de dashboard se aplicam às visualizações que reutilizam esse parâmetro de dashboard específico. Um painel pode ter vários parâmetros; cada um deles pode ser aplicado a algumas visualizações e não a outras.

• Valor estático

  Os valores estáticos são usados em vez de um parâmetro que responde às alterações. Os valores estáticos permitem codificar um valor no lugar de um parâmetro. Eles fazem o parâmetro desaparecer do painel ou do widget no qual apareceu anteriormente.

Ao adicionar uma visualização contendo uma consulta parametrizada, você pode escolher o título e a fonte do parâmetro na consulta de visualização clicando no ícone de lápis apropriado. Você também pode selecionar a palavra-chave e um valor padrão. Confira Propriedades de parâmetro.

Depois de adicionar uma exibição a um painel, acesse a interface de mapeamento de parâmetros clicando no menu kebab no canto superior direito de um widget de painel e clique em Alterar configurações do widget.

Propriedades do parâmetro

• Título: o nome de exibição que aparece ao lado do seletor de valor em seu painel. Ele usa como padrão o parâmetro palavra-chave. Para editá-lo, clique no ícone de lápis . Os títulos não são exibidos para parâmetros de painel estático, porque o seletor de valor está oculto. Se você selecionar Valor estático como a Origem do valor, o campo Título ficará esmaecido.

• Palavra-chave: o literal de cadeia de caracteres desse parâmetro na consulta subjacente. Isso será útil para depuração se o painel não retornar os resultados esperados.

• Valor padrão: o valor padrão será usado se nenhum outro valor for especificado. Para alterar isso na tela de consulta, execute a consulta com o valor do parâmetro desejado e clique no botão Salvar.

• Origem do valor: a origem do valor do parâmetro. Clique no ícone de lápis para escolher uma origem.

  • Novo parâmetro de painel: cria um novo parâmetro de nível de painel. Isso permite que você defina um valor de parâmetro em um único local no painel e o mapeie para várias visualizações.
  • Parâmetro de painel existente: mapear parâmetro para um parâmetro de painel existente. Você deve especificar o parâmetro de painel já existente.
  • Parâmetro de widget: exibe um seletor de valor dentro do widget de painel. Isso é útil para parâmetros únicos que não são compartilhados entre widgets.
  • Valor estático: escolhe um valor estático para o widget, independentemente dos valores usados em outros widgets. Os valores de parâmetros mapeados estaticamente não exibem um seletor de valor em lugares mais compactos do painel. Isso permite que você aproveite a flexibilidade dos parâmetros de consulta sem obstruir a interface do usuário em um painel quando não se espera que determinados parâmetros sejam alterados com frequência.

  

Perguntas Frequentes (FAQ)

• Posso reutilizar o mesmo parâmetro várias vezes em uma única consulta?
• Posso usar vários parâmetros em uma única consulta?

Posso reutilizar o mesmo parâmetro várias vezes em uma única consulta?

Sim. Use o mesmo identificador entre chaves. Este exemplo usa o parâmetro {{org_id}} duas vezes.

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

Posso usar vários parâmetros em uma única consulta?

Sim. Use um nome exclusivo para cada parâmetro. Este exemplo usa dois parâmetros: {{org_id}} e {{start_date}}.

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