Procedure consigliate per il caricamento dei dati in un pool SQL dedicato in Azure Synapse Analytics

  • Articolo

In questo articolo sono disponibili raccomandazioni e ottimizzazioni delle prestazioni per il caricamento dei dati.

Preparare i dati in Archiviazione di Azure

Per ridurre al minimo la latenza, usare un percorso condiviso per il livello di archiviazione e il pool SQL dedicato.

In caso di esportazione di dati in un formato di file ORC, quando sono presenti colonne di testo di grandi dimensioni potrebbero verificarsi errori di memoria insufficiente di Java. Per risolvere questo problema, esportare solo un subset di colonne.

PolyBase non può caricare righe contenenti più di 1.000.000 di byte di dati. I dati inseriti nei file di testo nell'archivio BLOB di Azure o in Azure Data Lake Store devono corrispondere a meno di 1.000.000 di byte. Questa limitazione in termini di byte vale indipendentemente dallo schema di tabella.

Tutti i formati di file hanno caratteristiche di prestazioni diverse. Per ottenere la velocità di caricamento massima, usare file di testo delimitati compressi. La differenza di prestazioni tra UTF-8 e UTF-16 è minima.

Suddividere i file compressi di grandi dimensioni in file compressi di dimensioni inferiori.

Eseguire i caricamenti con risorse di calcolo sufficienti

Per ottenere la velocità di caricamento massima, eseguire un solo processo di caricamento alla volta. Se questo approccio non è fattibile, eseguire contemporaneamente un numero minimo di caricamenti. Se si prevede un processo di caricamento di grandi dimensioni, è consigliabile aumentare le prestazioni del pool SQL dedicato prima del caricamento.

Per eseguire i caricamenti con risorse di calcolo appropriate, creare utenti designati addetti al caricamento. Assegnare ogni utente di caricamento a classe di risorse o a un gruppo di carico di lavoro specifico. Per eseguire un caricamento, effettuare l'accesso come utente di caricamento e quindi eseguire l'operazione. Il caricamento viene eseguito con la classe di risorse dell'utente. Questo metodo è più semplice rispetto al tentativo di modificare la classe di risorse di un utente in base alla classe di risorse attualmente necessaria.

Creare un utente di caricamento

In questo esempio viene creato un utente loader classificato per uno specifico gruppo di carico di lavoro. Il primo passaggio consiste nel connettersi al master e creare un account di accesso.

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

Connettersi al pool SQL dedicato e creare un utente. Il codice seguente presuppone che si sia connessi al database denominato mySampleDataWarehouse. Illustra come creare un utente denominato caricatore e concede all'utente le autorizzazioni per creare tabelle e caricare usando l'istruzione COPY. L'utente viene quindi classificato per il gruppo di carico di lavoro DataLoads con le risorse massime.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



Importante

Questo è un esempio estremo di allocazione del 100% delle risorse del pool SQL a un singolo caricamento. In questo modo si otterrà una concorrenza massima pari a 1. Tenere presente che questa operazione deve essere usata solo per il caricamento iniziale in cui sarà necessario creare gruppi di carico di lavoro aggiuntivi con le proprie configurazioni per bilanciare le risorse nei carichi di lavoro.

Per eseguire un caricamento con risorse per il gruppo di carico di lavoro di caricamento, accedere come caricatore ed eseguire il carico.

Consentire il caricamento a più utenti

È spesso necessario fare in modo che più utenti possano caricare dati in un data warehouse. Il caricamento con CREATE TABLE AS SELECT (Transact-SQL) richiede autorizzazioni CONTROL per il database. L'autorizzazione CONTROL fornisce il controllo degli accessi a tutti gli schemi. È consigliabile che non tutti gli utenti che eseguono caricamenti abbiano il controllo degli accessi a tutti gli schemi. Per limitare le autorizzazioni, usare l'istruzione DENY CONTROL.

Si supponga ad esempio che esistano gli schemi di database schema_A per il reparto A e schema_B per il reparto B e che gli utenti di database user_A e user_B siano gli utenti addetti al caricamento PolyBase rispettivamente nel reparto A e nel reparto B. A entrambi gli utenti sono state concesse le autorizzazioni di database CONTROL. Gli autori di schema A e B usano a questo punto DENY per bloccare i rispettivi schemi:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

Gli utenti user_A e user_B saranno ora esclusi dall'accesso allo schema dell'altro reparto.

Eseguire il caricamento in una tabella di staging

Per ottenere la massima velocità di caricamento per spostare i dati in una tabella di data warehouse, caricare i dati in una tabella di staging. Definire la tabella di staging come heap e usare round robin per l'opzione di distribuzione.

Il caricamento è in genere un processo in due fasi in cui i dati vengono prima caricati in una tabella di staging, quindi inseriti in una tabella di data warehouse di produzione. Se la tabella di produzione usa una distribuzione hash, il tempo totale necessario per il caricamento e l'inserimento può essere ridotto se si definisce una tabella di staging con la distribuzione hash. Il caricamento nella tabella di staging richiede più tempo, ma il secondo passaggio di inserimento delle righe nella tabella di produzione non comporta lo spostamento dei dati tra le distribuzioni.

