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

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

Gegevens voorbereiden in Azure Storage

Als u de latentie wilt minimaliseren, moet u uw opslaglaag en uw toegewezen SQL pool.

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 rijen met meer dan 1.000.000 bytes aan gegevens niet laden. 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.

Laadbelastingen uitvoeren 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 laadklus verwacht, kunt u uw toegewezen SQL vóór de belasting omhoog schalen.

Als u loads wilt uitvoeren met geschikte rekenresources, maakt u gebruikers voor het laadproces die zijn aangewezen voor het uitvoeren van loads. Wijs elke gebruiker voor het laden toe aan een specifieke resourceklasse of workloadgroep. Als u een load wilt uitvoeren, moet u zich aanmelden als een van de gebruikers voor het laden en vervolgens de belasting uitvoeren. 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 gebruiker voor laden maken

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

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

Verbinding maken aan de toegewezen SQL pool en maak een gebruiker. In de volgende code wordt ervan uitgenomen dat u bent verbonden met de database met de naam mySampleDataWarehouse. U ziet hoe u een gebruiker met de naam loader maakt en de gebruiker machtigingen geeft om tabellen te maken en te laden met behulp van de COPY-instructie. Vervolgens wordt de gebruiker met maximale resources in de workloadgroep DataLoads classificeert.

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

Als u een belasting wilt uitvoeren met resources voor de werkbelastinggroep voor laden, meld u zich aan als loader en voer de belasting uit.

Meerdere gebruikers toestaan te laden

Vaak is het nodig dat meerdere gebruikers gegevens kunnen laden in een datawarehouse. Voor het laden 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 vergrendeld voor 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 heeft gevolgen voor 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 het bulksgewijs laden worden elke 1.048.576 rijen als een volledige rijgroep rechtstreeks in de columnstore gecomprimeerd. 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 SQLBulkCopy-API of BCP

Laden met de COPY-instructie biedt de hoogste doorvoer met toegewezen SQL groepen. Als u de COPY niet kunt gebruiken om te laden en de SqLBulkCopy-API of bcpmoet gebruiken, kunt u overwegen om de batchgrootte te vergroten voor een betere doorvoer.

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 vervuild is, kunt u ervoor kiezen om deze records voor uw query's af te wijzen 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-invoegingen 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

Om de queryprestaties te verbeteren, is het belangrijk om statistieken te maken voor alle kolommen van alle tabellen na de eerste keer laden, of grote wijzigingen in de gegevens. Statistieken maken kan handmatig worden uitgevoerd of u kunt statistieken voor automatisch 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

• Zie ELTontwerpen voor meer informatie over PolyBase en het ontwerpen van een ELT-proces (Extract, Load, and Transform) Azure Synapse Analytics .
• Voor een zelfstudie over het laden gebruikt u PolyBase om gegevens uit Azure Blob Storage teladen naar Azure Synapse Analytics.
• Zie Uw workload controleren met DMV's voor het controleren van het laden van gegevens.