Considerazioni sulle prestazioni degli endpoint di analisi SQL

L'endpoint di analisi SQL consente di eseguire query sui dati in lakehouse usando il linguaggio T-SQL e il protocollo TDS.

Ogni lakehouse ha un endpoint di analisi SQL. Il numero di endpoint di analisi SQL in un'area di lavoro corrisponde al numero di lakehouse e database con mirroring di cui è stato effettuato il provisioning in tale area di lavoro.

Un processo in background è responsabile di eseguire la scansione del lakehouse per rilevare le modifiche e di mantenere aggiornato l'endpoint di analisi SQL con tutte le modifiche sottoposte a commit nei lakehouse di un'area di lavoro. La piattaforma Fabric gestisce in modo trasparente il processo di sincronizzazione. Quando viene rilevata una modifica in un lakehouse, un processo in background aggiorna i metadati e l'endpoint di analisi SQL riflette le modifiche di cui è stato eseguito il commit nelle tabelle lakehouse. In condizioni operative normali, il ritardo tra un lakehouse e un endpoint di analisi SQL è inferiore a un minuto. Il periodo di tempo effettivo può variare da pochi secondi a minuti a seconda di molti fattori illustrati in questo articolo. Il processo in background viene eseguito solo quando l'endpoint di analisi SQL è attivo e si interrompe dopo 15 minuti di inattività.

Guidance

• L'individuazione automatica dei metadati tiene traccia delle modifiche di cui è stato eseguito il commit nei lakehouse ed è un'istanza unica per ogni workspace di Fabric. Se si osserva una maggiore latenza per la sincronizzazione delle modifiche tra lakehouse e l'endpoint di analisi SQL, potrebbe essere dovuto a un numero elevato di lakehouse in un'area di lavoro. In uno scenario di questo tipo, valuta la migrazione di ciascun lakehouse in un'area di lavoro separata, poiché questo approccio consente al rilevamento automatico dei metadati di scalare.
• I file Parquet non sono modificabili per impostazione predefinita. Quando è presente un'operazione di aggiornamento o eliminazione, una tabella Delta aggiunge nuovi file Parquet con il set di modifiche, che aumenta il numero di file nel tempo, a seconda della frequenza di aggiornamenti ed eliminazioni. Se non si pianifica la manutenzione, questo modello crea un sovraccarico di lettura e questa condizione influisce sul tempo necessario per sincronizzare le modifiche all'endpoint di analisi SQL. Per risolvere questo problema, pianificare le normali operazioni di manutenzione delle tabelle lakehouse.
• In alcuni scenari, è possibile osservare che le modifiche di cui è stato eseguito il commit in un lakehouse non sono visibili nell'endpoint di analisi SQL associato. Ad esempio, è possibile creare una nuova tabella in lakehouse, ma non è ancora elencata nell'endpoint di analisi SQL. In alternativa, è possibile eseguire il commit di un numero elevato di righe in una tabella di un lakehouse, ma i dati non sono ancora visibili nell'endpoint di analisi SQL. È possibile avviare la sincronizzazione dei metadati su richiesta.
• Il processo di sincronizzazione automatica non supporta tutte le funzionalità Delta. Per altre informazioni sulle funzionalità supportate da ogni motore in Fabric, vedere Interoperabilità dei formati di tabella Delta Lake.
• Se è presente un volume estremamente elevato di modifiche di tabella durante l'elaborazione ETL (Extract Transform and Load), si verifica un ritardo previsto fino a quando non vengono elaborate tutte le modifiche.

Ottimizzazione delle tabelle lakehouse per l'esecuzione di query sull'endpoint di analisi SQL

Quando l'endpoint di analisi SQL legge le tabelle archiviate in un lakehouse, le prestazioni delle query dipendono principalmente dal layout fisico dei file Parquet sottostanti. Il motore esegue in parallelo le scansioni a livello di file Parquet. Troppi file piccoli aumentano il sovraccarico di file e metadati, mentre troppo pochi file grandi possono limitare il parallelismo di scansione.

Per le tabelle scritte da Spark, usa le impostazioni predefinite in runtime di Fabric Spark 2.0 o versioni successive. Questi runtime consentono di default la dimensione adattativa del file target per selezionare la dimensione target più ottimale per tabella, da 128 MB per tabelle più piccole fino a 1 GB per le tabelle più grandi. Evita di impostare target statici o limiti arbitrari di righe sopra le configurazioni predefinite. Un limite di righe non tiene conto della larghezza della riga e può creare piccoli file per tabelle ristrette.

Se utilizzi Fabric Spark runtime 1.3, abilita la dimensione adattiva del file di destinazione e i target di compattazione a livello di file, disponibili come funzionalità facoltative.

Non serve V-Order per migliorare le prestazioni degli endpoint di analisi SQL, dato che Spark scrive file parquet compressi con Snappy per ridurre sia l'I/O di lettura che di scrittura.

Le impostazioni di scrittura predefinite non sostituiscono la manutenzione della tabella. Utilizza le seguenti pratiche per mantenere un layout equilibrato quando le tabelle vengono modificate:

• Abilita la compattazione automatica per i carichi di lavoro in cui la latenza di scrittura sincrona aggiunta periodica è accettabile. La compattazione automatica è una funzione di Spark che si attiva solo quando ci sono troppi file piccoli in una tabella.
• Pianifica job periodici OPTIMIZE per i carichi di lavoro in cui la latenza aggiuntiva periodica dovuta alla compattazione automatica non soddisfa gli SLA di aggiornamento dei dati.
• Esegui VACUUM in base ai requisiti di conservazione e time travel per rimuovere i file a cui il log Delta non fa più riferimento. VACUUM riduce lo storage conservato ma non migliora la disposizione attiva dei file.
• Evita partizionamenti ad alta cardinalità e configurazioni di scrittura personalizzate che generano molti file piccoli.

