Partilhar via

Usar o Databricks SQL em um trabalho do Azure Databricks

  • Artigo

Você pode usar o tipo de tarefa SQL em um trabalho do Azure Databricks, permitindo criar, agendar, operar e monitorar fluxos de trabalho que incluem objetos SQL Databricks, como consultas, painéis herdados e alertas. Por exemplo, seu fluxo de trabalho pode ingerir dados, prepará-los, executar análises usando consultas Databricks SQL e, em seguida, exibir os resultados em um painel herdado.

Este artigo fornece um exemplo de fluxo de trabalho que cria um painel herdado exibindo métricas para contribuições do GitHub. Neste exemplo, você irá:

  • Ingerir dados do GitHub usando um script Python e a API REST do GitHub.
  • Transforme os dados do GitHub usando um pipeline Delta Live Tables.
  • Acione consultas SQL Databricks realizando análises nos dados preparados.
  • Exiba a análise em um painel herdado.

Painel de análise do GitHub

Antes de começar

Você precisa do seguinte para concluir este passo a passo:

  • Um token de acesso pessoal do GitHub. Esse token deve ter a permissão de recompra .
  • Um armazém SQL sem servidor ou um armazém SQL profissional. Consulte Tipos de armazém SQL.
  • Um escopo secreto do Databricks. O escopo secreto é usado para armazenar com segurança o token do GitHub. Consulte Etapa 1: armazenar o token do GitHub em segredo.

Etapa 1: Armazenar o token do GitHub em segredo

Em vez de codificar credenciais como o token de acesso pessoal do GitHub em um trabalho, o Databricks recomenda o uso de um escopo secreto para armazenar e gerenciar segredos com segurança. Os seguintes comandos da CLI do Databricks são um exemplo de criação de um escopo secreto e armazenamento do token 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 escopo secreto do Azure Databricks para armazenar o token.
  • Substitua <token-key> pelo nome de uma chave para atribuir ao token.
  • Substitua <token> pelo valor do token de acesso pessoal do GitHub.

Etapa 2: Criar um script para buscar dados do GitHub

O script Python a seguir usa a API REST do GitHub para buscar dados sobre confirmações e contribuições de um repositório GitHub. Os argumentos de entrada especificam o repositório GitHub. Os registros são salvos em um local no DBFS especificado por outro argumento de entrada.

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

  • Salve este script em um local no 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()

  • Carregue o script no DBFS:

    1. Vá para a página inicial do Azure Databricks e clique em Ícone do catálogo Catálogo na barra lateral.
    2. Clique em Procurar DBFS.
    3. No navegador de arquivos DBFS, clique em Carregar. A caixa de diálogo Carregar dados no DBFS é exibida.
    4. Insira um caminho no DBFS para armazenar o script, clique em Soltar arquivos para carregar ou clique para navegar e selecione o script Python.
    5. Clique em Concluído.

Etapa 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 por consultas SQL do Databricks. Para criar o pipeline, execute as seguintes etapas:

  1. Na barra lateral, clique em Novo ícone Novo e selecione Bloco de Anotações no menu. A caixa de diálogo Criar bloco de anotações é exibida.

  2. Em Default Language, insira um nome e selecione Python. Você pode deixar Cluster definido como o valor padrão. O tempo de execução do Delta Live Tables cria um cluster antes de executar seu pipeline.

  3. Clique em Criar.

  4. Copie o exemplo de código Python e cole-o em seu novo bloco de anotações. Você pode adicionar o código de exemplo a uma única célula do bloco de anotações 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"))
  )

  5. Na barra lateral, clique em Ícone Fluxos de Trabalho Fluxos de trabalho, clique na guia Delta Live Tables e clique em Criar pipeline.

  6. Dê um nome ao pipeline, por exemplo, Transform GitHub data.

  7. No campo Bibliotecas de blocos de notas, introduza o caminho para o seu bloco de notas ou clique Ícone do seletor de arquivos para selecionar o bloco de notas.

  8. Clique em Adicionar configuração. Na caixa de Key texto, digite commits-path. Na caixa de Value texto, digite o caminho DBFS onde os registros do GitHub serão gravados. Este 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.

  9. Clique em Adicionar configuração novamente. Na caixa de Key texto, digite contribs-path. Na caixa de Value texto, digite o caminho DBFS onde os registros do GitHub serão gravados. Este pode ser qualquer caminho que você escolher e é o mesmo caminho que você usará ao configurar a segunda tarefa Python ao criar o fluxo de trabalho.

  10. No campo Destino, insira 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.

  11. Clique em Guardar.

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

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

