Share via

Použití Databricks SQL v úloze Azure Databricks

  • Článek

Typ úlohy SQL můžete použít v úloze Azure Databricks, takže můžete vytvářet, plánovat, provozovat a monitorovat pracovní postupy, které zahrnují objekty SQL Databricks, jako jsou dotazy, starší řídicí panely a upozornění. Váš pracovní postup může například ingestovat data, připravit data, provést analýzu pomocí dotazů Sql Databricks a pak zobrazit výsledky na starším řídicím panelu.

Tento článek obsahuje ukázkový pracovní postup, který vytvoří starší řídicí panel zobrazující metriky pro příspěvky GitHubu. V tomto příkladu:

  • Ingestování dat GitHubu pomocí skriptu Pythonu a rozhraní REST API GitHubu
  • Transformujte data GitHubu pomocí kanálu Delta Live Tables.
  • Aktivujte dotazy SQL Databricks, které provádějí analýzu připravených dat.
  • Zobrazení analýzy na starším řídicím panelu

Řídicí panel analýzy GitHubu

Než začnete

K dokončení tohoto návodu potřebujete následující:

Krok 1: Uložení tokenu GitHubu do tajného kódu

Místo pevně zakódování přihlašovacích údajů, jako je osobní přístupový token GitHubu v úloze, databricks doporučuje bezpečně ukládat a spravovat tajné kódy pomocí oboru tajných kódů. Následující příkazy rozhraní příkazového řádku Databricks představují příklad vytvoření oboru tajného kódu a uložení tokenu GitHubu do tajného kódu v tomto oboru:

databricks secrets create-scope <scope-name>
databricks secrets put-secret <scope-name> <token-key> --string-value <token>
  • Nahraďte <scope-name názvem oboru tajných kódů Azure Databricks pro uložení tokenu.
  • Nahraďte <token-key> názvem klíče, který se má k tokenu přiřadit.
  • Nahraďte <token> hodnotou tokenu pat GitHubu.

Krok 2: Vytvoření skriptu pro načtení dat GitHubu

Následující skript Pythonu používá rozhraní REST API GitHubu k načtení dat o potvrzeních a příspěvcích z úložiště GitHub. Vstupní argumenty určují úložiště GitHub. Záznamy se ukládají do umístění v dbFS určeném jiným vstupním argumentem.

V tomto příkladu se k ukládání skriptu Pythonu používá DBFS, ale k ukládání a správě skriptu můžete použít také složky Gitu nebo soubory pracovního prostoru Databricks.

  • Uložte tento skript do umístění na místním disku:

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

  • Nahrajte skript do DBFS:

    1. Přejděte na cílovou stránku Azure Databricks a na bočním panelu klikněte na Ikona kataloguKatalog.
    2. Klikněte na Procházet DBFS.
    3. V prohlížeči souborů DBFS klikněte na Nahrát. Zobrazí se dialogové okno Nahrát data do DBFS .
    4. Zadejte cestu do DBFS pro uložení skriptu, klikněte na Drop files to upload, or click to browse, and select the Python script.
    5. Klikněte na tlačítko Hotovo.

Krok 3: Vytvoření kanálu Delta Live Tables pro zpracování dat GitHubu

