Ottimizzare le tabelle lakehouse in base alle verifiche di integrità

Si applica a:✅ Endpoint di analisi SQL in Microsoft Fabric

Questa esercitazione illustra come creare una pipeline di Microsoft Fabric per eseguire la manutenzione intelligente delle tabelle.

Questa soluzione chiama la sys.sp_get_table_health_metrics procedura archiviata T-SQL sull'endpoint di analisi SQL di Lakehouse, valuta il risultato ed esegue OPTIMIZE solo quando la tabella necessita effettivamente di manutenzione. Questo modello "check-then-act" evita costi di elaborazione inutili per le tabelle in buono stato, garantendo al contempo che le tabelle degradate siano sottoposte automaticamente a manutenzione.

Perché è necessaria la manutenzione

Le tabelle Lakehouse possono accumulare troppi file Parquet di piccole dimensioni nel tempo, che danneggiano le prestazioni delle query nell'endpoint di analisi SQL.

Anziché eseguire OPTIMIZE secondo una pianificazione fissa, indipendentemente dallo stato della tabella, questa pipeline prende una decisione basata sui dati: verifica prima lo stato di salute della tabella e attiva l'ottimizzazione solo quando viene rilevata un'anomalia.

Prerequisiti

Prima di iniziare, assicurarsi di avere:

• Un'area di lavoro Microsoft Fabric con autorizzazioni di collaboratore o superiori.
• Una lakehouse in tale area di lavoro contenente almeno una tabella Delta che si vuole monitorare. In questa esercitazione viene usato un lakehouse denominato SalesDataLakehouse.
• Familiarità con le pipeline di dati Fabric.
• Familiarità con i notebook di Fabric.

Struttura della soluzione

La pipeline completata ha la seguente struttura:

1. Attività script: viene eseguita sulla tabella di destinazione e restituisce sp_get_table_health_metrics le metriche di integrità della tabella come output strutturato.
2. attività If Condition: legge PotentialAnomalyType direttamente dall'output dello script e controlla se è maggiore di zero. Per ulteriori informazioni su PotentialAnomalyType, consulta i codici dei tipi di anomalia potenziale.
3. Attività del notebook (all'interno del ramo True): esegue OPTIMIZE sulla tabella da un notebook Spark.

Al termine di questa esercitazione, si avrà un notebook che accetta i parametri dalla pipeline e ottimizza una tabella quando viene attivata.

Passaggio 1: Creare il notebook di ottimizzazione

Il notebook accetta come parametri dalla pipeline il Lakehouse di destinazione, lo schema e il nome della tabella, quindi esegue OPTIMIZE usando Spark SQL.

1. Nell'area di lavoro Fabric selezionare + Nuovo elemento>Notebook.
2. Assegnare al notebook il nome Optimize-Table.
3. In Posizione, seleziona il lakehouse in cui sono archiviate le tabelle selezionate. Questo esercizio usa un Lakehouse chiamato SalesDataLakehouse.
4. Fare clic su Crea.

Aggiungere la cella del parametro

La prima cella definisce le variabili di cui la pipeline esegue l'override in fase di esecuzione.

1. Nella prima cella immettere i parametri seguenti. I valori non sono importanti e la pipeline ne esegue l'override in fase di esecuzione.

   # Parameters 
   lakehouse_name = "<LakehouseName>"
   schema_name    = "<SchemaName>"
   table_name     = "<TableName>"
   

   Importante

   Funzionamento della parametrizzazione nei notebook di Fabric: in fase di esecuzione, Fabric inserisce una nuova cella immediatamente dopo la cella del parametro che riassegna queste variabili con i valori passati dalla pipeline. I valori impostati qui inizializzano solo le variabili e migliorano la leggibilità.

2. Selezionare il menu cella (...) >Attivare o disattivare la cella del parametro per contrassegnare questa cella come cella di parametro.

Aggiungere la cella OPTIMIZE

Il OPTIMIZE comando è un comando Spark SQL, non un comando T-SQL. È necessario eseguirlo in ambienti Spark, ad esempio notebook, definizioni di processi Spark o l'interfaccia Lakehouse Maintenance. L'endpoint di analisi SQL e l'editor di query SQL warehouse non supportano direttamente questo comando.

1. Nella seconda cella immettere:

   full_name = f"{lakehouse_name}.{schema_name}.{table_name}"
   print(f"Optimizing {full_name} ...")
   
   result = spark.sql(f"OPTIMIZE {full_name}")
   result.show(truncate=False)
   

2. Aggiungere celle Markdown in base alle esigenze per documentare correttamente il notebook per altri utenti. Il notebook completato dovrebbe essere simile al seguente:

   

Passaggio 2: Creare la pipeline

1. Nell'area di lavoro di Fabric, seleziona + Nuovo elemento>Pipeline.

2. Assegna alla pipeline il nome Check-and-Optimize-Table.

3. Selezionare lo sfondo dell'area di disegno della pipeline e quindi aprire la scheda Parametri . Aggiungere tre parametri:

   Name	Type	Valore predefinito
   lakehouse_name	Stringa	SalesDataLakehouse
   schema_name	Stringa	dbo
   table_name	Stringa	FactSales

Passaggio 3: Aggiungere l'attività Script

L'attività Script viene eseguita sys.sp_get_table_health_metrics nell'endpoint di analisi SQL e acquisisce il risultato.

1. Nella scheda Attività selezionare Script per aggiungerlo nell'area di disegno.
2. Assegnale il nome Controllo integrità tabella.
3. Nella scheda Impostazioni :

   • Connessione: selezionare l'endpoint di analisi SQL per Lakehouse. Se non è elencato, selezionare Sfoglia tutto nella parte inferiore dell'elenco a discesa e quindi individuare l'endpoint di analisi SQL di Lakehouse.

   • Tipo di script: selezionare Query.

   • Script: selezionare Aggiungi contenuto dinamico e immettere l'espressione seguente:

     @concat('EXEC sys.sp_get_table_health_metrics ''',
             pipeline().parameters.schema_name, '.',
             pipeline().parameters.table_name, '''')
     

Questa espressione produce il comando SQL che esegue la stored procedure sulla tabella di destinazione, ad esempio : EXEC sys.sp_get_table_health_metrics 'dbo.FactSales'.

Verificare l'output dello script

Esegui la pipeline una volta ed esamina l'output dell'attività Script. Viene visualizzato un oggetto JSON simile al seguente:

{
  "resultSetCount": 1,
  "resultSets": [
    {
      "rowCount": 1,
      "rows": [
        {
          "PotentialAnomalyType": 3,
          "PotentialAnomalyDescription": "Too many small files...",
          "FileCount": 2688,
          "...": "..."
        }
      ]
    }
  ]
}

Passaggio 4: Aggiungi l'attività If Condition

L'attività Condizione If legge PotentialAnomalyType direttamente dall'output dell'attività Script e prende una decisione in base al risultato. Seguire questa procedura:

1. Nella scheda Attività selezionare If Condition (Condizione if ) per aggiungere un'attività nell'area di disegno.

2. Chiamalo Check Anomaly.

3. Disegna una freccia Successo (verde) da Verifica integrità tabella a Verifica anomalia.

4. Nella scheda Attività dell'attività If Condition, imposta Expression su:

   @greater(int(activity('Check Table Health').output.resultSets[0].rows[0]['PotentialAnomalyType']), 0)
   

Questa espressione legge la prima riga restituita da sys.sp_get_table_health_metrics, esegue il cast PotentialAnomalyType a un numero intero e restituisce true quando il valore è maggiore di zero, che indica un'anomalia rilevata nella tabella di destinazione.

Passaggio 5: Aggiungi l'attività Notebook (ramo True)

Con l'attività If Condition selezionata, seleziona Modifica (icona a forma di matita) accanto a True. Il canvas passa a un sottocanvas relativo al ramo True.

1. Trascina un'attività Notebook nella sotto-area di disegno True.

2. Chiamalo Run OPTIMIZE.

3. Nella scheda Impostazioni:

   • Notebook: selezionare il notebook Optimize-Table creato nel passaggio 1.

   • Espandere Parametri di base, quindi aggiungere tre righe:

     Name	Type	Value
     lakehouse_name	Stringa	@pipeline().parameters.lakehouse_name
     schema_name	Stringa	@pipeline().parameters.schema_name
     table_name	Stringa	@pipeline().parameters.table_name

I tre valori delle colonne dei nomi devono corrispondere esattamente ai nomi delle variabili nella cella del parametro del notebook.

La pipeline completata dovrebbe apparire come segue:

Passaggio 6: Convalidare ed eseguire

1. Selezionare Convalida sulla barra degli strumenti della pipeline per verificare la presenza di errori di configurazione.

2. Selezionare Esegui per eseguire manualmente la pipeline.

3. Monitorate l'esecuzione e confermate:

   1. Verificare lo stato della tabella: esaminare l'output generato da questa attività quando viene eseguita. L'output della sys.sp_get_table_health_metrics stored procedure dovrebbe essere visualizzato in formato JSON.
   2. Controllare l'anomalia: valuta correttamente leggendo PotentialAnomalyType direttamente dall'output dello script.
   3. Esegui OPTIMIZE (solo se PotentialAnomalyType > 0): se l'attività Check Anomaly restituisce True, esamina l'input dell'attività Run OPTIMIZE per verificare che usi i parametri corretti (nome del Lakehouse, schema e nome della tabella) e controlla l'output per esaminare i messaggi dell'operazione OPTIMIZE.

Pulire le risorse

Se sono state create risorse solo per questa esercitazione e non sono più necessarie, eliminare gli elementi seguenti dall'area di lavoro:

• La pipeline Check-and-Optimize-Table.
• Il notebook Optimize-Table.

Contenuti correlati

• sp_get_table_health_metrics (Transact-SQL)
• Linee guida sulle prestazioni in Fabric Data Warehouse