Teilen über

Strategien zum Laden von Daten für einen dedizierten SQL-Pool in Azure Synapse Analytics

  • Artikel

In herkömmlichen dedizierten SMP-SQL-Pools wird zum Laden von Daten ein ETL-Prozess (Extrahieren, Transformieren und Laden) verwendet. Synapse SQL verwendet in Azure Synapse Analytics eine Architektur zur Verarbeitung verteilter Abfragen, die die Vorteile der Skalierbarkeit und Flexibilität von Compute- und Speicherressourcen nutzt.

Ein ELT-Prozess (Extrahieren, Laden und Transformieren) nutzt die Verarbeitungsfunktionen der integrierten verteilten Abfragen und verringert die Ressourcen, die vor dem Laden zum Transformieren der Daten erforderlich sind.

Dedizierte SQL-Pools unterstützen zwar viele Lademethoden (unter anderem beliebte SQL Server-Optionen wie bcp und die SqlBulkCopy-API), doch die schnellste und am besten skalierbare Möglichkeit zum Laden von Daten stellen externe PolyBase-Tabellen und die COPY-Anweisung dar.

Mit PolyBase und der COPY-Anweisung können Sie über die T-SQL-Sprache auf externe Daten zugreifen, die in Azure Blob Storage oder in Azure Data Lake Storage gespeichert sind. Wir empfehlen die Verwendung der COPY-Anweisung, um beim Laden von Daten so flexibel wie möglich zu sein.

Was ist ELT?

ELT (Extrahieren, Laden und Transformieren) ist ein Prozess, bei dem Daten aus einem Quellsystem extrahiert, in einen dedizierten SQL-Pool geladen und dann transformiert werden.

Dies sind die grundlegenden Schritte für die Implementierung von ELT:

  1. Extrahieren Sie die Quelldaten in Textdateien.
  2. Legen Sie die Daten in Azure Blob Storage oder Azure Data Lake Store ab.
  3. Bereiten Sie die Daten für das Laden vor.
  4. Laden Sie die Daten mithilfe von PolyBase oder des COPY-Befehls in Stagingtabellen.
  5. Transformieren Sie die Daten.
  6. Fügen Sie die Daten in Produktionstabellen ein.

Ein Tutorial zum Ladevorgang finden Sie unter Laden von Daten aus Azure Blob Storage.

1. Extrahieren der Quelldaten in Textdateien

Das Abrufen von Daten aus dem Quellsystem hängt vom Speicherort ab. Ziel ist es, die Daten in unterstützte, durch Trennzeichen getrennte Text- oder CSV-Dateien zu verschieben.

Unterstützte Dateiformate

Mit PolyBase und der COPY-Anweisung können Sie Daten aus UTF-8- und UTF-16-codierten, durch Trennzeichen getrennten Text- oder CSV-Dateien laden. Neben durch Trennzeichen getrennten Text- oder CSV-Dateien können auch Daten auch aus Hadoop-Dateiformaten wie ORC oder Parquet geladen werden. Mit PolyBase und der COPY-Anweisung können auch Daten aus Dateien geladen werden, die mit Gzip und Snappy komprimiert wurden.

Erweitertes ASCII, Formate mit fester Breite und geschachtelte Formate wie WinZip oder XML werden nicht unterstützt. Beim Exportieren aus SQL Server können Sie die Daten mit dem Befehlszeilentool bcp in durch Trennzeichen getrennte Textdateien exportieren.

2. Laden der Daten in Azure Blob Storage oder Azure Data Lake Storage

Wenn Sie Daten in Azure Storage platzieren möchten, können Sie sie in Azure Blob Storage oder in Azure Data Lake Store Gen2 verschieben. In beiden Fällen sollten die Daten in Textdateien gespeichert werden. PolyBase und die COPY-Anweisung können Daten von beiden Orten laden.

Tools und Dienste, mit denen Sie Daten in Azure Storage verschieben können:

  • Der Azure ExpressRoute-Dienst verbessert Netzwerkdurchsatz, Leistung und Vorhersagbarkeit. ExpressRoute ist ein Dienst, der Ihre Daten über eine dedizierte private Verbindung zu Azure weiterleitet. Bei ExpressRoute-Verbindungen werden Daten nicht über das öffentliche Internet weitergeleitet. Die Verbindungen bieten mehr Zuverlässigkeit, eine höhere Geschwindigkeit, niedrigere Latenzzeiten und mehr Sicherheit als herkömmliche Verbindungen über das öffentliche Internet.
  • Das Hilfsprogramm AzCopy verschiebt Daten über das öffentliche Internet in Azure Storage. Dies funktioniert, wenn Ihre Datenmengen weniger als 10 TB umfassen. Wenn Sie Ladevorgänge in regelmäßigen Abständen mit AzCopy ausführen möchten, testen Sie die Netzwerkgeschwindigkeit, um festzustellen, ob diese geeignet ist.
  • Azure Data Factory (ADF) verfügt über ein Gateway, das Sie auf dem lokalen Server installieren können. Anschließend können Sie eine Pipeline erstellen, um Daten vom lokalen Server in Azure Storage zu verschieben. Weitere Informationen zum Verwenden der Data Factory bei dedizierten SQL-Pools finden Sie unter Laden von Daten für dedizierte SQL-Pools.

