Referenční informace k jazyku SQL delta Live Tables

Článek
11/07/2024

Tento článek obsahuje podrobnosti o programovacím rozhraní DELTA Live Tables SQL.

Informace o rozhraní Python API najdete v referenční dokumentaci jazyka Pythonu delta Live Tables.
Další informace o příkazech SQL naleznete v referenční dokumentaci jazyka SQL.

Uživatelem definované funkce Pythonu (UDF) můžete použít v dotazech SQL, ale tyto uživatelem definované funkce musíte definovat v souborech Pythonu, než je budete volat ve zdrojových souborech SQL. Viz uživatelem definované skalární funkce – Python.

Omezení

Klauzule PIVOT není podporována. Operace pivot ve Sparku vyžaduje dychtivé načítání vstupních dat pro výpočet výstupního schématu. Tato funkce není v dynamických tabulkách Delta podporovaná.

Vytvoření materializovaného zobrazení nebo streamované tabulky Delta Live Tables

Poznámka:

Syntaxe CREATE OR REFRESH LIVE TABLE pro vytvoření materializovaného zobrazení je zastaralá. Místo toho použijte CREATE OR REFRESH MATERIALIZED VIEW.
Pokud chcete použít CLUSTER BY klauzuli k povolení clusteringu liquid, musí být kanál nakonfigurovaný tak, aby používal kanál Preview.

Stejnou základní syntaxi SQL použijete při deklarování streamované tabulky nebo materializovaného zobrazení.

Deklarace materializovaného zobrazení Delta Live Tables pomocí SQL

Následující popis syntaxe pro deklarování materializovaného zobrazení v rozdílových živých tabulkách pomocí 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

Deklarace streamovací tabulky Delta Live Tables pomocí SQL

Streamované tabulky můžete deklarovat pouze pomocí dotazů, které se čtou proti zdroji streamování. Databricks doporučuje použít automatický zavaděč pro příjem streamovaných souborů z cloudového úložiště objektů. Viz syntaxe SQL automatického zavaděče.

Při zadávání jiných tabulek nebo zobrazení v kanálu jako zdroje streamování musíte zahrnout STREAM() funkci kolem názvu datové sady.

Následující popis syntaxe pro deklarování tabulky streamování v rozdílových živých tabulkách pomocí 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

Vytvoření zobrazení Delta Live Tables

Následující popis syntaxe pro deklarování zobrazení pomocí 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

Syntaxe SQL automatického zavaděče

Následující popis syntaxe pro práci s automatickým zavaděčem v 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",
      ...
    )
  )

U automatického zavaděče můžete použít podporované možnosti formátování. map() Pomocí funkce můžete metodě předat možnostiread_files(). Možnosti jsou páry klíč-hodnota, kde klíče a hodnoty jsou řetězce. Podrobnosti o formátech a možnostech podpory najdete v tématu Možnosti formátu souboru.

Příklad: Definování tabulek

Datovou sadu můžete vytvořit čtením z externího zdroje dat nebo datových sad definovaných v kanálu. Pokud chcete číst z interní datové sady, předpřipravené LIVE klíčové slovo na název datové sady. Následující příklad definuje dvě různé datové sady: tabulku s názvem taxi_raw , která přebírá soubor JSON jako vstupní zdroj a tabulku s názvem filtered_data , která přijímá taxi_raw tabulku jako vstup:

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

Příklad: Čtení ze zdroje streamování

Pokud chcete číst data ze zdroje streamování, například automatického zavaděče nebo interní datové sady, definujte STREAMING tabulku:

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)

Další informace o streamovaných datech najdete v tématu Transformace dat pomocí delta živých tabulek.

Řízení způsobu materializace tabulek

Tabulky také nabízejí další kontrolu nad jejich materializací:

Určete, jak se tabulky rozdělují pomocí PARTITIONED BY. K urychlení dotazů můžete použít dělení.
Vlastnosti tabulky můžete nastavit pomocí TBLPROPERTIES. Viz Vlastnosti tabulky Delta Live Tables.
Nastavte umístění úložiště pomocí LOCATION nastavení. Ve výchozím nastavení se data tabulky ukládají v umístění úložiště kanálu, pokud LOCATION nejsou nastavená.
V definici schématu můžete použít vygenerované sloupce . Viz příklad: Zadejte schéma a sloupce oddílů.

