Best practices voor het laden van gegevens in een toegewezen SQL-pool in Azure Synapse Analytics

  • Artikel

In dit artikel vindt u aanbevelingen en prestatieoptimalisaties voor het laden van gegevens.

Gegevens voorbereiden in Azure Storage

Als u de latentie wilt minimaliseren, voegt u de opslaglaag en de toegewezen SQL-pool toe.

Bij het exporteren van gegevens in een ORC-bestandsindeling kunnen er Java-geheugenfouten optreden wanneer er grote tekstkolommen zijn. U kunt deze beperking omzeilen door slechts een subset van de kolommen te exporteren.

PolyBase kan geen rijen laden die meer dan 1.000.000 bytes aan gegevens bevatten. Wanneer u gegevens in de tekstbestanden in Azure-blob-opslag of Azure Data Lake Store zet, moeten deze minder dan 1.000.000 bytes aan gegevens bevatten. Deze bytebeperking geldt ongeacht het tabelschema.

Alle bestandsindelingen hebben verschillende prestatiekenmerken. Gebruik voor het snelste laadproces gecomprimeerde tekstbestanden. Het verschil tussen UTF-8- en UTF-16-prestaties is minimaal.

Splits grote gecomprimeerde bestanden in kleinere gecomprimeerde bestanden.

Laadt met voldoende rekenkracht

Voer voor de hoogste laadsnelheid slechts één taak tegelijk uit. Voer een zo klein mogelijk aantal laadtaken tegelijk uit als dit niet haalbaar is. Als u een grote laadtaak verwacht, kunt u overwegen om uw toegewezen SQL-pool omhoog te schalen vóór het laden.

Als u loads wilt uitvoeren met geschikte rekenresources, maakt u gebruikers voor het laadproces die zijn aangewezen voor het uitvoeren van loads. Wijs elke ladende gebruiker toe aan een specifieke resourceklasse of workloadgroep. Als u een load wilt uitvoeren, meldt u zich aan als een van de laadgebruikers en voert u de load uit. De load wordt uitgevoerd met de resourceklasse van de gebruiker. Deze methode is eenvoudiger dan de resourceklasse van een gebruiker aanpassen om te voldoen aan de huidige benodigde resourceklasse.

Een laadgebruiker maken

In dit voorbeeld wordt een gebruiker gemaakt die is geclassificeerd voor een specifieke workloadgroep. De eerste stap is verbinding maken met de master en een aanmelding maken.

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

Maak verbinding met de toegewezen SQL-pool en maak een gebruiker. In de volgende code wordt ervan uitgegaan dat u bent verbonden met de database met de naam mySampleDataWarehouse. Het laat zien hoe u een gebruiker met de naam loader maakt en geeft de gebruiker machtigingen om tabellen te maken en te laden met behulp van de COPY-instructie. Vervolgens wordt de gebruiker met maximale resources in de workloadgroep DataLoads ingedeeld.

   -- 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'
   );



Belangrijk

Dit is een extreem voorbeeld van het toewijzen van 100% resources van de SQL-pool aan één belasting. Hiermee krijgt u een maximale gelijktijdigheid van 1. Houd er rekening mee dat dit alleen moet worden gebruikt voor de eerste belasting, waarbij u extra workloadgroepen met hun eigen configuraties moet maken om resources in uw workloads te verdelen.

Als u een load wilt uitvoeren met resources voor de laadworkloadgroep, meldt u zich aan als loader en voert u de belasting uit.

Meerdere gebruikers toestaan om te laden

Vaak is het nodig dat meerdere gebruikers gegevens kunnen laden in een datawarehouse. Voor het laden met CREATE TABLE AS SELECT (Transact-SQL) zijn CONTROL-machtigingen van de database vereist. De CONTROL-machtiging biedt beheertoegang tot alle schema's. Mogelijk wilt u niet alle gebruikers die laadtaken uitvoeren, beheertoegang tot alle schema's verlenen. Als u machtigingen wilt beperken, kunt u de instructie DENY CONTROL gebruiken.

Denk bijvoorbeeld aan databaseschema's, schema_A voor afdeling A, en schema_B voor afdeling B. Laat databasegebruikers gebruiker_A en gebruiker_B gebruikers zijn voor PolyBase die respectievelijk laden in afdeling A en B. Beide zijn voorzien van databasemachtigingen voor CONTROL. De makers van schema A en B vergrendelen nu hun schema's met DENY:

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

User_A en user_B zijn nu uitgesloten van het schema van de andere afdeling.

Laden naar een faseringstabel

De hoogste laadsnelheid voor het verplaatsen van gegevens naar een datawarehousetabel kunt u verkrijgen door gegevens te laden naar een tijdelijke tabel. Definieer de faseringstabel als een heap en gebruik round-robin voor de distributieoptie.

Bedenk dat laden meestal een proces met twee stappen is waarin u eerst naar een tijdelijke tabel laadt en de gegevens vervolgens in een productiedatawarehousetabel invoegt. Als de productietabel een hash-distributiepunt gebruikt, is de totale tijd voor het laden en invoegen mogelijk sneller als u de faseringstabel met de hash-distributie definieert. Het laden naar de faseringstabel duurt langer, maar de tweede stap van het invoegen van de rijen in de productietabel leidt niet tot de verplaatsing van gegevens in de distributies.

