Trabalhe com parâmetros de consulta

Este artigo explica como trabalhar com parâmetros de consulta no editor Databricks SQL.

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

Sintaxe do marcador de parâmetro nomeado

Os marcadores de parâmetros nomeados são variáveis de espaço reservado digitadas. Use essa sintaxe para escrever consultas nas seguintes partes da interface do usuário do Databricks:

• Editor de SQL
• cadernos
• AI/BI dashboard dataset editor
• AI/BI Genie spaces (visualização pública)

Insira parâmetros em suas consultas SQL digitando dois pontos seguidos de um nome de parâmetro, como :parameter_name. Quando você inclui um marcador de parâmetro nomeado em uma consulta, um widget aparece na interface do usuário. Você pode usar o widget para editar o tipo e o nome do parâmetro.

Adicionar um marcador de parâmetro nomeado a uma consulta

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

SQL

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

Essa consulta retorna um dataset que inclui apenas valores de tarifas inferiores a cinco dólares. Use as etapas a seguir para editar a consulta e usar um parâmetro em vez do valor codificado (5).

1. Exclua o número 5 da consulta.

2. Digite dois pontos (:) seguidos das cadeias de caracteres fare_parameter. A última linha da sua consulta atualizada deve dizer fare_amount < :fare_parameter.

3. Clique no ícone de engrenagem próximo ao widget de parâmetros. A caixa de diálogo mostra os seguintes campos:

   • Palavra-chave : a palavra-chave que representa o parâmetro na consulta. Você não pode editar esse campo. Para alterar a palavra-chave, edite o marcador na consulta SQL.
   • Título : O título que aparece sobre o widget. Em default, o título é o mesmo que a palavra-chave.
   • Type (Tipo ): Os tipos compatíveis são Texto, Número, Lista suspensa, Data, Data e hora e Data e hora (com segundos). O endereço default é Text.

4. Na caixa de diálogo, altere o Tipo para Número .

5. Insira um número no widget de parâmetros e clique em Aplicar alterações .

6. Clique em Salvar para salvar a consulta.

Exemplos de sintaxe de parâmetros nomeados

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

Inserir uma data

O exemplo a seguir inclui um parâmetro Date que limita os resultados da consulta aos registros após uma data específica.

SQL

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 aos registros em que o campo o_total_price é maior do que o valor do parâmetro fornecido.

SQL

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, o field_param é usado com a função IDENTIFIER para fornecer um valor limite para a consulta em tempo de execução. O valor do parâmetro deve ser um nome de coluna da tabela usada na consulta.

SQL

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

Insira objetos de banco de dados

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

SQL

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

Consulte a cláusula IDENTIFIER.

Concatene vários parâmetros

O senhor pode incluir parâmetros em outras funções SQL. Esse exemplo permite que o espectador selecione um título de funcionário e uma ID numérica. A consulta usa a função format_string para concatenar os dois strings e filtrar as linhas que correspondem. Veja a funçãoformat_string.

SQL

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

Trabalhe com JSON strings

O senhor pode usar parâmetros para extrair um atributo de uma cadeia de caracteres JSON. O exemplo a seguir usa a funçãofrom_json para converter as cadeias de caracteres JSON em um valor struct. Substituir as cadeias de caracteres a como o valor do parâmetro (param) retorna o atributo 1.

SQL

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

Crie um intervalo

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

Consulte o tipoINTERVAL para obter detalhes e sintaxe completos.

SQL

SELECT CAST(:param AS INTERVAL MINUTE)

Adicionar um intervalo de datas

O exemplo a seguir mostra como adicionar um intervalo de datas parametrizado para selecionar registros em um período específico.

SQL

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

Parametrize os rollups por dia, mês ou ano

O exemplo a seguir agrega dados de viagem 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 o alias de date_rollup e é usada na cláusula GROUP BY.

SQL

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

Use 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 cadeias 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 passada como list_parameter.

SQL

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

nota

Esse exemplo funciona para valores de strings. Para modificar a consulta para outros tipos de dados, como uma lista de números inteiros, envolva o TRANSFORM operações com um CAST operações para converter os valores de strings no tipo de dados desejado.

Mudanças de sintaxe

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

