Naslaginformatie van de DLT SQL-taal

In dit artikel vindt u meer informatie over de DLT SQL-programmeerinterface.

• Zie de DLT Python-taalreferentievoor meer informatie over de Python-API.
• Zie sql-taalreferentievoor meer informatie over SQL-opdrachten.

U kunt door de gebruiker gedefinieerde Python-functies (UDF's) in uw SQL-query's gebruiken, maar u moet deze UDF's definiëren in Python-bestanden voordat u ze aanroept in SQL-bronbestanden. Zie door de gebruiker gedefinieerde scalaire functies - Python.

Beperkingen

De PIVOT-component wordt niet ondersteund. De pivot-bewerking in Spark vereist het gretige laden van invoergegevens om het uitvoerschema te berekenen. Deze mogelijkheid wordt niet ondersteund in DLT.

Een gerealiseerde DLT-weergave of streamingtabel maken

Notitie

De CREATE OR REFRESH LIVE TABLE syntaxis voor het maken van een gerealiseerde weergave is afgeschaft. Gebruik in plaats daarvan CREATE OR REFRESH MATERIALIZED VIEW.

U gebruikt dezelfde eenvoudige SQL-syntaxis bij het declareren van een streamingtabel of een gerealiseerde weergave.

Een gerealiseerde DLT-weergave declareren met SQL

Hieronder wordt de syntaxis beschreven voor het declareren van een gerealiseerde weergave in DLT met 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, ... )]
  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

Een DLT-streamingtabel declareren met SQL

U kunt alleen streamingtabellen definiëren met behulp van query's die gebaseerd zijn op een streamingbron. Databricks raadt het gebruik van automatische laadprogramma's aan voor het streamen van bestanden uit de opslag van cloudobjecten. Zie SQL-syntaxis voor automatisch laden.

Wanneer u andere tabellen of weergaven in uw pijplijn opgeeft als streamingbronnen, moet u de STREAM() functie rond een naam van een gegevensset opnemen.

