Referência de linguagem SQL do Delta Live Tables
Este artigo contém detalhes sobre a interface de programação Delta Live Tables SQL .
Para obter informações sobre a API do Python, consulte a referência da linguagem Delta Live Tables Python.
Para obter mais informações sobre comandos SQL, consulte Referência de linguagem SQL.
Você pode usar as funções definidas pelo usuário (UDFs) do Python em sua query SQL, mas deve definir essas UDFs nos arquivos Python antes de chamá-los 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 requerem o carregamento ávido de dados de entrada para compute o esquema de saída. Esse recurso não é compatível com o site Delta Live Tables.
Crie uma visualização materializada do Delta Live Tables ou uma tabela transmitida
Observação
A sintaxe
CREATE OR REFRESH LIVE TABLE
para criar um view materializado está obsoleta. Em vez disso, useCREATE OR REFRESH MATERIALIZED VIEW
.Para usar a cláusula
CLUSTER BY
para ativar o líquido clustering, seu pipeline deve estar configurado para usar o canal de visualização.
O senhor usa a mesma sintaxe básica de SQL ao declarar uma tabela de transmissão ou uma tabela materializada view.
Declarar uma visualização materializada do Delta Live Tables com SQL
A seguir, descrevemos a sintaxe para declarar um view materializado em Delta Live Tables com SQL:
CREATE OR REFRESH MATERIALIZED VIEW view_name [CLUSTER BY (col_name1, col_name2, ... )]
[(
[
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, ... )]
[LOCATION path]
[COMMENT table_comment]
[TBLPROPERTIES (key1 [ = ] val1, key2 [ = ] val2, ... )]
[ WITH { ROW FILTER func_name ON ( [ column_name | constant_literal [, ...] ] ) [...] } ]
AS select_statement
Declare a tabela de transmissão Delta Live Tables com SQL
Você só pode declarar tabelas transmitidas usando query que lê contra uma fonte transmitida. Databricks recomenda o uso do Auto Loader para ingestão de transmissão de arquivos do armazenamento de objetos cloud . Consulte Sintaxe do Auto Loader SQL.
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 Delta Live Tables com SQL:
CREATE OR REFRESH [TEMPORARY] STREAMING TABLE table_name [CLUSTER BY (col_name1, col_name2, ... )]
[(
[
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, ... )]
[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 exibição Delta Live Tables
O seguinte descreve a sintaxe para declarar view 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 Auto Loader
O seguinte descreve a sintaxe para trabalhar com o Auto Loader em SQL:
CREATE OR REFRESH STREAMING TABLE table_name
AS SELECT *
FROM read_files(
"<file-path>",
"<file-format>",
map(
"<option-key>", "<option_value",
"<option-key>", "<option_value",
...
)
)
O senhor pode usar as opções de formato compatíveis com o Auto Loader. Usando a função map()
, você pode passar opções para o método read_files()
. As opções são par key-value, em que a chave e os valores são strings. Para obter detalhes sobre formatos e opções de suporte, consulte Opções de formato de arquivo.
Exemplo: definir tabelas
Você pode criar um dataset lendo de uma fonte de dados externa ou de dataset definido em um pipeline. Para ler um dataset interno , anexe a palavra-chave LIVE
ao nome dataset . O exemplo a seguir define dois dataset 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 json.`/databricks-datasets/nyctaxi/sample/json/`
CREATE OR REFRESH MATERIALIZED VIEW filtered_data
AS SELECT
...
FROM LIVE.taxi_raw
Exemplo: Ler de uma fonte transmitida
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 read_files("/databricks-datasets/retail-org/customers/", "csv")
CREATE OR REFRESH STREAMING TABLE customers_silver
AS SELECT * FROM STREAM(LIVE.customers_bronze)
Para mais informações sobre dados transmitidos, consulte transformação de dados com Delta Live Tables.
Controle como as tabelas são materializadas
As tabelas também oferecem controle adicional de sua materialização:
Especifique como as tabelas são particionadas usando
PARTITIONED BY
. Você pode usar o particionamento para acelerar query.Você pode definir as propriedades da tabela usando
TBLPROPERTIES
. Consulte as propriedades da tabela Delta Live Tables.Defina um local de armazenamento usando a configuração
LOCATION
. Por default, os dados da tabela são armazenados no local de armazenamento do pipeline seLOCATION
não estiver definido.Você pode usar colunas geradas em sua definição de esquema. Consulte Exemplo: Especifique um esquema e colunas de partição.
Observação
Para tabelas com menos de 1 TB de tamanho, a Databricks recomenda deixar que o Delta Live Tables controle 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 não especifique colunas de partição.
Exemplo: especificar um esquema e colunas de partição
Opcionalmente, você pode especificar um esquema ao definir uma tabela. O exemplo a seguir especifica o esquema para a tabela de destino, incluindo o uso de colunas geradas pelo Delta Lake e a definição de 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 ...
Por default, Delta Live Tables infere o esquema da definição table
se você não especificar um esquema.
Exemplo: Definir restrições de tabela
Observação
O suporte do Delta Live Tables para restrições de tabela está na visualização 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
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:
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(LIVE.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 LIVE.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 SQL
Observação
Para usar a cláusula CLUSTER BY
para ativar o líquido clustering, seu pipeline deve estar configurado para usar o canal de visualização.
CREATE TABLE ou VIEW |
---|
Criar uma tabela, mas não publicar os metadados da tabela. A cláusula |
Crie uma tabela que leia um dataset de entrada como uma transmissão. O dataset de entrada deve ser uma fonte de dados transmitida, por exemplo, Auto Loader ou uma tabela |
Habilite o clustering líquido na tabela e defina as colunas a serem usadas como chave clustering. |
Uma lista opcional de uma ou mais colunas a serem usadas para particionar a tabela. |
Um local de armazenamento opcional para dados da tabela. Se não for definido, o sistema assumirá default o local de armazenamento do pipeline. |
Uma descrição opcional para a tabela. |
Uma restrição opcional informativa primária key ou estrangeira key na coluna. |
Adiciona uma função de máscara de coluna para tornar anônimos os dados confidenciais. As consultas futuras para essa coluna retornam o resultado da função avaliada em vez do valor original da coluna. Isso é útil para o controle de acesso refinado, pois a função pode verificar a identidade do usuário e as associações de grupo para decidir se o valor deve ser redigido. Consulte a cláusula de máscara de coluna. |
Uma restrição opcional informativa primária key ou estrangeira key na tabela. |
Uma lista opcional de propriedades de tabela para a tabela. |
Adiciona uma função de filtro de linha à tabela. As consultas futuras para essa tabela recebem um subconjunto das linhas para as quais a função é avaliada como TRUE. Isso é útil para o controle de acesso refinado, pois permite que a função inspecione a identidade e as associações de grupo do usuário que a invoca para decidir se deve filtrar determinadas linhas. Consulte a cláusula ROW FILTER. |
Uma query Delta Live Tables que define o dataset para a tabela. |
Cláusula CONSTRAINT |
---|
Defina a restrição de qualidade de dados |
Ação opcional a ser executada para linhas com falha:
|
captura de dados de alterações (CDC) com SQL em Delta Live Tables
Use a instrução APPLY CHANGES INTO
para usar a funcionalidade Delta Live Tables CDC, conforme descrito a seguir:
CREATE OR REFRESH STREAMING TABLE table_name;
APPLY CHANGES INTO LIVE.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 destino APPLY CHANGES
usando a mesma cláusula CONSTRAINT
que não éAPPLY CHANGES
query. Veja como gerenciar a qualidade dos dados com Delta Live Tables.
Observação
O comportamento default para os eventos INSERT
e UPDATE
é atualizar os eventos CDC da origem: atualize todas as linhas na tabela de destino que correspondam à(s) key(s) especificada(s) ou insira uma nova linha quando um registro correspondente não existir na tabela de destino. A manipulação de eventos DELETE
pode ser especificada com a condição APPLY AS DELETE WHEN
.
Importante
Você deve declarar uma tabela de transmissão de destino para aplicar as alterações. Você pode opcionalmente especificar o esquema para sua tabela de destino. Ao especificar o esquema da tabela de destino APPLY CHANGES
, você também deve incluir as colunas __START_AT
e __END_AT
com o mesmo tipo de dados do campo sequence_by
.
Consulte a seção APPLY CHANGES APIs: Simplificar a captura de dados de alterações (CDC) com Delta Live Tables.
Cláusulas |
---|
A coluna ou combinação de colunas que identificam exclusivamente uma linha nos dados de origem. Isso é usado para identificar quais eventos 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. Esta cláusula é necessária. |
Permitir a ingestão de atualizações contendo um subconjunto da coluna de destino. Quando um evento CDC corresponde a uma linha existente e IGNORE NULL UPDATES é especificado, as colunas com um Esta cláusula é opcional. O default é sobrescrever colunas existentes com valores |
Especifica quando um evento CDC deve ser tratado como um Esta cláusula é opcional. |
Especifica quando um evento CDC deve ser tratado como uma tabela completa A cláusula Esta cláusula é opcional. |
O nome da coluna que especifica a ordem lógica dos eventos CDC nos dados de origem. Delta Live Tables 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. Esta cláusula é necessária. |
Especifica um subconjunto de colunas a serem incluídas na tabela de destino. Você também pode:
Esta cláusula é opcional. O default é incluir todas as colunas na tabela de destino quando a cláusula |
Se os registros devem ser armazenados como SCD tipo 1 ou SCD tipo 2. Esta cláusula é opcional. O default é SCD tipo 1. |
Especifica um subconjunto de colunas de saída para gerar registros de história quando houver alterações nessas colunas especificadas. Você também pode:
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 |