Strategier för inlösning av data för dedikerad SQL-pool i Azure Synapse Analytics
Traditionella SMP-dedikerade SQL-pooler använder en ETL-process (Extract, Transform, and Load) för inläsning av data. Synapse SQL i Azure Synapse Analytics använder distribuerad frågebearbetningsarkitektur som drar nytta av skalbarheten och flexibiliteten hos beräknings- och lagringsresurser.
Med hjälp av en ELT-process (Extract, Load, and Transform) används inbyggda funktioner för distribuerad frågebearbetning och eliminerar de resurser som behövs för datatransformering innan de läses in.
Även om dedikerade SQL-pooler stöder många inläsningsmetoder, inklusive populära SQL Server-alternativ som bcp och SQLBulkCopy-API:et, är det snabbaste och mest skalbara sättet att läsa in data via externa PolyBase-tabeller och COPY-instruktionen.
Med PolyBase och COPY-instruktionen kan du komma åt externa data som lagras i Azure Blob Storage eller Azure Data Lake Store via T-SQL-språket. För den största flexibiliteten vid inläsning rekommenderar vi att du använder COPY-instruktionen.
Vad är ELT?
Extrahering, inläsning och transformering (ELT) är en process genom vilken data extraheras från ett källsystem, läses in i en dedikerad SQL-pool och sedan transformeras.
De grundläggande stegen för att implementera ELT är:
- Extrahera källdata till textfiler.
- Landa data i Azure Blob Storage eller Azure Data Lake Store.
- Förbered data för inläsning.
- Läs in data i mellanlagringstabeller med PolyBase eller kommandot COPY.
- Transformera data.
- Infoga data i produktionstabeller.
En inläsningsguide finns i Läsa in data från Azure Blob Storage.
1. Extrahera källdata i textfiler
Hur du hämtar data från källsystemet beror på lagringsplatsen. Målet är att flytta data till avgränsade text- eller CSV-filer som stöds.
Filformat som stöds
Med PolyBase och COPY-instruktionen kan du läsa in data från UTF-8- och UTF-16-kodade avgränsade text- eller CSV-filer. Förutom avgränsad text eller CSV-filer läses den in från Hadoop-filformat som ORC och Parquet. PolyBase och COPY-instruktionen kan också läsa in data från Gzip- och Snappy-komprimerade filer.
Utökade ASCII-format, format med fast bredd och kapslade format som WinZip eller XML stöds inte. Om du exporterar från SQL Server kan du använda kommandoradsverktyget bcp för att exportera data till avgränsade textfiler.
2. Landa data i Azure Blob Storage eller Azure Data Lake Store
Om du vill landa data i Azure Storage kan du flytta dem till Azure Blob Storage eller Azure Data Lake Store Gen2. På båda platserna ska data lagras i textfiler. PolyBase och COPY-instruktionen kan läsas in från någon av platserna.
Verktyg och tjänster som du kan använda för att flytta data till Azure Storage:
- Azure ExpressRoute-tjänsten förbättrar nätverkets dataflöde, prestanda och förutsägbarhet. ExpressRoute är en tjänst som dirigerar dina data via en dedikerad privat anslutning till Azure. ExpressRoute-anslutningar dirigerar inte data via det offentliga Internet. Anslutningarna ger mer tillförlitlighet, snabbare hastigheter, kortare svarstider och högre säkerhet än vanliga anslutningar via det offentliga Internet.
- AzCopy-verktyget flyttar data till Azure Storage via det offentliga Internet. Detta fungerar om dina datastorlekar är mindre än 10 TB. Om du vill utföra belastningar regelbundet med AzCopy testar du nätverkets hastighet för att se om det är acceptabelt.
- Azure Data Factory (ADF) har en gateway som du kan installera på din lokala server. Sedan kan du skapa en pipeline för att flytta data från din lokala server upp till Azure Storage. Information om hur du använder Data Factory med dedikerade SQL-pooler finns i Läsa in data för dedikerade SQL-pooler.
3. Förbereda data för inläsning
Du kan behöva förbereda och rensa data i ditt lagringskonto innan du läser in dem. Dataförberedelser kan utföras när dina data finns i källan, när du exporterar data till textfiler eller efter att data finns i Azure Storage. Det är enklast att arbeta med data så tidigt som möjligt i processen.
Definiera tabellerna
Definiera först de tabeller som du läser in i din dedikerade SQL-pool när du använder COPY-instruktionen.
Om du använder PolyBase måste du definiera externa tabeller i din dedikerade SQL-pool innan du läser in. PolyBase använder externa tabeller för att definiera och komma åt data i Azure Storage. En extern tabell liknar en databasvy. Den externa tabellen innehåller tabellschemat och pekar på data som lagras utanför den dedikerade SQL-poolen.
Att definiera externa tabeller innebär att ange datakällan, formatet på textfilerna och tabelldefinitionerna. Referensartiklar för T-SQL-syntax som du behöver är:
Använd följande SQL-datatypsmappning vid inläsning av Parquet-filer:
| Parquet-typ | Parquet logisk typ (anteckning) | SQL-datatyp |
|---|---|---|
| BOOLESK | bit | |
| BINÄR / BYTE_ARRAY | varbinary | |
| DUBBEL | flyta | |
| FLYTA | verklig | |
| INT32 | Int | |
| INT64 | bigint | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binär | |
| BINÄR | UTF8 |
nvarchar |
| BINÄR | STRING |
nvarchar |
| BINÄR | ENUM |
nvarchar |
| BINÄR | UUID |
uniqueidentifier |
| BINÄR | DECIMAL |
decimal |
| BINÄR | JSON |
nvarchar(MAX) |
| BINÄR | BSON |
varbinary(MAX) |
| FIXED_LEN_BYTE_ARRAY | DECIMAL |
decimal |
| BYTE_ARRAY | INTERVAL |
varchar(MAX) |
| INT32 | INT(8, true) |
smallint |
| INT32 | INT(16, true) |
smallint |
| INT32 | INT(32, true) |
Int |
| INT32 | INT(8, false) |
tinyint |
| INT32 | INT(16, false) |
Int |
| INT32 | INT(32, false) |
bigint |
| INT32 | DATE |
datum |
| INT32 | DECIMAL |
decimal |
| INT32 | TIME (MILLIS) |
Tid |
| INT64 | INT(64, true) |
bigint |
| INT64 | INT(64, false ) |
decimal(20,0) |
| INT64 | DECIMAL |
decimal |
| INT64 | TIME (MILLIS) |
Tid |
| INT64 | TIMESTAMP (MILLIS) |
datetime2 |
| Komplex typ | LIST |
varchar(max) |
| Komplex typ | MAP |
varchar(max) |
Viktigt!
- SQL-dedikerade pooler stöder för närvarande inte Parquet-datatyper med MICROS- och NANOS-precision.
- Du kan få följande fel om typerna är matchningsfel mellan Parquet och SQL eller om du inte har parquet-datatyper som inte stöds:
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:... - Det går inte att läsa in ett värde utanför intervallet 0–127 i en liten kolumn för Filformatet Parquet och ORC.
Ett exempel på hur du skapar externa objekt finns i Skapa externa tabeller.
Formatera textfiler
Om du använder PolyBase måste de definierade externa objekten justera raderna i textfilerna med definitionen för extern tabell och filformat. Data på varje rad i textfilen måste överensstämma med tabelldefinitionen.
Så här formaterar du textfilerna:
- Om dina data kommer från en icke-relationell källa måste du omvandla dem till rader och kolumner. Oavsett om data kommer från en relationskälla eller en icke-relationell källa måste data transformeras så att de överensstämmer med kolumndefinitionerna för den tabell som du planerar att läsa in data i.
- Formatera data i textfilen så att de överensstämmer med kolumnerna och datatyperna i måltabellen. Feljustering mellan datatyper i de externa textfilerna och den dedikerade SQL-pooltabellen gör att rader avvisas under inläsningen.
- Avgränsa fält i textfilen med en avslutare. Se till att använda ett tecken eller en teckensekvens som inte finns i dina källdata. Använd den avslutare som du angav med CREATE EXTERNAL FILE FORMAT (SKAPA EXTERNT FILFORMAT).
4. Läs in data med PolyBase eller COPY-instruktionen
Det är bästa praxis att läsa in data i en mellanlagringstabell. Med mellanlagringstabeller kan du hantera fel utan att störa produktionstabellerna. En mellanlagringstabell ger dig också möjlighet att använda den dedikerade parallellbearbetningsarkitekturen för SQL-pooler för datatransformeringar innan du infogar data i produktionstabeller.
Alternativ för inläsning
Om du vill läsa in data kan du använda något av följande inläsningsalternativ:
- COPY-instruktionen är det rekommenderade inläsningsverktyget eftersom det gör att du smidigt och flexibelt kan läsa in data. Instruktionen har många ytterligare inläsningsfunktioner som PolyBase inte tillhandahåller. Se självstudien NY taxi cab COPY för att gå igenom en exempelsjälvstudie.
- PolyBase med T-SQL kräver att du definierar externa dataobjekt.
- PolyBase- och COPY-instruktionen med Azure Data Factory (ADF) är ett annat orkestreringsverktyg. Den definierar en pipeline och schemalägger jobb.
- PolyBase med SSIS fungerar bra när dina källdata finns i SQL Server. SSIS definierar käll-till-måltabellmappningar och dirigerar även belastningen. Om du redan har SSIS-paket kan du ändra paketen så att de fungerar med det nya informationslagrets mål.
- PolyBase med Azure Databricks överför data från en tabell till en Databricks-dataram och/eller skriver data från en Databricks-dataram till en tabell med PolyBase.
Granska tillgängliga självstudier:
- Självstudie: Läsa in externa data med Hjälp av Microsoft Entra-ID
- Självstudie: Läsa in externa data med hjälp av en hanterad identitet
- Självstudie: Läs in datauppsättningen New York Taxicab
- Självstudie: Läsa in data till Azure Synapse Analytics SQL-pool
- Läs in Contosos detaljhandelsdata i dedikerade SQL-pooler i Azure Synapse Analytics
Andra inläsningsalternativ
Förutom PolyBase och COPY-instruktionen kan du använda bcp eller SQLBulkCopy-API:et. Verktyget bcp läses in direkt till databasen utan att gå igenom Azure Blob Storage och är endast avsett för små belastningar.
Kommentar
Inläsningsprestandan för de här alternativen är långsammare än PolyBase och COPY-instruktionen.
5. Transformera data
Medan data finns i mellanlagringstabellen utför du transformeringar som din arbetsbelastning kräver. Flytta sedan data till en produktionstabell.
6. Infoga data i produktionstabeller
INFOGA I ... SELECT-instruktionen flyttar data från mellanlagringstabellen till den permanenta tabellen.
När du utformar en ETL-process kan du prova att köra processen i ett litet testexempel. Prova att extrahera 1 000 rader från tabellen till en fil, flytta den till Azure och försök sedan läsa in den i en mellanlagringstabell.
Partnerinläsningslösningar
Många av våra partners har inläsningslösningar. Mer information finns i en lista över våra lösningspartners.