3. Vorbereiten der Daten für das Laden

Möglicherweise müssen Sie die Daten in Ihrem Speicherkonto vorbereiten und bereinigen, bevor Sie den Ladevorgang durchführen. Die Datenvorbereitung kann durchgeführt werden, während sich Ihre Daten in der Quelle befinden, während Sie die Daten in Textdateien exportieren oder nachdem sich die Daten in Azure Storage befinden. Am einfachsten ist es, so früh wie möglich im Prozess mit den Daten zu arbeiten.

Definieren der Tabellen

Wenn Sie die COPY-Anweisung verwenden, müssen Sie zuerst die Tabelle(n) definieren, die Sie in Ihren dedizierten SQL-Pool laden.

Bei Verwendung von PolyBase müssen Sie vor dem Laden externe Tabellen in Ihrem dedizierten SQL-Pool definieren. PolyBase verwendet externe Tabellen, um Daten in Azure Storage zu definieren und auf diese zuzugreifen. Eine externe Tabelle ähnelt einer Datenbanksicht. Die externe Tabelle enthält das Tabellenschema und verweist auf Daten, die außerhalb des dedizierten SQL-Pools gespeichert sind.

Die Definition externer Tabellen umfasst die Angabe der Datenquelle, des Formats der Textdateien und der Tabellendefinitionen. Die folgenden Referenzartikel zur T-SQL-Syntax benötigen Sie:

Verwenden Sie beim Laden von Parquet-Dateien die folgende SQL-Datentypzuordnung:

Parquet-Typ Logischer Parquet-Typ (Anmerkung) SQL-Datentyp
BOOLEAN bit
BINARY/BYTE_ARRAY varbinary
Double float
GLEITKOMMAZAHL real
INT32 INT
INT64 BIGINT
INT96 datetime2
FIXED_LEN_BYTE_ARRAY BINARY
BINARY UTF8 NVARCHAR
BINARY STRING NVARCHAR
BINARY ENUM NVARCHAR
BINARY UUID UNIQUEIDENTIFIER
BINARY DECIMAL Decimal
BINARY JSON nvarchar(Max)
BINARY BSON varbinary(max)
FIXED_LEN_BYTE_ARRAY DECIMAL Decimal
BYTE_ARRAY INTERVAL varchar(max),
INT32 INT(8, true) SMALLINT
INT32 INT(16, true) SMALLINT
INT32 INT(32, true) INT
INT32 INT(8, false) TINYINT
INT32 INT(16, false) INT
INT32 INT(32, false) BIGINT
INT32 DATE date
INT32 DECIMAL Decimal
INT32 TIME (MILLIS) time
INT64 INT(64, true) BIGINT
INT64 INT(64, false) decimal(20,0)
INT64 DECIMAL Decimal
INT64 TIME (MILLIS) time
INT64 TIMESTAMP (MILLIS) datetime2
Komplexer Typ AUFLISTEN varchar(max)
Komplexer Typ MAP varchar(max)

Wichtig

  • Dedizierte SQL-Pools unterstützen derzeit keine Parquet-Datentypen mit einer Genauigkeit von MICROS oder NANOS.
  • Wenn die Typen zwischen Parquet und SQL nicht übereinstimmen oder wenn Sie nicht unterstützte Parquet-Datentypen verwenden, tritt eventuell der folgende Fehler auf: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:...
  • Das Laden eines Werts außerhalb des Bereichs von 0 bis 127 in eine tinyint-Spalte wird für Parquet und das ORC-Dateiformat nicht unterstützt.

Ein Beispiel für die Erstellung externer Objekte finden Sie unter Erstellen externer Tabellen.

Formatieren von Textdateien

