Lakehouse-tabellen optimaliseren op basis van statuscontroles

Van toepassing op:✅ SQL Analytics-eindpunt in Microsoft Fabric

In deze zelfstudie leert u hoe u een Microsoft Fabric Pipeline bouwt om intelligent tabelonderhoud uit te voeren.

Deze oplossing roept de sys.sp_get_table_health_metrics opgeslagen T-SQL-procedure aan op het Lakehouse SQL-analyse-eindpunt, evalueert het resultaat en wordt alleen uitgevoerd OPTIMIZE wanneer de tabel daadwerkelijk onderhoud nodig heeft. Dit patroon 'check-then-act' voorkomt onnodige rekenuitgaven voor tabellen die in orde zijn en zorgen ervoor dat gedegradeerde tabellen automatisch worden onderhouden.

Waarom onderhoud nodig is

Lakehouse-tabellen kunnen in de loop van de tijd te veel kleine Parquet-bestanden verzamelen, wat de queryprestaties op het SQL-analyse-eindpunt pijn doet.

In plaats van OPTIMIZE volgens een vast schema uit te voeren, ongeacht de status van de tabel, neemt deze pijplijn een weloverwogen beslissing: hij controleert eerst de status van de tabel en start alleen een optimalisatie als er een anomalie wordt gedetecteerd.

Prerequisites

Voordat u begint, moet u ervoor zorgen dat u het volgende hebt:

Oplossingsstructuur

De voltooide pijplijn heeft deze structuur:

  1. Scriptactiviteit: Voert sp_get_table_health_metrics uit op de doeltabel en retourneert statistieken over de status van de tabel als gestructureerde uitvoer.
  2. If Condition activity: Leest PotentialAnomalyType rechtstreeks vanuit de scriptuitvoer en controleert of deze groter is dan nul. Zie PotentialAnomalyType voor meer informatie over de code.
  3. Notebook-activiteit (binnen de True-vertakking): voert OPTIMIZE uit op de tabel vanuit een Spark-notebook.

Aan het einde van deze zelfstudie hebt u een notebook dat parameters uit de pijplijn gebruikt en een tabel optimaliseert wanneer deze wordt geactiveerd.

Stap 1: het optimalisatienotitieblok maken

Het notebook ontvangt de naam van het doellakehouse, het schema en de tabelnaam als parameters van de pijplijn en voert vervolgens OPTIMIZE uit met behulp van Spark SQL.

  1. Selecteer in uw Fabric-werkruimte + Nieuw item>Notitieblok.
  2. Geef het notebook de naam Optimize-Table.
  3. Selecteer onder Locatie het Lakehouse waar de tabellen die u controleert, zijn opgeslagen. In deze oefening wordt gebruikgemaakt van een Lakehouse met de naam SalesDataLakehouse.
  4. Klik op Creëren.

De parametercel toevoegen

De eerste cel definieert de variabelen die de pijplijn tijdens runtime overschrijft.

  1. Voer in de eerste cel de volgende parameters in. De waarden zijn niet belangrijk en de pijplijn overschrijft deze tijdens runtime.

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

    Important

    Hoe parameterisatie werkt in Fabric notebooks: tijdens runtime injecteert Fabric een nieuwe cel direct na de parametercel waarmee deze variabelen opnieuw worden toegewezen met de waarden die door de pijplijn worden doorgegeven. De waarden die u hier instelt initialiseren alleen de variabelen en verbeteren de leesbaarheid.

  2. Selecteer het celmenu (...) >Schakel parametercel in om deze cel als parametercel te markeren.

De cel OPTIMALISEREN toevoegen

De OPTIMIZE opdracht is een Spark SQL-opdracht, niet een T-SQL-opdracht. U moet deze uitvoeren in Spark-omgevingen, zoals notebooks, Spark-taakdefinities of de interface Voor Lakehouse-onderhoud. Het SQL Analytics-eindpunt en de SQL-query-editor van het warehouse bieden geen ondersteuning voor deze opdracht.

  1. Voer in de tweede cel het volgende in:

    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. Voeg waar nodig Markdown-cellen toe om het notitieblok goed te documenteren voor andere gebruikers. Uw voltooide notebook moet er ongeveer als volgt uitzien:

    Schermopname van een Fabric notebook met de titel 'Optimize a Lakehouse table when health checks show it's needed', met twee PySpark-cellen: één cel stelt de door de pijplijn opgegeven lakehouse-, schema- en tabelparameters in, en de andere cel voert een OPTIMIZE-opdracht uit voor de geselecteerde Lakehouse-tabel.

Note

In dit voorbeeld wordt uitgegaan van een Lakehouse waarbij schema's zijn ingeschakeld. Pas de driedelige naam full_name dienovereenkomstig aan als u geen Lakehouse-schema's gebruikt.

Stap 2: De pijplijn maken

  1. Selecteer in uw Fabric-werkruimte + Nieuw item>Pipeline.

  2. Geef de pijplijn de naam Check-and-Optimize-Table.

  3. Selecteer de achtergrond van het pijplijncanvas en open vervolgens het tabblad Parameters . Voeg drie parameters toe:

    Naam Type Standaardwaarde
    lakehouse_name String SalesDataLakehouse
    schema_name String dbo
    table_name String FactSales

Stap 3: De scriptactiviteit toevoegen

