Dela via

Metodtips för att läsa in data i en dedikerad SQL-pool i Azure Synapse Analytics

  • Artikel

I den här artikeln hittar du rekommendationer och prestandaoptimeringar för inläsning av data.

Förbereda data i Azure Storage

Minimera svarstiden genom att samplacera ditt lagringslager och din dedikerade SQL-pool.

När du exporterar data till ett ORC-filformat kan du råka ut för ”slut på minne”-fel i Java när det finns kolumner med mycket text. Du kan undvika denna begränsning genom att bara exportera en del av kolumnerna.

PolyBase kan inte läsa in rader som har mer än 1 000 000 byte data. När du placerar data i textfiler i Azure Blob Storage eller Azure Data Lake Store måste dessa data vara mindre än 1 000 000 byte. Den här begränsningen av byte gäller oavsett tabellschemat.

Alla filformat har olika prestandaegenskaper. Den snabbaste inläsningen får du om du använder komprimerade avgränsade textfiler. Skillnaden i prestanda mellan UTF-8 och UTF-16 är minimal.

Dela upp stora komprimerade filer i små komprimerade filer.

Köra inläsningar med tillräckligt med beräkning

För högsta hastighet för inläsning, kör du bara ett inläsningsjobb i taget. Om detta inte är möjligt, kör du ett minimalt antal belastningar samtidigt. Om du förväntar dig ett stort inläsningsjobb bör du överväga att skala upp din dedikerade SQL-pool före belastningen.

För att köra inläsningar med lämpliga beräkningsresurser skapar du inläsningsanvändare som är avsedda att köra inläsningar. Tilldela varje inläsningsanvändare till en specifik resursklass eller arbetsbelastningsgrupp. Om du vill köra en inläsning loggar du in som en av inläsningsanvändaren och kör sedan belastningen. Inläsningen körs med användarens resursklass. Den här metoden är enklare än att försöka ändra en användares resursklass så att den passar det aktuella behovet av resursklass.

Skapa en inläsningsanvändare

I det här exemplet skapas en inläsningsanvändare som klassificerats till en specifik arbetsbelastningsgrupp. Det första steget är att ansluta till huvudservern och skapa en inloggning.

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

Anslut till den dedikerade SQL-poolen och skapa en användare. Följande kod förutsätter att du är ansluten till databasen mySampleDataWarehouse. Den visar hur du skapar en användare med namnet loader och ger användaren behörighet att skapa tabeller och läsa in med copy-instruktionen. Sedan klassificeras användaren till arbetsbelastningsgruppen DataLoads med maximalt antal resurser.

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



Viktigt

Det här är ett extremt exempel på att allokera 100 % resurser i SQL-poolen till en enda belastning. Detta ger dig maximal samtidighet på 1. Tänk på att detta endast ska användas för den inledande belastningen där du behöver skapa ytterligare arbetsbelastningsgrupper med egna konfigurationer för att balansera resurser mellan dina arbetsbelastningar.

Om du vill köra en belastning med resurser för arbetsbelastningsgruppen för inläsning loggar du in som inläsare och kör belastningen.

Tillåt att flera användare läser in

Det finns ofta ett behov av att ha flera användare som kan läsa in data i informationslagret. Inläsning med CREATE TABLE AS SELECT (Transact-SQL) kräver KONTROLL-behörigheter för databasen. CONTROL-behörigheten ger kontrollbehörighet till alla scheman. Du kanske inte vill att alla användare som läser in ska ha behörighet för alla scheman. Om du vill begränsa behörigheten använder du DENY CONTROL-instruktionen.

Anta att du har följande databasscheman: schema_A för avdelning A och schema_B för avdelning B. Då låter du användare_A och användare_B vara användare för PolyBase-inläsning i avdelning A respektive avdelning B. Båda har beviljats kontrollbehörigheter till databasen. De som skapat schema_A och B låser nu deras scheman med DENY:

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

User_A och user_B är nu utelåst från den andra avptens schema.

Inläsning i en mellanlagringstabell

För att uppnå högsta inläsningshastighet vid flytt av data till en informationslagertabell, läs in data i en mellanlagringstabellen. Definiera mellanlagringstabellen som en heap och använd resursallokering som distributionsalternativ.

