Pular para o conteúdo principal

Referência da linguagem SQL DLT

Este artigo contém detalhes sobre a interface de programação do DLT SQL.

É possível usar as funções definidas pelo usuário (UDFs) do Python em suas consultas SQL, mas é preciso definir essas UDFs nos arquivos Python antes de chamá-las nos arquivos de origem SQL. Consulte Funções escalares definidas pelo usuário - Python.

Limitações​

A cláusula PIVOT não é suportada. As pivot operações em Spark exigem o carregamento ávido de dados de entrada para compute o esquema de saída. Esse recurso não é suportado no DLT.

Criar uma tabela materializada DLT view ou de transmissão​

nota

A sintaxe CREATE OR REFRESH LIVE TABLE para criar um view materializado está obsoleta. Em vez disso, use CREATE OR REFRESH MATERIALIZED VIEW.

O senhor usa a mesma sintaxe básica de SQL ao declarar uma tabela de transmissão ou uma tabela materializada view.

Declarar um DLT materializado view com SQL​

A seguir, descrevemos a sintaxe para declarar um view materializado em DLT com SQL:

CREATE OR REFRESH MATERIALIZED VIEW view_name
  [(
    [
    col_name1 col_type1 [ GENERATED ALWAYS AS generation_expression1 ] [ COMMENT col_comment1 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
    col_name2 col_type2 [ GENERATED ALWAYS AS generation_expression2 ] [ COMMENT col_comment2 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
    ...
    ]
    [
    CONSTRAINT expectation_name_1 EXPECT (expectation_expr1) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
    CONSTRAINT expectation_name_2 EXPECT (expectation_expr2) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
    ...
    ]
    [ table_constraint ] [, ...]
  )]
  [USING DELTA]
  [PARTITIONED BY (col_name1, col_name2, ... )]
  [CLUSTER BY clause]
  [LOCATION path]
  [COMMENT table_comment]
  [TBLPROPERTIES (key1 [ = ] val1, key2 [ = ] val2, ... )]
  [ WITH { ROW FILTER func_name ON ( [ column_name | constant_literal [, ...] ] ) [...] } ]
  AS select_statement

Declare uma tabela de transmissão DLT com SQL​

O senhor só pode declarar tabelas de transmissão usando consultas que leiam em uma fonte de transmissão. Databricks recomenda o uso do site Auto Loader para a transmissão e ingestão de arquivos do armazenamento de objetos na nuvem. Consulte Sintaxe SQL do Auto Loader.

Ao especificar outras tabelas ou visualizações em seu site pipeline como fontes de transmissão, o senhor deve incluir a função STREAM() em torno de um nome dataset.

A seguir, descrevemos a sintaxe para declarar uma tabela de transmissão em DLT com SQL:

CREATE OR REFRESH [TEMPORARY] STREAMING TABLE table_name
  [(
    [
    col_name1 col_type1 [ GENERATED ALWAYS AS generation_expression1 ] [ COMMENT col_comment1 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
    col_name2 col_type2 [ GENERATED ALWAYS AS generation_expression2 ] [ COMMENT col_comment2 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
    ...
    ]
    [
    CONSTRAINT expectation_name_1 EXPECT (expectation_expr1) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
    CONSTRAINT expectation_name_2 EXPECT (expectation_expr2) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
    ...
    ]
    [ table_constraint ] [, ...]
  )]
  [USING DELTA]
  [PARTITIONED BY (col_name1, col_name2, ... )]
  [CLUSTER BY clause]
  [LOCATION path]
  [COMMENT table_comment]
  [TBLPROPERTIES (key1 [ = ] val1, key2 [ = ] val2, ... )]
  [ WITH { ROW FILTER func_name ON ( [ column_name | constant_literal [, ...] ] ) [...] } ]
  AS select_statement

Criar uma DLT view​

A seguir, descrevemos a sintaxe para declarar a visualização com SQL:

CREATE TEMPORARY [STREAMING] LIVE VIEW view_name
  [(
    [
    col_name1 [ COMMENT col_comment1 ],
    col_name2 [ COMMENT col_comment2 ],
    ...
    ]
    [
    CONSTRAINT expectation_name_1 EXPECT (expectation_expr1) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
    CONSTRAINT expectation_name_2 EXPECT (expectation_expr2) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
    ...
    ]
  )]
  [COMMENT view_comment]
  AS select_statement

Sintaxe SQL do Auto Loader​

A seguir, descrevemos a sintaxe para trabalhar com o Auto Loader no SQL:

CREATE OR REFRESH STREAMING TABLE table_name
AS SELECT *
  FROM STREAM read_files(
    "<file-path>",
    [<option-key> => <option_value>, ...]
  )

As opções para Auto Loader são par key-value. Para obter detalhes sobre formatos e opções compatíveis, consulte Opções.

Por exemplo:

CREATE OR REFRESH STREAMING TABLE my_table
AS SELECT *
  FROM STREAM read_files(
    "/Volumes/my_volume/path/to/files/*",
    format => "json",
    inferColumnTypes => true,
    maxFilesPerTrigger => 100,
    schemaEvolutionMode => "addNewColumns",
    modifiedAfter => "2025-03-11T23:59:59.999+00:00"
  )

Exemplo: Definir tabelas​

O senhor pode criar um dataset lendo de uma fonte de dados externa ou de um conjunto de dados definido em um pipeline. Para ler de um dataset interno, especifique o nome da tabela que usará o pipeline padrão configurado para o catálogo e o esquema. O exemplo a seguir define dois conjuntos de dados diferentes: uma tabela chamada taxi_raw que usa um arquivo JSON como fonte de entrada e uma tabela chamada filtered_data que usa a tabela taxi_raw como entrada:

CREATE OR REFRESH MATERIALIZED VIEW taxi_raw
AS SELECT * FROM read_files("/databricks-datasets/nyctaxi/sample/json/")

CREATE OR REFRESH MATERIALIZED VIEW filtered_data
AS SELECT
  ...
FROM taxi_raw

Exemplo: Ler de uma fonte de transmissão​

Para ler dados de uma fonte de transmissão, por exemplo, Auto Loader ou um dataset interno, defina uma tabela STREAMING:

CREATE OR REFRESH STREAMING TABLE customers_bronze
AS SELECT * FROM STREAM read_files("/databricks-datasets/retail-org/customers/", format => "csv")

CREATE OR REFRESH STREAMING TABLE customers_silver
AS SELECT * FROM STREAM(customers_bronze)

Para obter mais informações sobre transmissão de dados, consulte transformação de dados com pipeline.

Excluir permanentemente registros de uma tabela materializada view ou de transmissão​

Para excluir permanentemente registros de uma tabela materializada view ou de transmissão com vetores de exclusão ativados, como para GDPR compliance, operações adicionais devem ser realizadas nas tabelas subjacentes Delta do objeto. Para garantir a exclusão de registros de um site materializado view, consulte Excluir permanentemente registros de um site materializado view com vetores de exclusão ativados. Para garantir a exclusão de registros de uma tabela de transmissão, consulte Excluir permanentemente registros de uma tabela de transmissão.

Controle como as tabelas são materializadas​

As tabelas também oferecem controle adicional de sua materialização:

nota

Para tabelas com menos de 1 TB de tamanho, a Databricks recomenda deixar a DLT controlar a organização dos dados. A menos que o senhor espere que sua tabela cresça além de um terabyte, a Databricks recomenda que o senhor não especifique colunas de partição.

Exemplo: Especificar um esquema e colunas de clustering​

Opcionalmente, você pode especificar um esquema ao definir uma tabela. O exemplo a seguir especifica o esquema da tabela de destino, incluindo o uso de colunas geradas pelo Delta Lake e define colunas de clustering para a tabela:

CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING,
  customer_name STRING,
  number_of_line_items STRING,
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
) CLUSTER BY (order_day_of_week, customer_id)
COMMENT "Raw data on sales"
AS SELECT * FROM ...

Por default, a DLT infere o esquema a partir da definição table se o usuário não especificar um esquema.

Exemplo: especificar colunas de partição​

Opcionalmente, você pode especificar colunas de partição para a tabela:

CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING,
  customer_name STRING,
  number_of_line_items STRING,
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
) PARTITIONED BY (order_day_of_week)
COMMENT "Raw data on sales"
AS SELECT * FROM ...

O Liquid clustering oferece soluções flexíveis e otimizadas para clustering. Considere usar CLUSTER BY em vez de PARTITIONED BY para DLT.

Exemplo: Definir restrições de tabela​

nota

O suporte DLT para restrições de tabela está na versão prévia pública. Para definir restrições de tabela, seu pipeline deve ser um pipeline habilitado para o Unity Catalog e configurado para usar o canal preview.

Ao especificar um esquema, o senhor pode definir chaves primárias e estrangeiras. As restrições são informativas e não são aplicadas. Consulte a cláusula CONSTRAINT na referência da linguagem SQL.

O exemplo a seguir define uma tabela com uma restrição primária e estrangeira key:

CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING NOT NULL PRIMARY KEY,
  customer_name STRING,
  number_of_line_items STRING,
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime)),
  CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES main.default.customers(customer_id)
)
COMMENT "Raw data on sales"
AS SELECT * FROM ...