Poznámka:

U tabulek, které mají velikost menší než 1 TB, databricks doporučuje řídit organizaci dat Delta Live Tables. Pokud neočekáváte, že se tabulka bude zvětšovat nad rámec terabajtu, databricks doporučuje, abyste nezadávejte sloupce oddílů.

Příklad: Zadání schématu a sloupců oddílů

Volitelně můžete zadat schéma při definování tabulky. Následující příklad určuje schéma cílové tabulky, včetně použití vygenerovaných sloupců Delta Lake a definování sloupců oddílů pro tabulku:

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 ...

Delta Live Tables ve výchozím nastavení odvodí schéma z table definice, pokud nezadáte schéma.

Příklad: Definování omezení tabulky

Poznámka:

Podpora tabulek Delta Live Tables je ve verzi Public Preview. Pokud chcete definovat omezení tabulek, musí být kanál s podporou katalogu Unity a nakonfigurovaný tak, aby používal kanál preview .

Při zadávání schématu můžete definovat primární a cizí klíče. Omezení jsou informativní a nevynucují se. Viz klauzule CONSTRAINT v referenční dokumentaci jazyka SQL.

Následující příklad definuje tabulku s omezením primárního a cizího klíče:

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 ...

Parametrizace hodnot použitých při deklarování tabulek nebo zobrazení pomocí SQL

Slouží SET k zadání hodnoty konfigurace v dotazu, který deklaruje tabulku nebo zobrazení, včetně konfigurací Sparku. Libovolná tabulka nebo zobrazení, které definujete v poznámkovém bloku po SET příkazu, má přístup k definované hodnotě. Všechny konfigurace Sparku zadané pomocí příkazu SET se použijí při provádění dotazu Sparku pro libovolnou tabulku nebo zobrazení za příkazem SET. Ke čtení konfigurační hodnoty v dotazu použijte syntaxi ${}interpolace řetězců . Následující příklad nastaví hodnotu konfigurace Sparku s názvem startDate a použije ji v dotazu:

SET startDate='2020-01-01';

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

Pokud chcete zadat více hodnot konfigurace, použijte pro každou hodnotu samostatný SET příkaz.

Příklad: Definování filtru řádků a masky sloupců

Důležité

Filtry řádků a masky sloupců jsou ve verzi Public Preview.

Pokud chcete vytvořit materializované zobrazení nebo tabulku Streamování s filtrem řádků a maskou sloupců, použijte klauzuli ROW FILTER a klauzuli MASK. Následující příklad ukazuje, jak definovat materializované zobrazení a streamovací tabulku s filtrem řádků i maskou sloupce:

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

Další informace o filtrech řádků a maskách sloupců najdete v tématu Publikování tabulek s filtry řádků a maskami sloupců.

Vlastnosti SQL

Poznámka:

Pokud chcete použít CLUSTER BY klauzuli k povolení clusteringu liquid, musí být kanál nakonfigurovaný tak, aby používal kanál Preview.

