Entwerfen einer Strategie zum Laden von PolyBase-Daten für einen dedizierten SQL-Pool in Azure Synapse Analytics

  • Artikel

In herkömmlichen SMP-Data Warehouses wird zum Laden von Daten ein ETL-Prozess (Extrahieren, Transformieren und Laden) verwendet. Der Azure SQL-Pool ist eine Architektur mit massiver Parallelverarbeitung (MPP), die die Vorteile der Skalierbarkeit und Flexibilität von Compute- und Speicherressourcen nutzt. Ein ELT-Prozess (Extrahieren, Laden, Transformieren) kann die Vorteile von integrierten verteilten Abfrageverarbeitungsfunktionen nutzen und Ressourcen beseitigen, die vor dem Laden zum Transformieren der Daten erforderlich sind.

Auch wenn der SQL-Pool viele Lademethoden unterstützt (u. a. Nicht-PolyBase-Optionen wie BCP und die SQL-BulkCopy-API), stellt PolyBase die schnellste und am besten skalierbare Möglichkeit zum Laden von Daten dar. PolyBase ist eine Technologie, die über die T-SQL-Sprache auf externe Daten zugreift, die in Azure Blob Storage oder Azure Data Lake Storage gespeichert sind.

Extrahieren, Laden und Transformieren (ELT)

ELT (Extrahieren, Laden und Transformieren) ist ein Prozess, bei dem Daten aus einem Quellsystem extrahiert, in ein Data Warehouse geladen und anschließend transformiert werden.

Grundlegende Schritte zum Implementieren eines PolyBase-ELT-Prozesses für den dedizierten SQL-Pool:

  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 in die Stagingtabellen des dedizierten SQL-Pools.
  5. Transformieren Sie die Daten.
  6. Fügen Sie die Daten in Produktionstabellen ein.

Ein Tutorial zum Ladevorgang finden Sie unter Verwenden von PolyBase zum Laden von Daten aus Azure Blob Storage in Azure Synapse Analytics.

Weitere Informationen finden Sie im Blog Lademuster.

1. Extrahieren der Quelldaten in Textdateien

Das Abrufen von Daten aus dem Quellsystem hängt vom Speicherort ab. Ziel ist es, die Daten in durch Trennzeichen getrennte Textdateien zu verschieben, die von PolyBase unterstützt werden.

Externe PolyBase-Dateiformate

PolyBase lädt Daten aus UTF-8- und UTF-16-codierten, durch Trennzeichen getrennten Textdateien. PolyBase wird auch aus den Hadoop-Dateiformaten RC File, ORC und Parquet geladen. PolyBase kann auch Daten aus Dateien laden, die mit Gzip und Snappy komprimiert wurden. PolyBase unterstützt derzeit kein erweitertes ASCII, keine Formate mit fester Breite und keine geschachtelten Formate wie WinZip, JSON und XML.

Wenn Sie aus SQL Server exportieren, können Sie die Daten mit dem Befehlszeilentool bcp in durch Trennzeichen getrennte Textdateien exportieren. Die Datentypzuordnung „Parquet zu Azure Synapse Analytics“ lautet wie folgt:

Parquet-Datentyp SQL-Datentyp
TINYINT TINYINT
SMALLINT SMALLINT
INT INT
BIGINT BIGINT
boolean bit
double float
float real
double money
double SMALLMONEY
Zeichenfolge NCHAR
Zeichenfolge NVARCHAR
Zeichenfolge char
Zeichenfolge varchar
BINARY BINARY
BINARY varbinary
timestamp date
timestamp smalldatetime
timestamp datetime2
timestamp datetime
timestamp time
date date
Decimal Decimal

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

Um die Daten in Azure Storage zu verschieben, können Sie sie in Azure Blob Storage oder Azure Data Lake Store verschieben. In beiden Fällen sollten die Daten in Textdateien gespeichert werden. PolyBase kann die Daten von beiden Speicherorten 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 mit dem dedizierten SQL-Pool finden Sie unter Laden von Daten in Azure Synapse Analytics mithilfe von Azure Data Factory.

3. Vorbereiten der Daten für das Laden

Möglicherweise müssen Sie die Daten in Ihrem Speicherkonto vorbereiten und bereinigen, bevor Sie sie in den dedizierten SQL-Pool laden. 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 externer Tabellen

Bevor Sie Daten laden können, müssen Sie externe Tabellen im Data Warehouse 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 Data Warehouse gespeichert sind.

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

Formatieren von Textdateien

Nachdem die externen Objekte definiert sind, müssen Sie die Zeilen der Textdateien mit der Definition der externen Tabelle und 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 des SQL-Pools übereinstimmen. Eine Nichtübereinstimmung zwischen den Datentypen in den externen Textdateien und der Data Warehouse-Tabelle 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 in die Stagingtabellen des dedizierten SQL-Pools mithilfe von PolyBase

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, in SQL-Pool integrierte verteilte Abfrageverarbeitungsfunktionen für Datentransformationen zu verwenden, bevor die Daten in Produktionstabellen eingefügt werden.

Optionen beim Laden mit PolyBase

Um Daten mit PolyBase zu laden, können Sie eine der folgenden Ladeoptionen nutzen:

  • PolyBase mit T-SQL funktioniert gut, wenn sich Ihre Daten in Azure Blob Storage oder Azure Data Lake Store befinden. Es bietet Ihnen die größtmögliche Kontrolle über den Ladevorgang, erfordert aber auch die Definition externer Datenobjekte. Die anderen Methoden definieren diese Objekte im Hintergrund, wenn Sie Quelltabellen zu Zieltabellen zuordnen. Zum Orchestrieren von T-SQL-Ladevorgängen können Sie Azure Data Factory-, SSIS- oder Azure-Funktionen verwenden.
  • 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 Data Factory (ADF) ist ein weiteres Orchestrierungstool. Es definiert eine Pipeline und plant Aufträge.
  • PolyBase mit Azure Databricks überträgt Daten aus einer Azure Synapse Analytics-Tabelle in einen Databricks-Datenrahmen und/oder schreibt Daten aus einem Databricks-Datenrahmen in eine Azure Synapse Analytics-Tabelle, die PolyBase verwendet.

Nicht von PolyBase stammende Ladeoptionen

Wenn Ihre Daten nicht mit PolyBase kompatibel sind, können Sie bcp oder die SQLBulkCopy API verwenden. Mit BCP werden Daten direkt in den dedizierten SQL-Pool geladen, ohne Azure Blob Storage zu durchlaufen. Daher ist diese Lösung nur für kleine Lasten vorgesehen. Beachten Sie, dass die Leistung dieser Optionen beim Laden langsamer ist als bei PolyBase.

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

Eine Anleitung zum Laden finden Sie unter Anleitung zum Laden von Daten.