Caso de uso de parâmetros	Sintaxe do parâmetro Mustache	Sintaxe do marcador de parâmetro nomeado
Carregar somente dados antes de uma data especificada	WHERE date_field < '{{date_param}}' Você deve incluir aspas ao redor do parâmetro de data e colchetes.	WHERE date_field < :date_param
Carregar somente dados menores que um valor numérico especificado	WHERE price < {{max_price}}	WHERE price < :max_price
Compare dois strings	WHERE region = {{region_param}}	WHERE region = :region_param
Especifique a tabela usada em uma consulta	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) Quando um usuário insere esse parâmetro, ele deve usar o namespace completo de três níveis para identificar a tabela.
Especifique de forma independente o catálogo, o esquema e a tabela usados em uma consulta	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog || '.' || :schema || '.' || :table)
Use os parâmetros como um padrão em uma cadeia de caracteres mais longa e formatada	“({{código de área}}) {{número_do_telefone}}” Os valores dos parâmetros são automaticamente concatenados como uma cadeia de caracteres.	formato_string (“(%d) %d, , ) Consulte Concatenar vários parâmetros para ver um exemplo completo.
Crie um intervalo	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Sintaxe do parâmetro Mustache

important

As seções a seguir se aplicam à sintaxe de consulta que pode ser usada somente no editor SQL. Isso significa que se o senhor copiar e colar uma consulta usando essa sintaxe em qualquer outra interface Databricks, como um Notebook ou um editor AI/BI dashboard dataset , a consulta deverá ser ajustada manualmente para usar marcadores de parâmetros nomeados antes de ser executada sem erros.

No editor SQL, qualquer cadeia de caracteres entre chaves duplas {{ }} é tratada como um parâmetro de consulta. Um widget aparece acima do painel de resultados onde o senhor define o valor do parâmetro. Embora a Databricks geralmente recomende o uso de marcadores de parâmetros nomeados, algumas funcionalidades são compatíveis apenas com a sintaxe de parâmetros do mustache.

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

• Filtros de painel antigos
• Valor múltiplo dropdown parameters
• Parâmetros suspensos baseados em consultas

Adicionar um parâmetro de bigode

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 sobre o widget. Em default, o título é o mesmo que a palavra-chave.
   • Tipo : Os tipos compatíveis são Texto, Número, Data, Data e Hora, Data e Hora (com segundos), Lista suspensa e Lista suspensa baseada em consulta. O endereço default é Text.

2. Insira a palavra-chave, substitua opcionalmente o título e selecione o tipo de parâmetro.

3. Clique em Adicionar parâmetro .

4. No widget de parâmetros, defina o valor do parâmetro.

5. Clique em Aplicar alterações .

6. Clique em Salvar .

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