CREATE TABLE or VIEW
`TEMPORARY` Vytvořte tabulku, ale nepublikujte metadata tabulky. Klauzule `TEMPORARY` dává Delta Live Tables pokyn k vytvoření tabulky, která je dostupná pro kanál, ale neměla by být přístupná mimo kanál. Aby se zkrátila doba zpracování, dočasná tabulka se zachová po celou dobu životnosti kanálu, který ho vytvoří, a ne jenom jednu aktualizaci.
`STREAMING` Vytvořte tabulku, která čte vstupní datovou sadu jako datový proud. Vstupní datová sada musí být streamovaným zdrojem dat, například automatický zavaděč nebo `STREAMING` tabulka.
`CLUSTER BY` Povolte v tabulce clustering liquid clustering a definujte sloupce, které se mají použít jako klíče clusteringu. Viz Použití liquid clusteringu pro tabulky Delta.
`PARTITIONED BY` Volitelný seznam jednoho nebo více sloupců, které se mají použít k dělení tabulky.
`LOCATION` Volitelné umístění úložiště pro data tabulky. Pokud není nastavená, systém ve výchozím nastavení nastaví umístění úložiště kanálu.
`COMMENT` Volitelný popis tabulky.
`column_constraint` Volitelné omezení primárního nebo cizího klíče ve sloupci.
`MASK clause` (Public Preview) Přidá funkci masky sloupce pro anonymizaci citlivých dat. Budoucí dotazy na tento sloupec vrátí výsledek vyhodnocené funkce místo původní hodnoty sloupce. To je užitečné pro jemně odstupňované řízení přístupu, protože funkce může zkontrolovat identitu uživatele a členství ve skupinách a rozhodnout se, jestli se má hodnota redigovat. Viz klauzule Maska sloupce.
`table_constraint` Volitelné omezení primárního nebo cizího klíče v tabulce.
`TBLPROPERTIES` Volitelný seznam vlastností tabulky pro tabulku.
`WITH ROW FILTER clause` (Public Preview) Přidá do tabulky funkci filtru řádků. Budoucí dotazy na danou tabulku obdrží podmnožinu řádků, pro které se funkce vyhodnotí jako PRAVDA. To je užitečné pro jemně odstupňované řízení přístupu, protože umožňuje funkci kontrolovat členství v identitě a skupinách vyvolání uživatele, aby se rozhodl, jestli se mají určité řádky filtrovat. Viz klauzule ROW FILTER.
`select_statement` Dotaz Delta Live Tables, který definuje datovou sadu pro tabulku.

Klauzule CONSTRAINT
`EXPECT expectation_name` Definujte omezení `expectation_name`kvality dat . `ON VIOLATION` Pokud omezení není definováno, přidejte řádky, které porušují omezení cílové datové sady.
`ON VIOLATION` Nepovinná akce, která se má provést pro neúspěšné řádky: - `FAIL UPDATE`: Okamžitě zastavte spuštění kanálu. - `DROP ROW`: Zahoďte záznam a pokračujte ve zpracování.

Změna zachytávání dat pomocí SQL v rozdílových živých tabulkách

APPLY CHANGES INTO Příkaz použijte k používání funkcí CDC delta live tables, jak je popsáno v následujících příkladech:

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)}]

Omezení kvality dat pro APPLY CHANGES cíl definujete pomocí stejné CONSTRAINT klauzule jako dotazyAPPLY CHANGES . Viz Správa kvality dat pomocí rozdílových živých tabulek.

Poznámka:

Výchozí chování INSERT a UPDATE události slouží k přenesení událostí CDC ze zdroje: aktualizujte všechny řádky v cílové tabulce, které odpovídají zadaným klíčům, nebo vložte nový řádek, pokud v cílové tabulce neexistuje odpovídající záznam. Zpracování událostí DELETE lze zadat pomocí APPLY AS DELETE WHEN podmínky.

Důležité

Pokud chcete použít změny, musíte deklarovat cílovou streamovací tabulku. Volitelně můžete zadat schéma cílové tabulky. Při zadávání schématu APPLY CHANGES cílové tabulky musíte zahrnout __START_AT __END_AT i sloupce se stejným datovým typem jako sequence_by pole.

Viz rozhraní API APPLY CHANGES: Zjednodušení zachytávání dat změn pomocí rozdílových živých tabulek.