De scriptactiviteit wordt uitgevoerd sys.sp_get_table_health_metrics op het SQL Analytics-eindpunt en legt het resultaat vast.

Important

Gebruik de scriptactiviteit , niet de activiteit Opgeslagen procedure . Alleen de scriptactiviteit maakt de resultatenset beschikbaar als gestructureerde JSON-uitvoer die downstreamactiviteiten kunnen parseren.

  1. Selecteer op het tabblad ActiviteitenScript om het aan het canvas toe te voegen.
  2. Noem het Tabelstatus controleren.
  3. Op het tabblad Instellingen :

    • Verbinding: Selecteer het SQL Analytics-eindpunt voor uw Lakehouse. Als deze niet in de lijst staat, selecteert u Alles weergeven onderaan de vervolgkeuzelijst en zoekt u vervolgens het SQL-analyse-eindpunt van uw Lakehouse.

    • Scripttype: Selecteer Query.

    • Script: Selecteer Dynamische inhoud toevoegen en voer de volgende expressie in:

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

Deze expressie produceert de SQL-opdracht waarmee de opgeslagen procedure wordt uitgevoerd voor de doeltabel, bijvoorbeeld: EXEC sys.sp_get_table_health_metrics 'dbo.FactSales'.

De scriptuitvoer controleren

Voer de pijplijn een keer uit en controleer de uitvoer van de Script-activiteit. U ziet een JSON-object dat lijkt op:

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

Important

Het werkelijke resultaat kan variëren op basis van de status van de tabel. Het belangrijkste is dat het de kolommen retourneert die door sys.sp_get_table_health_metrics worden blootgesteld.

Stap 4: De activiteit If Condition toevoegen

De If Condition-activiteit leest PotentialAnomalyType rechtstreeks vanuit de uitvoer van de scriptactiviteit en neemt een beslissing op basis van het resultaat. Gebruik de volgende stappen:

  1. Selecteer op het tabblad ActiviteitenIf Condition om een activiteit aan het canvas toe te voegen.

  2. Noem het Check Anomaly.

  3. Teken een Succes-pijl (groen) van De tabelstatus controleren naar De anomalie controleren.

  4. Stel op het tabblad Activiteiten van de activiteit If Condition de expressie in op:

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

Deze expressie leest de eerste rij die wordt geretourneerd door sys.sp_get_table_health_metrics, castt PotentialAnomalyType naar een geheel getal en evalueert naar true wanneer de waarde groter is dan nul, wat een anomalie aangeeft die in de doeltabel wordt gedetecteerd.

Stap 5: De Notebook-activiteit toevoegen (True-vertakking)

Als de activiteit If Condition is geselecteerd, selecteert u Bewerken (potloodpictogram) naast Waar. Het canvas schakelt over naar een subcanvas dat is afgebakend tot de vertakking True.

  1. Sleep een Notebook-activiteit naar het True-subcanvas.

  2. Noem het Run OPTIMIZE.

  3. Op het tabblad Instellingen :

    • Notebook: Selecteer het notebook Optimize-Table dat u in stap 1 hebt gemaakt.

    • Vouw basisparameters uit en voeg vervolgens drie rijen toe:

      Naam Type Waarde
      lakehouse_name String @pipeline().parameters.lakehouse_name
      schema_name String @pipeline().parameters.schema_name
      table_name String @pipeline().parameters.table_name

De waarden in de kolom Naam moeten precies overeenkomen met de namen van de variabelen in de parametercel van het notebook.

Note

U kunt onjuiste activiteiten leeg laten. De activiteit If Condition behandelt een lege False-vertakking als no-op en markeert de pijplijn als geslaagd.

De voltooide pijplijn moet er als volgt uitzien:

Schermopname van een Fabric-datapijplijn met een scriptactiviteit ‘Check Table Health’ die is verbonden met de voorwaardelijke activiteit ‘Check Anomaly’. De waar-tak voert een OPTIMIZE-notebookactiviteit uit, terwijl de onwaar-tak geen activiteiten heeft.

Stap 6: Valideren en uitvoeren

  1. Selecteer Valideren op de werkbalk van de pijplijn om te controleren op configuratiefouten.

  2. Selecteer Uitvoeren om de pijplijn handmatig uit te voeren.

  3. Controleer de uitvoering en bevestig het volgende:

    1. Controleer de tabelstatus: inspecteer de uitvoer van deze activiteit wanneer deze wordt uitgevoerd. U zou de uitvoer van de sys.sp_get_table_health_metrics opgeslagen procedure in JSON-indeling moeten zien.
    2. Controleer anomalie: evalueert correct door rechtstreeks vanuit de scriptuitvoer te lezen PotentialAnomalyType .
    3. Voer OPTIMIZE uit (alleen als PotentialAnomalyType > 0): als de activiteit AnomaliecontroleWaar evalueert, controleert u de invoer van de run OPTIMIZE-activiteit om te controleren of de juiste parameters (Lakehouse-naam, schema en tabelnaam) worden gebruikt en controleert u de uitvoer om de berichten van de OPTIMIZE bewerking te controleren.

De hulpbronnen opschonen

Als u alleen resources voor deze zelfstudie hebt gemaakt en deze niet meer nodig hebt, verwijdert u de volgende items uit uw werkruimte:

  • De Check-and-Optimize-Table-pijplijn.
  • Het notebook Optimize-Table.

Verwante inhoud

  • Last updated on