Para reexecutar a consulta com um valor de parâmetro diferente, insira o valor no widget e clique em Apply Changes (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 evitar que os usuários que não são proprietários da consulta alterem o parâmetro, clique em Mostrar somente resultados . A caixa de diálogo do parâmetro <Keyword> é exibida.

Remover um parâmetro de consulta

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

Alterar a ordem dos parâmetros

Para alterar a ordem na qual os parâmetros são exibidos, você pode clicar e arrastar cada parâmetro até a posição desejada.

Tipos de parâmetros de consulta

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

Texto

Recebe uma cadeia de caracteres como entrada. As barras invertidas, as aspas simples e duplas são escapadas, e o 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 uso poderia ser

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

Número

Usa um número como entrada. Um exemplo de uso disso pode ser

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

Lista suspensa

Para restringir o escopo dos possíveis valores de parâmetros ao executar uma consulta, use o menu suspenso Listar tipo de parâmetro. Um exemplo seria SELECT * FROM users WHERE name='{{ dropdown_param }}'. Quando selecionado no painel de configurações de parâmetros, aparece uma caixa de texto onde você insere os valores permitidos, cada valor separado por uma nova linha. As 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. O site strings não tem escapatória. O senhor pode escolher entre um dropdown de valor único ou de vários valores.

• Valor único : aspas simples ao redor do parâmetro são obrigatórias.
• Vários valores : ative a opção Permitir vários valores. No menu suspenso Cotação , escolha se deseja deixar os parâmetros como inseridos (sem aspas) ou envolvê-los com aspas simples ou duplas. Você não precisa 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 de parâmetros permite que você passe vários valores para o banco de dados. Se você selecionar a opção Aspas duplas para o parâmetro Cotação , sua consulta refletirá o seguinte formato: WHERE IN ("value1", "value2", "value3")

Lista suspensa baseada em consulta

Usa o resultado de uma consulta como entrada. Ele tem o mesmo comportamento que o parâmetro de lista suspensa . O senhor deve salvar a consulta da lista Databricks SQL dropdown para usá-la como entrada em outra consulta.

1. Clique na lista suspensa Query Based (Baseado em consulta ) em Type (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 sua consulta de destino retornar mais de uma coluna, o Databricks SQL usará a primeira . Se sua consulta de destino retornar as colunas name e value, o Databricks SQL preencherá o widget de seleção de parâmetros com a coluna name, mas executará a consulta com a coluna value associada.

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

SQL

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

valor	name
1001	John Smith
1002	Joana Doe
1003	Mesas Bobby

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

Data e hora

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

Opção	Precisão	Tipo
Data	Dia	DATE
Data e hora	Minuto	TIMESTAMP
Data e hora (com segundos)	Segundo	TIMESTAMP

Ao escolher uma opção de parâmetro Range , você cria dois parâmetros designados pelos sufixos .start e .end. Todas as opções passam parâmetros para a consulta como strings literais; o site Databricks exige que os valores de data e hora sejam colocados entre aspas simples ('). Por exemplo:

SQL

-- 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 default a data e a hora atuais.

nota

O parâmetro Date Range retorna somente resultados corretos para colunas do tipo DATE. Para as colunas TIMESTAMP, use uma das opções de intervalo de data e hora.

Valores dinâmicos de data e intervalo de datas

Quando você adiciona um parâmetro de data ou intervalo de datas à sua consulta, o widget de seleção mostra um ícone de raio azul. 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.

important

As datas dinâmicas e os intervalos de datas não são compatíveis com consultas agendadas.

Usando parâmetros de consulta em painéis

Opcionalmente, as consultas podem usar 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:

• Parâmetro do widget

  Os parâmetros do widget são específicos de uma única visualização em um painel, aparecem no painel de visualização e os valores dos parâmetros especificados se aplicam somente à consulta subjacente à visualização.

• Parâmetro do painel

  Os parâmetros do painel podem ser aplicados a várias visualizações. Quando o senhor adiciona uma visualização baseada em uma consulta parametrizada a um painel, o parâmetro será adicionado como um parâmetro de painel pelo site default. Os parâmetros do painel são configurados para uma ou mais visualizações em um painel e aparecem na parte superior do painel. Os valores dos parâmetros especificados para um parâmetro do painel se aplicam às visualizações que reutilizam esse parâmetro específico do painel. Um painel pode ter vários parâmetros, cada um dos quais pode ser aplicado a algumas visualizações e não a outras.

• Valor estático

  Valores estáticos são usados no lugar de um parâmetro que responde às mudanças. Os valores estáticos permitem que você codifique um valor no lugar de um parâmetro. Eles fazem com que o parâmetro “desapareça” do painel ou widget onde ele aparecia 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. O senhor também pode selecionar a palavra-chave e um valor default. Consulte Propriedades dos parâmetros.

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

Propriedades dos parâmetros

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

• Palavra-chave : a literalidade da cadeia de caracteres para esse parâmetro na consulta subjacente. Isso é útil para depuração se o painel não retornar os resultados esperados.

• Valor padrão : O valor 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 .

• Fonte do valor : A origem do valor do parâmetro. Clique no ícone do lápis para escolher uma fonte.

  • Novo parâmetro do painel : crie um novo parâmetro no nível do painel. Isso permite definir um valor de parâmetro em um só lugar no painel e mapeá-lo para várias visualizações.
  • Parâmetro de painel existente : Mapeie o parâmetro para um parâmetro de painel existente. Você deve especificar qual parâmetro de painel preexistente.
  • Parâmetro do widget : exibe um seletor de valores dentro do widget do painel. Isso é útil para parâmetros únicos que não são compartilhados entre widgets.
  • Valor estático : escolha um valor estático para o widget, independentemente dos valores usados em outros widgets. Os valores dos parâmetros mapeados estaticamente não exibem um seletor de valores em nenhum lugar do painel, o que é mais compacto. Isso permite que você aproveite a flexibilidade dos parâmetros de consulta sem sobrecarregar 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 nos colchetes. 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}}'