Strategieën voor het laden van gegevens voor toegewezen SQL-pool in Azure Synapse Analytics

  • Artikel
  • 7 minuten om te lezen

Traditionele SMP-SQL gebruiken een ETL-proces (Extract, Transform, and Load) voor het laden van gegevens. Synapse SQL maakt binnen Azure Synapse Analytics gebruik van een gedistribueerde queryverwerkingsarchitectuur die gebruikmaakt van de schaalbaarheid en flexibiliteit van reken- en opslagresources.

Met behulp van een ELT-proces (Extraheren, laden en transformeren) worden ingebouwde mogelijkheden voor gedistribueerde queryverwerking gebruikt en worden de resources geëlimineerd die nodig zijn voor gegevenstransformatie vóór het laden.

Hoewel toegewezen SQL-pools ondersteuning bieden voor veel laadmethoden, waaronder populaire SQL Server-opties zoals bcp en de SqlBulkCopy-API,is de snelste en meest schaalbare manier om gegevens te laden via externe PolyBase-tabellen en de COPY-instructie.

Met PolyBase en de COPY-instructie hebt u toegang tot externe gegevens die zijn opgeslagen in Azure Blob Storage of Azure Data Lake Store via de taal T-SQL. Voor de meeste flexibiliteit bij het laden raden we u aan de instructie COPY te gebruiken.

Wat is ELT?

Extraheren, laden en transformeren (ELT) is een proces waarmee gegevens worden geëxtraheerd uit een bronsysteem, in een toegewezen SQL-pool worden geladen en vervolgens getransformeerd.

De basisstappen voor het implementeren van ELT zijn:

  1. Extraheer de brongegevens naar tekstbestanden.
  2. De gegevens in Azure Blob Storage of Azure Data Lake Store.
  3. Bereid de gegevens voor op het laden.
  4. Laad de gegevens in faseringstabellen met PolyBase of de opdracht COPY.
  5. De gegevens transformeren.
  6. Voeg de gegevens in productietabellen in.

Zie Gegevens laden vanuit Azure Blob Storage voor een zelfstudie over het laden van gegevens.

1. De brongegevens uitpakken in tekstbestanden

Het verwijderen van gegevens uit uw bronsysteem is afhankelijk van de opslaglocatie. Het doel is om de gegevens te verplaatsen naar ondersteunde tekst- of CSV-bestanden met scheidingstekens.

Ondersteunde bestandsindelingen

Met PolyBase en de COPY-instructie kunt u gegevens laden uit UTF-8- en UTF-16-gecodeerde tekst- of CSV-bestanden. Naast tekst- of CSV-bestanden met scheidingstekens wordt deze geladen vanuit de Hadoop-bestandsindelingen zoals ORC en Parquet. PolyBase en de COPY-instructie kunnen ook gegevens laden uit gecomprimeerde Gzip- en Snappy-bestanden.

Uitgebreide ASCII-indelingen, indelingen met een vaste breedte en geneste indelingen, zoals WinZip of XML, worden niet ondersteund. Als u vanuit een SQL Server exporteert, kunt u het opdrachtregelprogramma BCP gebruiken om de gegevens te exporteren naar tekstbestanden met scheidingstekens.

2. De gegevens in Azure Blob Storage of Azure Data Lake Store

Als u de gegevens in Azure Storage wilt opslaan, kunt u deze verplaatsen naar Azure Blob Storage of Azure Data Lake Store Gen2. Op beide locaties moeten de gegevens worden opgeslagen in tekstbestanden. PolyBase en de COPY-instructie kunnen vanaf beide locaties worden geladen.