V této části vytvoříte kanál Delta Live Tables, který převede nezpracovaná data GitHubu na tabulky, které je možné analyzovat pomocí dotazů SQL Databricks. Pokud chcete vytvořit kanál, proveďte následující kroky:

  1. Na bočním panelu klikněte na Nová ikonaNový a v nabídce vyberte Poznámkový blok. Zobrazí se dialogové okno Vytvořit poznámkový blok .

  2. Ve výchozím jazyce zadejte název a vyberte Python. Cluster můžete nechat nastavený na výchozí hodnotu. Modul runtime Delta Live Tables vytvoří cluster před spuštěním kanálu.

  3. Klikněte na Vytvořit.

  4. Zkopírujte příklad kódu Pythonu a vložte ho do nového poznámkového bloku. Ukázkový kód můžete přidat do jedné buňky poznámkového bloku nebo více buněk.

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

  5. Na bočním panelu klikněte na Ikona Pracovních postupůPracovní postupy, klikněte na kartu Delta Live Tables a potom na Vytvořit kanál.

  6. Zadejte název kanálu, například Transform GitHub data.

  7. Do pole Knihovny poznámkových bloků zadejte cestu k poznámkovému bloku nebo kliknutím Ikona výběru souboru poznámkový blok vyberte.

  8. Klikněte na Přidat konfiguraci. Do textového Key pole zadejte commits-path. Do textového Value pole zadejte cestu DBFS, do které se zapíšou záznamy GitHubu. Může to být libovolná cesta, kterou zvolíte, a je to stejná cesta, kterou použijete při konfiguraci prvního úkolu Pythonu při vytváření pracovního postupu.

  9. Znovu klikněte na Přidat konfiguraci . Do textového Key pole zadejte contribs-path. Do textového Value pole zadejte cestu DBFS, do které se zapíšou záznamy GitHubu. Může to být libovolná cesta, kterou zvolíte, a je to stejná cesta, kterou použijete při konfiguraci druhé úlohy Pythonu při vytváření pracovního postupu.

  10. Do pole Cíl zadejte cílovou databázi, github_tablesnapříklad . Nastavení cílové databáze publikuje výstupní data do metastoru a vyžaduje se pro podřízené dotazy analyzující data vytvořená kanálem.

  11. Klikněte na Uložit.

Krok 4: Vytvoření pracovního postupu pro ingestování a transformaci dat GitHubu

Před analýzou a vizualizací dat GitHubu pomocí Databricks SQL je potřeba ingestovat a připravit data. Pokud chcete vytvořit pracovní postup pro dokončení těchto úloh, proveďte následující kroky:

Vytvoření úlohy Azure Databricks a přidání prvního úkolu

  1. Přejděte na cílovou stránku Azure Databricks a udělejte jednu z těchto věcí:

    • Na bočním panelu klikněte na Ikona Pracovních postupůPracovní postupy a klikněte na .Tlačítko Vytvořit úlohu
    • Na bočním panelu klikněte na Nová ikonaNový a v nabídce vyberte Úloha.

  2. V dialogovém okně úkolu, které se zobrazí na kartě Úkoly , nahraďte přidat název úlohy... názvem vaší úlohy, GitHub analysis workflownapříklad .

  3. Do pole Název úkolu zadejte název úkolu, get_commitsnapříklad .

  4. V příkazu Typ vyberte skript Pythonu.

  5. Ve zdroji vyberte DBFS / S3.

  6. V cestě zadejte cestu ke skriptu v DBFS.

  7. V části Parametry zadejte následující argumenty pro skript Pythonu:

    ["<owner>","<repo>","commits","<DBFS-output-dir>","<scope-name>","<github-token-key>"]

    • Nahraďte <owner> názvem vlastníka úložiště. Pokud například chcete načíst záznamy z github.com/databrickslabs/overwatch úložiště, zadejte databrickslabs.
    • Nahraďte <repo> například názvem overwatchúložiště .
    • Nahraďte <DBFS-output-dir> cestou v DBFS pro ukládání záznamů načtených z GitHubu.
    • Nahraďte <scope-name> názvem oboru tajného kódu, který jste vytvořili pro uložení tokenu GitHubu.
    • Nahraďte <github-token-key> názvem klíče, který jste přiřadili tokenu GitHubu.

  8. Klikněte na Uložit úkol.

Přidání dalšího úkolu

  1. Klikněte pod Tlačítko Přidat úkol úkol, který jste právě vytvořili.

  2. Do pole Název úkolu zadejte název úkolu, get_contributorsnapříklad .

  3. V části Typ vyberte typ úlohy skriptu Pythonu.

  4. Ve zdroji vyberte DBFS / S3.

  5. V cestě zadejte cestu ke skriptu v DBFS.

  6. V části Parametry zadejte následující argumenty pro skript Pythonu:

    ["<owner>","<repo>","contributors","<DBFS-output-dir>","<scope-name>","<github-token-key>"]

    • Nahraďte <owner> názvem vlastníka úložiště. Pokud například chcete načíst záznamy z github.com/databrickslabs/overwatch úložiště, zadejte databrickslabs.
    • Nahraďte <repo> například názvem overwatchúložiště .
    • Nahraďte <DBFS-output-dir> cestou v DBFS pro ukládání záznamů načtených z GitHubu.
    • Nahraďte <scope-name> názvem oboru tajného kódu, který jste vytvořili pro uložení tokenu GitHubu.
    • Nahraďte <github-token-key> názvem klíče, který jste přiřadili tokenu GitHubu.

  7. Klikněte na Uložit úkol.

