Använda Databricks SQL i ett Azure Databricks-jobb
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.
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:
- Gå till din Azure Databricks-landningssida och klicka på Katalog i sidofältet.
- Klicka på Bläddra i DBFS.
- I DBFS-filwebbläsaren klickar du på Ladda upp. Dialogrutan Ladda upp data till DBFS visas.
- 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.
- 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 ducommits-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 ducontribs-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_tables
till 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ångithub.com/databrickslabs/overwatch
lagringsplatsen anger dudatabrickslabs
. - Ersätt
<repo>
med lagringsplatsens namn, till exempeloverwatch
. - 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.
- Ersätt
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ångithub.com/databrickslabs/overwatch
lagringsplatsen anger dudatabrickslabs
. - Ersätt
<repo>
med lagringsplatsens namn, till exempeloverwatch
. - 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.
- Ersätt
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.