Parametrize os valores usados ao declarar tabelas ou exibições com SQL​

Use SET para especificar um valor de configuração em uma consulta que declare uma tabela ou view, incluindo as configurações de Spark. Qualquer tabela ou view que o senhor definir em um Notebook após a instrução SET terá acesso ao valor definido. Todas as configurações de Spark especificadas com a instrução SET são usadas ao executar a consulta Spark para qualquer tabela ou view após a instrução SET. Para ler um valor de configuração em uma consulta, use a sintaxe de interpolação de cadeias ${}. O exemplo a seguir define um valor de configuração do Spark chamado startDate e usa esse valor em uma consulta:

SET startDate='2020-01-01';

CREATE OR REFRESH MATERIALIZED VIEW filtered
AS SELECT * FROM src
WHERE date > ${startDate}

Para especificar vários valores de configuração, use uma instrução SET separada para cada valor.

Exemplo: definir um filtro de linha e uma máscara de coluna​

info

Visualização

Os filtros de linha e as máscaras de coluna estão na Pré-visualização pública.

Para criar uma tabela materializada view ou de transmissão com um filtro de linha e uma máscara de coluna, use a cláusula ROW FILTER e a cláusula MASK. O exemplo a seguir demonstra como definir uma tabela materializada view e uma tabela de transmissão com um filtro de linha e uma máscara de coluna:

SQL
CREATE OR REFRESH STREAMING TABLE customers_silver (
  id int COMMENT 'This is the customer ID',
  name string,
  region string,
  ssn string MASK catalog.schema.ssn_mask_fn COMMENT 'SSN masked for privacy'
)
WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
AS SELECT * FROM STREAM(customers_bronze)

CREATE OR REFRESH MATERIALIZED VIEW sales (
  customer_id STRING MASK catalog.schema.customer_id_mask_fn,
  customer_name STRING,
  number_of_line_items STRING COMMENT 'Number of items in the order',
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
)
COMMENT "Raw data on sales"
WITH ROW FILTER catalog.schema.order_number_filter_fn ON (order_number)
AS SELECT * FROM sales_bronze

Para obter mais informações sobre filtros de linha e máscaras de coluna, consulte Publicar tabelas com filtros de linha e máscaras de coluna.

Propriedades do SQL​

CREATE TABLE ou VIEW

TEMPORARY Crie uma tabela, mas não publique metadados para a tabela. A cláusula TEMPORARY instrui a DLT a criar uma tabela que está disponível para o pipeline, mas que não deve ser acessada fora do pipeline. Para reduzir o tempo de processamento, uma tabela temporária persiste durante o tempo de vida do pipeline que a cria, e não apenas em uma única atualização.

STREAMING Crie uma tabela que leia uma entrada dataset como uma transmissão. A entrada dataset deve ser uma fonte de transmissão de dados, por exemplo, Auto Loader ou uma tabela STREAMING.

CLUSTER BY Habilite o clustering líquido na tabela e defina as colunas a serem usadas como chave clustering. Consulte Usar clustering líquido para tabelas Delta.

PARTITIONED BY Uma lista opcional de uma ou mais colunas para usar no particionamento da tabela.

LOCATION Um local de armazenamento opcional para dados da tabela. Se não estiver definido, o sistema adotará como padrão o local de armazenamento do pipeline.

COMMENT Uma descrição opcional para a tabela.

column_constraint Uma restrição opcional informativa primária key ou estrangeira key na coluna.

MASK clause (Pré-visualização pública) Adiciona uma função de máscara de coluna para anonimizar dados confidenciais. Consultas futuras para essa coluna retornam o resultado da função avaliada em vez do valor original da coluna. Isso é útil para um controle de acesso refinado, pois a função pode verificar a identidade do usuário e as associações ao grupo para decidir se deseja editar o valor. Consulte a cláusula da coluna mask.

table_constraint Uma restrição opcional informativa primária key ou estrangeira key na tabela.

TBLPROPERTIES Uma lista opcional de propriedades da tabela.

WITH ROW FILTER clause (Pré-visualização pública) Adiciona uma função de filtro de linha à tabela. As consultas futuras dessa tabela recebem um subconjunto das linhas para as quais a função é avaliada como TRUE. Isso é útil para um controle de acesso refinado, pois permite que a função inspecione a identidade e as associações de grupo do usuário que está invocando para decidir se deseja filtrar determinadas linhas. Veja a cláusulaROW FILTER.

select_statement Uma consulta DLT que define o site dataset para a tabela.

Cláusula CONSTRAINT

EXPECT expectation_name Defina a restrição de qualidade de dados expectation_name. Se a restrição ON VIOLATION não estiver definida, adicione as linhas que violam a restrição ao destino dataset.

ON VIOLATION Ação opcional a ser tomada para linhas com falha: - FAIL UPDATE: Interrompe imediatamente a execução do pipeline. - DROP ROW: Solte o registro e continue processando.

captura de dados de alterações (CDC) com SQL em DLT​

Use a instrução APPLY CHANGES INTO para usar a funcionalidade DLT CDC, conforme descrito a seguir:

CREATE OR REFRESH STREAMING TABLE table_name;

APPLY CHANGES INTO table_name
FROM source
KEYS (keys)
[IGNORE NULL UPDATES]
[APPLY AS DELETE WHEN condition]
[APPLY AS TRUNCATE WHEN condition]
SEQUENCE BY orderByColumn
[COLUMNS {columnList | * EXCEPT (exceptColumnList)}]
[STORED AS {SCD TYPE 1 | SCD TYPE 2}]
[TRACK HISTORY ON {columnList | * EXCEPT (exceptColumnList)}]

Você define restrições de qualidade de dados para um alvo APPLY CHANGES usando a mesma cláusula CONSTRAINT das consultas nãoAPPLY CHANGES. Veja como gerenciar a qualidade dos dados com pipeline expectativas.

nota

O comportamento padrão para eventos de INSERT e UPDATE é realizar upsert de eventos do CDC a partir da fonte: atualizar quaisquer linhas na tabela de destino que correspondam à(s) chave(s) especificada(s) ou inserir uma nova linha quando um registro correspondente não existir na tabela de destino.O tratamento de DELETE eventos pode ser especificado com a APPLY AS DELETE WHEN condição.

important

Você deve declarar uma tabela de transmissão de destino para aplicar as alterações. Opcionalmente, você pode especificar o esquema para sua tabela de destino. Ao especificar o esquema da tabela de destino do APPLY CHANGES, você também deve incluir as colunas __START_AT e __END_AT com o mesmo tipo de dados que o campo sequence_by .

Consulte o site APPLY CHANGES APIs: Simplificar a captura de dados de alterações (CDC) com DLT.

Cláusulas

KEYS A coluna ou combinação de colunas que identificam exclusivamente uma linha nos dados de origem. Isso é usado para identificar quais eventos de CDC se aplicam a registros específicos na tabela de destino. Para definir uma combinação de colunas, use uma lista de colunas separadas por vírgula. Essa cláusula é obrigatória.

IGNORE NULL UPDATES Permitir a ingestão de atualizações que contenham um subconjunto da coluna de destino. Quando um evento CDC corresponde a uma linha existente e IGNORE NULL UPDATES é especificado, as colunas com null manterão seus valores existentes no destino. Isso também se aplica a colunas aninhadas com um valor de null. Esta cláusula é opcional. O site default substitui as colunas existentes por valores null.

APPLY AS DELETE WHEN Especifica quando um evento CDC deve ser tratado como DELETE em vez de upsert. Para lidar com dados fora de ordem, a linha excluída é temporariamente retida como uma lápide na tabela subjacente Delta e é criado um view no metastore que filtra essas lápides. O intervalo de retenção pode ser configurado com o pipelines.cdc.tombstoneGCThresholdInSeconds propriedade da tabela. Esta cláusula é opcional.

APPLY AS TRUNCATE WHEN Especifica quando um evento de CDC deve ser tratado como uma tabela completa TRUNCATE. Como esta cláusula aciona uma operação completa de truncamento da tabela de destino, ela deve ser usada apenas para casos de uso específicos que exijam essa funcionalidade. A cláusula APPLY AS TRUNCATE WHEN é compatível apenas com SCD type 1. SCD type 2 não é compatível com as operações truncate. Esta cláusula é opcional.

SEQUENCE BY O nome da coluna que especifica a ordem lógica dos eventos CDC nos dados de origem. O DLT usa esse sequenciamento para lidar com eventos de alteração que chegam fora de ordem. A coluna especificada deve ser um tipo de dados classificável. Essa cláusula é obrigatória.

COLUMNS Especifica um subconjunto de colunas a serem incluídas na tabela de destino. Você também pode: - Especifique a lista completa de colunas a serem incluídas: COLUMNS (userId, name, city). - Especifique uma lista de colunas a serem excluídas: COLUMNS * EXCEPT (operation, sequenceNum) Esta cláusula é opcional. O site default inclui todas as colunas da tabela de destino quando a cláusula COLUMNS não é especificada.

STORED AS Se os registros devem ser armazenados como SCD tipo 1 ou SCD tipo 2. Esta cláusula é opcional. O padrão é SCD tipo 1.

TRACK HISTORY ON Especifica um subconjunto de colunas de saída para gerar registros de histórico quando houver alguma alteração nas colunas especificadas. Você também pode: - Especifique a lista completa de colunas a serem rastreadas: COLUMNS (userId, name, city). - Especifique uma lista de colunas a serem excluídas do acompanhamento: COLUMNS * EXCEPT (operation, sequenceNum) Essa cláusula é opcional. O default é para rastrear a história de todas as colunas de saída quando houver alguma alteração, equivalente a TRACK HISTORY ON *.

Última atualização em