Přidání úkolu pro transformaci dat

  1. Klikněte pod Tlačítko Přidat úkol úkol, který jste právě vytvořili.
  2. Do pole Název úkolu zadejte název úkolu, transform_github_datanapříklad .
  3. V části Typ vyberte kanál Delta Live Tables a zadejte název úkolu.
  4. V kanálu vyberte kanál vytvořený v kroku 3: Vytvoření kanálu Delta Live Tables pro zpracování dat GitHubu.
  5. Klikněte na Vytvořit.

Krok 5: Spuštění pracovního postupu transformace dat

Kliknutím Tlačítko Spustit nyní spustíte pracovní postup. Pokud chcete zobrazit podrobnosti o spuštění, klikněte na odkaz ve sloupci Čas zahájení spuštění spuštění v zobrazení spuštění úlohy. Kliknutím na jednotlivé úlohy zobrazíte podrobnosti o spuštění úlohy.

Krok 6: (Volitelné) Chcete-li zobrazit výstupní data po dokončení pracovního postupu, proveďte následující kroky:

  1. V zobrazení podrobností spuštění klikněte na úlohu Delta Live Tables.
  2. Na panelu podrobností o spuštění úlohy klikněte na název kanálu v části Kanál. Zobrazí se stránka podrobností kanálu.
  3. commits_by_author Vyberte tabulku v dag kanálu.
  4. Na panelu commits_by_author klikněte na název tabulky vedle položky Metastore. Otevře se stránka Průzkumník katalogu.

V Průzkumníku katalogu můžete zobrazit schéma tabulky, ukázková data a další podrobnosti o datech. Data pro github_contributors_raw tabulku zobrazíte stejným postupem.

Krok 7: Odebrání dat GitHubu

V reálné aplikaci můžete průběžně ingestovat a zpracovávat data. Vzhledem k tomu, že tento příklad stáhne a zpracuje celou sadu dat, musíte odebrat již stažená data GitHubu, aby se při opětovném spuštění pracovního postupu zabránilo chybě. Pokud chcete stažená data odebrat, proveďte následující kroky:

  1. Vytvořte nový poznámkový blok a do první buňky zadejte následující příkazy:

    dbutils.fs.rm("<commits-path", True)
dbutils.fs.rm("<contributors-path", True)

    Nahraďte a <contributors-path> nahraďte <commits-path> cesty DBFS, které jste nakonfigurovali při vytváření úloh Pythonu.

  2. Klikněte a Nabídka Spustit vyberte Spustit buňku.

Tento poznámkový blok můžete také přidat jako úkol v pracovním postupu.

Krok 8: Vytvoření dotazů SQL databricks

Po spuštění pracovního postupu a vytvoření požadovaných tabulek vytvořte dotazy pro analýzu připravených dat. Pokud chcete vytvořit ukázkové dotazy a vizualizace, proveďte následující kroky:

Zobrazení 10 nejlepších přispěvatelů podle měsíce

  1. Klikněte na ikonu pod logem Logo Databricks Databricks na bočním panelu a vyberte SQL.

  2. Kliknutím na Vytvořit dotaz otevřete editor dotazů SQL Databricks.

  3. Ujistěte se, že je katalog nastavený na hive_metastore. Klikněte na výchozí hodnotu vedle hive_metastore a nastavte databázi na cílovou hodnotu, kterou jste nastavili v kanálu Delta Live Tables.

  4. Na kartě Nový dotaz zadejte následující dotaz:

    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

  5. Klikněte na kartu Nový dotaz a přejmenujte dotaz, Commits by month top 10 contributorsnapříklad .

  6. Ve výchozím nastavení se výsledky zobrazí jako tabulka. Pokud chcete změnit způsob vizualizace dat, například pomocí pruhového grafu, klikněte Svislé tři tečky úloh na panelu Výsledky a klikněte na Upravit.

  7. V typu Vizualizace vyberte pruh.

  8. Ve sloupci X vyberte měsíc.

  9. Ve sloupcích Y vyberte počet(1).

  10. V oblasti Seskupte podle vyberte název.

  11. Klikněte na Uložit.

