Trabalhar com a consulta parameters

Este artigo explica como trabalhar com a consulta parameters no editor SQL do Azure Databricks.

A consulta parameters permite tornar as suas consultas mais dinâmicas e flexíveis, inserindo a variável values em tempo de execução. Em vez de codificar values específicos nas suas consultas, pode definir parameters para filtrar dados ou modificar a saída com base na entrada do utilizador. Essa abordagem melhora a reutilização de consultas, aumenta a segurança impedindo 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 esta sintaxe para escrever consultas nas seguintes partes da interface do usuário do Azure Databricks:

• Editor SQL
• Notebooks
• Editor de conjunto de dados do painel de IA/BI
• Espaços AI/BI Genie (Pré-visualização Pública)

Insert parameters em suas consultas SQL digitando dois pontos seguidos por 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

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 codificado (5).

1. Exclua o número 5 da consulta.
2. Digite dois pontos (:) seguidos pela cadeia de caracteres fare_parameter. A última linha da sua consulta atualizada deve dizer fare_amount < :fare_parameter.
3. Clique no ícone de engrenagem perto do 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. Não é possível editar este campo. Para alterar a palavra-chave, edite o marcador na consulta SQL.
   • Título: O título que aparece sobre o widget. Por padrão, o título é o mesmo que a palavra-chave.
   • Tipo: Os tipos suportados são Texto, Número, ListLista Suspensa, Data, Data e Hora e Data e Hora (com Segundos). O padrão é 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 Guardar para guardar a consulta.

Exemplos de sintaxe de parâmetros nomeados

Os exemplos a seguir demonstram alguns casos de uso comuns para parameters.

Insert uma data

O exemplo a seguir inclui um parâmetro Date 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

Insert um número

O exemplo a seguir inclui um parâmetro Number que limita os resultados a registros where o campo o_total_price for maior do que o valor do 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

Insert um nome de campo

No exemplo a seguir, o field_param é usado com a IDENTIFIER função para fornecer um valor limite para a consulta em tempo de execução. O valor do parâmetro deve ser um nome de column do table usado na consulta.

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

Insert objetos de banco de dados

O exemplo a seguir cria três parameters: catalog, schemae table.

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

Ver IDENTIFIER cláusula.

Concatenar vários parameters

Você pode incluir parameters em outras funções SQL. Este exemplo permite que o visualizador select um título de funcionário e um número ID. A consulta usa a format_string função para concatenar as duas cadeias de caracteres e filtrar as linhas correspondentes. Veja 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 parameters para extrair um atributo de uma cadeia de caracteres JSON. O exemplo a seguir usa a from_json função para converter a cadeia de caracteres JSON em um valor struct. Substituir a cadeia de caracteres a como o valor para o parâmetro (param) retorna o atributo 1.

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

Criar um intervalo

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

Consulte Tipo de intervalo para obter detalhes completos e sintaxe.

SELECT CAST(:param AS INTERVAL MINUTE)

Adicionar um intervalo de datas

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

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

Parametrizar pacotes cumulativos 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 DATE_TRUNC função trunca o tpep_pickup_datetime valor com base no valor do :date_granularity parâmetro, como DAY, MONTHou YEAR. A data truncada é aliased como date_rollup e usada na 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

Usar vários values numa única consulta

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

O valor :list_parameter assume um list de valuesseparados por vírgulas. A função SPLIT analisa essa list, dividindo a values separada por vírgulas em uma matriz. A TRANSFORM função transforma cada elemento na matriz removendo qualquer espaço em branco. A função ARRAY_CONTAINS verifica se o valor dropoff_zip do tripstable está contido na matriz de values passada como o list_parameter.

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

Alterações de sintaxe

O table a seguir mostra casos de uso comuns para parameters, a sintaxe original do mustache SQL do Databricks, e a sintaxe equivalente usando o marcador de parâmetro nomeado.

Sintaxe do parâmetro Mustache

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 where você set o valor do parâmetro. Embora o Azure Databricks geralmente recomende o uso de marcadores de parâmetros nomeados, algumas funcionalidades só são suportadas usando a sintaxe do parâmetro bigode.

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

• Filtros de painel herdados
• Lista suspensa de vários valores parameters
• Menu suspenso baseado em consulta parameters

Adicionar um parâmetro de bigode

1. Escreva Cmd + I. O parâmetro é inserido no cursor do 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. Por padrão, o título é o mesmo que a palavra-chave.
   • Tipo: Os tipos suportados são Texto, Número, Data, Data e Hora, Data e Hora (com Segundos), Menu Suspenso Liste Menu Suspenso Baseado em Consulta List. O padrão é Text.
2. Insira a palavra-chave, opcionalmente substitua o título e select o tipo de parâmetro.
3. Clique em Adicionar parâmetro.
4. No widget de parâmetro, ajuste o valor do parâmetro para set.
5. Clique em Aplicar alterações.
6. Clique em Guardar.

Como alternativa, digite chaves duplas e clique no ícone de {{ }} engrenagem perto do widget de parâmetros para editar as configurações.

Para executar novamente 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 são proprietários da consulta alterem o parâmetro, clique em Mostrar Somente Resultados. A <Keyword> caixa de diálogo do parâmetro é exibida.

Remove um parâmetro de consulta

Para remove um parâmetro de consulta, exclua o parâmetro da consulta. O widget de parâmetros desaparece e você pode reescrever sua consulta usando valuesestática .

Alterar a ordem dos parameters

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

Tipos de parâmetros de consulta

• Texto
• Número
• Dropdown List
• Query-Based Dropdown List
• Data e Hora

Texto

Usa uma cadeia de caracteres como entrada. Barras invertidas, aspas simples e duplas são escapadas, 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 uso disso pode ser

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

Número