Criar um trabalho do Azure Databricks e adicionar a primeira tarefa

  1. Vá para a página inicial do Azure Databricks e siga um destes procedimentos:

    • Na barra lateral, clique em Ícone Fluxos de Trabalho Fluxos de trabalho e clique em Botão Criar Trabalho.
    • Na barra lateral, clique em Novo ícone Novo e selecione Trabalho no menu.

  2. Na caixa de diálogo da tarefa que aparece na guia Tarefas, substitua Adicionar um nome para o trabalho... pelo nome do trabalho, por exemplo, GitHub analysis workflow.

  3. Em Nome da tarefa, insira um nome para a tarefa, por exemplo, get_commits.

  4. Em Type, selecione Python script.

  5. Em Source, selecione DBFS / S3.

  6. Em Caminho, insira o caminho para o script no DBFS.

  7. Em Parâmetros, insira 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 no github.com/databrickslabs/overwatch repositório, 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 no GitHub.
    • Substitua <scope-name> pelo nome do escopo secreto que você criou para armazenar o token do GitHub.
    • Substitua <github-token-key> pelo nome da chave atribuída ao token do GitHub.

  8. Clique em Salvar tarefa.

Adicionar outra tarefa

  1. Clique Botão Adicionar Tarefa abaixo da tarefa que acabou de criar.

  2. Em Nome da tarefa, insira um nome para a tarefa, por exemplo, get_contributors.

  3. Em Tipo, selecione o tipo de tarefa de script Python.

  4. Em Source, selecione DBFS / S3.

  5. Em Caminho, insira o caminho para o script no DBFS.

  6. Em Parâmetros, insira 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 no github.com/databrickslabs/overwatch repositório, 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 no GitHub.
    • Substitua <scope-name> pelo nome do escopo secreto que você criou para armazenar o token do GitHub.
    • Substitua <github-token-key> pelo nome da chave atribuída ao token do GitHub.

  7. Clique em Salvar tarefa.

Adicionar uma tarefa para transformar os dados

  1. Clique Botão Adicionar Tarefa abaixo da tarefa que acabou de criar.
  2. Em Nome da tarefa, insira um nome para a tarefa, por exemplo, transform_github_data.
  3. Em Tipo, selecione pipeline Delta Live Tables e insira um nome para a tarefa.
  4. Em Pipeline, selecione o pipeline criado em Etapa 3: Criar um pipeline Delta Live Tables para processar os dados do GitHub.
  5. Clique em Criar.

Etapa 5: Executar o fluxo de trabalho de transformação de dados

Clique Botão Executar agora para executar o fluxo de trabalho. Para exibir os detalhes da execução, clique no link na coluna Hora de início da execução na exibição de execução do trabalho. Clique em cada tarefa para ver os detalhes da tarefa executada.

Etapa 6: (Opcional) Para exibir os dados de saída após a conclusão da execução do fluxo de trabalho, execute as seguintes etapas:

  1. Na visualização de detalhes da execução, clique na tarefa Delta Live Tables.
  2. No painel Detalhes da execução da tarefa, clique no nome do pipeline em Pipeline. A página Detalhes do pipeline é exibida.
  3. Selecione a commits_by_author tabela no DAG de pipeline.
  4. Clique no nome da tabela ao lado de Metastore no painel commits_by_author . A página Catalog Explorer é aberta.

No Gerenciador de Catálogos, você pode exibir o esquema da tabela, os dados de exemplo e outros detalhes dos dados. Siga as mesmas etapas para exibir dados para a github_contributors_raw tabela.

Etapa 7: Remover os dados do GitHub

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

  1. Crie um novo bloco de notas e introduza os seguintes comandos 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 você configurou ao criar as tarefas do Python.

  2. Clique Executar Menu e selecione Executar célula.

Você também pode adicionar este bloco de anotações como uma tarefa no fluxo de trabalho.

Etapa 8: Criar as consultas SQL do Databricks

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