Laden naar een columnstore-index

Columnstore-indexen vereisen grote hoeveelheden geheugen voor het comprimeren van gegevens tot hoogwaardige rijgroepen. Voor de beste compressie en efficiëntie van de index moet de columnstore-index maximaal 1.048.576 rijen in elke rijgroep comprimeren. Bij geheugenbelasting kan het zijn dat de columnstore-index de maximale compressiesnelheden niet kan halen. Dit is van invloed op de queryprestaties. Zie voor gedetailleerde informatie Columnstore geheugenoptimalisaties.

  • Zorg dat de gebruiker van het laadproces voldoende geheugen heeft om maximale compressiesnelheden te bereiken. Gebruik hiervoor gebruikers voor het laadproces die lid zijn van een middelgrote of grote resourceklasse.
  • Laad genoeg rijen om nieuwe rijgroepen volledig te vullen. Tijdens bulksgewijs laden worden elke 1.048.576 rijen rechtstreeks in de columnstore gecomprimeerd als een volledige rijgroep. Laadtaken met minder dan 102.400 rijen verzenden de rijen naar de deltastore waarin rijen zijn ondergebracht in een b-tree-index. Als u te weinig rijen laadt, gaan deze mogelijk allemaal naar de deltastore en worden ze niet direct naar columnstore-indeling gecomprimeerd.

Batchgrootte vergroten bij gebruik van DE SQLBulkCopy-API of BCP

Laden met de INSTRUCTIE COPY biedt de hoogste doorvoer met toegewezen SQL-pools. Als u copy niet kunt gebruiken om te laden en de SqLBulkCopy-API of bcp moet gebruiken, kunt u overwegen de batchgrootte te vergroten voor een betere doorvoer.

Tip

Een batchgrootte tussen 100 K en 1 miljoen rijen is de aanbevolen basislijn voor het bepalen van de optimale batchgroottecapaciteit.

Laadfouten beheren

Een load met behulp van een externe tabel kan mislukken met de fout Query afgebroken--de maximale weigeringsdrempelwaarde is bereikt tijdens het lezen vanuit een externe bron. Dit bericht geeft aan dat uw externe gegevens vervuilde records bevatten. Een gegevensrecord wordt als 'vervuild' beschouwd als de gegevenstypen en het aantal kolommen niet overeenkomen met de kolomdefinities van de externe tabel of als de gegevens niet overeenkomen met de externe bestandsindeling.

U kunt vervuilde records voorkomen door ervoor te zorgen dat uw externe tabel- en bestandindelingsdefinities correct zijn en uw externe gegevens overeenstemmen met deze definities. Als een subset van externe gegevensrecords vuil is, kunt u ervoor kiezen om deze records voor uw query's te weigeren met behulp van de weigeringsopties in 'CREATE EXTERNAL TABLE' .

Gegevens invoegen in een productietabel

Een eenmalige laadtaak naar een kleine tabel met een INSERT-instructie of zelfs een periodieke herlaadtaak kan een acceptabel resultaat geven met een instructie zoals INSERT INTO MyLookup VALUES (1, 'Type 1'). Singleton-inserts zijn echter niet zo efficiënt als het uitvoeren van bulksgewijs laden.

Als u de hele dag door duizenden of meerdere enkele gegevens wilt invoeren, voeg de gegevens dan samen tot een batch zodat deze bulksgewijs kunt laden. Ontwikkel uw processen om de afzonderlijke gegevens aan een bestand toe te voegen en maak vervolgens een ander proces dat het bestand periodiek laadt.

Statistieken maken na het laden

Als u de queryprestaties wilt verbeteren, is het belangrijk om statistieken te maken voor alle kolommen van alle tabellen na de eerste belasting of nadat er grote wijzigingen in de gegevens optreden. Statistieken kunnen handmatig worden gemaakt of u kunt automatisch statistieken maken inschakelen.

Zie statistieken voor gedetailleerde uitleg van statistieken. In het volgende voorbeeld ziet u hoe u handmatig statistieken maakt voor vijf kolommen van de Customer_Speed tabel.

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]);

Opslagsleutels draaien

Het is verstandig uit veiligheidsoverwegingen de toegangssleutel in de blob-opslag regelmatig te wijzigen. Er zijn twee opslagsleutels voor uw blob-opslagaccount, waarmee u de sleutels kunt wijzigen.

Sleutels van het Microsoft Azure Storage-account draaien:

Voor elk opslagaccount waarvan de sleutel is gewijzigd, moet u ALTER DATABASE SCOPED CREDENTIAL uitvoeren.

Voorbeeld:

De oorspronkelijke sleutel wordt gemaakt

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

Rotate key from key 1 to key 2

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

Er hoeven geen andere wijzigingen te worden aangebracht aan onderliggende externe gegevensbronnen.

Volgende stappen