Hieronder wordt de syntaxis beschreven voor het declareren van een streamingtabel in DLT met 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, ... )]
  [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

een DLT-weergave maken

Hier volgt een beschrijving van de syntaxis voor het declareren van weergaven met 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

SQL-syntaxis voor automatisch laden

Hier volgt een beschrijving van de syntaxis voor het werken met automatisch laden in 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 kunt ondersteunde indelingsopties gebruiken met Auto Loader. Met de functie map() kunt u opties doorgeven aan de methode read_files(). Opties zijn sleutel-waardeparen, waarbij de sleutels en waarden tekenreeksen zijn. Zie Opties voor bestandsindelingenvoor meer informatie over ondersteuningsindelingen en opties.

Voorbeeld: Tabellen definiëren

U kunt een gegevensset maken door gegevens te lezen uit een externe gegevensbron of uit gegevenssets die zijn gedefinieerd in een pijplijn. Als u wilt lezen uit een interne gegevensset, geeft u de tabelnaam op die gebruikmaakt van de geconfigureerde pijplijnstandaarden voor catalogus en schema. In het volgende voorbeeld worden twee verschillende gegevenssets gedefinieerd: een tabel met de naam taxi_raw die een JSON-bestand als invoerbron gebruikt en een tabel met de naam filtered_data die de taxi_raw tabel als invoer gebruikt:

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 taxi_raw

Voorbeeld: Lezen uit een streamingbron

Als u gegevens wilt lezen uit een streamingbron, bijvoorbeeld Automatisch laden of een interne gegevensset, definieert u een STREAMING tabel:

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(customers_bronze)

Zie Gegevens transformeren met pijplijnenvoor meer informatie over het streamen van gegevens.

Records definitief verwijderen uit een gerealiseerde weergave of streamingtabel

Als u records definitief wilt verwijderen uit een gerealiseerde weergave of streamingtabel waarvoor verwijderingsvectoren zijn ingeschakeld, zoals voor AVG-naleving, moeten extra bewerkingen worden uitgevoerd op de onderliggende Delta-tabellen van het object. Om ervoor te zorgen dat records uit een gerealiseerde weergave worden verwijderd, zie Records definitief verwijderen uit een gerealiseerde weergave met verwijderingsvectoren ingeschakeld. Zie Records definitief verwijderen uit een streamingtabelom ervoor te zorgen dat records uit een streamingtabel worden verwijderd.

Bepalen hoe tabellen worden gematerialiseerd

Tabellen bieden ook extra controle over hun materialisatie:

• Specificeer hoe u tabellen clustert met behulp van CLUSTER BY. U kunt liquide clustering gebruiken om query's te versnellen. Zie Liquid Clustering gebruiken voor Delta-tabellen.
• Geef op hoe tabellen worden gepartitioneerd met behulp van PARTITIONED BY.
• U kunt tabeleigenschappen instellen met TBLPROPERTIES. Zie DLT-tabeleigenschappen.
• Stel een opslaglocatie in met behulp van de LOCATION-instelling. Tabelgegevens worden standaard opgeslagen in de opslaglocatie voor pijplijnen als LOCATION niet is ingesteld.
• U kunt gegenereerde kolommen in uw schemadefinitie gebruiken. Zie voorbeeld: Een schema en clusterkolommen opgeven.

Notitie

Voor tabellen die kleiner zijn dan 1 TB, raadt Databricks aan om DLT de organisatie van gegevens te laten beheren. Tenzij u verwacht dat uw tabel groter wordt dan een terabyte, raadt Databricks aan dat u geen partitiekolommen opgeeft.

voorbeeld: een schema en clusterkolommen opgeven

U kunt desgewenst een schema opgeven wanneer u een tabel definieert. Op het volgende voorbeeld wordt het schema voor de doeltabel opgegeven, inclusief het gebruik van Delta Lake gegenereerde kolommen. Het definieert ook clusterkolommen voor de tabel.

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

Standaard wordt het schema afgeleid van de table definitie als u geen schema opgeeft.

voorbeeld: partitiekolommen opgeven

U kunt desgewenst partitiekolommen voor de tabel opgeven:

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

Liquid clustering biedt een flexibele, geoptimaliseerde oplossing voor clustering. Overweeg het gebruik van CLUSTER BY in plaats van PARTITIONED BY voor DLT.

voorbeeld: tabelbeperkingen definiëren

Notitie

DLT-ondersteuning voor tabelbeperkingen bevindt zich in openbare preview. Als u tabelbeperkingen wilt definiëren, moet uw pijplijn een Unity Catalog-pijplijn zijn en zijn geconfigureerd voor het gebruik van het preview-kanaal.

Wanneer u een schema opgeeft, kunt u primaire en buitenlandse sleutels definiëren. De beperkingen zijn informatief en worden niet afgedwongen. Zie de CONSTRAINT clausule in de SQL-taalreferentie.

In het volgende voorbeeld wordt een tabel met een primaire en vreemde sleutelbeperking gedefinieerd.

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

Waarden parameteriseren die worden gebruikt bij het declareren van tabellen of weergaven met SQL

Gebruik SET om een configuratiewaarde op te geven in een query die een tabel of weergave declareert, inclusief Spark-configuraties. Elke tabel of weergave die u in een notitieblok definieert nadat de instructie SET toegang heeft tot de gedefinieerde waarde. Spark-configuraties die zijn opgegeven met behulp van de SET-instructie, worden gebruikt bij het uitvoeren van de Spark-query voor een tabel of weergave na de SET-instructie. Als u een configuratiewaarde in een query wilt lezen, gebruikt u de syntaxis van tekenreeksinterpolatie ${}. In het volgende voorbeeld wordt een Spark-configuratiewaarde met de naam startDate ingesteld en wordt die waarde in een query gebruikt:

SET startDate='2020-01-01';

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

Als u meerdere configuratiewaarden wilt opgeven, gebruikt u een afzonderlijke SET-instructie voor elke waarde.

voorbeeld: Een rijfilter en kolommasker definiëren

Belangrijk

Rijfilters en kolommaskers zijn in Publieke Preview.

Als u een gerealiseerde weergave of streamingtabel wilt maken met een rijfilter en kolommasker, gebruikt u de ROW FILTER component en de MASK-component. In het volgende voorbeeld ziet u hoe u een gerealiseerde weergave en een streamingtabel definieert met zowel een rijfilter als een kolommasker:

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

Zie Tabellen publiceren met rijfilters en kolommaskersvoor meer informatie over rijfilters en kolommaskers.

SQL-eigenschappen

CREATE TABLE of WEERGAVE
TEMPORARY Maak een tabel, maar publiceer geen metagegevens voor de tabel. Met de TEMPORARY-component wordt DLT geïnstrueerd om een tabel te maken die beschikbaar is voor de pijplijn, maar die niet buiten de pijplijn mag worden geopend. Om de verwerkingstijd te verminderen, blijft een tijdelijke tabel behouden gedurende de levensduur van de pijplijn die deze maakt, en niet slechts één update.
STREAMING Maak een tabel die een invoergegevensset leest als een stroom. De invoergegevensset moet een streaminggegevensbron zijn, bijvoorbeeld Automatisch laden of een STREAMING tabel.
CLUSTER BY Schakel vloeistofclustering in de tabel in en definieer de kolommen die moeten worden gebruikt als clustersleutels. Zie Liquid Clustering gebruiken voor Delta-tabellen.
PARTITIONED BY Een optionele lijst met een of meer kolommen die moeten worden gebruikt voor het partitioneren van de tabel.
LOCATION Een optionele opslaglocatie voor tabelgegevens. Als dit niet is ingesteld, wordt het systeem standaard ingesteld op de opslaglocatie van de pijplijn.
COMMENT Een optionele beschrijving voor de tabel.
column_constraint Een informatieve, optionele primaire sleutel of buitenlandse-sleutelbeperking op de kolom.
MASK clause (publieke preview) Voegt een kolommaskerfunctie toe om gevoelige gegevens anoniem te maken. Toekomstige query's voor die kolom retourneren het resultaat van de geëvalueerde functie in plaats van de oorspronkelijke waarde van de kolom. Dit is handig voor fijnmazig toegangsbeheer, omdat de functie de identiteit en groepslidmaatschappen van de gebruiker kan controleren om te bepalen of de waarde moet worden bewerkt. Zie kolomclausulemask.
table_constraint Een optionele informatieve primaire of vreemde-sleutelconstraint op de tabel.
TBLPROPERTIES Een optionele lijst met tabeleigenschappen voor de tabel.
WITH ROW FILTER clause (Openbare Preview) Hiermee voegt u een rijfilterfunctie toe aan de tabel. Toekomstige query's voor die tabel ontvangen een deelverzameling van de rijen waarvoor de functie als WAAR wordt geëvalueerd. Dit is handig voor gedetailleerd toegangsbeheer, omdat hiermee de functie de identiteit en groepslidmaatschappen van de aanroepende gebruiker kan inspecteren om te bepalen of bepaalde rijen moeten worden gefilterd. Zie ROW FILTER clausule.
select_statement Een DLT-query waarmee de gegevensset voor de tabel wordt gedefinieerd.

CONSTRAINT-clausule
EXPECT expectation_name Beperking voor gegevenskwaliteit definiëren expectation_name. Als de ON VIOLATION beperking niet is gedefinieerd, voegt u rijen toe die de beperking aan de doelgegevensset schenden.
ON VIOLATION Optionele actie die moet worden uitgevoerd voor mislukte rijen: FAIL UPDATE: pijplijnuitvoering onmiddellijk stoppen. DROP ROW: verwijder de record en ga verder met verwerken.

Wijzigingen in gegevens vastleggen met SQL in DLT

Gebruik de APPLY CHANGES INTO instructie om DLT CDC-functionaliteit te gebruiken, zoals beschreven in het volgende:

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

U definieert beperkingen voor gegevenskwaliteit voor een APPLY CHANGES doel met behulp van dezelfde CONSTRAINT component als niet-APPLY CHANGES query's. Zie Gegevenskwaliteit beheren met de verwachtingen van pijplijnen.

Notitie

Het standaardgedrag voor INSERT- en UPDATE-gebeurtenissen is om CDC-gebeurtenissen uit de bron te upserten: werk alle rijen in de doeltabel bij die overeenkomen met de opgegeven sleutel(en) of voeg een nieuwe rij in wanneer er geen overeenkomstig record in de doeltabel bestaat. Afhandeling van DELETE-gebeurtenissen kan worden gespecificeerd met de voorwaarde APPLY AS DELETE WHEN.

Belangrijk

U moet een doelstreamingtabel declareren waarop u wijzigingen wilt toepassen. U kunt desgewenst het schema voor uw doeltabel opgeven. Wanneer u het schema van de APPLY CHANGES doeltabel opgeeft, moet u ook de kolommen __START_AT en __END_AT met hetzelfde gegevenstype opnemen als het sequence_by veld.

Zie De APPLY CHANGES-API's: Het vastleggen van wijzigingen vereenvoudigen met DLT-.

Clausules
KEYS De kolom of combinatie van kolommen waarmee een rij in de brongegevens uniek wordt geïdentificeerd. Dit wordt gebruikt om te bepalen welke CDC-gebeurtenissen van toepassing zijn op specifieke records in de doeltabel. Als u een combinatie van kolommen wilt definiëren, gebruikt u een door komma's gescheiden lijst met kolommen. Deze clausule is vereist.
IGNORE NULL UPDATES Mogelijk maken dat updates worden geïmplementeerd die een subset van de doelkolommen bevatten. Wanneer een CDC-gebeurtenis overeenkomt met een bestaande rij en IGNORE NULL UPDATES is opgegeven, behouden kolommen met een null hun bestaande waarden in het doel. Dit geldt ook voor geneste kolommen met een waarde van null. Deze component is optioneel. De standaardinstelling is om bestaande kolommen te overschrijven met null waarden.
APPLY AS DELETE WHEN Hiermee geeft u op wanneer een CDC-gebeurtenis dient te worden behandeld als een DELETE in plaats van een upsert. Om gegevens die niet op volgorde zijn te verwerken, wordt de verwijderde rij tijdelijk als een 'tombstone' bewaard in de onderliggende Delta-tabel, en wordt in de metastore een weergave gecreëerd die deze tombstones uitfiltert. Het bewaarinterval kan worden geconfigureerd met de pipelines.cdc.tombstoneGCThresholdInSeconds tabeleigenschap. Deze component is optioneel.
APPLY AS TRUNCATE WHEN Hiermee geeft u op wanneer een CDC-gebeurtenis moet worden behandeld als een volledige tabel TRUNCATE. Omdat deze clausule een volledige verwijdering van de doeltabel activeert, moet deze alleen worden gebruikt voor specifieke gevallen waarin deze functionaliteit nodig is. De APPLY AS TRUNCATE WHEN-component wordt alleen ondersteund voor SCD-type 1. SCD type 2 ondersteunt het trunceren niet. Deze component is optioneel.
SEQUENCE BY De kolomnaam waarmee de logische volgorde van CDC-gebeurtenissen in de brongegevens wordt opgegeven. DLT maakt gebruik van deze sequentiëring om wijzigingsevenementen af te handelen die buiten de volgorde aankomen. De opgegeven kolom moet een sorteerbaar gegevenstype zijn. Deze clausule is vereist.
COLUMNS Hiermee geeft u een subset van kolommen op die moeten worden opgenomen in de doeltabel. U kunt het volgende doen: Geef de volledige lijst met kolommen op die u wilt opnemen: COLUMNS (userId, name, city). Geef een lijst met kolommen op die moeten worden uitgesloten: COLUMNS * EXCEPT (operation, sequenceNum) Deze component is optioneel. De standaardinstelling is om alle kolommen in de doeltabel op te nemen wanneer de COLUMNS component niet is opgegeven.
STORED AS Of er records moeten worden opgeslagen als SCD-type 1 of SCD-type 2. Deze component is optioneel. De standaardwaarde is SCD type 1.
TRACK HISTORY ON Hiermee geeft u een subset van uitvoerkolommen op voor het genereren van geschiedenisrecords wanneer er wijzigingen zijn in die opgegeven kolommen. U kunt het volgende doen: Geef de volledige lijst met kolommen op die u wilt bijhouden: COLUMNS (userId, name, city). Geef een lijst op met kolommen die moeten worden uitgesloten van het bijhouden: COLUMNS * EXCEPT (operation, sequenceNum) Deze component is optioneel. De standaardinstelling is het bijhouden van de geschiedenis voor alle uitvoerkolommen wanneer er wijzigingen zijn, gelijk aan TRACK HISTORY ON *.