Hulpprogramma's en services die u kunt gebruiken om gegevens te verplaatsen naar Azure Storage:

  • Azure ExpressRoute-service verbetert de netwerkdoorvoer, prestaties en voorspelbaarheid. ExpressRoute is een service die uw gegevens routeert via een toegewezen privéverbinding naar Azure. ExpressRoute-verbindingen leiden geen gegevens door via het openbare internet. De verbindingen bieden meer betrouwbaarheid, hogere snelheden, lagere latentie en hogere beveiliging dan gewone verbindingen via het openbare internet.
  • Het AzCopy-hulpprogramma verplaatst gegevens naar Azure Storage via het openbare internet. Dit werkt als uw gegevens kleiner zijn dan 10 TB. Als u regelmatig belasting wilt uitvoeren met AzCopy, test u de netwerksnelheid om te zien of deze acceptabel is.
  • Azure Data Factory (ADF) heeft een gateway die u op uw lokale server kunt installeren. Vervolgens kunt u een pijplijn maken om gegevens van uw lokale server naar de Azure Storage. Zie Loading Data Factory data for dedicated SQL pools (Gegevens laden voor toegewezen SQL-pools)voor meer informatie over het gebruik SQL toegewezen groepen.

3. De gegevens voorbereiden voor het laden

Mogelijk moet u de gegevens in uw opslagaccount voorbereiden en ops schonen voordat u ze laadt. Gegevensvoorbereiding kan worden uitgevoerd terwijl uw gegevens zich in de bron, wanneer u de gegevens exporteert naar tekstbestanden of nadat de gegevens zich in de Azure Storage. Het is het gemakkelijkst om zo vroeg mogelijk in het proces met de gegevens te werken.

De tabellen definiëren

U moet eerst de tabel(en) definiëren waarin u laadt in uw toegewezen SQL pool wanneer u de COPY-instructie gebruikt.

Als u PolyBase gebruikt, moet u externe tabellen definiëren in uw toegewezen SQL pool voordat u deze laadt. PolyBase maakt gebruik van externe tabellen voor het definiëren en openen van de gegevens in Azure Storage. Een externe tabel is vergelijkbaar met een databaseweergave. De externe tabel bevat het tabelschema en wijst naar gegevens die buiten de toegewezen SQL opgeslagen.

Het definiëren van externe tabellen omvat het opgeven van de gegevensbron, de indeling van de tekstbestanden en de tabeldefinities. Naslagartikelen SQL T-SQL syntaxis die u nodig hebt, zijn:

Gebruik de volgende SQL gegevenstypetoewijzing bij het laden van Parquet-bestanden:

Parquet-type Logisch type van Parquet (annotatie) SQL-gegevenstype
BOOLEAN bit
BINARY / BYTE_ARRAY varbinary
DOUBLE float
FLOAT werkelijk
INT32 int
INT64 bigint
INT96 datetime2
FIXED_LEN_BYTE_ARRAY binair
BINARY UTF8 nvarchar
BINARY STRING 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 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 date
INT32 DECIMAL decimal
INT32 TIME (MILLIS ) tijd
INT64 INT(64, true) bigint
INT64 INT(64, false ) decimal(20,0)
INT64 DECIMAL decimal
INT64 TIME (MILLIS) tijd
INT64 TIJDSTEMPEL (UURSTEMPELS) datetime2
Complex type LIST varchar(max)
Complex type MAP varchar(max)

Belangrijk

  • SQL toegewezen pools bieden momenteel geen ondersteuning voor Parquet-gegevenstypen met MICROS- en NANOS-precisie.
  • U kunt de volgende fout tegenkomen als de typen niet overeenkomen tussen Parquet en SQL of als u niet-ondersteunde Parquet-gegevenstypen hebt: "HdfsBridge::recordReaderFillBuffer - Onverwachte fout opgetreden bij het invullen van de buffer voor de recordlezer: ClassCastException: ..."
  • Het laden van een waarde buiten het bereik van 0-127 in een tinyint-kolom voor parquet- en ORC-bestandsindeling wordt niet ondersteund.

Zie Externe tabellen maken voor een voorbeeld van het maken van externe objecten.

Tekstbestanden opmaken

