Bewährte Methoden zum Laden von Daten in einen dedizierten SQL-Pool in Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse ist ein relationales Enterprise-Warehouse auf einem Data Lake-Fundament mit zukunftsfähiger Architektur, integrierter KI und neuen Features. Wenn Sie mit Data Warehouse noch nicht vertraut sind, beginnen Sie mit Fabric Data Warehouse. Vorhandene dedizierte SQL-Pool-Workloads können auf Fabric aktualisieren, um neue Funktionen in den Bereichen Data Science, Echtzeitanalyse und Berichterstellung zu nutzen.

In diesem Artikel finden Sie Empfehlungen und Leistungsoptimierungen zum Laden von Daten.

Vorbereiten von Daten in Azure Storage

Um die Latenz zu minimieren, ordnen Sie Die Speicherschicht und den dedizierten SQL-Pool zu.

Beim Exportieren von Daten in ein ORC-Dateiformat erhalten Sie möglicherweise Java Nichtspeicherfehler, wenn große Textspalten vorhanden sind. Um diese Einschränkung zu umgehen, exportieren Sie nur eine Teilmenge der Spalten.

PolyBase kann keine Zeilen laden, die mehr als 1.000.000 Byte Daten enthalten. Wenn Sie Daten in die Textdateien in Azure Blob-Speicher oder Azure Data Lake Store einfügen, müssen sie weniger als 1.000.000 Byte Daten enthalten. Diese Bytebeschränkung gilt unabhängig vom Tabellenschema.

Alle Dateiformate weisen unterschiedliche Leistungsmerkmale auf. Verwenden Sie zum schnellsten Laden komprimierte, durch Trennzeichen getrennte Textdateien. Der Unterschied in der Leistung zwischen UTF-8 und UTF-16 ist minimal.

Teilen Sie große komprimierte Dateien in kleinere komprimierte Dateien auf.

Lasten mit ausreichender Rechenleistung ausführen

Führen Sie für die schnellste Ladegeschwindigkeit jeweils nur einen Ladeauftrag aus. Wenn dies nicht möglich ist, führen Sie eine minimale Anzahl von Arbeitslasten parallel aus. Wenn Sie einen großen Ladeauftrag erwarten, sollten Sie den dedizierten SQL-Pool vor dem Laden skalieren.

Um Lasten mit den entsprechenden Rechenressourcen auszuführen, erstellen Sie Ladebenutzer, die zum Ausführen von Lasten vorgesehen sind. Weisen Sie jedem Ladebenutzer eine bestimmte Ressourcenklasse oder Workloadgruppe zu. Um eine Last auszuführen, melden Sie sich als einer der ladeberechtigten Benutzer an, und führen Sie dann die Last aus. Die Last wird mit der Ressourcenklasse des Benutzers ausgeführt. Diese Methode ist einfacher als der Versuch, die Ressourcenklasse eines Benutzers an die derzeit erforderlichen Ressourcenklasse anzupassen.

Erstellen eines Ladebenutzers

In diesem Beispiel wird ein geladener Benutzer erstellt, der in eine bestimmte Workloadgruppe klassifiziert wurde. Der erste Schritt besteht darin, eine Verbindung mit master herzustellen und eine Anmeldung zu erstellen.

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

Stellen Sie eine Verbindung mit dem dedizierten SQL-Pool her, und erstellen Sie einen Benutzer. Im folgenden Code wird davon ausgegangen, dass Sie mit der Datenbank "mySampleDataWarehouse" verbunden sind. Es zeigt, wie ein Benutzer namens Loader erstellt und dem Benutzer Berechtigungen zum Erstellen von Tabellen und Laden mithilfe der COPY-Anweisung erteilt wird. Anschließend klassifiziert er den Benutzer mit maximaler Ressourcen in die Workloadgruppe "DataLoads".

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



Important