Exibir os 10 principais contribuidores por mês

  1. Clique no ícone abaixo do logotipo Logótipo Databricks do Databricks na barra lateral e selecione SQL.

  2. Clique em Criar uma consulta para abrir o editor de consultas Databricks SQL.

  3. Verifique se o catálogo está definido como hive_metastore. Clique em padrão ao lado de hive_metastore e defina o banco de dados para o valor de destino definido no pipeline Delta Live Tables.

  4. No separador Nova Consulta, introduza 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

  5. Clique na guia Nova consulta e renomeie a consulta, por exemplo, Commits by month top 10 contributors.

  6. Por padrão, 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 Resultados cliqueMenu de kebab em e clique em Editar.

  7. Em Tipo de visualização, selecione Barra.

  8. Na coluna X, selecione mês.

  9. Nas colunas Y, selecione count(1).

  10. Em Agrupar por, selecione o nome.

  11. Clique em Guardar.

Exibir os 20 principais contribuidores

  1. Clique em + > Criar nova consulta e verifique se o catálogo está definido como hive_metastore. Clique em padrão ao lado de hive_metastore e defina o banco de dados para o valor de destino definido no pipeline Delta Live Tables.

  2. Introduza a seguinte consulta:

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

  3. Clique na guia Nova consulta e renomeie a consulta, por exemplo, Top 20 contributors.

  4. Para alterar a visualização da tabela padrão, no painel Resultados, clique em Menu de kebab Editar.

  5. Em Tipo de visualização, selecione Barra.

  6. Na coluna X, selecione login.

  7. Nas colunas Y, selecione contribuições.

  8. Clique em Guardar.

Exibir o total de confirmações por autor

  1. Clique em + > Criar nova consulta e verifique se o catálogo está definido como hive_metastore. Clique em padrão ao lado de hive_metastore e defina o banco de dados para o valor de destino definido no pipeline Delta Live Tables.

  2. Introduza a seguinte consulta:

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

  3. Clique na guia Nova consulta e renomeie a consulta, por exemplo, Total commits by author.

  4. Para alterar a visualização da tabela padrão, no painel Resultados, clique em Menu de kebab Editar.

  5. Em Tipo de visualização, selecione Barra.

  6. Na coluna X, selecione o nome.

  7. Nas colunas Y, selecione confirmações.

  8. Clique em Guardar.

Etapa 9: Criar um painel

  1. Na barra lateral, clique em Ícone Dashboards Painéis
  2. Clique em Criar painel.
  3. Insira um nome para o painel, por exemplo, GitHub analysis.
  4. Para cada consulta e visualização criada na Etapa 8: Criar as consultas SQL do Databricks, clique em Adicionar > Visualização e selecione cada visualização.

Etapa 10: Adicionar as tarefas SQL ao fluxo de trabalho

Para adicionar as novas tarefas de consulta ao fluxo de trabalho criado em Criar um trabalho do Azure Databricks e adicionar a primeira tarefa, para cada consulta criada na Etapa 8: Criar as consultas SQL do Databricks:

  1. Clique em Ícone Fluxos de Trabalho Fluxos de trabalho na barra lateral.
  2. Na coluna Nome, clique no nome do trabalho.
  3. Clique na guia Tarefas .
  4. Clique Botão Adicionar Tarefa abaixo da última tarefa.
  5. Insira um nome para a tarefa, em Tipo selecione SQL e em Tarefa SQL selecione Consulta.
  6. Selecione a consulta na consulta SQL.
  7. No SQL warehouse, selecione um SQL warehouse sem servidor ou um pro SQL warehouse para executar a tarefa.
  8. Clique em Criar.

Etapa 11: Adicionar uma tarefa do painel

  1. Clique Botão Adicionar Tarefa abaixo da última tarefa.
  2. Insira um nome para a tarefa, em Tipo, selecione SQL e, em Tarefa SQL, selecione Painel herdado.
  3. Selecione o painel criado em Etapa 9: Criar um painel.
  4. No SQL warehouse, selecione um SQL warehouse sem servidor ou um pro SQL warehouse para executar a tarefa.
  5. Clique em Criar.

Etapa 12: Executar o fluxo de trabalho completo

Para executar o fluxo de trabalho, clique em Botão Executar agora. Para exibir os detalhes da execução, clique no link na coluna Hora de início da execução na exibição de execução do trabalho.

Passo 13: Ver os resultados

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