Als u PolyBase gebruikt, moeten de gedefinieerde externe objecten de rijen van de tekstbestanden uitlijnen met de definitie van de externe tabel en bestandsindeling. De gegevens in elke rij van het tekstbestand moeten worden uitgelijnd met de tabeldefinitie. De tekstbestanden opmaken:

  • Als uw gegevens afkomstig zijn van een niet-relationele bron, moet u deze transformeren in rijen en kolommen. Of de gegevens nu afkomstig zijn van een relationele of niet-relationele bron, de gegevens moeten worden getransformeerd om ze uit te lijnen met de kolomdefinities voor de tabel waarin u de gegevens wilt laden.
  • Maak gegevens op in het tekstbestand om ze uit te lijnen met de kolommen en gegevenstypen in de doeltabel. Onjuiste uitlijning tussen gegevenstypen in de externe tekstbestanden en de toegewezen SQL pooltabel zorgt ervoor dat rijen worden geweigerd tijdens het laden.
  • Scheid velden in het tekstbestand met een eind. Zorg ervoor dat u een tekenreeks of tekenreeks gebruikt die niet in uw brongegevens wordt gevonden. Gebruik het eind eind dat u hebt opgegeven met CREATE EXTERNAL FILE FORMAT.

4. De gegevens laden met PolyBase of de COPY-instructie

Het is best practice gegevens in een faseringstabel te laden. Met faseringstabellen kunt u fouten afhandelen zonder de productietabellen te verstoren. Een faseringstabel biedt u ook de mogelijkheid om de toegewezen architectuur SQL pool parallelle verwerking te gebruiken voor gegevenstransformaties voordat u de gegevens in productietabellen invoegt.

Opties voor laden

Als u gegevens wilt laden, kunt u een van deze laadopties gebruiken:

  • De COPY-instructie is het aanbevolen laadprogramma, omdat u hiermee naadloos en flexibel gegevens kunt laden. De instructie heeft veel extra laadmogelijkheden die PolyBase niet biedt. Zie de zelfstudie COPY voor taxi's in NY om een voorbeeldzelfstudie uit te voeren.
  • PolyBase met T-SQL vereist dat u externe gegevensobjecten definieert.
  • PolyBase en copy-instructie met Azure Data Factory (ADF) is een ander hulpprogramma voor orchestration. Hiermee definieert u een pijplijn en worden taken gepland.
  • PolyBase met SSIS werkt goed wanneer uw brongegevens zich in de SQL Server. SSIS definieert de toewijzingen van de bron-naar-doeltabel en orkestreert ook de belasting. Als u al SSIS-pakketten hebt, kunt u de pakketten wijzigen zodat ze werken met de nieuwe datawarehouse-bestemming.
  • PolyBase met Azure Databricks brengt gegevens over van een tabel naar een Databricks-gegevensframe en/of schrijft gegevens van een Databricks-gegevensframe naar een tabel met behulp van PolyBase.

Andere laadopties

Naast PolyBase en de COPY-instructie kunt u bcp of de SqlBulkCopy-API gebruiken. BCP wordt rechtstreeks in de database geladen zonder via Azure Blob Storage te gaan en is alleen bedoeld voor kleine belastingen.

Notitie

De laadprestaties van deze opties zijn trager dan PolyBase en de COPY-instructie.

5. De gegevens transformeren

Terwijl gegevens zich in de faseringstabel staan, voert u transformaties uit die uw workload vereist. Verplaats de gegevens vervolgens naar een productietabel.

6. De gegevens invoegen in productietabellen

De INSERT INTO ... De select-instructie verplaatst de gegevens van de faseringstabel naar de permanente tabel.

Als u een ETL-proces ontwerpt, kunt u het proces uitvoeren in een klein testvoorbeeld. Probeer 1000 rijen uit de tabel te extraheren naar een bestand, verplaats deze naar Azure en laad deze vervolgens in een faseringstabel.

Oplossingen voor het laden van partners

Veel van onze partners hebben laadoplossingen. Zie een lijst met onze oplossingspartners voor meer informatie.

Volgende stappen

Zie Best practices voor het laden van gegevens voor hulp bij het laden van gegevens.