Zobrazení 20 nejlepších přispěvatelů

  1. Klikněte na + > Vytvořit nový dotaz a ujistěte se, že je katalog nastavený na hive_metastore. Klikněte na výchozí hodnotu vedle hive_metastore a nastavte databázi na cílovou hodnotu, kterou jste nastavili v kanálu Delta Live Tables.

  2. Zadejte následující dotaz:

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

  3. Klikněte na kartu Nový dotaz a přejmenujte dotaz, Top 20 contributorsnapříklad .

  4. Pokud chcete změnit vizualizaci z výchozí tabulky, klikněte na panelu Výsledky a Svislé tři tečky úloh klikněte na Upravit.

  5. V typu Vizualizace vyberte pruh.

  6. Ve sloupci X vyberte přihlášení.

  7. Ve sloupcích Y vyberte příspěvky.

  8. Klikněte na Uložit.

Zobrazení celkových potvrzení autorem

  1. Klikněte na + > Vytvořit nový dotaz a ujistěte se, že je katalog nastavený na hive_metastore. Klikněte na výchozí hodnotu vedle hive_metastore a nastavte databázi na cílovou hodnotu, kterou jste nastavili v kanálu Delta Live Tables.

  2. Zadejte následující dotaz:

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

  3. Klikněte na kartu Nový dotaz a přejmenujte dotaz, Total commits by authornapříklad .

  4. Pokud chcete změnit vizualizaci z výchozí tabulky, klikněte na panelu Výsledky a Svislé tři tečky úloh klikněte na Upravit.

  5. V typu Vizualizace vyberte pruh.

  6. Ve sloupci X vyberte název.

  7. Ve sloupcích Y vyberte potvrzení.

  8. Klikněte na Uložit.

Krok 9: Vytvoření řídicího panelu

  1. Na bočním panelu klikněte na Ikona řídicích panelůŘídicí panely.
  2. Klikněte na Vytvořit řídicí panel.
  3. Zadejte název řídicího panelu, GitHub analysisnapříklad .
  4. Pro každý dotaz a vizualizaci vytvořenou v kroku 8: Vytvořte dotazy SQL Databricks, klikněte na Přidat > vizualizaci a vyberte jednotlivé vizualizace.

Krok 10: Přidání úkolů SQL do pracovního postupu

Pokud chcete do pracovního postupu, který jste vytvořili v úloze Azure Databricks, přidat nové úkoly dotazu a přidat první úkol pro každý dotaz, který jste vytvořili v kroku 8: Vytvoření dotazů SQL Databricks:

  1. Na bočním panelu klikněte na Ikona Pracovních postupůPracovní postupy.
  2. Ve sloupci Název klikněte na název úlohy.
  3. Klikněte na kartu Úkoly .
  4. Klikněte Tlačítko Přidat úkol pod poslední úkol.
  5. Zadejte název úkolu, do pole Typ vyberte SQL a v úkolu SQL vyberte Dotaz.
  6. Vyberte dotaz v dotazu SQL.
  7. Ve službě SQL Warehouse vyberte bezserverový SQL Warehouse nebo pro SQL Warehouse, aby se úloha spustila.
  8. Klikněte na Vytvořit.

Krok 11: Přidání úlohy řídicího panelu

  1. Klikněte Tlačítko Přidat úkol pod poslední úkol.
  2. Zadejte název úkolu, do pole Typ, vyberte SQL a v úloze SQL vyberte řídicí panel Starší verze.
  3. Vyberte řídicí panel vytvořený v kroku 9: Vytvoření řídicího panelu.
  4. Ve službě SQL Warehouse vyberte bezserverový SQL Warehouse nebo pro SQL Warehouse, aby se úloha spustila.
  5. Klikněte na Vytvořit.

Krok 12: Spuštění úplného pracovního postupu

Chcete-li spustit pracovní postup, klepněte na tlačítko Tlačítko Spustit nyní. Pokud chcete zobrazit podrobnosti o spuštění, klikněte na odkaz ve sloupci Čas zahájení spuštění spuštění v zobrazení spuštění úlohy.

Krok 13: Zobrazení výsledků

Pokud chcete zobrazit výsledky po dokončení spuštění, klikněte na poslední úlohu řídicího panelu a v pravém panelu klikněte na název řídicího panelu SQL.