Delen via

Aanbevolen procedures voor het laden van gegevens in een toegewezen 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 de opslaglaag en uw toegewezen SQL-pool instellen.

Wanneer u gegevens exporteert naar een ORC-bestandsindeling, krijgt u mogelijk geheugenfouten in Java wanneer er grote tekstkolommen zijn. Als u deze beperking wilt omzeilen, exporteert u alleen een subset van de kolommen.

PolyBase kan geen rijen laden met meer dan 1.000.000 bytes aan gegevens. Wanneer u gegevens in de tekstbestanden in Azure Blob Storage of Azure Data Lake Store plaatst, moeten ze minder dan 1000.000 bytes aan gegevens hebben. Deze bytebeperking geldt ongeacht het tabelschema.

Alle bestandsindelingen hebben verschillende prestatiekenmerken. Gebruik gecomprimeerde tekstbestanden die door scheidingstekens gescheiden zijn voor het snelste laden. Het verschil tussen UTF-8 en UTF-16 prestaties is minimaal.

Splitsen grote gecomprimeerde bestanden in kleinere gecomprimeerde bestanden.

Run loads with enough compute

Voor de snelste laadsnelheid voert u slechts één laadtaak tegelijk uit. Als dat niet haalbaar is, voert u een minimaal aantal taken gelijktijdig uit. Als u een grote laadtaak verwacht, kunt u overwegen om uw toegewezen SQL-pool vóór de belasting omhoog te schalen.

Als u werklasten wilt uitvoeren met de juiste rekenresources, maakt u gebruikers aan die zijn toegewezen voor het uitvoeren van deze taken. Wijs elke laadgebruiker toe aan een specifieke resourceklasse of workloadgroep. Om een lading uit te voeren, log in als een van de laadgebruikers en voer vervolgens de lading uit. De taak draait met de resourceklasse van de gebruiker. Deze methode is eenvoudiger dan proberen de resourceklasse van een gebruiker te wijzigen om te voldoen aan de behoefte van de huidige resourceklasse.

Een laadgebruiker maken

In dit voorbeeld wordt een laadgebruiker gemaakt die is geclassificeerd voor een specifieke workloadgroep. The first step is to connect to master and create a login.

   -- 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 mySampleDataWarehouse. Het laat zien hoe u een gebruiker maakt met de naam loader en de gebruiker machtigingen geeft om tabellen te maken en te laden met behulp van de COPY-instructie. Vervolgens classificeert het de gebruiker naar de werklastgroep DataLoads met maximale middelen.

   -- 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. Dit geeft u een maximale gelijktijdigheid van 1. Houd er rekening mee dat dit alleen moet worden gebruikt voor de eerste belasting waarbij u andere workloadgroepen moet maken met hun eigen configuraties om resources over uw workloads te verdelen.

To run a load with resources for the loading workload group, sign in as loader and run the load.

Toestaan dat meerdere gebruikers laden

Er is vaak behoefte aan meerdere gebruikers om gegevens in een datawarehouse te laden. Loading with the CREATE TABLE AS SELECT (Transact-SQL) requires CONTROL permissions of the database. De CONTROL-machtiging geeft beheertoegang tot alle schema's. Mogelijk wilt u niet dat alle ladende gebruikers toegang hebben tot alle schema's. Gebruik de instructie DENY CONTROL om machtigingen te beperken.

Denk bijvoorbeeld aan databaseschema's, schema_A voor afdeling A en schema_B voor afdeling B. Laat databasegebruikers respectievelijk user_A en user_B gebruikers zijn voor het laden van PolyBase in afdeling A en B. Ze hebben beide machtigingen gekregen voor de CONTROL-database. De makers van schema A en B vergrendelen nu hun schema's met behulp van 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.

Load to a staging table

Als u de snelste laadsnelheid voor het verplaatsen van gegevens naar een datawarehouse-tabel wilt bereiken, laadt u gegevens in een faseringstabel. Define the staging table as a heap and use round-robin for the distribution option.