Dies ist ein extremes Beispiel für die Zuordnung von 100% Ressourcen des SQL-Pools zu einer einzelnen Last. Dadurch erhalten Sie eine maximale Gleichzeitigkeit von 1. Beachten Sie, dass dies nur für die anfängliche Last verwendet werden sollte, in der Sie andere Workloadgruppen mit ihren eigenen Konfigurationen erstellen müssen, um Ressourcen über Ihre Workloads hinweg auszugleichen.

Um eine Last mit den Ressourcen der Arbeitslastgruppe zu verarbeiten, melden Sie sich als Ladeprogramm an und führen Sie die Last aus.

Erlauben, dass mehrere Benutzer laden können

Es ist häufig erforderlich, dass mehrere Benutzer Daten in ein Data Warehouse laden müssen. Das Laden mit CREATE TABLE AS SELECT (Transact-SQL) erfordert die CONTROL-Berechtigungen in der Datenbank. Die CONTROL-Berechtigung ermöglicht vollen Zugriff auf alle Schemas. Möglicherweise möchten Sie nicht, dass alle Benutzer uneingeschränkten Zugriff auf alle Schemas haben. Verwenden Sie zum Einschränken von Berechtigungen die DENY CONTROL-Anweisung.

Betrachten Sie z. B. Datenbankschemata, schema_A für Abteilung A und schema_B für Abteilung B. Lassen Sie Datenbankbenutzer user_A und user_B Benutzer für das Laden mit PolyBase in den Abteilungen A und B sein. Beide haben CONTROL-Datenbankberechtigungen erhalten. Die Ersteller von Schema A und B sperren nun ihre Schemas mithilfe von DENY:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A und user_B sind jetzt aus dem Schema des anderen Depts gesperrt.

In eine Stagingtabelle laden

Um die schnellste Ladegeschwindigkeit zum Verschieben von Daten in eine Data Warehouse-Tabelle zu erzielen, laden Sie Daten in eine Stagingtabelle. Definieren Sie die Stagingtabelle als Heap, und verwenden Sie Roundrobin für die Verteilungsoption.

Denken Sie daran, dass das Laden in der Regel ein zweistufiger Prozess ist, in dem Sie zuerst in eine Stagingtabelle laden und dann die Daten in eine Produktionsdatenlagertabelle einfügen. Wenn die Produktionstabelle eine Hashverteilung verwendet, ist die Gesamtzeit zum Laden und Einfügen möglicherweise schneller, wenn Sie die Stagingtabelle mit der Hashverteilung definieren. Das Laden in die Stagingtabelle dauert länger, der zweite Schritt beim Einfügen der Zeilen in die Produktionstabelle führt jedoch nicht zu Datenbewegungen über die Verteilungen hinweg.

In einen Columnstore-Index laden

Spaltenspeicherindizes erfordern große Speichermengen, um Daten in qualitativ hochwertige Zeilengruppen zu komprimieren. Um optimale Komprimierung und Indexeffizienz zu erzielen, muss der Spaltenspeicherindex maximal 1.048.576 Zeilen in jede Zeilengruppe komprimieren. Wenn arbeitsspeicherdruck besteht, kann der Spaltenspeicherindex möglicherweise keine maximalen Komprimierungsraten erzielen. Dies wirkt sich auf die Abfrageleistung aus. Ausführliche Informationen finden Sie unter Columnstore-Speicheroptimierungen.

  • Um sicherzustellen, dass der Ladebenutzer über genügend Arbeitsspeicher verfügt, um maximale Komprimierungsraten zu erzielen, verwenden Sie das Laden von Benutzern, die Mitglied einer mittleren oder großen Ressourcenklasse sind.
  • Laden Sie genügend Zeilen, um neue Zeilengruppen vollständig auszufüllen. Während eines Massenladevorgangs werden alle 1.048.576 Zeilen direkt in den Columnstore als vollständige Zeilengruppe komprimiert. Datenmengen mit weniger als 102.400 Zeilen senden diese an den Deltastore, wo die Zeilen in einem B-Baum-Index gehalten werden. Wenn Sie zu wenige Zeilen laden, gehen sie möglicherweise alle in den Deltastore und werden nicht sofort in das Spaltenspeicherformat komprimiert.

