Delen via

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

  • Artikel

Traditionele SMP-toegewezen SQL-pools maken gebruik van een ETL-proces (Extract, Transform en Load) voor het laden van gegevens. Synapse SQL maakt in Azure Synapse Analytics gebruik van gedistribueerde queryverwerkingsarchitectuur die profiteert van de schaalbaarheid en flexibiliteit van reken- en opslagresources.

Met behulp van een ELT-proces (Extract, Load en Transform) worden ingebouwde mogelijkheden voor gedistribueerde queryverwerking gebruikt en worden de resources verwijderd die nodig zijn voor gegevenstransformatie voordat ze worden geladen.

Hoewel toegewezen SQL-pools veel laadmethoden ondersteunen, 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 T-SQL-taal. Voor de meeste flexibiliteit bij het laden raden we u aan de COPY-instructie te gebruiken.

Wat is ELT?

ELT (Extract, Load, and Transform) is een proces waarmee gegevens worden geëxtraheerd uit een bronsysteem, geladen in een toegewezen SQL-pool en vervolgens worden getransformeerd.

De basisstappen voor het implementeren van ELT zijn:

  1. Extraheer de brongegevens naar tekstbestanden.
  2. Land de gegevens in Azure Blob Storage of Azure Data Lake Store.
  3. Bereid de gegevens voor voor 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 voor een zelfstudie over laden het laden van gegevens uit Azure Blob Storage.

1. Pak de brongegevens uit in tekstbestanden

Het ophalen van gegevens uit uw bronsysteem is afhankelijk van de opslaglocatie. Het doel is om de gegevens te verplaatsen naar ondersteunde tekstbestanden 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-, indeling met vaste breedte en geneste indelingen zoals WinZip of XML worden niet ondersteund. Als u exporteert vanuit SQL Server, 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 plaatsen

Als u de gegevens in Azure Storage wilt plaatsen, 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 naar Azure Storage te verplaatsen:

  • De Azure ExpressRoute-service verbetert de netwerkdoorvoer, prestaties en voorspelbaarheid. ExpressRoute is een service waarmee uw gegevens worden gerouteerd via een toegewezen privéverbinding naar Azure. ExpressRoute-verbindingen routeren geen gegevens via het openbare internet. De verbindingen bieden meer betrouwbaarheid, snellere snelheden, lagere latenties en hogere beveiliging dan typische 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 dit 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 Azure Storage te verplaatsen. Als u Data Factory wilt gebruiken met toegewezen SQL-pools, raadpleegt u Gegevens laden voor toegewezen SQL-pools.

3. De gegevens voorbereiden voor het laden

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

De tabellen definiëren

Definieer eerst de tabellen waarnaar u laadt in uw toegewezen SQL-pool wanneer u de COPY-instructie gebruikt.

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

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

Gebruik de volgende TOEWIJZING van sql-gegevenstypen bij het laden van Parquet-bestanden:

Parquet-type Logisch type van Parquet (annotatie) SQL-gegevenstype
BOOLEAANS bit
BINAIRE / BYTE_ARRAY varbinary
DUBBEL drijven
DRIJVEN werkelijk
INT32 int
INT64 bigint
INT96 datetime2
FIXED_LEN_BYTE_ARRAY binair
BINAIR UTF8 nvarchar
BINAIR STRING nvarchar
BINAIR ENUM nvarchar
BINAIR UUID uniqueidentifier
BINAIR DECIMAL decimaal
BINAIR JSON nvarchar(MAX)
BINAIR BSON varbinary(MAX)
FIXED_LEN_BYTE_ARRAY DECIMAL decimaal
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 decimaal
INT32 TIME (MILLIS) time
INT64 INT(64, true) bigint
INT64 INT(64, false ) decimal(20,0)
INT64 DECIMAL decimaal
INT64 TIME (MILLIS) time
INT64 TIMESTAMP (MILLIS) datetime2
Complex type LIST varchar(max)
Complex type MAP varchar(max)

Belangrijk

  • Toegewezen SQL-pools bieden momenteel geen ondersteuning voor Parquet-gegevenstypen met MICROS- en NANOS-precisie.
  • Mogelijk treedt de volgende fout op als typen niet overeenkomen tussen Parquet en SQL of als u niet-ondersteunde Parquet-gegevenstypen hebt: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:...
  • Het laden van een waarde buiten het bereik van 0-127 in een kleine 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. Ongeacht of de gegevens afkomstig zijn van een relationele of niet-relationele bron, moeten de gegevens worden getransformeerd om te worden afgestemd op de kolomdefinities voor de tabel waarin u de gegevens wilt laden.
  • Gegevens in het tekstbestand opmaken om te worden uitgelijnd 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 tijdens de belasting worden geweigerd.
  • Scheid velden in het tekstbestand met een afsluiter. Zorg ervoor dat u een teken of een tekenreeks gebruikt die niet in uw brongegevens wordt gevonden. Gebruik het afsluitprogramma dat u hebt opgegeven met CREATE EXTERNAL FILE FORMAT.

4. Laad de gegevens met behulp van PolyBase of de COPY-instructie

Het is raadzaam om 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 architectuur voor parallelle verwerking van toegewezen SQL-pools te gebruiken voor gegevenstransformaties voordat u de gegevens in productietabellen invoegt.

Opties voor laden

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

  • De COPY-instructie is het aanbevolen hulpprogramma voor laden, omdat u hiermee naadloos en flexibel gegevens kunt laden. De instructie heeft veel extra laadmogelijkheden die PolyBase niet biedt. Zie de zelfstudie VOOR HET KOPIËREN van taxi's van 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 indelingsprogramma. Hiermee definieert u een pijplijn en plant u taken.
  • PolyBase met SSIS werkt goed wanneer uw brongegevens zich in SQL Server bevinden. SSIS definieert de bron-naar-doeltabeltoewijzingen en organiseert ook de belasting. Als u al SSIS-pakketten hebt, kunt u de pakketten wijzigen zodat deze werken met de nieuwe datawarehouse-bestemming.
  • PolyBase met Azure Databricks draagt gegevens over van een tabel naar een Databricks-dataframe en/of schrijft gegevens van een Databricks-dataframe naar een tabel met behulp van PolyBase.

Bekijk de beschikbare zelfstudies:

Andere laadopties

Naast PolyBase en de COPY-instructie kunt u bcp of de SqlBulkCopy-API gebruiken. Het bcp hulpprogramma wordt rechtstreeks naar de database geladen zonder azure Blob Storage te doorlopen en is alleen bedoeld voor kleine belastingen.

Notitie

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

5. De gegevens transformeren

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

6. De gegevens invoegen in productietabellen

Insert INTO ... Select-instructie verplaatst de gegevens uit de faseringstabel naar de permanente tabel.

Wanneer u een ETL-proces ontwerpt, voert u het proces uit op een klein testvoorbeeld. Pak 1000 rijen uit de tabel uit naar een bestand, verplaats het 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 stap

Aanbevolen procedures voor het laden van gegevens