Strategier för inlösning av data för dedikerad SQL-pool i Azure Synapse Analytics
Traditionella SMP-dedikerade SQL-pooler använder en process för extrahering, transformering och inläsning (ETL) 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 (extrahering, inläsning och transformering) utnyttjar inbyggda funktioner för distribuerad frågebearbetning och eliminerar de resurser som krävs för datatransformering före inläsning.
Dedikerade SQL-pooler stöder många inläsningsmetoder, inklusive populära SQL Server-alternativ som bcp och SqlBulkCopy API.Det snabbaste och mest skalbara sättet att läsa in data är 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åk. För största flexibilitet 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 till 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 självstudiekurs om inläsning finns i Läsa in data från Azure Blob Storage.
1. Extrahera källdata till textfiler
Att hämta data från källsystemet beror på lagringsplatsen. Målet är att flytta data till avgränsad 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-kodad avgränsad 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 komprimerade Gzip- och Snappy-filer.
Utökad ASCII, 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 bättre tillförlitlighet, snabbare hastigheter, kortare svarstider och högre säkerhet än vanliga anslutningar över offentligt Internet.
- AzCopy-verktyget flyttar data Azure Storage över 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ätverkshastigheten 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. Om du vill Data Factory med dedikerade SQL-pooler kan du läsa in data för dedikerade SQL pooler.
3. Förbered data för inläsning
Du kan behöva förbereda och rensa data i ditt lagringskonto innan du läser in dem. Förberedelse av data kan utföras när dina data finns i källan, när du exporterar data till textfiler eller när data finns i Azure Storage. Det är enklast att arbeta med data så tidigt i processen som möjligt.
Definiera tabellerna
Du måste först definiera de tabeller som du läser in till 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 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 för textfilerna och tabelldefinitionerna. De SQL syntaxreferensartiklar som du behöver är:
Använd följande mappning SQL datatyp vid inläsning av Parquet-filer:
| Parquet-typ | Logisk parquet-typ (anteckning) | SQL datatyp |
|---|---|---|
| BOOLEAN | bit | |
| BINÄR/BYTE_ARRAY | varbinary | |
| DOUBLE | flyt | |
| FLYTA | real | |
| INT32 | int | |
| INT64 | bigint | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binary | |
| BINARY | UTF8 | nvarchar |
| BINARY | STRÄNG | nvarchar |
| BINARY | ENUM | nvarchar |
| BINARY | UUID | uniqueidentifier |
| BINARY | DECIMAL | decimal |
| BINARY | JSON | nvarchar(MAX) |
| BINARY | BSON | varbinary(max) |
| FIXED_LEN_BYTE_ARRAY | DECIMAL | decimal |
| BYTE_ARRAY | INTERVALL | 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 | date |
| INT32 | DECIMAL | decimal |
| INT32 | TIME (MILLAS) | time |
| INT64 | INT(64, true) | bigint |
| INT64 | INT(64, false ) | decimal(20,0) |
| INT64 | DECIMAL | decimal |
| INT64 | TIME (MILLAS) | time |
| INT64 | TIDSSTÄMPEL (AMP) | datetime2 |
| Komplex typ | LISTA | varchar(max) |
| Komplex typ | KARTA | varchar(max) |
Viktigt
- SQL dedikerade pooler stöder för närvarande inte Parquet-datatyper med MICROS- och NANOS-precision.
- Följande fel kan uppstå om typerna är felmatchade mellan Parquet och SQL eller om du har Parquet-datatyper som inte stöds: "HdfsBridge::recordReaderFillBuffer – Oväntat fel uppstod vid fyllning av postläsarbufferten: ClassCastException: ..."
- Det går inte att läsa in ett värde utanför intervallet 0–127 i en tinyint-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 externa objekt som definierats justera raderna i textfilerna med den externa tabellen och filformatdefinitionen. Data på varje rad i textfilen måste justeras 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 omvandlas 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. Justeringsfel 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 avgränsare. Se till att använda ett tecken eller en teckensekvens som inte finns i dina källdata. Använd den avslutande som du angav med CREATE EXTERNAL FILE FORMAT.
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 SQL för parallell bearbetning av datatransformationer 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 kan läsa in data sömlöst och flexibelt. -instruktionen har många ytterligare inläsningsfunktioner som PolyBase inte tillhandahåller. Se självstudien OM TAXI COPY för att gå igenom en exempelse tutorial.
- PolyBase med T-SQL kräver att du definierar externa dataobjekt.
- PolyBase- och COPY-instruktion 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 SQL Server. SSIS definierar käll-till-måltabellmappningar och orkestrering av belastningen. Om du redan har SSIS-paket kan du ändra paketen så att de fungerar med det nya informationslagermålet.
- 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.
Andra inläsningsalternativ
Förutom PolyBase och COPY-instruktionen kan du använda bcp eller SQLBulkCopy API. bcp läser in direkt till databasen utan att gå via Azure Blob Storage och är endast avsett för små belastningar.
Anteckning
Inläsningsprestandan för dessa alternativ är långsammare än PolyBase och COPY-instruktionen.
5. Transformera data
När data finns i mellanlagringstabellen utför du transformningar 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 på ett litet testexempel. Prova att extrahera 1 000 rader från tabellen till en fil, flytta den till Azure och prova sedan att läsa in den i en mellanlagringstabell.
Inläsningslösningar för partner
Många av våra partner har inläsningslösningar. Mer information finns i en lista över våra lösningspartner.
Nästa steg
Vägledning för inläsning finns i Metodtips för inläsning av data.