HausfluchtAm veröffentlicht

Azure Synapse Analytics – Load Data with Polybase in Data Factory

Der Lagerung großer Mengen unstrukturierter Daten beispielsweise im Azure Blob Storage, der aufgrund seiner hohen Skalierbarkeit und kosteneffizienten Speicherungsmöglichkeit eine beliebte Möglichkeit in der Azure Cloud Umgebung bietet, folgt häufig die Beladung des Azure Data Warehouses, jetzt Azure Synapse Analytics. Eine komfortable Möglichkeit bietet hier die Verwendung der Azure Data Factory.

Die Azure Data Factory kann übersichtlich ETL und ELT Vorgänge entweder durch Verwendung eigens geschriebenen SQL-Codes, oder ohne Code durch Auswahl der passenden Einstellungen und per Drag und Drop erstellen. Dadurch ist sie erster Anlaufpunkt, wenn Daten im Synapse Analytics integriert werden sollen. Zum Datentransfer werden hier als eine Möglichkeit Pipelines erstellt, die einen Rahmen für die jeweiligen gewünschten Aktivitäten darstellen. Eine Pipeline besteht aus einer oder mehreren Activities, die definieren, wie die Daten transformiert werden sollen, von einem einfachen Copy-Job bis hin zu komplexen Transformationen.

Beispielhaft sollen folgend die Daten einer Datei aus dem Azure Blob Storage in Azure Synapse Analytics verschoben werden, dabei werden die einzelnen Schritte beschrieben und im Zuge des Kopierens auf die Vorteile des Ladens mit PolyBase eingegangen.

Zunächst wird auf dem Startbildschirm der Data Factory Create Pipeline ausgewählt.

 

 

 

 

Es wird eine neue Pipeline mit Namen pipeline1 angelegt. Unter Activities – Move & Transform kann die Funktion Copy Data ausgewählt und in das Fenster gezogen werden.

 

 

 

 

 

Unter den Reitern Source  und Sink wird der Input- (beispielsweise aus dem Azure Blob Storage) und der Output-Datensatz (Zieldatensatz im Synapse Analytics) definiert.

                                                                     

Die Zuordnung der Spalten erfolgt über den Reiter Mapping. Automatisch erfolgt das Mapping über die Spaltennamen, bei Bedarf kann auch eine explizite Spaltenzuweisung angegeben werden.

Unter dem Sink Reiter stehen drei verschiedene Copy-Methoden zur Verfügung. PolyBase, Copy command (Preview) und Bulk insert. Bulk insert ist standardmäßig ausgewählt, die anderen beiden Methoden sind jedoch wesentlich performanter. Im Folgenden wird insbesondere auf die Vorteile des Kopierens mit PolyBase eingegangen.

PolyBase ist die performanteste und am Besten skalierbare Belademethode. Der Durchsatz kann im Vergleich zur Bulk-Insert Methode erheblich gesteigert werden. PolyBase kann Daten aus relationalen Datenbanken (wie beispielsweise die Synapse) mit Daten aus nicht-relationalen Datenbanken (Hadoop, Blob Storage, Data Lake Storage) auf Abfrage-Level mit Standard-T-SQL vereinen. Um Kopieren mit PolyBase ausführen zu können, müssen einige Eigenschaften erfüllt werden.

Zunächst müssen die Quelldaten in einem PolyBase-kompatiblen Format vorliegen. Darunter fällt das Parquet-, das ORC- oder das Textformat mit Trennzeichen. Darüber hinaus muss der User über die Berechtigung Control für die Zieldatenbank verfügen. Datenzeilen dürfen nicht größer als 1MB sein und die Ziel- und Beladetabelle müssen gleich viele Spalten haben. Zusätzlich erweist sich ein Load mit CTAS (Create Table As Select) als performanter als ein INSERT INTO. Bei einer Beladung mit der Data Factory ist allerdings INSERT INTO die standardmäßige Form. Ein CTAS kann beispielsweise durch das Erstellen einer Stored Procedure ausgewählt werden. Sollten insbesondere die Anforderungen an die Größe der Tabelle nicht eingehalten werden können, empfiehlt es sich, die Tabelle in mehrere Teiltabellen (vertikal) zu trennen, zu kopieren und anschließend wieder zusammen zu mergen.

Sollten Vorgaben nicht erfüllt werden können, besteht darüber hinaus die Möglichkeit, die Daten mit Hilfe des gestaffelten Kopierens mit PolyBase zu kopieren. Dieser Vorgang bringt die Quelldaten automatisch in ein kompatibles Format, speichert die Daten im Blob Storage zwischen und lädt sie anschließend nach Synapse. Der zusätzliche Schritt des Zwischenspeicherns verlangsamt den Prozess allerdings. Der Vorgang des gestaffelten Kopierens ermöglicht es zudem, auch on-premise Daten mit einzubinden. Hier werden die Daten durch PolyBase automatisch in einem Azure Storage Account zwischengespeichert und anschließend in die Synapse geladen. Werden die Bedingungen für PolyBase nicht erfüllt und kein gestaffeltes Kopieren gewählt, wird automatisch zu Bulk-insert gewechselt.

Wesentlicher Grund für die performantere Beladung mit PolyBase ist das Umgehen des Control Nodes  und somit die Möglichkeit, die Daten direkt auf die compute nodes zu bringen ohne einen vorherigen Verteilmechanismus abarbeiten zu müssen. Die Daten werden so direkt an die Verarbeitung gegeben.

Im Folgenden wird exemplarisch eine Datenquelle mit PolyBase und zum Vergleich mit dem standardmäßigen BULK INSERT vom Azure Blob Storage in die Synapse verschoben. Die gewählte Synapse hat 400 DataWarehouseUnits (DWUs) zur Verfügung und läuft auf einer compute-node.

Der erste Datensatz enthält eine Millionen Datensätze und 40 Spalten und hat eine Gesamtgröße von 230MB. Der zweite Datensatz  hat 45 Millionen Datensätze, 7 Spalten und eine Gesamtgröße von drei GB. Im ersten Fall wurde eine csv Datei verschoben, der zweite Fall verschiebt 10 csv Files aus einem gemeinsamen Unterordner. In beiden Fällen wird genau eine Tabelle in der Synapse neu angelegt und beladen.

DatensatzGesamtgrößePolyBaseBulk Insert
1Mio Datensätze230MB00:00:32 Std00:02:41 Std
45Mio Datensätze3GB00:00:22 Std01:08:57 Std

Zunächst zeigt sich, dass kleine Dateien mit PolyBase wesentlich schneller verschoben werden können als mit Bulk Insert. Zentraler noch zeigt sich, dass eine starke Aufstockung der Anzahl an Datensätzen für PolyBase keinen Unterschied macht, insofern die Daten sinnvoll getrennt abgelegt werden. Wird beispielsweise ein Datensatz mit Spark separiert nach Partitionen in dem Blob Storage abgelegt und als Quellordner eines Copy-Jobs für PolyBase angegeben, macht es keinen Unterschied, ob 1 Millionen Datensätze, 50 oder 100 Millionen Datensätze verschoben werden.

Somit zeigt sich, dass beim Verschieben von Daten unbedingt auf PolyBase zurückzugreifen ist und im Zweifel der Aufwand einer Aufbereitung in ein kompatibles Format lohnenswert ist, da durch diese Art des Datentransfers starke Performanceverbesserungen erreicht werden können. Unter Zuhilfenahme der Azure Data Factory lässt sich der Vorgang einfach und übersichtlich aufbauen.