Eseguire il caricamento in un indice columnstore

Gli indici columnstore richiedono una grande quantità di memoria per la compressione dei dati in rowgroup di qualità elevata. Per una compressione e un'efficienza dell'indice ottimali, l'indice columnstore deve comprimere il valore massimo di 1.048.576 righe in ogni rowgroup. In caso di utilizzo elevato di memoria, l'indice columnstore potrebbe non riuscire a raggiungere i tassi di compressione massimi. Questo influisce sulle prestazioni delle query. Per un approfondimento, vedere l'articolo relativo alle ottimizzazioni della memoria columnstore.

  • Per garantire all'utente addetto al caricamento una quantità di memoria sufficiente per raggiungere i massimi tassi di compressione, usare utenti addetti al caricamento che siano membri di una classe di risorse di medie o grandi dimensioni.
  • Caricare un numero di righe sufficiente a riempire completamente i nuovi rowgroup. Durante un caricamento bulk, ogni gruppo di 1.048.576 righe viene compresso direttamente nel columnstore come rowgroup completo. In caso di caricamenti con meno di 102.400 righe, le righe vengono inviate nell'archivio differenziale, in cui vengono mantenute in un indice albero B. Se si carica un numero troppo basso di righe, le righe potrebbero essere inserite tutte nell'archivio differenziale e non essere immediatamente compresse nel formato columnstore.

Aumentare le dimensioni del batch quando si usa l'API SQLBulkCopy o l'utilità BCP

Il caricamento con l'istruzione COPY fornirà la velocità effettiva più elevata con pool SQL dedicati. Se non è possibile usare l'istruzione COPY per il caricamento e occorre usare l'API SqLBulkCopy o l'utilità bcp, valutare la possibilità di aumentare dimensioni del batch per migliorare la velocità effettiva.

Suggerimento

Dimensioni batch comprese tra 100.000 e 1 milione di righe è la baseline consigliata per determinare la capacità ottimale delle dimensioni del batch.

Gestire degli errori di caricamento

Un caricamento con una tabella esterna può avere esito negativo con l'errore "Query interrotta. È stata raggiunta la soglia massima di rifiuti durante la lettura da un'origine esterna". Questo messaggio indica che i dati esterni contengono record dirty. Un record di dati viene considerato dirty se i tipi di dati e il numero di colonne non corrispondono alle definizioni di colonna della tabella esterna oppure se i dati non sono conformi al formato di file esterno specificato.

Per risolvere questo problema, assicurarsi che la tabella esterna e le definizioni del formato di file esterno siano corrette e che i dati esterni siano conformi a queste definizioni. Nel caso in cui un subset di record di dati esterni sia dirty, è possibile scegliere di rifiutare tali record per le query usando le opzioni di rifiuto in 'CREATE EXTERNAL TABLE'.

Inserire i dati in una tabella di produzione

Per una singola operazione di caricamento in una tabella di piccole dimensioni con un'istruzione INSERT o anche un ricaricamento periodico di una ricerca, è possibile ottenere prestazioni soddisfacenti con un'istruzione come INSERT INTO MyLookup VALUES (1, 'Type 1'). Gli inserimenti singleton non offrono tuttavia la stessa efficienza di un caricamento bulk.

Se nel corso di una giornata si eseguono migliaia di singoli inserimenti o più, inviare in batch gli inserimenti per poterne eseguire il caricamento bulk. Sviluppare i processi per aggiungere i singoli inserimenti in un file e quindi creare un altro processo che carica periodicamente il file.

Creare statistiche dopo il caricamento

Per migliorare le prestazioni delle query, è importante creare statistiche su tutte le colonne di tutte le tabelle dopo il primo caricamento o modifiche importanti ai dati. È possibile creare statistiche manualmente oppure abilitare la creazione automatica delle statistiche.

Per una spiegazione dettagliata delle statistiche, vedere Statistiche. L'esempio seguente mostra come creare manualmente le statistiche su cinque colonne della tabella Customer_Speed.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Ruotare le chiavi di archiviazione

Ai fini della sicurezza è consigliabile modificare regolarmente la chiave di accesso all'archivio BLOB. Sono disponibili due chiavi di archiviazione per l'account di archiviazione BLOB ed è quindi possibile passare da una chiave all'altra.

Per ruotare le chiavi dell'account di archiviazione di Azure:

Per ogni account di archiviazione la cui chiave ha subito modifiche, eseguire ALTER DATABASE SCOPED CREDENTIAL.

Esempio:

Creazione della chiave originale

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

Rotazione della chiave dalla chiave 1 alla chiave 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

Non sono necessarie altre modifiche alle origini dati esterne.

Passaggi successivi