Strategie di caricamento dei dati per pool SQL dedicati in Azure Synapse Analytics
I pool SQL dedicati SMP tradizionali usano un processo di estrazione, trasformazione e caricamento (ETL, Extract, Transform, Load) per il caricamento dei dati. Synapse SQL, all'interno di Azure Synapse Analytics, ha un'architettura di elaborazione delle query che sfrutta la scalabilità e la flessibilità delle risorse di calcolo e archiviazione.
L'uso di un processo di estrazione, caricamento e trasformazione (ELT) sfrutta le funzionalità di elaborazione delle query distribuite predefinite ed elimina le risorse necessarie per la trasformazione dei dati prima del caricamento.
Benché i pool SQL dedicati supportino molti metodi di caricamento, incluse le opzioni di SQL Server più diffuse come bcp e l'API SqlBulkCopy, il modo più rapido e scalabile per caricare i dati è attraverso tabelle esterne PolyBase e l'istruzione COPY (anteprima).
Con PolyBase e l'istruzione COPY, è possibile accedere ai dati archiviati esterni in Archiviazione BLOB di Azure o Azure Data Lake Store tramite il linguaggio T-SQL. Per la massima flessibilità durante il caricamento, è consigliabile usare l'istruzione COPY.
Definizione di ELT
ELT è un processo mediante il quale i dati vengono estratti da un sistema di origine, caricati in un pool SQL dedicato e quindi trasformati.
I passaggi di base per l'implementazione del processo ELT sono:
- 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 nelle tabelle di staging con PolyBase o il comando COPY.
- Trasformare i dati.
- Inserire i dati in tabelle di produzione.
Per un'esercitazione sul caricamento, vedere caricamento dei dati dall'archivio BLOB di Azure.
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 o CSV supportati.
Formati di file supportati
Con PolyBase e l'istruzione COPY, è possibile caricare dati da file di testo o CSV delimitati con codifica UTF-8 e UTF-16. Oltre ai file di testo o CSV delimitati, è supportato il caricamento da formati di file Hadoop come ORC e Parquet. PolyBase e l'istruzione COPY possono anche caricare dati da file compressi Gzip e Snappy.
I formati ASCII esteso, a larghezza fissa e annidati, come WinZip o XML, non sono attualmente supportati. 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.
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 Gen2. In entrambe le posizioni, i dati devono essere archiviati in file di testo. PolyBase e l'istruzione COPY supportano 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 regolarmente caricamenti con AzCopy, assicurasi che la velocità di rete 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 pool SQL dedicati, vedere Caricamento di dati per pool SQL dedicati.
3. Preparare i dati per il caricamento
Potrebbe essere necessario preparare e pulire i dati nell'account di archiviazione prima del caricamento. 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 le tabelle
È prima necessario definire le tabelle in cui si esegue il caricamento nel pool SQL dedicato quando si usa l'istruzione COPY.
Se si usa PolyBase, è necessario definire tabelle esterne nel pool SQL dedicato prima del caricamento. 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 pool SQL dedicato.
La definizione di tabelle esterne include la specifica dell'origine dati, del formato dei file di testo e delle definizioni delle tabelle. Gli articoli di riferimento per la sintassi T-SQL che saranno necessari sono:
Usare il mapping del tipo di dati SQL seguente durante il caricamento di file Parquet:
| Tipo Parquet | Tipo logico Parquet (annotazione) | Tipo di dati SQL |
|---|---|---|
| BOOLEAN | bit | |
| BINARY/BYTE_ARRAY | varbinary | |
| DOUBLE | float | |
| FLOAT | real | |
| INT32 | int | |
| INT64 | bigint | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binary | |
| 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 | data |
| 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 | TIMESTAMP (MILLIS) | datetime2 |
| Tipo complesso | INSERZIONE | ntext |
| Tipo complesso | MAP | ntext |
Importante
- Attualmente, i pool SQL dedicati non supportano i tipi di dati Parquet con precisione MICROS e NANOS.
- È possibile che si verifichi l'errore seguente in caso di mancata corrispondenza tra Parquet e SQL o se sono presenti tipi di dati Parquet non supportati:
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:... - Il caricamento di un valore non compreso nell'intervallo da 0 a 127 in una colonna tinyint per il formato di file Parquet e ORC non è supportato.
Per un esempio di creazione di oggetti esterni, vedere Creare tabelle esterne.
Formattare i file di testo
Se si usa PolyBase, per gli oggetti esterni definiti è 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. In caso di non allineamento dei tipi di dati nei file di testo esterni e nella tabella del pool SQL dedicati, 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 usando PolyBase o l'istruzione COPY
È 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 offre anche l'opportunità di usare l’architettura di elaborazione parallela del pool SQL dedicato per eseguire trasformazioni di dati prima di inserirli nelle tabelle di produzione.
Opzioni per il caricamento
Per caricare i dati, è possibile usare una delle seguenti opzioni di caricamento:
- L'istruzione COPY è l'utilità di caricamento consigliata, in quanto consente di caricare i dati in modo semplice e flessibile. L'istruzione include molte funzionalità di caricamento aggiuntive che PolyBase non comprende. Per eseguire un'esercitazione di esempio, vedere l'esercitazione sull’istruzione COPY per i taxi di NY.
- PolyBase con T-SQL richiede di definire oggetti dati esterni.
- PolyBase e istruzione COPY con Azure Data Factory (ADF) è un altro strumento di orchestrazione, che definisce una pipeline e pianifica i processi.
- 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 Databricks trasferisce i dati da una tabella in un dataframe di Databricks e/o scrive i dati da un dataframe di Databricks in una tabella usando PolyBase.
Altre opzioni di caricamento
Oltre a PolyBase e all'istruzione COPY, è possibile usare bcp o l'API SqlBulkCopy. bcp carica direttamente i dati nel database senza dover passare attraverso l'archivio BLOB di Azure ed è destinato esclusivamente a piccoli caricamenti.
Nota
Le prestazioni di caricamento di queste opzioni sono inferiori rispetto a PolyBase e all'istruzione COPY.
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 indicazioni sul caricamento, vedere Procedure consigliate per il caricamento dei dati.