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

Tips/Råd

Microsoft Fabric Data Warehouse är ett relationslager i företagsskala på en datasjögrund med en framtidsklar arkitektur, inbyggd AI och nya funktioner. Om du är nybörjare på datalager börjar du med Fabric Data Warehouse. Befintliga dedicerade SQL-poolarbetsbelastningar kan uppgraderas till Fabric för att få åtkomst till nya funktioner inom datavetenskap, realtidsanalys och rapportering.

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

Förbereda data i Azure Storage

För att minimera svarstiden kan du samplacera ditt lagringslager och din dedikerade SQL-pool.

När du exporterar data till det ORC-filformatet kan du få Java-minnesfel när det finns stora textkolumner. Om du vill kringgå den här begränsningen exporterar du bara en delmängd av kolumnerna.

PolyBase kan inte läsa in rader som har mer än 1 000 000 byte data. När du placerar data i textfilerna i Azure Blob Storage eller Azure Data Lake Store måste de ha färre än 1 000 000 byte data. Den här bytebegränsningen gäller oavsett tabellschema.

Alla filformat har olika prestandaegenskaper. Använd komprimerade avgränsade textfiler för den snabbaste belastningen. Skillnaden mellan UTF-8- och UTF-16-prestanda är minimal.

Dela upp stora komprimerade filer i mindre komprimerade filer.

Köra arbetslaster med tillräcklig beräkningskraft

Kör bara ett inläsningsjobb i taget för att uppnå högsta inläsningshastighet. Om det 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.

Om du vill köra inläsningar med lämpliga beräkningsresurser skapar du inläsningsanvändare som är avsedda för att köra belastningar. Tilldela varje inläsningsanvändare till en specifik resursklass eller arbetsbelastningsgrupp. Logga in som en av lastanvändarna och kör sedan lasten. Belastningen 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 resursklassbehovet.

Skapa en laddningsanvändare

I det här exemplet skapas en användare för inläsning som tilldelas 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å hur du allokerar 100% resurser i SQL-poolen till en enda belastning. Detta ger dig maximal samtidighet på 1. Tänk på att detta endast bör användas för den inledande belastningen där du behöver skapa andra 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 flera användare att ladda upp

Det finns ofta ett behov av att flera användare läser in data till ett informationslager. Inläsning med CREATE TABLE AS SELECT (Transact-SQL) kräver KONTROLLbehörigheter för databasen. Behörigheten CONTROL ger kontrollåtkomst till alla scheman. Du kanske inte vill att alla inläsningsanvändare ska ha kontrollåtkomst för alla scheman. Om du vill begränsa behörigheter använder du instruktionen DENY CONTROL.

Du kan till exempel överväga databasscheman, schema_A för dept A och schema_B för dept B. Låt databasanvändare user_A och user_B vara användare för PolyBase-inläsning i dept A respektive B. Båda har beviljats behörigheter för CONTROL-databasen. Skaparna av schema A och B låser nu ner sina scheman genom att använda NEKA:

   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åsta från den andra avdelningens schema.

Ladda till en stagingtabell

För att uppnå den snabbaste inläsningshastigheten för att flytta data till en informationslagertabell läser du in data i en mellanlagringstabell. Definiera mellanlagringstabellen som en heap och använd round-robin för distributionsalternativet.

Tänk på att laddning vanligtvis är en tvåstegsprocess där du först läser in till en staging-tabell och sedan infogar data i en produktionsdatalager-tabell. Om produktionstabellen använder en hash-distribution kan den totala tiden för inläsning och infogning vara snabbare om du definierar stagingtabellen med en hash-distribution. Inläsningen till mellanlagringstabellen tar längre tid, men det andra steget för att infoga raderna i produktionstabellen medför inte dataförflyttningar mellan distributionerna.

Ladda till ett columnstore-index

Kolumnlagringsindex kräver stora mängder minne för att komprimera data till högkvalitativa radgrupper. För bästa komprimerings- och indexeffektivitet måste kolumnlagringsindexet komprimera högst 1 048 576 rader till varje radgrupp. När det finns minnestryck kanske kolumnlagringsindexet inte kan uppnå maximal komprimeringshastighet. Detta påverkar frågeprestanda. För en djupdykning, se Columnstore minnesoptimeringar.

  • För att säkerställa att inläsningsanvändaren har tillräckligt med minne för att uppnå maximala komprimeringshastigheter använder du inläsningsanvändare som är medlemmar i en medelhög eller stor resursklass.
  • Läs in tillräckligt många rader för att helt fylla nya radgrupper. Vid massinläsning komprimeras varje 1 048 576 rad direkt till kolumnlagret som en fullständig radgrupp. Inläsningar med färre än 102 400 rader skickar raderna till deltastore där rader lagras i ett B-trädindex. Om du läser in för få rader kan alla gå till deltaarkivet och inte komprimeras omedelbart till kolumnlagringsformat.

Ö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/Råd

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

Hantera inläsningsfel

En inläsning med hjälp av en extern tabell kan misslyckas med felet "Frågan avbröts– det högsta tröskelvärdet för avvisande uppnåddes vid läsning från en extern källa". Det här meddelandet anger att dina externa data innehåller smutsiga register. En datapost anses vara smutsig om datatyperna och antalet kolumner inte matchar kolumndefinitionerna i den externa tabellen, eller om data inte överensstämmer med det angivna externa filformatet.

Om du vill åtgärda de felaktiga posterna kontrollerar du att definitionerna för den externa tabellen och det externa filformatet är korrekta och att dina externa data överensstämmer med dessa definitioner. Om en delmängd av externa dataposter är felaktiga kan du välja att avvisa dessa poster i dina frågeställningar genom att använda avvisningsalternativet i SKAPA EXTERN TABELL .

Infoga data i en produktionstabell

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

Om du har tusentals eller fler enskilda infogningar under dagen kan du sätta ihop dem i batchar så att du kan läsa in dem i bulk. Utveckla dina processer för att lägga till de enskilda infogningarna i en fil och skapa sedan en annan process som regelbundet läser in filen.

Skapa statistik efter belastningen

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 att större ändringar sker i data. Du kan skapa statistik manuellt eller så kan du aktivera statistik för automatisk skapande.

En detaljerad förklaring av statistik finns i Statistik. I följande exempel visas hur du manuellt skapar statistik på fem kolumner i 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 bra säkerhetspraxis att ändra åtkomstnyckeln till bloblagringen regelbundet. Du har två lagringsnycklar för ditt bloblagringskonto, vilket gör att du kan ö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:

Ursprunglig nyckel skapas

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

Rotera nyckeln från nyckel 1 till nyckel 2

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

Inga andra ändringar av underliggande externa datakällor behövs.

Relaterat innehåll

  • Last updated on