A Databricks SQL használata Egy Azure Databricks-feladatban

Cikk
05/08/2024

Az SQL-feladattípust egy Azure Databricks-feladatban használhatja, lehetővé téve olyan munkafolyamatok létrehozását, ütemezését, üzemeltetését és monitorozását, amelyek olyan Databricks SQL-objektumokat tartalmaznak, mint a lekérdezések, az örökölt irányítópultok és a riasztások. A munkafolyamat például betöltheti az adatokat, előkészítheti az adatokat, elemezhet Databricks SQL-lekérdezésekkel, majd megjelenítheti az eredményeket egy örökölt irányítópulton.

Ez a cikk egy példa-munkafolyamatot mutat be, amely egy örökölt irányítópultot hoz létre, amely metrikákat jelenít meg a GitHub-hozzájárulásokhoz. Ebben a példában a következőt fogja:

GitHub-adatok betöltése Python-szkript és a GitHub REST API használatával.
A GitHub-adatok átalakítása Delta Live Tables-folyamattal.
Databricks SQL-lekérdezések aktiválása, amely elemzést végez az előkészített adatokon.
Az elemzés megjelenítése egy örökölt irányítópulton.

GitHub-elemzési irányítópult

Előkészületek

Az útmutató elvégzéséhez az alábbiakra van szüksége:

Egy GitHub személyes hozzáférési jogkivonat. Ennek a jogkivonatnak rendelkeznie kell az adattár engedélyével.
Kiszolgáló nélküli SQL-raktár vagy profi SQL-raktár. Lásd az SQL Warehouse típusait.
A Databricks titkos hatóköre. A titkos kulcs hatóköre a GitHub-jogkivonat biztonságos tárolására szolgál. Lásd : 1. lépés: A GitHub-jogkivonat tárolása titkos kódban.

1. lépés: A GitHub-jogkivonat tárolása titkos kódban

A databricks ahelyett, hogy hitelesítő adatokat, például a GitHub személyes hozzáférési jogkivonatát rögzítene egy feladatban, egy titkos hatókör használatát javasolja a titkos kódok biztonságos tárolásához és kezeléséhez. A következő Databricks CLI-parancsok egy titkos hatókör létrehozására és a GitHub-jogkivonat egy titkos kulcsban való tárolására használhatók az adott hatókörben:

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

Cserélje le <scope-name egy Azure Databricks titkos hatókör nevére a jogkivonat tárolásához.
Cserélje le <token-key> a jogkivonathoz hozzárendelni kívánt kulcs nevére.
Cserélje le <token> a GitHub személyes hozzáférési jogkivonatának értékére.

2. lépés: Szkript létrehozása GitHub-adatok beolvasásához

A következő Python-szkript a GitHub REST API-val kér le adatokat a véglegesítésekről és a hozzájárulásokról egy GitHub-adattárból. A bemeneti argumentumok határozzák meg a GitHub-adattárat. A rekordok egy másik bemeneti argumentum által megadott helyre lesznek mentve a DBFS-ben.

Ez a példa a DBFS használatával tárolja a Python-szkriptet, de a szkript tárolásához és kezeléséhez Databricks Git-mappákat vagy munkaterületfájlokat is használhat.

Mentse a szkriptet a helyi lemez egyik helyére:

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

Töltse fel a szkriptet a DBFS-be:
1. Nyissa meg az Azure Databricks kezdőlapját, és kattintson a Katalógus gombra az oldalsávon.
2. Kattintson a Tallózás a DBFS-ben elemre.
3. A DBFS fájlböngészőben kattintson a Feltöltés gombra. Megjelenik az Adatok feltöltése a DBFS-be párbeszédpanel.
4. Adjon meg egy elérési utat a DBFS-ben a szkript tárolásához, kattintson a Feltöltendő fájlok elvetése elemre, vagy kattintson a tallózáshoz, és válassza ki a Python-szkriptet.
5. Kattintson a Done (Kész) gombra.

3. lépés: Delta Live Tables-folyamat létrehozása a GitHub-adatok feldolgozásához

Ebben a szakaszban egy Delta Live Tables-folyamatot hoz létre, amely a nyers GitHub-adatokat olyan táblákká alakítja, amelyeket a Databricks SQL-lekérdezések elemezhetnek. A folyamat létrehozásához hajtsa végre a következő lépéseket:

Az oldalsávon kattintson az Új gombra, és válassza a Jegyzetfüzet lehetőséget a menüből. Megjelenik a Jegyzetfüzet létrehozása párbeszédpanel.
Az alapértelmezett nyelven adjon meg egy nevet, és válassza a Pythont. A fürtöt az alapértelmezett értékre állíthatja. A Delta Live Tables futtatókörnyezete létrehoz egy fürtöt a folyamat futtatása előtt.
Kattintson a Létrehozás gombra.