Houd er rekening mee dat laden meestal een proces in twee stappen is waarbij u eerst in een faseringstabel laadt en vervolgens de gegevens in een productiedatawarehouse-tabel invoegt. Als de productietabel gebruikmaakt van een hash-distributie, kan de totale tijd die moet worden geladen en ingevoegd sneller zijn als u de faseringstabel definieert met de hash-distributie. Laden naar de faseringstabel duurt langer, maar de tweede stap van het invoegen van de rijen in de productietabel leidt niet tot gegevensverplaatsing over de distributies.

Load to a columnstore index

Columnstore-indexen vereisen grote hoeveelheden geheugen om gegevens te comprimeren in rijengroepen van hoge kwaliteit. Voor de beste compressie- en indexefficiëntie moet de columnstore-index het maximum van 1.048.576 rijen in elke rijgroep comprimeren. Wanneer er geheugendruk is, kan de columnstore-index mogelijk geen maximale compressiesnelheden bereiken. Dit heeft gevolgen voor queryprestaties. Zie Columnstore-geheugenoptimalisatiesvoor uitgebreide informatie.

  • Als u ervoor wilt zorgen dat de laadgebruiker voldoende geheugen heeft om maximale compressiesnelheden te bereiken, gebruikt u het laden van gebruikers die lid zijn van een middelgrote of grote resourceklasse.
  • Laad voldoende rijen om nieuwe rijgroepen volledig te vullen. During a bulk-load, every 1,048,576 rows get compressed directly into the columnstore as a full rowgroup. Ladingen met minder dan 102.400 rijen worden naar de deltastore verzonden, waar de rijen in een b-tree-index worden bewaard. Als u te weinig rijen laadt, gaan ze mogelijk allemaal naar de deltastore en worden ze niet direct gecomprimeerd naar het columnstore-formaat.

De batchgrootte vergroten bij het gebruik van de SQLBulkCopy-API of BCP

Loading with the COPY statement will provide the highest throughput with dedicated SQL pools. Als u de COPY niet kunt gebruiken om te laden en de SqLBulkCopy-API of bcp-moet gebruiken, kunt u overwegen om de batchgrootte te vergroten voor een betere doorvoer.

Hint

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

Laadfouten beheren

Een laadactie met behulp van een externe tabel kan mislukken met de fout "Query afgebroken-- de maximale afkeuringsdrempel is bereikt tijdens het lezen van een externe bron". Dit bericht geeft aan dat uw externe gegevens vuile records bevatten. Een gegevensrecord wordt als vuil beschouwd als de gegevenstypen en het aantal kolommen niet overeenkomen met de kolomdefinities van de externe tabel of als de gegevens niet voldoen aan de opgegeven externe bestandsindeling.

Als u de vuile records wilt herstellen, moet u ervoor zorgen dat de definities van de externe tabel en de externe bestandsindeling correct zijn en dat uw externe gegevens voldoen aan 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

A one-time load to a small table with an INSERT statement, or even a periodic reload of a look-up might perform good enough with a statement like INSERT INTO MyLookup VALUES (1, 'Type 1'). However, singleton inserts aren't as efficient as performing a bulk-load.

If you have thousands or more single inserts throughout the day, batch the inserts so you can bulk load them. Ontwikkel uw processen om de afzonderlijke invoegingen toe te voegen aan een bestand en maak vervolgens een ander proces waarmee het bestand periodiek wordt geladen.

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 belasting of grote wijzigingen in de gegevens. U kunt statistieken handmatig maken of u kunt statistieken automatisch makeninschakelen.

Zie Statisticsvoor een 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]);

Rotate storage keys

It's good security practice to change the access key to your blob storage regularly. U hebt twee opslagsleutels voor uw Blob Storage-account, waarmee u de sleutels kunt overschakelen.

To rotate Azure Storage account keys:

Voor elk opslagaccount waarvan de sleutel is gewijzigd, voert u ALTER DATABASE SCOPED CREDENTIALuit.

Voorbeeld:

De oorspronkelijke sleutel wordt gemaakt

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

Sleutel draaien van sleutel 1 naar sleutel 2

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

Er zijn geen andere wijzigingen in onderliggende externe gegevensbronnen nodig.

Verwante inhoud