Progettare una strategia di caricamento dei dati di PolyBase per il pool SQL dedicato in Azure Synapse Analytics
I data warehouse SMP tradizionali usano un processo di estrazione, trasformazione e caricamento (ETL) per il caricamento dei dati. Un pool Azure SQL è un'architettura di elaborazione parallela massiva (MPP, Massively Parallel Processing) che sfrutta la scalabilità e la flessibilità delle risorse di calcolo e archiviazione. Un processo di estrazione, caricamento e trasformazione (ELT) consente di sfruttare le funzionalità di elaborazione delle query distribuite predefinite e di eliminare le risorse necessarie per trasformare i dati prima del caricamento.
Anche se il pool SQL supporta molti metodi di caricamento, incluse opzioni non PolyBase come BCP e l'API BulkCopy di SQL, il modo più veloce e scalabile per caricare i dati consiste nell'usare PolyBase. una tecnologia che accede ai dati archiviati esterni in Archiviazione BLOB di Azure o Azure Data Lake Store tramite il linguaggio T-SQL.
Estrazione, caricamento e trasformazione (ELT)
Nel processo di estrazione, caricamento e trasformazione (ELT) i dati vengono estratti da un sistema di origine, caricati in un data warehouse e quindi trasformati.
Per l'implementazione di un processo ELT di PolyBase per pool SQL dedicati è necessario eseguire questi passaggi:
- Estrarre i dati di origine in file di testo.
- Trasferire i dati nell'archivio BLOB di Azure o in Azure Data Lake Store.
- Preparare i dati per il caricamento.
- Caricare i dati in tabelle di staging di pool SQL dedicati con PolyBase.
- Trasformare i dati.
- Inserire i dati in tabelle di produzione.
Per un'esercitazione sul caricamento, vedere l'articolo relativo all'uso di PolyBase per caricare dati da Archiviazione BLOB di Azure in Azure Synapse Analytics.
Per altre informazioni, vedere il blog sui modelli di caricamento.
1. Estrarre i dati di origine in file di testo
La modalità di recupero dei dati dal sistema di origine dipende dalla posizione di archiviazione. L'obiettivo è spostare i dati in file di testo delimitati supportati da PolyBase.
Formati di file esterni PolyBase
PolyBase carica i dati da file di testo delimitati con codifica UTF-8 e UTF-16. Inoltre, PolyBase supporta il caricamento da formati di file Hadoop, ovvero RC, ORC e Parquet. PolyBase può anche caricare dati da file compressi Gzip e Snappy. PolyBase non supporta attualmente i formati ASCII esteso, a larghezza fissa e annidati, come WinZip, JSON e XML.
Se si esegue l'esportazione da SQL Server, è possibile usare lo strumento da riga di comando bcp per esportare i dati in file di testo delimitati. Il mapping dei tipi di dati tra Parquet e Azure Synapse Analytics è il seguente:
| Tipo di dati Parquet | Tipo di dati SQL |
|---|---|
| tinyint | tinyint |
| smallint | smallint |
| int | int |
| bigint | bigint |
| boolean | bit |
| double | float |
| float | real |
| double | money |
| double | smallmoney |
| string | nchar |
| string | nvarchar |
| string | char |
| string | varchar |
| binary | binary |
| binary | varbinary |
| timestamp | data |
| timestamp | smalldatetime |
| timestamp | datetime2 |
| timestamp | datetime |
| timestamp | time |
| data | data |
| decimal | decimal |
2. Trasferire i dati in Archiviazione BLOB di Azure o in Azure Data Lake Store
Per trasferire i dati in Archiviazione di Azure, è possibile spostarli nell'archivio BLOB di Azure o in Azure Data Lake Store. In entrambe le posizioni, i dati devono essere archiviati in file di testo. PolyBase può eseguire il caricamento da entrambe le posizioni.
Strumenti e servizi che è possibile usare per spostare i dati in Archiviazione di Azure:
- Il servizio Azure ExpressRoute migliora la velocità effettiva della rete, le prestazioni e la prevedibilità. ExpressRoute è un servizio che instrada i dati tramite una connessione privata dedicata ad Azure. Le connessioni ExpressRoute non instradano i dati attraverso la rete Internet pubblica. Queste connessioni offrono maggiore affidabilità, velocità più elevate, latenze minori e sicurezza superiore rispetto alle tipiche connessioni tramite la rete Internet pubblica.
- L'utilità AzCopy sposta i dati in Archiviazione di Azure tramite la rete Internet pubblica. Si tratta di un'opzione appropriata se le dimensioni dei dati sono inferiori a 10 TB. Per eseguire periodicamente caricamenti con AzCopy, testare la velocità di rete per assicurarsi che sia accettabile.
- Azure Data Factory (ADF) include un gateway che è possibile installare nel server locale. È quindi possibile creare una pipeline per spostare i dati dal server locale ad Archiviazione di Azure. Per usare Data Factory con il pool SQL dedicato, vedere Caricare i dati nel pool SQL dedicato.
3. Preparare i dati per il caricamento
Potrebbe essere necessario preparare e pulire i dati nell'account di archiviazione prima di caricarli nel pool SQL dedicato. La preparazione dei dati può essere eseguita nella posizione di origine dei dati, mentre si esportano i dati in file di testo o quando i dati raggiungono Archiviazione di Azure. È più facile lavorare con i dati il prima possibile nel processo.
Definire tabelle esterne
Prima di caricare i dati, è necessario definire le tabelle esterne nel data warehouse. PolyBase usa le tabelle esterne per definire i dati e accedervi in Archiviazione di Azure. Una tabella esterna è simile a una vista di database. La tabella esterna contiene lo schema di tabella e punta a dati archiviati all'esterno del data warehouse.
La definizione di tabelle esterne include la specifica dell'origine dati, del formato dei file di testo e delle definizioni delle tabelle. Di seguito sono riportati gli argomenti relativi alla sintassi T-SQL necessari:
Formattare i file di testo
Dopo aver definito gli oggetti esterni, è necessario allineare le righe dei file di testo alla definizione della tabella esterna e del formato del file. I dati in ogni riga del file di testo devono essere allineati alla definizione della tabella. Per formattare i file di testo:
- Se i dati provengono da un'origine non relazionale, è necessario trasformarli in righe e colonne. Sia che i dati provengano da un'origine relazionale o non relazionale, devono essere trasformati per allinearli alle definizioni di colonna per la tabella in cui si prevede di caricare i dati.
- Formattare i dati nel file di testo per allinearli alle colonne e ai tipi di dati nella tabella di destinazione dei pool SQL. In caso di non allineamento dei tipi di dati nei file di testo esterni e nella tabella del data warehouse, le righe verranno rifiutate durante il caricamento.
- Separare i campi nel file di testo con un carattere di terminazione. Assicurarsi di usare un carattere o una sequenza di caratteri non inclusi nei dati di origine. Usare il carattere di terminazione specificato con CREATE EXTERNAL FILE FORMAT.
4. Caricare i dati in tabelle di staging di pool SQL dedicati con PolyBase
È consigliabile caricare i dati in una tabella di staging. Le tabelle di staging consentono di gestire gli errori senza interferire con le tabelle di produzione. Una tabella di staging consente anche di usare le funzionalità di elaborazione delle query distribuite predefinite del pool SQL per le trasformazioni di dati prima di inserire i dati nelle tabelle di produzione.
Opzioni per il caricamento con PolyBase
Per caricare i dati con PolyBase, è possibile usare una di queste opzioni di caricamento:
- PolyBase con T-SQL: ideale quando i dati sono nell'archivio BLOB di Azure o in Azure Data Lake Store. Questa opzione offre il massimo controllo sul processo di caricamento, ma richiede anche di definire oggetti dati esterni. Gli altri metodi definiscono questi oggetti dietro le quinte, man mano che si esegue il mapping di tabelle di origine e tabelle di destinazione. Per orchestrare i caricamenti con T-SQL, è possibile usare Azure Data Factory, SSIS o funzioni di Azure.
- PolyBase con SSIS è ideale quando i dati di origine sono in SQL Server. SSIS definisce i mapping delle tabelle di origine e di destinazione, oltre a orchestrare il caricamento. Se sono già disponibili pacchetti SSIS, è possibile modificarli per utilizzare la nuova destinazione di data warehouse.
- PolyBase con Azure Data Factory (ADF) è un altro strumento di orchestrazione, che definisce una pipeline e pianifica i processi.
- PolyBase con Azure Databricks trasferisce i dati da una tabella di Azure Synapse Analytics in un dataframe di Databricks e/o scrive i dati da un dataframe di Databricks in una tabella di Azure Synapse Analytics usando PolyBase.
Opzioni di caricamento non PolyBase
Se i dati non sono compatibili con PolyBase, è possibile usare bcp o l'API SQLBulkCopy. BCP carica direttamente i dati nei pool SQL dedicati senza usare Archiviazione BLOB di Azure ed è destinato esclusivamente a caricamenti di piccole dimensioni. Si noti che le prestazioni di caricamento di queste opzioni sono inferiori rispetto a PolyBase.
5. Trasformare i dati
Mentre i dati sono nella tabella di staging, eseguire le trasformazioni richieste dal carico di lavoro, quindi spostare i dati in una tabella di produzione.
6. Inserire i dati in tabelle di produzione
L'istruzione INSERT INTO... SELECT sposta i dati dalla tabella di staging alla tabella permanente.
Quando si progetta un processo ETL, provare a eseguire il processo su un campione di test di piccole dimensioni. Provare a estrarre 1000 righe dalla tabella in un file, spostarlo in Azure e quindi provare a caricarlo in una tabella di staging.
Soluzioni di caricamento dei partner
Molti partner Microsoft dispongono di soluzioni di caricamento. Per altre informazioni, vedere l'elenco dei partner che offrono soluzioni.
Passaggi successivi
Per linee guida relative al caricamento, vedere Linee guida per il caricamento di dati.