Använda Databricks SQL i ett Azure Databricks-jobb

Artikel
04/16/2024

Du kan använda SQL-aktivitetstypen i ett Azure Databricks-jobb så att du kan skapa, schemalägga, driva och övervaka arbetsflöden som innehåller Databricks SQL-objekt som frågor, äldre instrumentpaneler och aviseringar. Ditt arbetsflöde kan till exempel mata in data, förbereda data, utföra analys med databricks SQL-frågor och sedan visa resultatet i en äldre instrumentpanel.

Den här artikeln innehåller ett exempelarbetsflöde som skapar en äldre instrumentpanel som visar mått för GitHub-bidrag. I det här exemplet kommer du att:

Mata in GitHub-data med hjälp av ett Python-skript och GitHub REST API.
Transformera GitHub-data med hjälp av en Delta Live Tables-pipeline.
Utlösa Databricks SQL-frågor som utför analys på förberedda data.
Visa analysen på en äldre instrumentpanel.

Instrumentpanel för GitHub-analys

Innan du börjar

Du behöver följande för att slutföra den här genomgången:

En personlig Åtkomsttoken för GitHub. Den här token måste ha lagringsplatsens behörighet.
Ett serverlöst SQL-lager eller ett pro SQL-lager. Se SQL-lagertyper.
Ett databrickshemlighetsomfång. Det hemliga omfånget används för att lagra GitHub-token på ett säkert sätt. Se Steg 1: Lagra GitHub-token i en hemlighet.

Steg 1: Lagra GitHub-token i en hemlighet

I stället för att hårdkoda autentiseringsuppgifter som personlig åtkomsttoken för GitHub i ett jobb rekommenderar Databricks att du använder ett hemligt omfång för att lagra och hantera hemligheter på ett säkert sätt. Följande Databricks CLI-kommandon är ett exempel på hur du skapar ett hemligt omfång och lagrar GitHub-token i en hemlighet i det omfånget:

databricks secrets create-scope <scope-name>
databricks secrets put-secret <scope-name> <token-key> --string-value <token>

Ersätt <scope-name med namnet på ett hemligt Azure Databricks-omfång för att lagra token.
Ersätt <token-key> med namnet på en nyckel som token ska tilldelas.
Ersätt <token> med värdet för den personliga åtkomsttoken för GitHub.

Steg 2: Skapa ett skript för att hämta GitHub-data

Följande Python-skript använder GitHub REST API för att hämta data om incheckningar och bidrag från en GitHub-lagringsplats. Indataargument anger GitHub-lagringsplatsen. Posterna sparas på en plats i DBFS som anges av ett annat indataargument.

I det här exemplet används DBFS för att lagra Python-skriptet, men du kan också använda Databricks Git-mappar eller arbetsytefiler för att lagra och hantera skriptet.

Spara skriptet på en plats på den lokala disken:

import json
import requests
import sys

api_url = "https://api.github.com"

def get_commits(owner, repo, token, path):
  page = 1
  request_url =  f"{api_url}/repos/{owner}/{repo}/commits"
  more = True

  get_response(request_url, f"{path}/commits", token)

def get_contributors(owner, repo, token, path):
  page = 1
  request_url =  f"{api_url}/repos/{owner}/{repo}/contributors"
  more = True

  get_response(request_url, f"{path}/contributors", token)

def get_response(request_url, path, token):
  page = 1
  more = True

  while more:
    response = requests.get(request_url, params={'page': page}, headers={'Authorization': "token " + token})
    if response.text != "[]":
      write(path + "/records-" + str(page) + ".json", response.text)
      page += 1
    else:
      more = False

def write(filename, contents):
  dbutils.fs.put(filename, contents)

def main():
  args = sys.argv[1:]
  if len(args) < 6:
    print("Usage: github-api.py owner repo request output-dir secret-scope secret-key")
    sys.exit(1)

  owner = sys.argv[1]
  repo = sys.argv[2]
  request = sys.argv[3]
  output_path = sys.argv[4]
  secret_scope = sys.argv[5]
  secret_key = sys.argv[6]

  token = dbutils.secrets.get(scope=secret_scope, key=secret_key)

  if (request == "commits"):
    get_commits(owner, repo, token, output_path)
  elif (request == "contributors"):
    get_contributors(owner, repo, token, output_path)

if __name__ == "__main__":
    main()

Ladda upp skriptet till DBFS:
1. Gå till din Azure Databricks-landningssida och klicka på Katalog i sidofältet.
2. Klicka på Bläddra i DBFS.
3. I DBFS-filwebbläsaren klickar du på Ladda upp. Dialogrutan Ladda upp data till DBFS visas.
4. Ange en sökväg i DBFS för att lagra skriptet, klicka på Släpp filer att ladda upp eller klicka för att bläddra och välj Python-skriptet.
5. Klicka på Klart.