Usa um número como entrada. Um exemplo de utilização desta medida poderia ser:

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

Lista suspensa List

Para restringir o âmbito do parâmetro values possível ao executar uma consulta, use o tipo de parâmetro Dropdown List. Um exemplo seria SELECT * FROM users WHERE name='{{ dropdown_param }}'. Quando selecionada no painel de configurações de parâmetros, uma caixa de texto aparece where você insere o valuespermitido, cada valor separado por uma nova linha. As listas suspensas são texto de código parameters. Para usar datas ou datas e horas na sua caixa suspensa List, insira-as no formato exigido pela fonte de dados. As cadeias de caracteres não são escapadas. Você pode escolher entre uma lista suspensa de valor único ou de vários valores.

• Valor único: aspas simples ao redor do parâmetro são necessárias.
• multivalor: altere a opção Permitir múltiplos values. Na lista suspensa Citação, escolha se deseja deixar o parameters como inserido (sem aspas) ou envolver o parameters com aspas simples ou duplas. Você não precisa adicionar aspas ao redor do parâmetro se escolher aspas.

Altere sua WHERE cláusula para usar a IN palavra-chave 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 values para o banco de dados. Se você select a opção de aspas duplas para o parâmetro de cotação, sua consulta refletirá o seguinte formato: WHERE IN ("value1", "value2", "value3")

Query-Based Menu Suspenso List

Toma o resultado de uma consulta como sua entrada. Ele tem o mesmo comportamento que o Dropdown List parâmetro. Você deve salvar a consulta Databricks SQL da lista pendente list para usá-la como entrada em outra consulta.

1. Clique em Dropdown Baseado em Consulta list em Tipo no painel de configurações.
2. Clique no campo Consulta e 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 um column, o Databricks SQL usará o primeiro um. Se sua consulta de destino retornar name e valuecolumns, o Databricks SQL preencherá o widget de seleção de parâmetros com o namecolumn mas executará a consulta com o valueassociado.

Por exemplo, suponha que a consulta a seguir retorna os dados no table.

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 seria 1001, 1002 ou 1003.

Data e Hora

O Azure Databricks tem várias opções para parametrizar datas e carimbos de data e hora, incluindo values, com opções para simplificar a parametrização de intervalos de tempo. Select a partir de três opções de precisão variável:

Ao escolher uma opção de parâmetro de gama , são criados dois parameters designados pelos sufixos .start e .end. Todas as opções passam parameters para a sua consulta como literais de cadeia de caracteres; O Azure Databricks requer que você envolva values 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 }}'

Data parameters utilize uma interface de seleção de calendário e defina como padrão a data e hora atuais.

Data dinâmica e intervalo de datas values

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 values dinâmicos como today, yesterday, this week, last week, last monthou last year. Dinamize esses valuesupdate.

Usar a consulta parameters nos painéis

Opcionalmente, as consultas podem usar parameters ou estática values. Quando uma visualização baseada em uma consulta parametrizada é adicionada a um painel, a visualização pode ser configurada para usar um:

• Parâmetro do widget

  Os widgets parameters são específicos de uma única visualização num dashboard, aparecem no painel de visualização e o parâmetro values especificado aplica-se apenas à consulta subjacente à visualização.

• Parâmetro do painel

  O painel parameters pode aplicar-se a várias visualizações. Quando você adiciona uma visualização com base em uma consulta parametrizada a um painel, o parâmetro será adicionado como um parâmetro de painel por padrão. Os painéis de controlo parameters são configurados para uma ou mais visualizações e aparecem no topo do painel de controlo. O parâmetro values especificado para um parâmetro dashboard se aplica a visualizações que reutilizam esse parâmetro específico do painel. Um painel pode ter vários parameters, cada um dos quais pode ser aplicado a algumas visualizações e não a outras.

• Valor estático

  Os values estáticos são usados no lugar de um parâmetro que responde a alterações. Os values estáticos permitem codificar um valor no lugar de um parâmetro. Eles fazem com que o parâmetro "desapareça" do painel ou do widget where onde ele anteriormente apareceu.

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

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 de painel e, em seguida, clicando em Alterar configurações do widget.

Propriedades do parâmetro

• Título: O nome para exibição que aparece ao lado do seletor de valores no painel. O padrão é o parâmetro Keyword. Para editá-lo, clique no ícone de lápis . Os títulos não são exibidos para o dashboard estático parameters porque o seletor de valores está oculto. Se definir selectValor Estático como a sua Fonte de Valor, o campo Título ficará acinzentado.

• Palavra-chave: A cadeia de caracteres literal para este 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 de parâmetro desejado e clique no botão Salvar .

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

  • Novo parâmetro do painel: crie um novo parâmetro no nível do painel. Isso permite set um valor de parâmetro num único local no seu painel de controlo e mapeá-lo para várias visualizações.
  • Parâmetro do painel existente: mapeie o parâmetro para um parâmetro do painel existente. Você deve especificar qual parâmetro de painel pré-existente.
  • Parâmetro do widget: exibe um seletor de valor dentro do widget do painel. Isso é útil para parameters únicos que não são compartilhados entre widgets.
  • Valor estático: Escolha um valor estático para o widget, independentemente do values usado em outros widgets. Parâmetro values mapeado estaticamente não exibe um seletor de valores em nenhum lugar do painel, tornando-o mais compacto. Isso permite-lhe tirar partido da flexibilidade da consulta parameters sem criar desordem na interface do utilizador num painel quando se espera que determinados parameters não mudem com frequência.

  

Perguntas Mais Frequentes (FAQ)

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

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

Sim. Use o mesmo identifier nos colchetes. Este exemplo usa o {{org_id}} parâmetro duas vezes.

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

Posso usar vários parameters em uma única consulta?

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

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