Używanie usługi Databricks SQL w zadaniu usługi Azure Databricks

Artykuł
10/04/2024

Możesz użyć typu zadania SQL w zadaniu usługi Azure Databricks, co pozwala tworzyć, planować, obsługiwać i monitorować przepływy pracy obejmujące obiekty SQL usługi Databricks, takie jak zapytania, starsze pulpity nawigacyjne i alerty. Na przykład przepływ pracy może pozyskiwać dane, przygotowywać dane, przeprowadzać analizę przy użyciu zapytań SQL usługi Databricks, a następnie wyświetlać wyniki na starszym pulpicie nawigacyjnym.

Ten artykuł zawiera przykładowy przepływ pracy, który tworzy starszy pulpit nawigacyjny wyświetlający metryki na potrzeby współtworzenia usługi GitHub. W tym przykładzie wykonasz następujące elementy:

Pozyskiwanie danych usługi GitHub przy użyciu skryptu języka Python i interfejsu API REST usługi GitHub.
Przekształć dane usługi GitHub przy użyciu potoku Delta Live Tables.
Wyzwalanie zapytań SQL usługi Databricks wykonujących analizę przygotowanych danych.
Wyświetlanie analizy na starszym pulpicie nawigacyjnym.

Pulpit nawigacyjny analizy usługi GitHub

Przed rozpoczęciem

Aby ukończyć ten przewodnik, musisz wykonać następujące czynności:

Osobisty token dostępu usługi GitHub. Ten token musi mieć uprawnienie repozytorium .
Bezserwerowa usługa SQL Warehouse lub pro SQL Warehouse. Zobacz Typy usługi SQL Warehouse.
Zakres wpisów tajnych usługi Databricks. Zakres wpisu tajnego służy do bezpiecznego przechowywania tokenu usługi GitHub. Zobacz Krok 1. Przechowywanie tokenu usługi GitHub w kluczu tajnym.

Krok 1. Przechowywanie tokenu usługi GitHub w kluczu tajnym

Zamiast trwale zapisywać poświadczenia, takie jak osobisty token dostępu usługi GitHub w zadaniu, usługa Databricks zaleca bezpieczne przechowywanie wpisów tajnych i zarządzanie nimi. Następujące polecenia interfejsu wiersza polecenia usługi Databricks to przykład tworzenia zakresu wpisów tajnych i przechowywania tokenu usługi GitHub w kluczu tajnym w tym zakresie:

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

Zastąp <scope-name ciąg nazwą zakresu wpisu tajnego usługi Azure Databricks, aby przechowywać token.
Zastąp <token-key> ciąg nazwą klucza, który ma zostać przypisany do tokenu.
Zastąp <token> ciąg wartością osobistego tokenu dostępu usługi GitHub.

Krok 2. Tworzenie skryptu w celu pobrania danych usługi GitHub

Poniższy skrypt języka Python używa interfejsu API REST usługi GitHub do pobierania danych dotyczących zatwierdzeń i współtworzenia z repozytorium GitHub. Argumenty wejściowe określają repozytorium GitHub. Rekordy są zapisywane w lokalizacji w systemie plików DBFS określonych przez inny argument wejściowy.

W tym przykładzie użyto systemu plików DBFS do przechowywania skryptu języka Python, ale do przechowywania skryptu i zarządzania nim można również użyć folderów Git lub plików obszaru roboczego usługi Databricks.

Zapisz ten skrypt w lokalizacji na dysku lokalnym:

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

Przekaż skrypt do systemu plików DBFS:
1. Przejdź do strony docelowej usługi Azure Databricks i kliknij pozycję Wykaz na pasku bocznym.
2. Kliknij pozycję Przeglądaj system plików DBFS.
3. W przeglądarce plików DBFS kliknij pozycję Przekaż. Zostanie wyświetlone okno dialogowe Przekazywanie danych do systemu plików DBFS .
4. Wprowadź ścieżkę w systemie dbFS do przechowywania skryptu, kliknij pozycję Upuść pliki do przekazania lub kliknij, aby przejść, a następnie wybierz skrypt języka Python.
5. Kliknij Gotowe.

Krok 3. Tworzenie potoku delta live tables w celu przetwarzania danych usługi GitHub

W tej sekcji utworzysz potok Delta Live Tables, aby przekonwertować nieprzetworzone dane usługi GitHub na tabele, które mogą być analizowane przez zapytania SQL usługi Databricks. Aby utworzyć potok, wykonaj następujące kroki:

Na pasku bocznym kliknij pozycję Nowy i wybierz pozycję Notes z menu. Zostanie wyświetlone okno dialogowe Tworzenie notesu .
W polu Język domyślny wprowadź nazwę i wybierz pozycję Python. Możesz pozostawić ustawienie Klaster ustawiony na wartość domyślną. Środowisko uruchomieniowe delta Live Tables tworzy klaster przed uruchomieniem potoku.
Kliknij pozycję Utwórz.

Skopiuj przykładowy kod w języku Python i wklej go do nowego notesu. Przykładowy kod można dodać do pojedynczej komórki notesu lub wielu komórek.

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

Na pasku bocznym kliknij pozycję Przepływy pracy, kliknij kartę Delta Live Tables i kliknij pozycję Utwórz potok.
Nadaj potokowi nazwę, na przykład Transform GitHub data.
W polu Biblioteki notesów wprowadź ścieżkę do notesu lub kliknij, aby wybrać notes.
Kliknij pozycję Dodaj konfigurację. W polu tekstowym Key wprowadź .commits-path W polu tekstowym Value wprowadź ścieżkę systemu plików DBFS, w której będą zapisywane rekordy usługi GitHub. Może to być dowolna wybrana ścieżka i jest tą samą ścieżką, której użyjesz podczas konfigurowania pierwszego zadania języka Python podczas tworzenia przepływu pracy.
Kliknij ponownie pozycję Dodaj konfigurację . W polu tekstowym Key wprowadź .contribs-path W polu tekstowym Value wprowadź ścieżkę systemu plików DBFS, w której będą zapisywane rekordy usługi GitHub. Może to być dowolna wybrana ścieżka i jest tą samą ścieżką, której użyjesz podczas konfigurowania drugiego zadania języka Python podczas tworzenia przepływu pracy.
W polu Target (Cel) wprowadź docelową bazę danych, na przykład github_tables. Ustawienie docelowej bazy danych publikuje dane wyjściowe w magazynie metadanych i jest wymagane w przypadku zapytań podrzędnych analizujących dane generowane przez potok.
Kliknij przycisk Zapisz.

Krok 4. Tworzenie przepływu pracy w celu pozyskiwania i przekształcania danych usługi GitHub

Przed przeanalizowaniem i wizualizowanie danych usługi GitHub za pomocą usługi Databricks SQL należy pozyskać i przygotować dane. Aby utworzyć przepływ pracy w celu wykonania tych zadań, wykonaj następujące kroki:

Tworzenie zadania usługi Azure Databricks i dodawanie pierwszego zadania

Przejdź do strony docelowej usługi Azure Databricks i wykonaj jedną z następujących czynności:
- Na pasku bocznym kliknij pozycję Przepływy pracy, a następnie kliknij pozycję .
- Na pasku bocznym kliknij pozycję Nowy i wybierz z menu pozycję Zadanie .
W oknie dialogowym zadania wyświetlonym na karcie Zadania zastąp ciąg Dodaj nazwę zadania... nazwą zadania, na przykład GitHub analysis workflow.
W polu Nazwa zadania wprowadź nazwę zadania, na przykład get_commits.
W polu Typ wybierz pozycję Skrypt języka Python.
W obszarze Źródło wybierz pozycję DBFS/S3.
W polu Ścieżka wprowadź ścieżkę do skryptu w systemie plików DBFS.
W obszarze Parametry wprowadź następujące argumenty dla skryptu języka Python:

["<owner>","<repo>","commits","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Zastąp <owner> ciąg nazwą właściciela repozytorium. Aby na przykład pobrać rekordy z github.com/databrickslabs/overwatch repozytorium, wprowadź .databrickslabs
- Zastąp <repo> ciąg nazwą repozytorium, na przykład overwatch.
- Zastąp <DBFS-output-dir> ciąg ścieżką w systemie plików DBFS, aby przechowywać rekordy pobrane z usługi GitHub.
- Zastąp <scope-name> ciąg nazwą utworzonego zakresu wpisu tajnego do przechowywania tokenu usługi GitHub.
- Zastąp <github-token-key> ciąg nazwą klucza przypisanego do tokenu usługi GitHub.
Kliknij pozycję Zapisz zadanie.

Dodaj kolejne zadanie

Kliknij poniżej właśnie utworzonego zadania.
W polu Nazwa zadania wprowadź nazwę zadania, na przykład get_contributors.
W polu Typ wybierz typ zadania skryptu języka Python.
W obszarze Źródło wybierz pozycję DBFS/S3.
W polu Ścieżka wprowadź ścieżkę do skryptu w systemie plików DBFS.
W obszarze Parametry wprowadź następujące argumenty dla skryptu języka Python:

["<owner>","<repo>","contributors","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Zastąp <owner> ciąg nazwą właściciela repozytorium. Aby na przykład pobrać rekordy z github.com/databrickslabs/overwatch repozytorium, wprowadź .databrickslabs
- Zastąp <repo> ciąg nazwą repozytorium, na przykład overwatch.
- Zastąp <DBFS-output-dir> ciąg ścieżką w systemie plików DBFS, aby przechowywać rekordy pobrane z usługi GitHub.
- Zastąp <scope-name> ciąg nazwą utworzonego zakresu wpisu tajnego do przechowywania tokenu usługi GitHub.
- Zastąp <github-token-key> ciąg nazwą klucza przypisanego do tokenu usługi GitHub.
Kliknij pozycję Zapisz zadanie.

Dodawanie zadania w celu przekształcenia danych

Kliknij poniżej właśnie utworzonego zadania.
W polu Nazwa zadania wprowadź nazwę zadania, na przykład transform_github_data.
W polu Typ wybierz potok Delta Live Tables i wprowadź nazwę zadania.
W obszarze Potok wybierz potok utworzony w kroku 3: Tworzenie potoku delta live tables w celu przetworzenia danych usługi GitHub.
Kliknij pozycję Utwórz.

Krok 5. Uruchamianie przepływu pracy przekształcania danych

Kliknij , aby uruchomić przepływ pracy. Aby wyświetlić szczegóły przebiegu, kliknij link w kolumnie Godzina rozpoczęcia przebiegu w widoku przebiegów zadania. Kliknij każde zadanie, aby wyświetlić szczegóły przebiegu zadania.

Krok 6. (Opcjonalnie) Aby wyświetlić dane wyjściowe po zakończeniu przebiegu przepływu pracy, wykonaj następujące kroki:

W widoku szczegółów przebiegu kliknij zadanie Delta Live Tables.
W panelu Szczegóły przebiegu zadania kliknij nazwę potoku w obszarze Potok. Zostanie wyświetlona strona Szczegóły potoku.
Wybierz tabelę commits_by_author w potoku DAG.
Kliknij nazwę tabeli obok pozycji Magazyn metadanych w panelu commits_by_author . Zostanie otwarta strona Eksplorator wykazu.

W Eksploratorze wykazu można wyświetlić schemat tabeli, przykładowe dane i inne szczegóły dotyczące danych. Wykonaj te same kroki, aby wyświetlić dane dla github_contributors_raw tabeli.

Krok 7. Usuwanie danych usługi GitHub

W rzeczywistej aplikacji możesz stale pozyskiwać i przetwarzać dane. Ponieważ ten przykład pobiera i przetwarza cały zestaw danych, musisz usunąć już pobrane dane usługi GitHub, aby zapobiec błędowi podczas ponownego uruchamiania przepływu pracy. Aby usunąć pobrane dane, wykonaj następujące kroki:

Utwórz nowy notes i wprowadź następujące polecenia w pierwszej komórce:
```
dbutils.fs.rm("<commits-path", True)
dbutils.fs.rm("<contributors-path", True)
```
Zastąp <commits-path> wartości i <contributors-path> ścieżkami systemu plików DBFS skonfigurowanymi podczas tworzenia zadań języka Python.
Kliknij i wybierz pozycję Uruchom komórkę.

Ten notes można również dodać jako zadanie w przepływie pracy.

Krok 8. Tworzenie zapytań SQL usługi Databricks

Po uruchomieniu przepływu pracy i utworzeniu wymaganych tabel utwórz zapytania w celu przeanalizowania przygotowanych danych. Aby utworzyć przykładowe zapytania i wizualizacje, wykonaj następujące kroki:

Wyświetlanie 10 najważniejszych współautorów według miesiąca

Kliknij ikonę poniżej logo usługi Databricks na pasku bocznym i wybierz pozycję SQL.
Kliknij pozycję Utwórz zapytanie , aby otworzyć edytor zapytań SQL usługi Databricks.
Upewnij się, że wykaz jest ustawiony na hive_metastore. Kliknij przycisk domyślny obok hive_metastore i ustaw bazę danych na wartość docelową ustawioną w potoku Delta Live Tables.

Na karcie Nowe zapytanie wprowadź następujące zapytanie:

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

Kliknij kartę Nowe zapytanie i zmień nazwę zapytania, na przykład Commits by month top 10 contributors.
Domyślnie wyniki są wyświetlane jako tabela. Aby zmienić sposób wizualizacji danych, na przykład przy użyciu wykresu słupkowego, na panelu Wyniki kliknij i kliknij przycisk Edytuj.
W obszarze Typ wizualizacji wybierz pozycję Pasek.
W kolumnie X wybierz pozycję miesiąc.
W kolumnach Y wybierz pozycję count(1).
W obszarze Grupuj według wybierz nazwę.
Kliknij przycisk Zapisz.

Wyświetlanie 20 najważniejszych współautorów

Kliknij pozycję + > Utwórz nowe zapytanie i upewnij się, że wykaz jest ustawiony na hive_metastore. Kliknij przycisk domyślny obok hive_metastore i ustaw bazę danych na wartość docelową ustawioną w potoku Delta Live Tables.

Wprowadź następujące zapytanie:

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

Kliknij kartę Nowe zapytanie i zmień nazwę zapytania, na przykład Top 20 contributors.
Aby zmienić wizualizację z tabeli domyślnej, na panelu Wyniki kliknij i kliknij przycisk Edytuj.
W obszarze Typ wizualizacji wybierz pozycję Pasek.
W kolumnie X wybierz pozycję Login (Logowanie).
W kolumnach Y wybierz pozycję Współtworzenie.
Kliknij przycisk Zapisz.

Wyświetlanie łącznych zatwierdzeń według autora

Kliknij pozycję + > Utwórz nowe zapytanie i upewnij się, że wykaz jest ustawiony na hive_metastore. Kliknij przycisk domyślny obok hive_metastore i ustaw bazę danych na wartość docelową ustawioną w potoku Delta Live Tables.

Wprowadź następujące zapytanie:

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

Kliknij kartę Nowe zapytanie i zmień nazwę zapytania, na przykład Total commits by author.
Aby zmienić wizualizację z tabeli domyślnej, na panelu Wyniki kliknij i kliknij przycisk Edytuj.
W obszarze Typ wizualizacji wybierz pozycję Pasek.
W kolumnie X wybierz nazwę.
W kolumnach Y wybierz pozycję Zatwierdzenia.
Kliknij przycisk Zapisz.

Krok 9. Tworzenie pulpitu nawigacyjnego

Na pasku bocznym kliknij pozycję Pulpity nawigacyjne
Kliknij pozycję Utwórz pulpit nawigacyjny.
Wprowadź nazwę pulpitu nawigacyjnego, na przykład GitHub analysis.
Dla każdego zapytania i wizualizacji utworzonej w kroku 8. Tworzenie zapytań SQL usługi Databricks kliknij pozycję Dodaj > wizualizację i wybierz każdą wizualizację.

Krok 10. Dodawanie zadań SQL do przepływu pracy

Aby dodać nowe zadania zapytań do przepływu pracy utworzonego w sekcji Tworzenie zadania usługi Azure Databricks i dodać pierwsze zadanie, dla każdego zapytania utworzonego w kroku 8: Tworzenie zapytań SQL usługi Databricks:

Kliknij pozycję Przepływy pracy na pasku bocznym.
W kolumnie Nazwa kliknij nazwę zadania.
Kliknij kartę Zadania .
Kliknij poniżej ostatniego zadania.
Wprowadź nazwę zadania, w polu Typ wybierz pozycję SQL, a w zadaniu SQL wybierz pozycję Zapytanie.
Wybierz zapytanie w zapytaniu SQL.
W usłudze SQL Warehouse wybierz bezserwerową usługę SQL Warehouse lub pro SQL Warehouse, aby uruchomić zadanie.
Kliknij pozycję Utwórz.

Krok 11. Dodawanie zadania pulpitu nawigacyjnego

Kliknij poniżej ostatniego zadania.
Wprowadź nazwę zadania, w polu Typ wybierz pozycję SQL, a w zadaniu SQL wybierz pozycję Starszy pulpit nawigacyjny.
Wybierz pulpit nawigacyjny utworzony w kroku 9: Tworzenie pulpitu nawigacyjnego.
W usłudze SQL Warehouse wybierz bezserwerową usługę SQL Warehouse lub pro SQL Warehouse, aby uruchomić zadanie.
Kliknij pozycję Utwórz.

Krok 12. Uruchamianie kompletnego przepływu pracy

Aby uruchomić przepływ pracy, kliknij pozycję . Aby wyświetlić szczegóły przebiegu, kliknij link w kolumnie Godzina rozpoczęcia przebiegu w widoku przebiegów zadania.

Krok 13. Wyświetlanie wyników

Aby wyświetlić wyniki po zakończeniu przebiegu, kliknij końcowe zadanie pulpitu nawigacyjnego i kliknij nazwę pulpitu nawigacyjnego w obszarze Pulpit nawigacyjny SQL w prawym panelu.

Udostępnij za pośrednictwem