Aufnehmen von Daten mit SQL-Methoden

Abgeschlossen

SQL-Befehle bieten einen deklarativen Ansatz für die Datenaufnahme in Azure Databricks. Wenn Sie bereits mit der SQL-Syntax vertraut sind, können Sie mit diesen Methoden Tabellen erstellen und auffüllen, ohne prozeduralen Code zu schreiben. Die drei primären SQL-Aufnahmetechniken – CREATE TABLE AS SELECT (CTAS), CREATE OR REPLACE TABLE und COPY INTO – behandeln jeweils unterschiedliche Ingestion-Szenarien, während die vollständige Kompatibilität mit Unity Catalog beibehalten wird.

Erstellen von Tabellen aus Abfragen mit CTAS

Die CREATE TABLE AS SELECT Anweisung kombiniert tabellenerstellung und Datenpopulation in einem einzigen Vorgang. Sie definieren eine neue Tabelle basierend auf den Ergebnissen einer SELECT Abfrage, wodurch sie ideal zum Transformieren von Daten während des Datenimports geeignet ist.

Betrachten Sie ein Szenario, in dem Sie Kundendaten aus einer externen Quelle aufnehmen und Transformationen anwenden müssen. Mit CTAS schreiben Sie eine Abfrage, die aus der Quelle liest, Ihre Transformationen anwendet und die Ergebnisse in einer neuen verwalteten Tabelle speichert:

CREATE TABLE catalog.schema.customers AS
SELECT 
    customer_id,
    UPPER(customer_name) AS customer_name,
    email,
    created_date
FROM external_staging.raw_customers
WHERE customer_status = 'active';

Das Tabellenschema wird automatisch aus den Abfrageergebnissen abgeleitet. Azure Databricks erstellt standardmäßig die Tabelle mithilfe des Delta-Formats, die ACID-Transaktionen, Zeitreisen und optimierte Leistung bereitstellt.

CTAS eignet sich gut für anfängliche Datenladevorgänge und einmalige Migrationen. Wenn Sie Daten aus Dateien lesen müssen, kombinieren Sie CTAS mit der read_files Tabellenwertfunktion:

CREATE TABLE catalog.schema.sales_data AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/sales/*.parquet',
    format => 'parquet'
);

Hinweis

CTAS erstellt bei jeder Ausführung eine neue Tabelle. Wenn die Tabelle bereits vorhanden ist, schlägt der Befehl fehl, es sei denn, Sie verwenden die IF NOT EXISTS Klausel – diese Klausel überspringt jedoch die Ausführung vollständig, anstatt die Tabelle zu aktualisieren.

Aktualisieren von Tabellen mit CREATE OR REPLACE TABLE

Wenn Sie den Inhalt einer Tabelle vollständig aktualisieren müssen, bietet CREATE OR REPLACE TABLE eine saubere Lösung. Dieser Befehl erstellt entweder eine neue Tabelle oder ersetzt vollständig eine vorhandene Tabelle, bewahrt den Tabellenverlauf, gewährte Berechtigungen und alle Zeilenfilter oder Spaltenmasken, die Sie konfiguriert haben.

Dieser Ansatz erweist sich als nützlich für regelmäßige Datenaktualisierungen, bei denen Sie alle vorhandenen Daten ersetzen möchten:

CREATE OR REPLACE TABLE catalog.schema.daily_metrics AS
SELECT 
    report_date,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM catalog.schema.transactions
WHERE report_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY report_date;

Im Gegensatz zum Löschen und Neuanlegen einer Tabelle werden bei CREATE OR REPLACE die Metadaten und Berechtigungen der Tabelle beibehalten. Nachgeschaltete Benutzer und Anwendungen greifen weiterhin ohne Neukonfiguration auf die Tabelle zu.

Sie können diesen Befehl auch verwenden, um Daten direkt aus Dateien zu laden. Das Schema wird automatisch aus den Abfrageergebnissen abgeleitet:

CREATE OR REPLACE TABLE catalog.schema.products AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/products.csv',
    format => 'csv',
    header => true
);

Von Bedeutung

CREATE OR REPLACE führt eine vollständige Tabellenersetzung aus. Verwenden COPY INTO Sie stattdessen für inkrementelle Updates, bei denen Sie nur neue Datensätze hinzufügen möchten.

Inkrementelles Laden von Dateien mit COPY INTO

COPY INTO geht auf eine häufige Herausforderung bei der Datenaufnahme ein: Dateien zuverlässig und wiederholbar aus dem Cloud-Speicher zu laden. Im Gegensatz zu CTAS, das einmal ausgeführt wird und eine Tabelle erstellt, ist COPY INTO für fortlaufende Erfassungsworkflows gedacht, bei denen regelmäßig neue Dateien eingehen.

Der Befehl liest Dateien von einem angegebenen Speicherort und fügt sie an eine vorhandene Delta-Tabelle an. Das Schlüsselfeature ist idempotenz – Dateien, die bereits geladen wurden, werden automatisch übersprungen, auch über mehrere Ausführungen hinweg:

COPY INTO catalog.schema.events
FROM '/Volumes/catalog/schema/volume/events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiline' = 'true');

Vor dem Ausführen COPY INTOmuss die Zieltabelle bereits vorhanden sein. Erstellen Sie es mit dem entsprechenden Schema:

CREATE TABLE IF NOT EXISTS catalog.schema.events (
    event_id STRING,
    event_type STRING,
    event_timestamp TIMESTAMP,
    payload STRING
);

Dateiauswahl konfigurieren

Wenn Ihr Quellverzeichnis Dateien mit unterschiedlichen Benennungsmustern enthält oder Sie bestimmte Dateien laden müssen, verwenden Sie die Optionen PATTERN oder FILES:

-- Load only files matching a pattern
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
PATTERN = 'orders_2024*.parquet';

-- Load specific files by name
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
FILES = ('orders_001.parquet', 'orders_002.parquet');

Verwaltung von Schema- und Datenqualität

COPY INTO bietet Optionen zum Behandeln von Schemaänderungen und zum Überprüfen von Daten vor dem Laden:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
FORMAT_OPTIONS (
    'header' = 'true',
    'inferSchema' = 'true'
)
COPY_OPTIONS ('mergeSchema' = 'true');

Mit mergeSchema der Option kann sich das Tabellenschema weiterentwickeln, wenn neue Spalten in Quelldateien angezeigt werden. Um Daten zu validieren, ohne sie zu laden, fügen Sie die VALIDATE-Klausel hinzu:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
VALIDATE ALL;

Mit dieser Validierung wird geprüft, ob Daten analysiert werden können, mit dem Tabellenschema übereinstimmen und die Nullwert- und Check-Beschränkungen erfüllen.

Wählen Sie die richtige Methode aus.

Jede SQL-Erfassungsmethode dient verschiedenen Zwecken in Ihren Data Engineering-Workflows:

Methode	Am besten geeignet für:	Verhalten
CTAS	Anfängliche Daten werden geladen, einmalige Migrationen, Erstellen von Tabellen aus Abfragen	Erstellt eine neue Tabelle; schlägt fehl, wenn tabelle vorhanden ist
ERSTELLEN ODER ERSETZEN	Regelmäßige vollständige Aktualisierungen, Ersetzen von Stagingtabellen	Ersetzt die gesamte Tabelle; behält Berechtigungen bei
KOPIEREN IN	Fortlaufende Dateiaufnahme, inkrementelle Ladevorgänge	Hängt an bestehende Tabelle an; überspringt geladene Dateien.

Für dateibasierte Erfassung, die automatische Schema-Inferenz, Dateibenachrichtigungen oder Exact-once-Garantien erfordert, sollten Sie Auto Loader als ergänzenden Ansatz in Betracht ziehen. Wenn Ihre Aufnahmeanforderungen unkompliziert sind und Sie deklaratives SQL gegenüber prozeduralem Code bevorzugen, bieten diese drei Methoden ein vollständiges Toolkit zum Verwalten des Datenflusses in Unity-Katalog.

Hinweis

COPY INTO funktioniert gut beim Einspeisen von Tausenden von Dateien im Laufe der Zeit. Für Quellen, die auf Millionen von Dateien oder mehr wachsen, ist das automatische Laden (in der nächsten Einheit behandelt) der empfohlene Ansatz – es ermittelt Dateien effizienter, unterstützt eine umfangreichere Schemaentwicklung und skaliert ohne den Aufwand der Verzeichnisauflistung. Databricks empfiehlt auch streamingtabellen, die von Auto Loader unterstützt werden, als skalierbare, langfristige Alternative für die SQL-basierte Dateiaufnahme.