Aanbevolen procedures voor het laden van gegevens in een toegewezen SQL-pool in Azure Synapse Analytics

• Start een gratis proefversie van Fabric.
• Migration Assistant voor het Fabric Data Warehouse.

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.

Workloads uitvoeren met voldoende rekenkracht

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. De eerste stap bestaat uit het maken van verbinding met de master en het maken van een aanmelding.

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

Om een lading uit te voeren met resources voor de laadwerklastgroep, meldt u zich aan als laadprogramma en voert u de lading uit.

Toestaan dat meerdere gebruikers laden

Er is vaak behoefte aan meerdere gebruikers om gegevens in een datawarehouse te laden. Laden met de CREATE TABLE AS SELECT (Transact-SQL) vereist CONTROL-machtigingen van de 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.

Laden naar een faseringstabel

Als u de snelste laadsnelheid voor het verplaatsen van gegevens naar een datawarehouse-tabel wilt bereiken, laadt u gegevens in een faseringstabel. Definieer de stagingtabel als een heap en gebruik round-robin als distributieoptie.

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.

Laden naar een 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. Tijdens bulksgewijs laden worden elke 1.048.576 rijen rechtstreeks in de columnstore gecomprimeerd als een volledige rijgroep. 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

Laden met de COPY-instructie zorgt voor de hoogste doorvoersnelheid bij speciale 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.

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

Een eenmalige belasting van een kleine tabel met een INSERT-instructie of zelfs een periodieke herlading van een look-up kan goed genoeg zijn 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 duizenden of meer individuele invoegingen hebt, moet u de invoegingen groeperen, zodat u ze in bulk kunt laden. 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]);

Opslagsleutels rouleren

Een goede beveiligingspraktijk is om de toegangssleutel van uw blobopslag regelmatig te wijzigen. U hebt twee opslagsleutels voor uw Blob Storage-account, waarmee u de sleutels kunt overschakelen.

Om de accountsleutels van Azure Storage te roteren:

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

• Zie Design ELT voor Azure Synapse Analyticsvoor meer informatie over PolyBase en het ontwerpen van een ELT-proces (Extract, Load and Transform).
• Voor een laadzelfstudie, gebruik PolyBase om gegevens uit Azure Blob Storage te laden naar Azure Synapse Analytics.
• Zie Uw workload bewaken met DMV's om de belasting van gegevens te bewaken.