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 Microsoft 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.

Un numero elevato di file Parquet di piccole dimensioni crea un sovraccarico e influisce negativamente sulle prestazioni delle query. Per garantire prestazioni prevedibili ed efficienti, mantenere l'archiviazione delle tabelle in modo tale che ogni file Parquet contenga due milioni di righe. Questo numero di righe fornisce un livello bilanciato di parallelismo senza frammentare il set di dati in sezioni eccessivamente piccole.

Oltre alle linee guida per il conteggio delle righe, le dimensioni dei file sono ugualmente importanti. L'endpoint di analisi SQL offre prestazioni ottimali quando i file Parquet sono sufficientemente grandi per ridurre al minimo il sovraccarico di gestione dei file, ma non così grandi che limitano l'efficienza di analisi parallela. Per la maggior parte dei carichi di lavoro, mantenere singoli file Parquet vicini a 400 MB raggiunge il miglior equilibrio. Per ottenere questo equilibrio, seguire questa procedura:

1. Impostare maxRecordsPerFile su 2.000.000 prima che si verifichino modifiche ai dati.
2. Eseguire le modifiche ai dati (inserimento dati, aggiornamenti, eliminazioni).
3. Impostare maxFileSize a 4 GB.
4. Eseguire OPTIMIZE. Per i dettagli sull'uso di OPTIMIZE, vedi Eseguire la manutenzione delle tabelle da Lakehouse.

Lo script seguente fornisce un modello per questi passaggi e deve essere eseguito in un lakehouse:

from delta.tables import DeltaTable

# 1. CONFIGURE LIMITS

# Cap files to 2M rows during writes. This should be done before data ingestion occurs. 
spark.conf.set("spark.sql.files.maxRecordsPerFile", 2000000)

# 2. INGEST DATA
# Here, you ingest data into your table 

# 3. CAP FILE SIZE (~4GB)
spark.conf.set("spark.databricks.delta.optimize.maxFileSize", 4 * 1024 * 1024 * 1024)

# 4. RUN OPTIMIZE (bin-packing)
spark.sql("""
    OPTIMIZE myTable
""")

Per mantenere dimensioni dei file ottimali, eseguire periodicamente operazioni di ottimizzazione di Delta come OPTIMIZE, in particolare per le tabelle soggette a frequenti inserimenti incrementali, aggiornamenti ed eliminazioni. Queste operazioni di manutenzione compattano i file di piccole dimensioni in quelli di dimensioni appropriate, assicurandosi che l'endpoint di analisi SQL possa elaborare le query in modo efficiente.

Considerazioni sulle dimensioni delle partizioni

La scelta della colonna di partizione per una tabella delta in un lakehouse influisce anche sul tempo necessario per sincronizzare le modifiche all'endpoint di analisi SQL. 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. Seguire le procedure consigliate per la manutenzione e l'ottimizzazione delle 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. È possibile trovarsi con un grande numero 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

Usare il notebook seguente per stampare un report che descrive le dimensioni e i dettagli delle partizioni alla base di una Tabella Delta.

1. In primo luogo, specificare il percorso ABFSS per la 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 restituisce 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 delle tabelle Delta Lake e V-Order
• La compattazione della tabella
• Ottimizzare le dimensioni del file
• Casi d'uso degli endpoint di analisi SQL di Lakehouse