Bei Verwendung von PolyBase müssen die definierten externen Objekte die Zeilen der Textdateien mit der externen Tabelle und der Definition des Dateiformats abgleichen. Die Daten in den einzelnen Zeilen der Textdatei müssen mit der Tabellendefinition übereinstimmen. So formatieren Sie die Textdateien

  • Wenn Ihre Daten aus einer nicht relationalen Quelle stammen, müssen Sie sie in Zeilen und Spalten transformieren. Unabhängig davon, ob die Daten aus einer relationalen oder nicht relationalen Quelle stammen, müssen die Daten so transformiert werden, dass sie mit den Spaltendefinitionen der Tabelle übereinstimmen, in die die Daten geladen werden sollen.
  • Formatieren Sie die Daten in der Textdatei so, dass sie mit den Spalten und Datentypen in der Zieltabelle übereinstimmen. Eine Nichtübereinstimmung zwischen Datentypen in den externen Textdateien und der Tabelle des dedizierten SQL-Pools führt dazu, dass Zeilen beim Laden zurückgewiesen werden.
  • Trennen Sie Felder in der Textdatei mit einem Abschlusszeichen. Stellen Sie sicher, dass Sie ein Zeichen oder eine Zeichenfolge verwenden, die nicht in Ihren Quelldaten enthalten ist. Verwenden Sie das durch CREATE EXTERNAL FILE FORMAT angegebene Abschlusszeichen.

4. Laden der Daten mithilfe von PolyBase oder der COPY-Anweisung

Es hat sich bewährt, die Daten in eine Stagingtabelle zu laden. Stagingtabellen ermöglichen das Behandeln von Fehlern, ohne die Produktionstabellen zu beeinträchtigen. Eine Stagingtabelle bietet Ihnen außerdem die Möglichkeit, die parallele Verarbeitungsarchitektur des dedizierten SQL-Pools für Datentransformationen zu verwenden, bevor die Daten in Produktionstabellen eingefügt werden.

Ladeoptionen

Zum Laden von Daten können Sie eine der folgenden Ladeoptionen nutzen:

  • Die COPY-Anweisung ist das empfohlene Ladehilfsprogramm, da sie das nahtlose und flexible Laden von Daten ermöglicht. Die Anweisung verfügt über viele zusätzliche Ladefunktionen, die PolyBase nicht bereitstellt. Sie können das kopierbare Tutorial zu Taxis in New York als Beispiel ausführen.
  • PolyBase mit T-SQL setzt die Definition externer Datenobjekte voraus.
  • PolyBase und COPY-Anweisung mit Azure Data Factory (ADF) ist ein weiteres Orchestrierungstool. Es definiert eine Pipeline und plant Aufträge.
  • PolyBase mit SSIS funktioniert gut, wenn sich die Quelldaten in SQL Server befinden. SSIS definiert die Zuordnung von Quell- zu Zieltabellen und orchestriert zudem die Workload. Wenn Sie bereits über SSIS-Pakete verfügen, können Sie die Pakete so ändern, dass sie mit dem neuen Data Warehouse-Ziel funktionieren.
  • PolyBase mit Azure Databricks überträgt Daten aus einer Tabelle in einen Databricks-Datenrahmen und/oder schreibt Daten aus einem Databricks-Datenrahmen in eine SQL Data Warehouse-Tabelle, die PolyBase verwendet.

Weitere Ladeoptionen

Neben PolyBase und der COPY-Anweisung können Sie auch bcp oder die SqlBulkCopy-API verwenden. Mit „bcp“ werden Daten direkt in die Datenbank geladen, ohne Azure Blob Storage zu durchlaufen. Daher ist es nur für kleine Workloads konzipiert.

Hinweis

Die Ladeleistung dieser Optionen ist geringer als bei PolyBase und der COPY-Anweisung.

5. Transformieren der Daten

Führen Sie während der Bereitstellung der Daten in der Stagingtabelle entsprechende Transformationen durch, die für Ihre Workload erforderlich sind. Anschließend verschieben Sie die Daten in eine Produktionstabelle.

6. Einfügen der Daten in Produktionstabellen

Die SELECT-Anweisung „INSERT INTO...“ verschiebt die Daten aus der Stagingtabelle in die permanente Tabelle.

Versuchen Sie beim Entwerfen eines ETL-Prozesses, den Prozess für ein kleines Testbeispiel auszuführen. Versuchen Sie, 1000 Zeilen aus der Tabelle in eine Datei zu extrahieren, sie nach Azure zu verschieben und sie dann in eine Stagingtabelle zu laden.

Ladelösungen von Partnern

Viele unserer Partner stellen Ladelösungen bereit. Weitere Informationen finden Sie in der Liste mit unseren Lösungspartnern.

Nächste Schritte

Anleitungen zum Laden finden Sie unter Bewährte Methoden für das Laden von Daten.