Databricks SQL gebruiken in een Azure Databricks-taak

Artikel
10/04/2024

U kunt het TYPE SQL-taak gebruiken in een Azure Databricks-taak, zodat u werkstromen kunt maken, plannen, gebruiken en bewaken die Databricks SQL-objecten bevatten, zoals query's, verouderde dashboards en waarschuwingen. Uw werkstroom kan bijvoorbeeld gegevens opnemen, de gegevens voorbereiden, analyses uitvoeren met behulp van Databricks SQL-query's en vervolgens de resultaten weergeven in een verouderd dashboard.

Dit artikel bevat een voorbeeldwerkstroom waarmee een verouderd dashboard wordt gemaakt met metrische gegevens voor GitHub-bijdragen. In dit voorbeeld gaat u het volgende doen:

GitHub-gegevens opnemen met behulp van een Python-script en de GitHub REST API.
Transformeer de GitHub-gegevens met behulp van een Delta Live Tables-pijplijn.
Activeer Databricks SQL-query's die analyses uitvoeren op de voorbereide gegevens.
De analyse weergeven in een verouderd dashboard.

GitHub-analysedashboard

Voordat u begint

U hebt het volgende nodig om dit scenario te voltooien:

Een persoonlijk GitHub-toegangstoken. Dit token moet de machtiging voor de opslagplaats hebben.
Een serverloos SQL Warehouse of een pro SQL Warehouse. Zie SQL Warehouse-typen.
Een geheim bereik van Databricks. Het geheime bereik wordt gebruikt om het GitHub-token veilig op te slaan. Zie stap 1: het GitHub-token opslaan in een geheim.

Stap 1: het GitHub-token opslaan in een geheim

In plaats van referenties zoals het persoonlijke toegangstoken van GitHub in een taak te coderen, raadt Databricks aan om een geheim bereik te gebruiken om geheimen veilig op te slaan en te beheren. De volgende Databricks CLI-opdrachten zijn een voorbeeld van het maken van een geheim bereik en het opslaan van het GitHub-token in een geheim in dat bereik:

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

Vervang <scope-name door de naam van een Azure Databricks-geheim bereik om het token op te slaan.
Vervang <token-key> door de naam van een sleutel die u aan het token wilt toewijzen.
Vervang door <token> de waarde van het persoonlijke GitHub-toegangstoken.

Stap 2: Een script maken om GitHub-gegevens op te halen

Het volgende Python-script maakt gebruik van de GitHub REST API voor het ophalen van gegevens over doorvoeringen en bijdragen uit een GitHub-opslagplaats. Invoerargumenten geven de GitHub-opslagplaats op. De records worden opgeslagen op een locatie in DBFS die is opgegeven door een ander invoerargument.

In dit voorbeeld wordt DBFS gebruikt om het Python-script op te slaan, maar u kunt ook Databricks Git-mappen of werkruimtebestanden gebruiken om het script op te slaan en te beheren.

Sla dit script op een locatie op uw lokale schijf op:

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()

Upload het script naar DBFS:
1. Ga naar de landingspagina van Azure Databricks en klik op Catalogus in de zijbalk.
2. Klik op DBFS bladeren.
3. Klik in de DBFS-bestandsbrowser op Uploaden. Het dialoogvenster Gegevens uploaden naar DBFS wordt weergegeven.
4. Voer een pad in DBFS in om het script op te slaan, klik op Bestanden neerzetten om te uploaden of klik om te bladeren en selecteer het Python-script.
5. Klik op Gereed.

Stap 3: Een Delta Live Tables-pijplijn maken om de GitHub-gegevens te verwerken

In deze sectie maakt u een Delta Live Tables-pijplijn om de onbewerkte GitHub-gegevens te converteren naar tabellen die kunnen worden geanalyseerd door Databricks SQL-query's. Voer de volgende stappen uit om de pijplijn te maken:

Klik in de zijbalk op Nieuw en selecteer Notitieblok in het menu. Het dialoogvenster Notitieblok maken wordt weergegeven.
Voer in de standaardtaal een naam in en selecteer Python. U kunt Cluster ingesteld laten op de standaardwaarde. De Delta Live Tables-runtime maakt een cluster voordat uw pijplijn wordt uitgevoerd.
Klik op Create.

