Strategieën voor het laden van gegevens voor toegewezen SQL-pool in Azure Synapse Analytics
Traditionele toegewezen SMP SQL-pools gebruiken een ETL-proces (Extract, Transform, and Load) voor het laden van gegevens. Synapse SQL maakt binnen Azure Synapse Analytics gebruik van een architectuur voor gedistribueerde queryverwerking die profiteert van de schaalbaarheid en flexibiliteit van reken- en opslagresources.
Het gebruik van een ELT-proces (Extraheren, Laden en Transformeren) maakt gebruik van ingebouwde mogelijkheden voor het verwerken van gedistribueerde query's en elimineert de resources die nodig zijn voor gegevenstransformatie vóór het laden.
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 INSTRUCTIE COPY te gebruiken.
Wat is ELT?
ELT (Extract, Load, and Transform) is een proces waarmee gegevens worden geëxtraheerd uit een bronsysteem, worden geladen in een toegewezen SQL-pool en vervolgens worden getransformeerd.
De basisstappen voor het implementeren van ELT zijn:
- Extraheer de brongegevens naar tekstbestanden.
- Plaats de gegevens in Azure Blob Storage of Azure Data Lake Store.
- Bereid de gegevens voor voor het laden.
- Laad de gegevens in faseringstabellen met PolyBase of de opdracht COPY.
- De gegevens transformeren.
- Voeg de gegevens in productietabellen in.
Zie Gegevens laden vanuit Azure Blob Storage voor een zelfstudie over het laden.
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 met scheidingstekens of CSV-bestanden.
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 met scheidingstekens of CSV-bestanden wordt het 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. Plaats de gegevens in Azure Blob Storage of Azure Data Lake Store
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 te verplaatsen naar Azure Storage:
- De Azure ExpressRoute-service verbetert de doorvoer, prestaties en voorspelbaarheid van het netwerk. 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, hogere snelheden, lagere latenties en hogere beveiliging dan gewone verbindingen via het openbare internet.
- 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 Azure Storage te verplaatsen. Zie Gegevens laden voor toegewezen SQL-pools als u Data Factory wilt gebruiken met toegewezen SQL-pools.
3. De gegevens voorbereiden voor het laden
Mogelijk moet u de gegevens in uw opslagaccount voorbereiden en opschonen voordat u deze laadt. Gegevensvoorbereiding kan worden uitgevoerd terwijl uw gegevens zich in de bron bevinden, wanneer u de gegevens exporteert naar tekstbestanden of nadat de gegevens zich in Azure Storage bevinden. Het is het eenvoudigst om zo vroeg mogelijk in het proces met de gegevens te werken.
De tabellen definiëren
U moet eerst de tabel(s) definiëren 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 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 verwijst naar gegevens die zijn opgeslagen buiten de toegewezen SQL-pool.
Bij het definiëren van externe tabellen moet u de gegevensbron, de indeling van de tekstbestanden en de tabeldefinities opgeven. T-SQL-syntaxisverwijzingsartikelen 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 |
|---|---|---|
| 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 | 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 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 de 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 uit te lijnen met de kolomdefinities voor de tabel waarin u de gegevens wilt laden.
- Maak gegevens in het tekstbestand op om 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 eindteken. Zorg ervoor dat u een teken of een tekenreeks gebruikt die niet in uw brongegevens wordt gevonden. Gebruik de afsluiter die u hebt opgegeven met CREATE EXTERNAL FILE FORMAT.
4. Laad de gegevens met polybase of de COPY-instructie
Het is een best practice 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 de toegewezen SQL-pool te gebruiken voor gegevenstransformaties voordat u de gegevens invoegt in productietabellen.
Opties voor laden
Als u gegevens wilt laden, kunt u een van deze 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 COPY-zelfstudie voor ny taxi's voor een voorbeeldzelfstudie.
- Voor PolyBase met T-SQL moet u externe gegevensobjecten definiëren.
- PolyBase en COPY-instructie met Azure Data Factory (ADF) is een ander indelingsprogramma. Het definieert een pijplijn en plant taken.
- PolyBase met SSIS werkt goed wanneer uw brongegevens zich in SQL Server. SSIS definieert de toewijzingen van bron-naar-doeltabellen en organiseert ook de belasting. Als u al SSIS-pakketten hebt, kunt u de pakketten aanpassen om te werken met de nieuwe datawarehouse-bestemming.
- PolyBase met Azure Databricks draagt gegevens over van een tabel naar een Databricks-dataframe en/of schrijft gegevens uit 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 Azure Blob Storage te doorlopen 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 de gegevens zich in de faseringstabel bevinden, moet u transformaties uitvoeren die voor uw workload nodig zijn. 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.
Wanneer u een ETL-proces ontwerpt, voert u het proces uit op een klein testvoorbeeld. Probeer 1000 rijen uit de tabel te extraheren naar een bestand, verplaats het naar Azure en probeer het vervolgens in een faseringstabel te laden.
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.