Másolja ki a Python-példakódot, és illessze be az új jegyzetfüzetbe. A példakódot a jegyzetfüzet egyetlen cellájába vagy több cellába is hozzáadhatja.

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

Az oldalsávon kattintson a Munkafolyamatok elemre, kattintson a Delta Live Tables fülre, majd a Folyamat létrehozása parancsra.
Adjon nevet a folyamatnak, például Transform GitHub data.
A Jegyzetfüzettárak mezőben adja meg a jegyzetfüzet elérési útját, vagy kattintson a jegyzetfüzet kijelöléséhez.
Kattintson a Konfiguráció hozzáadása elemre. Key A szövegmezőbe írja be a következőtcommits-path: Value A szövegmezőbe írja be azt a DBFS-elérési utat, ahol a GitHub-rekordok meg lesznek írva. Ez bármely választott elérési út lehet, és ugyanaz az elérési út, amelyet a munkafolyamat létrehozásakor az első Python-feladat konfigurálásakor fog használni.
Kattintson ismét a Konfiguráció hozzáadása elemre . Key A szövegmezőbe írja be a következőtcontribs-path: Value A szövegmezőbe írja be azt a DBFS-elérési utat, ahol a GitHub-rekordok meg lesznek írva. Ez bármely választott elérési út lehet, és ugyanaz az elérési út, amelyet a második Python-feladat konfigurálásakor fog használni a munkafolyamat létrehozásakor.
A Cél mezőben adjon meg például egy céladatbázistgithub_tables. A céladatbázis beállítása közzéteszi a kimeneti adatokat a metaadattárban, és a folyamat által előállított adatokat elemző alsóbb rétegbeli lekérdezésekhez szükséges.
Kattintson a Mentés gombra.

4. lépés: Munkafolyamat létrehozása a GitHub-adatok betöltéséhez és átalakításához

A GitHub-adatok Databricks SQL-sel való elemzése és vizualizációja előtt be kell használnia és elő kell készítenie az adatokat. Ha munkafolyamatot szeretne létrehozni a feladatok elvégzéséhez, hajtsa végre a következő lépéseket:

Azure Databricks-feladat létrehozása és az első tevékenység hozzáadása

Nyissa meg az Azure Databricks kezdőlapját, és tegye az alábbiak egyikét:
- Az oldalsávon kattintson a Munkafolyamatok elemre, majd a gombra.
- Az oldalsávon kattintson az Új gombra, és válassza a Feladat lehetőséget a menüből.
A Feladatok lapon megjelenő feladat párbeszédpanelen cserélje le a Feladat neve hozzáadása... elemet például GitHub analysis workflowa feladat nevére.
A Tevékenység név mezőbe írja be például get_commitsa tevékenység nevét.
A Típus mezőben válassza a Python-szkriptet.
A Forrás területen válassza a DBFS/S3 lehetőséget.
Az Elérési út mezőbe írja be a szkript elérési útját a DBFS-ben.
A Paraméterek mezőben adja meg a Python-szkript alábbi argumentumait:

["<owner>","<repo>","commits","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Cserélje le <owner> az adattár tulajdonosának nevére. Ha például rekordokat szeretne beolvasni az adattárból, írja be a github.com/databrickslabs/overwatch következőt databrickslabs:
- Cserélje le <repo> például az adattár nevét overwatch.
- Cserélje le <DBFS-output-dir> egy elérési útra a DBFS-ben a GitHubról lekért rekordok tárolásához.
- Cserélje le <scope-name> a GitHub-jogkivonat tárolásához létrehozott titkos kulcs hatókörének nevét.
- Cserélje le <github-token-key> a GitHub-jogkivonathoz hozzárendelt kulcs nevét.
Kattintson a Feladat mentése gombra.

Másik tevékenység hozzáadása

Kattintson az imént létrehozott feladat alá.
A Tevékenység név mezőbe írja be például get_contributorsa tevékenység nevét.
A Típus mezőben válassza ki a Python-szkript feladattípusát.
A Forrás területen válassza a DBFS/S3 lehetőséget.
Az Elérési út mezőbe írja be a szkript elérési útját a DBFS-ben.
A Paraméterek mezőben adja meg a Python-szkript alábbi argumentumait:

["<owner>","<repo>","contributors","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Cserélje le <owner> az adattár tulajdonosának nevére. Ha például rekordokat szeretne beolvasni az adattárból, írja be a github.com/databrickslabs/overwatch következőt databrickslabs:
- Cserélje le <repo> például az adattár nevét overwatch.
- Cserélje le <DBFS-output-dir> egy elérési útra a DBFS-ben a GitHubról lekért rekordok tárolásához.
- Cserélje le <scope-name> a GitHub-jogkivonat tárolásához létrehozott titkos kulcs hatókörének nevét.
- Cserélje le <github-token-key> a GitHub-jogkivonathoz hozzárendelt kulcs nevét.
Kattintson a Feladat mentése gombra.

Feladat hozzáadása az adatok átalakításához

Kattintson az imént létrehozott feladat alá.
A Tevékenység név mezőbe írja be például transform_github_dataa tevékenység nevét.
A Típus mezőben válassza a Delta Live Tables-folyamatot , és adja meg a feladat nevét.
A Folyamat területen válassza ki a 3. lépésben létrehozott folyamatot: Delta Live Tables-folyamat létrehozása a GitHub-adatok feldolgozásához.
Kattintson a Létrehozás gombra.

5. lépés: Az adatátalakítási munkafolyamat futtatása

Kattintson ide a munkafolyamat futtatásához. A futtatás részleteinek megtekintéséhez kattintson a feladatfuttatási nézetben a futtatás Kezdési idő oszlopában található hivatkozásra. Kattintson az egyes tevékenységekre a feladatfuttatás részleteinek megtekintéséhez.

6. lépés: (Nem kötelező) Ha meg szeretné tekinteni a kimeneti adatokat a munkafolyamat futtatása után, hajtsa végre a következő lépéseket:

A Futtatás részletei nézetben kattintson a Delta Live Tables tevékenységre.
A Feladatfuttatás részletei panelen kattintson a folyamat nevére a Folyamat csoportban. Megjelenik a Folyamat részletei lap.
Jelölje ki a commits_by_author táblát a folyamat DAG-jában.
Kattintson a metaadattár melletti tábla nevére a commits_by_author panelen. Megnyílik a Katalóguskezelő lap.

A Katalóguskezelőben megtekintheti a táblázatsémát, a mintaadatokat és az adatok egyéb adatait. Kövesse ugyanazokat a lépéseket a github_contributors_raw tábla adatainak megtekintéséhez.

7. lépés: A GitHub-adatok eltávolítása

Egy valós alkalmazásban előfordulhat, hogy folyamatosan betölti és feldolgozja az adatokat. Mivel ez a példa letölti és feldolgozza a teljes adatkészletet, el kell távolítania a már letöltött GitHub-adatokat, hogy megakadályozza a munkafolyamat újrafutásakor előforduló hibát. A letöltött adatok eltávolításához hajtsa végre a következő lépéseket:

Hozzon létre egy új jegyzetfüzetet, és írja be a következő parancsokat az első cellába:
```
dbutils.fs.rm("<commits-path", True)
dbutils.fs.rm("<contributors-path", True)
```
Cserélje le és <contributors-path> cserélje le <commits-path> a Python-feladatok létrehozásakor konfigurált DBFS-útvonalakat.
Kattintson és válassza a Cella futtatása lehetőséget.

Ezt a jegyzetfüzetet feladatként is hozzáadhatja a munkafolyamathoz.

8. lépés: A Databricks SQL-lekérdezések létrehozása

A munkafolyamat futtatása és a szükséges táblák létrehozása után hozzon létre lekérdezéseket az előkészített adatok elemzéséhez. A példaként szolgáló lekérdezések és vizualizációk létrehozásához hajtsa végre a következő lépéseket:

Az első 10 közreműködő megjelenítése hónap szerint

Kattintson a Databricks embléma alatti ikonra az oldalsávon, és válassza az SQL lehetőséget.
Kattintson a Lekérdezés létrehozása gombra a Databricks SQL-lekérdezésszerkesztő megnyitásához.
Győződjön meg arról, hogy a katalógus hive_metastore van beállítva. Kattintson az alapértelmezett gombra a hive_metastore mellett, és állítsa az adatbázist a Delta Live Tables folyamatban beállított célértékre .

Az Új lekérdezés lapon adja meg a következő lekérdezést:

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

Kattintson az Új lekérdezés fülre, és nevezze át például a lekérdezést Commits by month top 10 contributors.
Alapértelmezés szerint az eredmények táblázatként jelennek meg. Ha módosítani szeretné az adatok vizualizációjának módját( például sávdiagram használatával), az Eredmények panelen kattintson a Szerkesztés gombra.
A Vizualizáció típusa területen válassza a Sáv lehetőséget.
Az X oszlopban válassza ki a hónapot.
Az Y oszlopokban válassza a Darabszám(1) lehetőséget.
A Csoportosítás csoportban válassza ki a nevet.
Kattintson a Mentés gombra.

A 20 legjobb közreműködő megjelenítése

Kattintson a + > Új lekérdezés létrehozása elemre, és győződjön meg arról, hogy a katalógus hive_metastore van beállítva. Kattintson az alapértelmezett gombra a hive_metastore mellett, és állítsa az adatbázist a Delta Live Tables folyamatban beállított célértékre .

Adja meg a következő lekérdezést:

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

Kattintson az Új lekérdezés fülre, és nevezze át például a lekérdezést Top 20 contributors.
Ha módosítani szeretné a vizualizációt az alapértelmezett tábláról, az Eredmények panelen kattintsonés kattintson a Szerkesztés gombra.
A Vizualizáció típusa területen válassza a Sáv lehetőséget.
Az X oszlopban válassza a bejelentkezést.
Az Y oszlopokban válassza ki a hozzájárulásokat.
Kattintson a Mentés gombra.

Összes véglegesítés megjelenítése szerző szerint

Kattintson a + > Új lekérdezés létrehozása elemre, és győződjön meg arról, hogy a katalógus hive_metastore van beállítva. Kattintson az alapértelmezett gombra a hive_metastore mellett, és állítsa az adatbázist a Delta Live Tables folyamatban beállított célértékre .

Adja meg a következő lekérdezést:

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

Kattintson az Új lekérdezés fülre, és nevezze át például a lekérdezést Total commits by author.
Ha módosítani szeretné a vizualizációt az alapértelmezett tábláról, az Eredmények panelen kattintsonés kattintson a Szerkesztés gombra.
A Vizualizáció típusa területen válassza a Sáv lehetőséget.
Az X oszlopban válassza ki a nevet.
Az Y oszlopokban válassza ki a véglegesítéseket.
Kattintson a Mentés gombra.

9. lépés: Irányítópult létrehozása

Az oldalsávon kattintson az Irányítópultok elemre
Kattintson az Irányítópult létrehozása elemre.
Adja meg például az irányítópult GitHub analysisnevét.
A 8. lépésben létrehozott összes lekérdezéshez és vizualizációhoz kattintson a Databricks SQL-lekérdezések létrehozása gombra, és > válassza ki az egyes vizualizációkat.

10. lépés: Az SQL-feladatok hozzáadása a munkafolyamathoz

Ha hozzá szeretné adni az új lekérdezési feladatokat az Azure Databricks-feladat létrehozásakor létrehozott munkafolyamathoz, és hozzá szeretné adni az első feladatot a 8. lépésben létrehozott összes lekérdezéshez: Databricks SQL-lekérdezések létrehozása:

Kattintson a Munkafolyamatok elemre az oldalsávon.
A Név oszlopban kattintson a feladat nevére.
Kattintson a Feladatok fülre.
Kattintson az utolsó tevékenység alá.
Adja meg a feladat nevét, írja be az SQL típusának kiválasztását, az SQL-feladatban pedig a Lekérdezés lehetőséget.
Válassza ki a lekérdezést az SQL-lekérdezésben.
Az SQL Warehouse-ban válasszon ki egy kiszolgáló nélküli SQL-raktárt vagy egy profi SQL-raktárt a feladat futtatásához.
Kattintson a Létrehozás gombra.

11. lépés: Irányítópult-feladat hozzáadása

Kattintson az utolsó tevékenység alá.
Adja meg a tevékenység nevét, írja be a Típus, az SQL lehetőséget, az SQL-feladatbanpedig az Örökölt irányítópult lehetőséget.
Válassza ki a 9. lépésben létrehozott irányítópultot: Irányítópult létrehozása.
Az SQL Warehouse-ban válasszon ki egy kiszolgáló nélküli SQL-raktárt vagy egy profi SQL-raktárt a feladat futtatásához.
Kattintson a Létrehozás gombra.

12. lépés: A teljes munkafolyamat futtatása

A munkafolyamat futtatásához kattintson a gombra . A futtatás részleteinek megtekintéséhez kattintson a feladatfuttatási nézetben a futtatás Kezdési idő oszlopában található hivatkozásra.

13. lépés: Az eredmények megtekintése

Ha meg szeretné tekinteni az eredményeket, amikor a futtatás befejeződik, kattintson a végső irányítópult-feladatra, és kattintson az irányítópult nevére az SQL-irányítópult alatt a jobb oldali panelen.

Share via