Kopieer het Python-codevoorbeeld en plak deze in uw nieuwe notebook. U kunt de voorbeeldcode toevoegen aan één cel van het notebook of meerdere cellen.

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"))
  )

Klik in de zijbalk op Werkstromen, klik op het tabblad Delta Live-tabellen en klik op Pijplijn maken.
Geef de pijplijn een naam, Transform GitHub databijvoorbeeld.
Voer in het veld Notebook-bibliotheken het pad naar uw notitieblok in of klik hierop om het notitieblok te selecteren.
Klik op Configuratie toevoegen. Typ in commits-pathhet Key tekstvak . Voer in het Value tekstvak het DBFS-pad in waar de GitHub-records worden geschreven. Dit kan elk pad zijn dat u kiest en is hetzelfde pad dat u gebruikt bij het configureren van de eerste Python-taak wanneer u de werkstroom maakt.
Klik nogmaals op Configuratie toevoegen. Typ in contribs-pathhet Key tekstvak . Voer in het Value tekstvak het DBFS-pad in waar de GitHub-records worden geschreven. Dit kan elk pad zijn dat u kiest en is hetzelfde pad dat u gebruikt bij het configureren van de tweede Python-taak wanneer u de werkstroom maakt.
Voer in het veld Doel bijvoorbeeld een doeldatabase github_tablesin. Als u een doeldatabase instelt, worden de uitvoergegevens naar de metastore gepubliceerd en zijn ze vereist voor de downstreamquery's die de gegevens analyseren die door de pijplijn worden geproduceerd.
Klik op Opslaan.

Stap 4: Een werkstroom maken om GitHub-gegevens op te nemen en te transformeren

Voordat u de GitHub-gegevens analyseert en visualiseert met Databricks SQL, moet u de gegevens opnemen en voorbereiden. Voer de volgende stappen uit om een werkstroom te maken om deze taken uit te voeren:

Een Azure Databricks-taak maken en de eerste taak toevoegen

Ga naar de landingspagina van Azure Databricks en voer een van de volgende handelingen uit:
- Klik in de zijbalk op Werkstromen en klik op .
- Klik in de zijbalk op Nieuw en selecteer Taak in het menu.
Vervang in het taakdialoogvenster dat wordt weergegeven op het tabblad Taken een naam voor uw taak toevoegen... door uw taaknaam, bijvoorbeeldGitHub analysis workflow.
Voer in De naam van de taak een naam in voor de taak, bijvoorbeeldget_commits.
Selecteer in Type het Python-script.
Selecteer IN Bron DBFS/S3.
Voer in Pad het pad naar het script in DBFS in.
Voer in Parameters de volgende argumenten in voor het Python-script:

["<owner>","<repo>","commits","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Vervang door <owner> de naam van de eigenaar van de opslagplaats. Als u bijvoorbeeld records uit de github.com/databrickslabs/overwatch opslagplaats wilt ophalen, voert u het volgende in databrickslabs.
- Vervang bijvoorbeeld door <repo> de naam overwatchvan de opslagplaats.
- Vervang <DBFS-output-dir> door een pad in DBFS om de records op te slaan die zijn opgehaald uit GitHub.
- Vervang <scope-name> door de naam van het geheime bereik dat u hebt gemaakt om het GitHub-token op te slaan.
- Vervang <github-token-key> door de naam van de sleutel die u hebt toegewezen aan het GitHub-token.
Klik op Taak opslaan.

Nog een taak toevoegen

Klik onder de taak die u zojuist hebt gemaakt.
Voer in De naam van de taak een naam in voor de taak, bijvoorbeeldget_contributors.
Selecteer in Type het type Python-scripttaak .
Selecteer IN Bron DBFS/S3.
Voer in Pad het pad naar het script in DBFS in.
Voer in Parameters de volgende argumenten in voor het Python-script:

["<owner>","<repo>","contributors","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Vervang door <owner> de naam van de eigenaar van de opslagplaats. Als u bijvoorbeeld records uit de github.com/databrickslabs/overwatch opslagplaats wilt ophalen, voert u het volgende in databrickslabs.
- Vervang bijvoorbeeld door <repo> de naam overwatchvan de opslagplaats.
- Vervang <DBFS-output-dir> door een pad in DBFS om de records op te slaan die zijn opgehaald uit GitHub.
- Vervang <scope-name> door de naam van het geheime bereik dat u hebt gemaakt om het GitHub-token op te slaan.
- Vervang <github-token-key> door de naam van de sleutel die u hebt toegewezen aan het GitHub-token.
Klik op Taak opslaan.

Een taak toevoegen om de gegevens te transformeren

Klik onder de taak die u zojuist hebt gemaakt.
Voer in De naam van de taak een naam in voor de taak, bijvoorbeeldtransform_github_data.
Selecteer in Type de pijplijn van Delta Live Tables en voer een naam in voor de taak.
Selecteer in Pijplijn de pijplijn die in stap 3 is gemaakt: Maak een Delta Live Tables-pijplijn om de GitHub-gegevens te verwerken.
Klik op Create.

Stap 5: De werkstroom voor gegevenstransformatie uitvoeren

Klik hier om de werkstroom uit te voeren. Als u details voor de uitvoering wilt weergeven, klikt u op de koppeling in de kolom Begintijd voor de uitvoering in de weergave taakuitvoeringen. Klik op elke taak om details voor de taakuitvoering weer te geven.

Stap 6: (Optioneel) Voer de volgende stappen uit om de uitvoergegevens weer te geven nadat de werkstroom is voltooid:

Klik in de weergave uitvoeringsdetails op de taak Delta Live Tables.
Klik in het deelvenster Details van taakuitvoering op de naam van de pijplijn onder Pijplijn. De pagina Pijplijndetails wordt weergegeven.
Selecteer de commits_by_author tabel in de pijplijn-DAG.
Klik op de tabelnaam naast Metastore in het deelvenster commits_by_author . De pagina Catalogusverkenner wordt geopend.

In Catalog Explorer kunt u het tabelschema, voorbeeldgegevens en andere details voor de gegevens bekijken. Volg dezelfde stappen om gegevens voor de github_contributors_raw tabel weer te geven.

Stap 7: De GitHub-gegevens verwijderen

In een echte toepassing kunt u mogelijk continu gegevens opnemen en verwerken. Omdat in dit voorbeeld de volledige gegevensset wordt gedownload en verwerkt, moet u de al gedownloade GitHub-gegevens verwijderen om een fout te voorkomen bij het opnieuw uitvoeren van de werkstroom. Voer de volgende stappen uit om de gedownloade gegevens te verwijderen:

Maak een nieuw notitieblok en voer de volgende opdrachten in de eerste cel in:
```
dbutils.fs.rm("<commits-path", True)
dbutils.fs.rm("<contributors-path", True)
```
Vervang <commits-path> en <contributors-path> door de DBFS-paden die u hebt geconfigureerd bij het maken van de Python-taken.
Klik en selecteer Cel uitvoeren.

U kunt dit notitieblok ook toevoegen als een taak in de werkstroom.

Stap 8: De Databricks SQL-query's maken

Nadat u de werkstroom hebt uitgevoerd en de vereiste tabellen hebt gemaakt, maakt u query's om de voorbereide gegevens te analyseren. Voer de volgende stappen uit om de voorbeeldquery's en visualisaties te maken:

De tien belangrijkste inzenders per maand weergeven

Klik op het pictogram onder het Databricks-logo in de zijbalk en selecteer SQL.
Klik op Een query maken om de Databricks SQL-queryeditor te openen.
Zorg ervoor dat de catalogus is ingesteld op hive_metastore. Klik op de standaardinstelling naast hive_metastore en stel de database in op de doelwaarde die u hebt ingesteld in de Delta Live Tables-pijplijn.

Voer op het tabblad Nieuwe query de volgende query in:

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

Klik op het tabblad Nieuwe query en wijzig de naam van de query, Commits by month top 10 contributorsbijvoorbeeld.
De resultaten worden standaard weergegeven als een tabel. Als u wilt wijzigen hoe de gegevens worden gevisualiseerd, bijvoorbeeld met behulp van een staafdiagram, klikt u in het deelvenster Resultaten op Bewerken en klikt u op Bewerken.
Selecteer Balk in visualisatietype.
Selecteer in de kolom X de maand.
Selecteer count(1) in Y-kolommen.
Selecteer in Groeperen op de naam.
Klik op Opslaan.

De bovenste 20 inzenders weergeven

Klik op + > Nieuwe query maken en zorg ervoor dat de catalogus is ingesteld op hive_metastore. Klik op de standaardinstelling naast hive_metastore en stel de database in op de doelwaarde die u hebt ingesteld in de Delta Live Tables-pijplijn.

Voer de volgende query in:

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

Klik op het tabblad Nieuwe query en wijzig de naam van de query, Top 20 contributorsbijvoorbeeld.
Als u de visualisatie van de standaardtabel wilt wijzigen, klikt u in het deelvenster Resultaten op Bewerken en klikt u op Bewerken.
Selecteer Balk in visualisatietype.
Selecteer in de kolom X de optie Aanmelden.
Selecteer bijdragen in Y-kolommen.
Klik op Opslaan.

De totale doorvoeringen per auteur weergeven

Klik op + > Nieuwe query maken en zorg ervoor dat de catalogus is ingesteld op hive_metastore. Klik op de standaardinstelling naast hive_metastore en stel de database in op de doelwaarde die u hebt ingesteld in de Delta Live Tables-pijplijn.

Voer de volgende query in:

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

Klik op het tabblad Nieuwe query en wijzig de naam van de query, Total commits by authorbijvoorbeeld.
Als u de visualisatie van de standaardtabel wilt wijzigen, klikt u in het deelvenster Resultaten op Bewerken en klikt u op Bewerken.
Selecteer Balk in visualisatietype.
Selecteer in de kolom X de naam.
Selecteer doorvoeringen in Y-kolommen.
Klik op Opslaan.

Stap 9: Een dashboard maken

Klik in de zijbalk op Dashboards
Klik op Dashboard maken.
Voer bijvoorbeeld GitHub analysiseen naam in voor het dashboard.
Voor elke query en visualisatie die u in stap 8 hebt gemaakt: Maak de Databricks SQL-query's, klik op Visualisatie toevoegen > en selecteer elke visualisatie.

Stap 10: De SQL-taken toevoegen aan de werkstroom

Als u de nieuwe querytaken wilt toevoegen aan de werkstroom die u hebt gemaakt in Een Azure Databricks-taak maken en de eerste taak toevoegen, voegt u voor elke query die u in stap 8 hebt gemaakt, de Databricks SQL-query's maken:

Klik op Werkstromen in de zijbalk.
Klik in de kolom Naam op de taaknaam.
Klik op het tabblad Taken .
Klik onder de laatste taak.
Voer een naam in voor de taak, selecteer SQL in Type en selecteer query in SQL-taak.
Selecteer de query in sql-query.
Selecteer in SQL Warehouse een serverloze SQL Warehouse of een pro SQL Warehouse om de taak uit te voeren.
Klik op Create.

Stap 11: Een dashboardtaak toevoegen

Klik onder de laatste taak.
Voer een naam in voor de taak, selecteer SQL in Type, selecteer SQL en selecteer in SQL-taak verouderd dashboard.
Selecteer het dashboard dat u in stap 9 hebt gemaakt: Een dashboard maken.
Selecteer in SQL Warehouse een serverloze SQL Warehouse of een pro SQL Warehouse om de taak uit te voeren.
Klik op Create.

Stap 12: De volledige werkstroom uitvoeren

Als u de werkstroom wilt uitvoeren, klikt u op . Als u details voor de uitvoering wilt weergeven, klikt u op de koppeling in de kolom Begintijd voor de uitvoering in de weergave taakuitvoeringen.

Stap 13: de resultaten weergeven

Als u de resultaten wilt weergeven wanneer de uitvoering is voltooid, klikt u op de laatste dashboardtaak en klikt u op de naam van het dashboard onder het SQL-dashboard in het rechterdeelvenster.

Delen via