Usar o Databricks SQL em um job do Databricks

É possível usar o tipo de tarefa SQL em um trabalho Databricks, permitindo que o senhor crie, programe, opere e monitore fluxos de trabalho que incluam objetos Databricks SQL, como consultas, painéis legados e alertas. Por exemplo, seu fluxo de trabalho pode ingerir dados, preparar os dados, realizar análises usando consultas Databricks SQL e, em seguida, exibir os resultados em um painel legado.

Este artigo fornece um exemplo de fluxo de trabalho que cria um painel de controle legado exibindo métricas para as contribuições do GitHub. Neste exemplo, o senhor irá:

Ingest GitHub uso de dados a Python script e o GitHub REST API.
Transforme o GitHub uso de dados em Delta Live Tables pipeline.
Acionar Databricks SQL consultas que realizam análises nos dados preparados.
Exibir a análise em um dashboard legado.

Antes de começar

O senhor precisa dos seguintes itens para concluir este passo a passo:

Um GitHub pessoal access token. Esses tokens devem ter a permissão repo permissão.
Um serverless SQL warehouse ou um pro SQL warehouse. Consulte SQL warehouse types.
Um Secret Scope do Databricks. O Secret Scope é usado para armazenar com segurança os tokens do GitHub. Consulte a passo 1: armazene os tokens do GitHub em um segredo.

passo 1: armazene os tokens do GitHub em segredo

Em vez de codificar credenciais como a GitHub pessoal access token em um Job, a Databricks recomenda o uso de um Secret Scope para armazenar e gerenciar segredos com segurança. Os seguintes comandos Databricks CLI são um exemplo da criação de um escopo secreto e do armazenamento dos tokens GitHub em um segredo nesse escopo:

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

Substitua <scope-name pelo nome de um Databricks Secret Scope para armazenar os tokens.
Substitua <token-key> pelo nome de um key para atribuir aos tokens.
Substitua <token> pelo valor do GitHub pessoal access token.

passo 2: Criar um script para buscar dados do GitHub

O script Python a seguir usa o endereço GitHub REST API para obter dados sobre o commit e as contribuições de um GitHub repo. Os argumentos de entrada especificam o endereço GitHub repo. Os registros são salvos em um local no DBFS especificado por outro argumento de entrada.

Este exemplo usa o DBFS para armazenar o script Python, mas o senhor também pode usar pastas Git do Databricks ou arquivos de espaço de trabalho para armazenar e gerenciar o script.

Salve esse script em um local em seu disco local:

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

Faça o upload do script para o DBFS:
1. Acesse o site Databricks páginas de aterrissagem e clique em Catalog na barra lateral.
2. Clique em Browse DBFS.
3. No navegador de arquivos DBFS, clique em upload. A caixa de diálogo upload Data to DBFS é exibida.
4. Digite um caminho no DBFS para armazenar o script, clique em Drop files to upload (Soltar arquivos para carregar) ou clique para procurar e selecione o script Python.
5. Clique em Concluído.

passo 3: criar um pipeline Delta Live Tables para processar os dados do GitHub

Nesta seção, você cria um pipeline Delta Live Tables para converter os dados brutos do GitHub em tabelas que podem ser analisadas pela query Databricks SQL. Para criar o pipeline, execute as seguintes passos:

Na barra lateral, clique em New e selecione Notebook no menu. A caixa de diálogo Create Notebook é exibida.
No idioma padrão, digite um nome e selecione Python. O senhor pode deixar o cluster definido com o valor default. O tempo de execução do Delta Live Tables cria um cluster antes de executar o pipeline.
Clique em Criar.

Copie o exemplo de código Python e cole-o em seu novo Notebook. O senhor pode adicionar o código de exemplo a uma única célula do Notebook ou a várias células.

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 barra lateral, clique em fluxo de trabalho, clique no botão Delta Live Tables tab e clique em Criar pipeline.
Dê um nome ao pipeline, por exemplo, Transform GitHub data.
No campo Notebook biblioteca, digite o caminho para o Notebook ou clique em para selecionar o Notebook.
Clique em Add configuration (Adicionar configuração). Na caixa de texto Key, digite commits-path. Na caixa de texto Value, insira o caminho DBFS onde os registros do GitHub serão gravados. Esse pode ser qualquer caminho que você escolher e é o mesmo caminho que você usará ao configurar a primeira tarefa Python ao criar o fluxo de trabalho.
Clique novamente em Add configuration (Adicionar configuração ). Na caixa de texto Key, digite contribs-path. Na caixa de texto Value, insira o caminho DBFS onde os registros do GitHub serão gravados. Esse pode ser qualquer caminho que o senhor escolher e é o mesmo caminho que usará ao configurar a segunda tarefa do Python quando criar o fluxo de trabalho.
No campo Target (Destino ), digite um banco de dados de destino, por exemplo, github_tables. A definição de um banco de dados de destino publica os dados de saída no metastore e é necessária para as consultas downstream que analisam os dados produzidos pelo pipeline.
Clique em Salvar.