Överväg att inläsning vanligtvis är en tvåstegsprocess där du först läser in till en mellanlagringstabellen och sedan infogar data i en informationslagertabell för produktion. Om produktionstabellen använder en hash-distribution, kan den totala tiden för att läsa in och infoga bli snabbare om du definierar mellanlagringstabellen i hash-distributionen. Inläsning till mellanlagringstabellen tar längre tid, men det andra steget i att infoga rader i produktionstabellen skapar inte dataförflyttning över distributioner.

Läsa in till ett kolumnlagringsindex

Kolumnlagringsindex kräver en stor mängd minne för att komprimera data i högkvalitativa radgrupper. För bästa komprimerings- och indexeffektivitet behöver kolumnlagringsindexet komprimera högst 1 048 576 rader till varje radgrupp. Vid brist på minne kanske kolumnlagringsindexet inte kan uppnå den maximala komprimeringsgraden. Detta påverkar frågeprestanda. Mer detaljer finns i Minnesoptimering för kolumnlagring.

  • För att säkerställa att inläsningsanvändaren har tillräckligt med minne för att uppnå maximal komprimeringsgrad ska du använda inläsningsanvändare som är medlemmar i en mellanstor eller stor resursklass.
  • Läs in tillräckligt med rader för att helt fylla de nya radgrupperna. Vid massinläsning komprimeras var 1 048 576 rad direkt i kolumnlagringen som en fullständig radgrupp. Belastningar med färre än 102 400 rader skickar raderna till deltastore där raderna förvaras i ett b-trädindex. Om du läser in för få rader kan alla rader hamna i deltalagringen och inte bli komprimerade direkt i kolumnlagringsformatet.

Öka batchstorleken när du använder SQLBulkCopy API eller BCP

Inläsning med COPY-instruktionen ger det högsta dataflödet med dedikerade SQL-pooler. Om du inte kan använda COPY för att läsa in och måste använda SqLBulkCopy-API :et eller bcp bör du överväga att öka batchstorleken för bättre dataflöde.

Tips

En batchstorlek mellan 100 och 1 miljon rader är den rekommenderade baslinjen för att fastställa optimal batchstorlekskapacitet.

Hantera inläsningsfel

Vid en inläsning med en extern tabell kan ett felmeddelande som ser ut ungefär så här visas: "Frågan avbröts. Det högsta tröskelvärdet för avslag nåddes vid inläsning från en extern källa". Detta meddelande anger att dina externa data innehåller ändrade poster. En datapost anses vara ändrade om datatyperna och antalet kolumner inte matchar kolumndefinitionen för den externa tabellen eller om data inte följer det angivna externa filformatet.

Du kan åtgärda de ändrade posterna genom att se till att definitionerna för den externa tabellen och det externa filformatet är korrekta och att dina externa data följer dessa definitioner. Om en delmängd av externa dataposter är felaktiga kan du välja att avvisa dessa poster för dina frågor genom att använda avvisningsalternativen i SKAPA EXTERN TABELL .

Infoga data i en produktionstabell

En engångsinläsning i en liten tabell med en INSERT-instruktion eller till och med en regelbunden inläsning på nytt av en sökning kan fungera tillräckligt bra för dina behov med en instruktion som INSERT INTO MyLookup VALUES (1, 'Type 1'). Singleton-infogningar är dock inte lika effektiva som att utföra en massinläsning.

Om du har tusentals eller fler enskilda infogningar under dagen bör du gruppera dem så att du kan infoga dem med en massinläsning. Utveckla dina processer så att enskilda infogningar bifogas i en fil och skapa sedan en annan process som regelbundet läser in filen.

Skapa statistik efter inläsningen

För att förbättra frågeprestandan är det viktigt att skapa statistik för alla kolumner i alla tabeller efter den första inläsningen, eller större ändringar i data. Skapa statistik kan göras manuellt eller så kan du aktivera automatisk skapande av statistik.

En detaljerad förklaring av statistik finns i Statistik. I följande exempel visas hur du manuellt skapar statistik för fem kolumner i den Customer_Speed tabellen.

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

Rotera lagringsnycklar

Det är en bra säkerhetsrutin att regelbundet ändra åtkomstnyckeln till din Blob Storage. Du har två lagringsnycklar för ditt blob storage-konto, som gör det möjligt att överföra nycklarna.

Så här roterar du Azure Storage-kontonycklar:

För varje lagringskonto vars nyckel har ändrats utfärdar du ALTER DATABASE SCOPED CREDENTIAL.

Exempel:

Den ursprungliga nyckeln skapas

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

Rotera nyckel från nyckel 1 till nyckel 2

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

Det behövs inga andra ändringar i underliggande externa datakällor.

Nästa steg