Strategier för inlösning av data för dedikerad SQL-pool i Azure Synapse Analytics

  • Artikel

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 skalbarhet och flexibilitet för 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 inläsningen.

Dedikerade SQL-pooler stöder många inläsningsmetoder, inklusive populära SQL Server alternativ som bcp och SqlBulkCopy API, men det snabbaste och mest skalbara sättet att läsa in data är via polybase externa 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 att få största möjliga 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:

  1. Extrahera källdata till textfiler.
  2. Landa data i Azure Blob Storage eller Azure Data Lake Store.
  3. Förbered data för inläsning.
  4. Läs in data i mellanlagringstabeller med PolyBase eller kommandot COPY.
  5. Transformera data.
  6. 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 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ö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 båda 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ätverkshastigheten för att se om det är acceptabelt.
  • Azure Data Factory (ADF) har en gateway som du kan installera på den lokala servern. Sedan kan du skapa en pipeline för att flytta data från din lokala server 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ö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-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-datatypmappning när du läser in Parquet-filer:

Parquet-typ Logisk parquet-typ (kommentar) 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 datum
INT32 DECIMAL decimal
INT32 TIME (MILLIS ) time
INT64 INT(64, true) bigint
INT64 INT(64, false ) decimal(20,0)
INT64 DECIMAL decimal
INT64 TIME (MILLIS) time
INT64 TIDSSTÄMPEL (MILLIS) datetime2
Komplex typ LISTA varchar(max)
Komplex typ KARTA varchar(max)

Viktigt

  • Dedikerade SQL-pooler stöder för närvarande inte Parquet-datatyper med MICROS- och NANOS-precision.
  • Du kan få följande fel om typerna är felmatchade mellan Parquet och SQL eller om du 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 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 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 relationsbaserad eller icke-relationell källa måste data omvandlas för att överensstämma 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 datatyperna i de externa textfilerna och den dedikerade SQL-pooltabellen gör att rader avvisas under inläsningen.
  • Avgränsa fälten 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.

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 arkitekturen för parallell bearbetning av 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älvstudiekursen KOPIERA NY-taxi 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 orkestrerar även 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:et. bcp läses in direkt till databasen utan att gå via Azure Blob Storage och är endast avsedd 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 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 prova sedan att 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.

Nästa steg

Information om inläsningsvägledning finns i Metodtips för datainläsning.