passo 4: Criar um fluxo de trabalho para ingerir e transformar dados do GitHub

Antes de analisar e visualizar os dados do GitHub com Databricks SQL, você precisa ingerir e preparar os dados. Para criar um fluxo de trabalho para concluir essas tarefas, execute as seguintes passos:

Crie um Databricks Job e adicione a primeira tarefa

Acesse o site Databricks páginas de aterrissagem e faça uma das seguintes ações:
- Na barra lateral, clique em fluxo de trabalho e clique em .
- Na barra lateral, clique em New e selecione Job no menu.
Na caixa de diálogo da tarefa que aparece na tarefa tab, substitua Add a name for your Job (Adicionar um nome para o trabalho ) pelo nome do trabalho, por exemplo, GitHub analysis workflow.
Em Nome da tarefa, insira um nome para a tarefa, por exemplo, get_commits.
Em Type, selecione Python script.
Em Source, selecione DBFS / S3.
Em Path (Caminho), digite o caminho para o script no DBFS.
Em Parameters (Parâmetros), digite os seguintes argumentos para o script Python:

["<owner>","<repo>","commits","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Substitua <owner> pelo nome do proprietário do repositório. Por exemplo, para buscar registros do repositório github.com/databrickslabs/overwatch, digite databrickslabs.
- Substitua <repo> pelo nome do repositório, por exemplo, overwatch.
- Substitua <DBFS-output-dir> por um caminho no DBFS para armazenar os registros obtidos do GitHub.
- Substitua <scope-name> pelo nome do Secret Scope que o senhor criou para armazenar os tokens GitHub.
- Substitua <github-token-key> pelo nome do key que o senhor atribuiu aos tokens GitHub.
Clique em Save task (Salvar tarefa).

Adicionar outra tarefa

Clique em abaixo da tarefa que o senhor acabou de criar.
Em Nome da tarefa, insira um nome para a tarefa, por exemplo, get_contributors.
Em Type (Tipo), selecione o tipo de tarefa de script Python.
Em Source, selecione DBFS / S3.
Em Path (Caminho), digite o caminho para o script no DBFS.
Em Parameters (Parâmetros), digite os seguintes argumentos para o script Python:

["<owner>","<repo>","contributors","<DBFS-output-dir>","<scope-name>","<github-token-key>"]
- Substitua <owner> pelo nome do proprietário do repositório. Por exemplo, para buscar registros do repositório github.com/databrickslabs/overwatch, digite databrickslabs.
- Substitua <repo> pelo nome do repositório, por exemplo, overwatch.
- Substitua <DBFS-output-dir> por um caminho no DBFS para armazenar os registros obtidos do GitHub.
- Substitua <scope-name> pelo nome do Secret Scope que o senhor criou para armazenar os tokens GitHub.
- Substitua <github-token-key> pelo nome do key que o senhor atribuiu aos tokens GitHub.
Clique em Save task (Salvar tarefa).

Adicionar uma tarefa para transformar os dados

Clique em abaixo da tarefa que o senhor acabou de criar.
Em Nome da tarefa, insira um nome para a tarefa, por exemplo, transform_github_data.
Em Type (Tipo), selecione o pipeline Delta Live Tables e digite um nome para a tarefa.
Em Pipeline, selecione o pipeline criado na passo 3: Crie um pipeline Delta Live Tables para processar os dados do GitHub.
Clique em Criar.

passo 5: a execução dos dados mudou o fluxo de trabalho

Clique em para executar o fluxo de trabalho. Para view os detalhes da execução, clique no link na coluna de tempo de início da execução no Job execução view. Clique em cada tarefa para acessar view detalhes da execução da tarefa.

passo 6: (opcional) para visualizar os dados de saída após a conclusão da execução do fluxo de trabalho, execute as seguintes passos:

Nos detalhes da execução view, clique na tarefa Delta Live Tables.
No painel de detalhes da execução da tarefa, clique no nome pipeline em pipeline. A página de detalhes do pipeline é exibida.
Selecione a tabela commits_by_author no DAG do pipeline.
Clique no nome da tabela ao lado de Metastore no painel de confirmação. A página Catalog Explorer é aberta.

No Catalog Explorer, o senhor pode acessar view o esquema da tabela, os dados de amostra e outros detalhes dos dados. Siga os mesmos passos para view os dados da tabela github_contributors_raw.

passo 7: Remova os dados do GitHub

Em um aplicativo do mundo real, o senhor pode estar continuamente ingerindo e processando dados. Como este exemplo downloads e processa todo o conjunto de dados, o senhor deve remover os dados já baixados GitHub para evitar um erro ao executar novamente o fluxo de trabalho. Para remover os dados de downloads, execute os seguintes passos:

Crie um novo Notebook e digite o seguinte comando na primeira célula:
```
dbutils.fs.rm("<commits-path", True)
dbutils.fs.rm("<contributors-path", True)
```
Substitua <commits-path> e <contributors-path> pelos caminhos DBFS que o senhor configurou ao criar a tarefa Python.
Clique em e selecione executar Cell.

O senhor também pode adicionar esse Notebook como uma tarefa no fluxo de trabalho.

passo 8: criar a query Databricks SQL

Depois de executar o fluxo de trabalho e criar as tabelas necessárias, crie query para analisar os dados preparados. Para criar a query de exemplo e as visualizações, execute as seguintes passos:

Exibir os 10 principais colaboradores por mês

Clique no ícone abaixo do logotipo da Databricks na barra lateral e selecione SQL.
Clique em Create a query (Criar uma consulta ) para abrir o editor de consultas Databricks SQL.
Certifique-se de que o catálogo esteja definido como hive_metastore. Clique em default ao lado de hive_metastore e defina o banco de dados com o valor Target que o senhor definiu no pipeline Delta Live Tables.

Em New Query (Nova consulta ) tab, digite a seguinte consulta:

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

Clique em New query (Nova consulta ) tab e renomeie a consulta, por exemplo, Commits by month top 10 contributors.
Em default, os resultados são exibidos como uma tabela. Para alterar a forma como os dados são visualizados, por exemplo, usando um gráfico de barras, no painel Results (Resultados ), clique em e clique em Edit (Editar).
Em Visualization type (Tipo de visualização), selecione Bar (Barra).
Na coluna X, selecione o mês.
Nas colunas Y, selecione count(1).
Em Group by, selecione o nome.
Clique em Salvar.

Exibir os 20 principais colaboradores

Clique em + > Create new query e verifique se o catálogo está definido como hive_metastore. Clique em default ao lado de hive_metastore e defina o banco de dados com o valor Target que o senhor definiu no pipeline Delta Live Tables.

Digite a seguinte consulta:

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

Clique em New query (Nova consulta ) tab e renomeie a consulta, por exemplo, Top 20 contributors.
Para alterar a visualização da tabela default, no painel Results (Resultados ), clique em e clique em Edit (Editar).
Em Visualization type (Tipo de visualização), selecione Bar (Barra).
Na coluna X, selecione login.
Nas colunas Y, selecione contribuições.
Clique em Salvar.

Exibir o total de confirmações por autor

Clique em + > Create new query e verifique se o catálogo está definido como hive_metastore. Clique em default ao lado de hive_metastore e defina o banco de dados com o valor Target que o senhor definiu no pipeline Delta Live Tables.

Digite a seguinte consulta:

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

Clique em New query (Nova consulta ) tab e renomeie a consulta, por exemplo, Total commits by author.
Para alterar a visualização da tabela default, no painel Results (Resultados ), clique em e clique em Edit (Editar).
Em Visualization type (Tipo de visualização), selecione Bar (Barra).
Na coluna X, selecione o nome.
Nas colunas Y, selecione commit.
Clique em Salvar.

passo 9: criar um painel

Na barra lateral, clique em Dashboards
Clique em Create dashboard (Criar painel).
Digite um nome para o painel, por exemplo, GitHub analysis.
Para cada query e visualização criada na passo 8: Crie a query Databricks SQL, clique em Adicionar > Visualização e selecione cada visualização.

passo 10: Adicionar as tarefas SQL ao fluxo de trabalho

Para adicionar as novas tarefas query ao fluxo de trabalho que você criou em Criar um Job do Databricks e adicionar a primeira tarefa, para cada query criada na passo 8: Crie a query Databricks SQL:

Clique em fluxo de trabalho na barra lateral.
Na coluna Nome, clique no nome do cargo.
Clique na aba Tarefas.
Clique em abaixo da última tarefa.
Digite um nome para a tarefa, em Type selecione SQLe, em SQL tarefa, selecione Query (Consulta).
Selecione a consulta na consulta SQL.
Em SQL warehouseselecione um serverless SQL warehouse ou um pro SQL warehouse para executar a tarefa.
Clique em Criar.

passo 11: Adicionar uma tarefa de painel

Clique em abaixo da última tarefa.
Digite um nome para a tarefa, em Type (Tipo), selecione SQLe, em SQL tarefa, selecione Legacy dashboard (Painel legado).
Selecione o painel criado na passo 9: Criar um painel.
Em SQL warehouseselecione um serverless SQL warehouse ou um pro SQL warehouse para executar a tarefa.
Clique em Criar.

passo 12: execução do fluxo de trabalho completo

Para executar o fluxo de trabalho, clique em . Para view os detalhes da execução, clique no link na coluna de tempo de início da execução no Job execução view.

passo 13: Veja os resultados

Para view os resultados quando a execução for concluída, clique na tarefa do painel final e clique no nome do painel em SQL dashboard no painel direito.