Klauzule
`KEYS` Sloupec nebo kombinace sloupců, které jednoznačně identifikují řádek ve zdrojových datech. Slouží k identifikaci událostí CDC, které se vztahují na konkrétní záznamy v cílové tabulce. Pokud chcete definovat kombinaci sloupců, použijte čárkami oddělený seznam sloupců. Tato klauzule je povinná.
`IGNORE NULL UPDATES` Povolit ingestování aktualizací obsahujících podmnožinu cílových sloupců Pokud se událost CDC shoduje s existujícím řádkem a je zadána možnost IGNOROVAT AKTUALIZACE s hodnotou NULL, sloupce se `null` zachovají své stávající hodnoty v cíli. To platí také pro vnořené sloupce s hodnotou `null`. Tato klauzule je nepovinná. Výchozí hodnotou je přepsání existujících sloupců `null` hodnotami.
`APPLY AS DELETE WHEN` Určuje, kdy se má událost CDC považovat za událost `DELETE` , nikoli jako upsert. Aby bylo možné zpracovat data mimo pořadí, odstraněný řádek se dočasně zachová jako náhrobek v podkladové tabulce Delta a v metastoru se vytvoří zobrazení, které vyfiltruje tyto náhrobky. Interval uchovávání informací je možné nakonfigurovat pomocí `pipelines.cdc.tombstoneGCThresholdInSeconds`vlastnost table. Tato klauzule je nepovinná.
`APPLY AS TRUNCATE WHEN` Určuje, kdy má být událost CDC považována za úplnou tabulku `TRUNCATE`. Vzhledem k tomu, že tato klauzule aktivuje úplné zkrácení cílové tabulky, měla by být použita pouze pro konkrétní případy použití vyžadující tuto funkci. Klauzule `APPLY AS TRUNCATE WHEN` je podporována pouze pro SCD typu 1. ScD typu 2 nepodporuje operaci zkrácení. Tato klauzule je nepovinná.
`SEQUENCE BY` Název sloupce určující logické pořadí událostí CDC ve zdrojových datech. Delta Live Tables používá toto sekvencování ke zpracování událostí změn, které přicházejí mimo pořadí. Zadaný sloupec musí být seřazený datový typ. Tato klauzule je povinná.
`COLUMNS` Určuje podmnožinu sloupců, které se mají zahrnout do cílové tabulky. Máte tyto možnosti: - Zadejte úplný seznam sloupců, které se mají zahrnout: `COLUMNS (userId, name, city)`. – Zadejte seznam sloupců, které chcete vyloučit: `COLUMNS * EXCEPT (operation, sequenceNum)` Tato klauzule je nepovinná. Výchozí hodnota je zahrnout všechny sloupce v cílové tabulce, pokud `COLUMNS` není klauzule zadána.
`STORED AS` Určuje, zda se mají ukládat záznamy jako SCD typu 1 nebo SCD typu 2. Tato klauzule je nepovinná. Výchozí hodnota je SCD typu 1.
`TRACK HISTORY ON` Určuje podmnožinu výstupních sloupců pro generování záznamů historie, pokud dojde k nějakým změnám těchto zadaných sloupců. Máte tyto možnosti: - Zadejte úplný seznam sloupců, které chcete sledovat: `COLUMNS (userId, name, city)`. – Zadejte seznam sloupců, které mají být vyloučeny ze sledování: `COLUMNS * EXCEPT (operation, sequenceNum)` Tato klauzule je nepovinná. Výchozí hodnota je sledovat historii všech výstupních sloupců, pokud dojde ke změnám, které jsou ekvivalentní `TRACK HISTORY ON *`.

Sdílet prostřednictvím

Referenční informace k jazyku SQL delta Live Tables

Omezení

Vytvoření materializovaného zobrazení nebo streamované tabulky Delta Live Tables

Deklarace materializovaného zobrazení Delta Live Tables pomocí SQL

Deklarace streamovací tabulky Delta Live Tables pomocí SQL

Vytvoření zobrazení Delta Live Tables

Syntaxe SQL automatického zavaděče

Příklad: Definování tabulek

Příklad: Čtení ze zdroje streamování

Řízení způsobu materializace tabulek

Příklad: Zadání schématu a sloupců oddílů

Příklad: Definování omezení tabulky

Parametrizace hodnot použitých při deklarování tabulek nebo zobrazení pomocí SQL

Příklad: Definování filtru řádků a masky sloupců

Vlastnosti SQL

Změna zachytávání dat pomocí SQL v rozdílových živých tabulkách

Váš názor

Další materiály