Se non usi la compattazione automatica, per identificare le tabelle che necessitano di manutenzione, usa una pipeline di dati e la stored procedure T-SQL sys.sp_get_table_health_metrics prima di eseguire OPTIMIZE. Per un'esercitazione, vedi Ottimizzare le tabelle Lakehouse in base alle verifiche di integrità.

Considerazioni sulle dimensioni delle partizioni

La scelta della colonna di partizione per una tabella Delta in una casa di lago influisce anche sul tempo necessario per sincronizzare le modifiche con l'endpoint SQL analytics. Il numero e le dimensioni delle partizioni della colonna di partizione sono importanti per le prestazioni:

• Una colonna con cardinalità elevata (per lo più o interamente costituita da valori univoci) comporta un numero elevato di partizioni. Un numero elevato di partizioni influisce negativamente sulle prestazioni dell'analisi di individuazione dei metadati per individuare le modifiche. Se la cardinalità di una colonna è elevata, scegliere un'altra colonna per il partizionamento.
• Anche le dimensioni di ogni partizione possono influire sulle prestazioni. Usare una colonna che restituisce una partizione di almeno (o vicino a) 1 GB. Segui le migliori pratiche per la manutenzione e la partizionazionedelle tabelle Delta. Per uno script Python per valutare le partizioni, vedere ScriptSample per i dettagli della partizione.

Un volume elevato di file Parquet di piccole dimensioni aumenta il tempo necessario per sincronizzare le modifiche tra una lakehouse e l'endpoint di analisi SQL associato. Potresti ritrovarti con un numero elevato di file Parquet in una tabella Delta per uno o più motivi:

• Se si sceglie una partizione per una tabella Delta con un numero elevato di valori univoci, la tabella viene partizionata da ogni valore univoco e potrebbe essere sovra partizionata. Scegliere una colonna di partizione che non ha una cardinalità elevata e comporta almeno 1 GB di partizioni singole.
• La velocità di inserimento dei dati in batch e di streaming può comportare anche file di piccole dimensioni a seconda della frequenza e delle dimensioni delle modifiche scritte in un lakehouse. Ad esempio, potrebbe esserci un piccolo volume di modifiche che arrivano al lakehouse, che generano piccoli file parquet. Per risolvere questo problema, eseguire una manutenzione regolare delle tabelle lakehouse.

Script di esempio per i dettagli della partizione

Usa il seguente quaderno per stampare un report che dettaglia la dimensione e i dettagli delle partizioni che sostengono una tabella Delta.

1. Per prima cosa, fornisci il percorso ABFSS per la tua tabella Delta nella variabile delta_table_path.
   • È possibile ottenere il percorso ABFSS di una tabella delta da Esplora del portale di Fabric. Fare clic con il pulsante destro del mouse sul nome della tabella, quindi scegliere COPY PATH dall'elenco di opzioni.
2. Lo script esegue tutte le partizioni per la tabella Delta.
3. Lo script scorre ogni partizione per calcolare le dimensioni totali e il numero di file.
4. Lo script restituisce i dettagli delle partizioni, dei file per partizioni e delle dimensioni per partizione in GB.

È possibile copiare lo script completo dal blocco di codice seguente:

# Purpose: Print out details of partitions, files per partitions, and size per partition in GB.
from notebookutils import mssparkutils

# Define ABFSS path for your delta table. You can get ABFSS path of a delta table by simply right-clicking on table name and selecting COPY PATH from the list of options.
delta_table_path = "abfss://<workspace id>@<onelake>.dfs.fabric.microsoft.com/<lakehouse id>/Tables/<tablename>"

# List all partitions for given delta table
partitions = mssparkutils.fs.ls(delta_table_path)

# Initialize a dictionary to store partition details
partition_details = {}

# Iterate through each partition
for partition in partitions:
  if partition.isDir:
      partition_name = partition.name
      partition_path = partition.path
      files = mssparkutils.fs.ls(partition_path)
      
      # Calculate the total size of the partition

      total_size = sum(file.size for file in files if not file.isDir)
      
      # Count the number of files

      file_count = sum(1 for file in files if not file.isDir)
      
      # Write partition details

      partition_details[partition_name] = {
          "size_bytes": total_size,
          "file_count": file_count
      }
      
# Print the partition details
for partition_name, details in partition_details.items():
  print(f"{partition_name}, Size: {details['size_bytes']:.2f} bytes, Number of files: {details['file_count']}")

Schema generato automaticamente nell'endpoint di analisi SQL di Lakehouse

Per ogni tabella Delta in Lakehouse, l'endpoint di analisi SQL genera automaticamente una tabella nello schema appropriato. Il motore endpoint di analisi SQL si basa sul motore di Fabric Data Warehouse.

Per altre informazioni, vedere Sincronizzazione dei metadati degli endpoint di analisi SQL. È anche possibile forzare a livello di codice un aggiornamento dell'analisi automatica dei metadati usando l'API REST Aggiorna metadati dell'endpoint SQL.

Contenuti correlati

• Manutenzione e ottimizzazione delle tabelle tra carichi di lavoro
• Ottimizzazione della tabella Delta Lake e V-order
• La compattazione della tabella
• Ottimizzare le dimensioni del file
• Casi d'uso degli endpoint di analisi SQL di Lakehouse