Steg 3: Skapa en Delta Live Tables-pipeline för att bearbeta GitHub-data

I det här avsnittet skapar du en Delta Live Tables-pipeline för att konvertera råa GitHub-data till tabeller som kan analyseras av Databricks SQL-frågor. Utför följande steg för att skapa pipelinen:

I sidofältet klickar du på Ny och väljer Anteckningsbok på menyn. Dialogrutan Skapa anteckningsbok visas.
I Standardspråk anger du ett namn och väljer Python. Du kan lämna Kluster inställt på standardvärdet. Delta Live Tables-körningen skapar ett kluster innan pipelinen körs.
Klicka på Skapa.

Kopiera Python-kodexemplet och klistra in det i den nya notebook-filen. Du kan lägga till exempelkoden i en enda cell i notebook-filen eller flera celler.

import dlt
from pyspark.sql.functions import *

def parse(df):
   return (df
     .withColumn("author_date", to_timestamp(col("commit.author.date")))
     .withColumn("author_email", col("commit.author.email"))
     .withColumn("author_name", col("commit.author.name"))
     .withColumn("comment_count", col("commit.comment_count"))
     .withColumn("committer_date", to_timestamp(col("commit.committer.date")))
     .withColumn("committer_email", col("commit.committer.email"))
     .withColumn("committer_name", col("commit.committer.name"))
     .withColumn("message", col("commit.message"))
     .withColumn("sha", col("commit.tree.sha"))
     .withColumn("tree_url", col("commit.tree.url"))
     .withColumn("url", col("commit.url"))
     .withColumn("verification_payload", col("commit.verification.payload"))
     .withColumn("verification_reason", col("commit.verification.reason"))
     .withColumn("verification_signature", col("commit.verification.signature"))
     .withColumn("verification_verified", col("commit.verification.signature").cast("string"))
     .drop("commit")
   )

@dlt.table(
   comment="Raw GitHub commits"
)
def github_commits_raw():
  df = spark.read.json(spark.conf.get("commits-path"))
  return parse(df.select("commit"))

@dlt.table(
  comment="Info on the author of a commit"
)
def commits_by_author():
  return (
    dlt.read("github_commits_raw")
      .withColumnRenamed("author_date", "date")
      .withColumnRenamed("author_email", "email")
      .withColumnRenamed("author_name", "name")
      .select("sha", "date", "email", "name")
  )

@dlt.table(
  comment="GitHub repository contributors"
)
def github_contributors_raw():
  return(
    spark.readStream.format("cloudFiles")
      .option("cloudFiles.format", "json")
      .load(spark.conf.get("contribs-path"))
  )

I sidofältet klickar du på Arbetsflöden, klickar på fliken Delta Live Tables och klickar på Skapa pipeline.
Ge pipelinen ett namn, till exempel Transform GitHub data.
I fältet Notebook-bibliotek anger du sökvägen till anteckningsboken eller klickar på för att välja anteckningsboken.
Klicka på Lägg till konfiguration. Key I textrutan anger du commits-path. Value I textrutan anger du DBFS-sökvägen där GitHub-posterna ska skrivas. Det kan vara vilken sökväg du vill och är samma sökväg som du använder när du konfigurerar den första Python-uppgiften när du skapar arbetsflödet.
Klicka på Lägg till konfiguration igen. Key I textrutan anger du contribs-path. Value I textrutan anger du DBFS-sökvägen där GitHub-posterna ska skrivas. Det här kan vara vilken sökväg du vill och är samma sökväg som du använder när du konfigurerar den andra Python-uppgiften när du skapar arbetsflödet.
I fältet Mål anger du en måldatabas, github_tablestill exempel . Om du anger en måldatabas publiceras utdata till metaarkivet och krävs för de underordnade frågorna som analyserar data som produceras av pipelinen.
Klicka på Spara.

Steg 4: Skapa ett arbetsflöde för att mata in och transformera GitHub-data

Innan du analyserar och visualiserar GitHub-data med Databricks SQL måste du mata in och förbereda data. Utför följande steg för att skapa ett arbetsflöde för att slutföra dessa uppgifter:

Skapa ett Azure Databricks-jobb och lägg till den första uppgiften

Gå till din Azure Databricks-landningssida och gör något av följande:
- I sidofältet klickar du på Arbetsflöden och klickar på .
- I sidofältet klickar du på Nytt och väljer Jobb på menyn.
I dialogrutan aktivitet som visas på fliken Uppgifter ersätter du Lägg till ett namn för jobbet... med ditt jobbnamn, till exempel GitHub analysis workflow.
I Uppgiftsnamn anger du ett namn för aktiviteten, till exempel get_commits.
I Typ väljer du Python-skript.
I Källa väljer du DBFS/S3.
I Sökväg anger du sökvägen till skriptet i DBFS.
I Parametrar anger du följande argument för Python-skriptet:

["<owner>","<repo>","commits","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Ersätt <owner> med namnet på lagringsplatsens ägare. Om du till exempel vill hämta poster från github.com/databrickslabs/overwatch lagringsplatsen anger du databrickslabs.
- Ersätt <repo> med lagringsplatsens namn, till exempel overwatch.
- Ersätt <DBFS-output-dir> med en sökväg i DBFS för att lagra de poster som hämtats från GitHub.
- Ersätt <scope-name> med namnet på det hemliga omfång som du skapade för att lagra GitHub-token.
- Ersätt <github-token-key> med namnet på den nyckel som du har tilldelat till GitHub-token.
Klicka på Spara uppgift.

Lägg till en annan uppgift

Klicka under den uppgift som du nyss skapade.
I Uppgiftsnamn anger du ett namn för aktiviteten, till exempel get_contributors.
I Typ väljer du aktivitetstypen Python-skript .
I Källa väljer du DBFS/S3.
I Sökväg anger du sökvägen till skriptet i DBFS.
I Parametrar anger du följande argument för Python-skriptet:

["<owner>","<repo>","contributors","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Ersätt <owner> med namnet på lagringsplatsens ägare. Om du till exempel vill hämta poster från github.com/databrickslabs/overwatch lagringsplatsen anger du databrickslabs.
- Ersätt <repo> med lagringsplatsens namn, till exempel overwatch.
- Ersätt <DBFS-output-dir> med en sökväg i DBFS för att lagra de poster som hämtats från GitHub.
- Ersätt <scope-name> med namnet på det hemliga omfång som du skapade för att lagra GitHub-token.
- Ersätt <github-token-key> med namnet på den nyckel som du har tilldelat till GitHub-token.
Klicka på Spara uppgift.

Lägga till en uppgift för att transformera data

Klicka under den uppgift som du nyss skapade.
I Uppgiftsnamn anger du ett namn för aktiviteten, till exempel transform_github_data.
I Typ väljer du Delta Live Tables-pipeline och anger ett namn för uppgiften.
I Pipeline väljer du den pipeline som skapades i steg 3: Skapa en Delta Live Tables-pipeline för att bearbeta GitHub-data.
Klicka på Skapa.

Steg 5: Kör arbetsflödet för datatransformering

Klicka för att köra arbetsflödet. Om du vill visa information om körningen klickar du på länken i kolumnen Starttid för körningen i jobbkörningsvyn. Klicka på varje aktivitet för att visa information om aktivitetskörningen.

Steg 6: (Valfritt) Utför följande steg för att visa utdata när arbetsflödeskörningen har slutförts:

I körningsinformationsvyn klickar du på aktiviteten Delta Live Tables.
I panelen Aktivitetskörningsinformation klickar du på pipelinenamnet under Pipeline. Sidan Pipelineinformation visas.
Välj tabellen commits_by_author i pipelinen DAG.
Klicka på tabellnamnet bredvid Metaarkiv i panelen commits_by_author . Sidan Katalogutforskaren öppnas.

I Katalogutforskaren kan du visa tabellschemat, exempeldata och annan information för data. Följ samma steg för att visa data för github_contributors_raw tabellen.

Steg 7: Ta bort GitHub-data

I ett verkligt program kanske du kontinuerligt matar in och bearbetar data. Eftersom det här exemplet laddar ned och bearbetar hela datamängden måste du ta bort de redan nedladdade GitHub-data för att förhindra ett fel när arbetsflödet körs igen. Utför följande steg för att ta bort nedladdade data:

Skapa en ny notebook-fil och ange följande kommandon i den första cellen:
```
dbutils.fs.rm("<commits-path", True)
dbutils.fs.rm("<contributors-path", True)
```
Ersätt <commits-path> och <contributors-path> med de DBFS-sökvägar som du konfigurerade när du skapade Python-aktiviteterna.
Klicka och välj Kör cell.

Du kan också lägga till den här notebook-filen som en uppgift i arbetsflödet.

Steg 8: Skapa Databricks SQL-frågor

När du har kört arbetsflödet och skapat de obligatoriska tabellerna skapar du frågor för att analysera förberedda data. Utför följande steg för att skapa exempelfrågor och visualiseringar:

Visa de 10 främsta bidragsgivarna per månad

Klicka på ikonen under Databricks-logotypen i sidofältet och välj SQL.
Klicka på Skapa en fråga för att öppna Databricks SQL-frågeredigeraren.
Kontrollera att katalogen är inställd på hive_metastore. Klicka på standard bredvid hive_metastore och ange databasen till det målvärde som du angav i pipelinen Delta Live Tables.

På fliken Ny fråga anger du följande fråga:

SELECT
  date_part('YEAR', date) AS year,
  date_part('MONTH', date) AS month,
  name,
  count(1)
FROM
  commits_by_author
WHERE
  name IN (
    SELECT
      name
    FROM
      commits_by_author
    GROUP BY
      name
    ORDER BY
      count(name) DESC
    LIMIT 10
  )
  AND
    date_part('YEAR', date) >= 2022
GROUP BY
  name, year, month
ORDER BY
  year, month, name

Klicka på fliken Ny fråga och byt namn på frågan, till exempel Commits by month top 10 contributors.
Som standard visas resultatet som en tabell. Om du vill ändra hur data visualiseras, till exempel med hjälp av ett stapeldiagram, klickar du på och klickar på Redigera i resultatpanelen.
I Visualiseringstyp väljer du Stapel.
I X-kolumnen väljer du månad.
I Y-kolumner väljer du count(1).
I Gruppera efter väljer du namn.
Klicka på Spara.

Visa de 20 främsta bidragsgivarna

Klicka på + Skapa ny fråga och kontrollera att katalogen är inställd på hive_metastore>. Klicka på standard bredvid hive_metastore och ange databasen till det målvärde som du angav i pipelinen Delta Live Tables.

Ange följande fråga:

SELECT
  login,
  cast(contributions AS INTEGER)
FROM
  github_contributors_raw
ORDER BY
  contributions DESC
LIMIT 20

Klicka på fliken Ny fråga och byt namn på frågan, till exempel Top 20 contributors.
Om du vill ändra visualiseringen från standardtabellen klickar du påoch klickar på Redigera i resultatpanelen.
I Visualiseringstyp väljer du Stapel.
I X-kolumnen väljer du inloggning.
I Y-kolumner väljer du bidrag.
Klicka på Spara.

Visa totalt antal incheckningar per författare

Klicka på + Skapa ny fråga och kontrollera att katalogen är inställd på hive_metastore>. Klicka på standard bredvid hive_metastore och ange databasen till det målvärde som du angav i pipelinen Delta Live Tables.

Ange följande fråga:

SELECT
  name,
  count(1) commits
FROM
  commits_by_author
GROUP BY
  name
ORDER BY
  commits DESC
LIMIT 10

Klicka på fliken Ny fråga och byt namn på frågan, till exempel Total commits by author.
Om du vill ändra visualiseringen från standardtabellen klickar du påoch klickar på Redigera i resultatpanelen.
I Visualiseringstyp väljer du Stapel.
I X-kolumnen väljer du namn.
I Y-kolumner väljer du incheckningar.
Klicka på Spara.

Steg 9: Skapa en instrumentpanel

I sidopanelen klickar du på Instrumentpaneler
Klicka på Skapa instrumentpanel.
Ange ett namn för instrumentpanelen, till exempel GitHub analysis.
För varje fråga och visualisering som skapas i steg 8: Skapa Databricks SQL-frågor klickar du på Lägg till > visualisering och väljer varje visualisering.

Steg 10: Lägg till SQL-uppgifter i arbetsflödet

Om du vill lägga till de nya frågeuppgifterna i arbetsflödet som du skapade i Skapa ett Azure Databricks-jobb och lägga till den första uppgiften för varje fråga som du skapade i steg 8: Skapa Databricks SQL-frågor:

Klicka på Arbetsflöden i sidofältet.
Klicka på jobbnamnet i kolumnen Namn.
Klicka på fliken Uppgifter .
Klicka under den senaste aktiviteten.
Ange ett namn för uppgiften i Typ väljer DU SQL och i SQL-uppgift väljer du Fråga.
Välj frågan i SQL-frågan.
I SQL Warehouse väljer du ett serverlöst SQL-lager eller ett pro SQL-lager för att köra uppgiften.
Klicka på Skapa.

Steg 11: Lägg till en instrumentpanelsaktivitet

Klicka under den senaste aktiviteten.
Ange ett namn för uppgiften i Typ, välj SQL och välj Äldre instrumentpanel i SQL-uppgift.
Välj instrumentpanelen som skapades i Steg 9: Skapa en instrumentpanel.
I SQL Warehouse väljer du ett serverlöst SQL-lager eller ett pro SQL-lager för att köra uppgiften.
Klicka på Skapa.

Steg 12: Kör det fullständiga arbetsflödet

Om du vill köra arbetsflödet klickar du på . Om du vill visa information om körningen klickar du på länken i kolumnen Starttid för körningen i jobbkörningsvyn.

Steg 13: Visa resultatet

Om du vill visa resultatet när körningen är klar klickar du på den slutliga instrumentpanelsaktiviteten och klickar på instrumentpanelens namn under SQL-instrumentpanelen i den högra panelen.