Erhöhen der Batchgröße bei Verwendung der SQLBulkCopy-API oder BCP

Das Laden mit der COPY-Anweisung stellt den höchsten Durchsatz mit dedizierten SQL-Pools bereit. Wenn Sie die COPY nicht zum Laden verwenden können und die SqLBulkCopy-API oder bcp verwenden müssen, sollten Sie erwägen, die Batchgröße für einen besseren Durchsatz zu erhöhen.

Tip

Eine Batchgröße zwischen 100.000 und 1.000.000 Zeilen wird als empfohlene Grundlage zur Bestimmung der optimalen Kapazität angesehen.

Verwalten von Ladefehlern

Eine Last mit einer externen Tabelle kann mit dem Fehler "Abfrage abgebrochen" fehlschlagen– der maximale Ablehnungsschwellenwert wurde beim Lesen aus einer externen Quelle erreicht". Diese Meldung gibt an, dass Ihre externen Daten schmutzige Datensätze enthalten. Ein Datensatz wird als schmutzig betrachtet, wenn die Datentypen und die Anzahl der Spalten nicht den Spaltendefinitionen der externen Tabelle entsprechen oder die Daten nicht dem angegebenen externen Dateiformat entsprechen.

Um die verunreinigten Datensätze zu beheben, stellen Sie sicher, dass Ihre Tabellen- und Dateiformatdefinitionen für externe Daten korrekt sind und die externen Daten diesen Definitionen entsprechen. Im Fall, dass eine Teilmenge externer Datensätze fehlerhaft ist, können Sie diese Datensätze für Ihre Abfragen ausschließen, indem Sie die Ablehnungsoptionen in "CREATE EXTERNAL TABLE" verwenden.

Einfügen von Daten in eine Produktionstabelle

Ein einmaliges Laden einer kleinen Tabelle mit einer INSERT-Anweisung oder sogar ein regelmäßiges Neuladen einer Nachschlagetabelle kann mit einer Anweisung wie INSERT INTO MyLookup VALUES (1, 'Type 1') ausreichend gut funktionieren. Singleton-Einfügungen sind jedoch nicht so effizient wie das Ausführen eines Massenladevorgangs.

Wenn Sie im Laufe des Tages Tausende oder mehr einzelne Einträge haben, bündeln Sie die Einträge, damit Sie sie im Bulk-Verfahren laden können. Entwickeln Sie Ihre Prozesse, um die einzelnen Einfügungen an eine Datei anzufügen, und erstellen Sie dann einen weiteren Prozess, der die Datei regelmäßig lädt.

Erstellen von Statistiken nach dem Laden

Um die Abfrageleistung zu verbessern, ist es wichtig, Statistiken zu allen Spalten aller Tabellen nach dem ersten Laden zu erstellen, oder es treten wichtige Änderungen in den Daten auf. Das Erstellen von Statistiken kann manuell erfolgen, oder Sie können die automatische Erstellung von Statistiken aktivieren.

Eine ausführliche Erläuterung der Statistik finden Sie unter "Statistik". Das folgende Beispiel zeigt, wie Sie Statistiken für fünf Spalten der Customer_Speed Tabelle manuell erstellen.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Drehen von Speicherschlüsseln

Es ist eine gute Sicherheitsvorkehrung, den Zugriffsschlüssel zu Ihrem Blob Storage regelmäßig zu ändern. Sie verfügen über zwei Speicherschlüssel für Ihr BLOB-Speicherkonto, mit dem Sie die Schlüssel übertragen können.

So wechseln Sie die Schlüssel Ihres Azure Storage Kontos:

Geben Sie für jedes Speicherkonto, dessen Schlüssel geändert wurde, ALTER DATABASE SCOPED CREDENTIAL aus.

Beispiel:

Der ursprüngliche Schlüssel wird erstellt.

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

Drehen des Schlüssels von Schlüssel 1 auf Schlüssel 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

Es sind keine weiteren Änderungen an zugrunde liegenden externen Datenquellen erforderlich.

Verwandte Inhalte

  • Last updated on