Consulte dados em tabelas do BigQuery no JupyterLab

Consulte dados em tabelas do BigQuery no JupyterLab

Nesta página, mostramos como consultar dados armazenados no BigQuery a partir da interface do JupyterLab na instância de notebooks gerenciados do Vertex AI Workbench.

Métodos para consultar dados do BigQuery em arquivos de notebook (IPYNB)

Para consultar dados do BigQuery de um arquivo de notebook do JupyterLab, use o comando mágico %%bigquery e a biblioteca de cliente do BigQuery para Python.

As instâncias do Vertex AI Workbench também incluem uma integração do BigQuery que permite navegar e consultar dados na interface do JupyterLab.

Nesta página, descrevemos como usar cada um desses métodos.

Antes de começar

Crie uma instância do Vertex AI Workbench, caso ainda não tenha feito isso.

Funções exigidas

Para garantir que a conta de serviço da instância tenha as permissões necessárias para consultar dados no BigQuery, peça ao administrador para conceder à conta de serviço da instância o papel de consumidor do Service Usage (roles/serviceusage.serviceUsageConsumer) do IAM no projeto. Para mais informações sobre como conceder papéis, consulte Gerenciar acesso.

O administrador também pode conceder à conta de serviço da instância as permissões necessárias por meio de papéis personalizados ou de outros papéis predefinidos

Abrir JupyterLab

1. No Console do Google Cloud, acesse a página Instâncias.

   Acesse "Instâncias"

2. Ao lado do nome da instância do Vertex AI Workbench, clique em Abrir JupyterLab.

   Sua instância do Vertex AI Workbench abre o JupyterLab.

Procurar recursos do BigQuery

A integração do BigQuery fornece um painel para procurar os recursos do BigQuery a que você tem acesso.

1. No menu de navegação do JupyterLab, clique em  BigQuery em Notebooks.

   O painel do BigQuery lista projetos e conjuntos de dados disponíveis, em que é possível realizar tarefas da seguinte maneira:

   • Para ver a descrição de um conjunto de dados, clique duas vezes no nome dele.
   • Para mostrar as tabelas, visualizações e modelos de um conjunto de dados, expanda o conjunto de dados.
   • Para abrir uma descrição resumida como uma guia no JupyterLab, clique duas vezes em uma tabela, visualização ou modelo.

   Observação: na descrição resumida de uma tabela, clique na guia Visualização para visualizar os dados de uma tabela. A imagem a seguir mostra uma visualização da tabela international_top_terms encontrada no conjunto de dados google_trends no projeto bigquery-public-data:

   

Consultar dados usando o comando mágico %%bigquery

Nesta seção, você escreverá o SQL diretamente nas células do notebook e lerá os dados do BigQuery no notebook do Python.

Os comandos mágicos que usam um caractere de porcentagem única ou dupla (% ou %%) permitem usar sintaxe mínima para interagir com o BigQuery no notebook. A biblioteca de cliente do BigQuery para Python é instalada automaticamente em uma instância do Vertex AI Workbench. Em segundo plano, o comando mágico %%bigquery usa a biblioteca de cliente do BigQuery para Python para executar a consulta especificada, converter os resultados em um DataFrame do Pandas, salvá-los em uma variável e exibir os resultados.

Observação: a partir da versão 1.26.0 do pacote google-cloud-bigquery do Python, a API BigQuery Storage é usada por padrão para fazer o download dos resultados dos comandos mágicos %%bigquery.

1. Para abrir um arquivo de notebook, selecione File > New > Notebook.

2. Na caixa de diálogo Selecionar kernel, selecione Python 3 e clique em Selecionar.

   O novo arquivo IPYNB será aberto.

3. Para ver o número de regiões por país no conjunto de dados international_top_terms, insira a seguinte instrução:

   %%bigquery
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code,
     country_name
   ORDER BY
     num_regions DESC;
   

4. Clique em  Executar célula.

   O resultado será assim:

   Query complete after 0.07s: 100%|██████████| 4/4 [00:00<00:00, 1440.60query/s]
   Downloading: 100%|██████████| 41/41 [00:02><00:00, 20.21rows/s]
   ... country_code country_name num_regions 0 TR Turkey 81 1 TH Thailand 77 2 VN Vietnam 63 3 JP Japan 47 4 RO Romania 42 5 NG Nigeria 37 6 IN India 36 7 ID Indonesia 34 8 CO Colombia 33 9 MX Mexico 32 10 BR Brazil 27 11 EG Egypt 27 12 UA Ukraine 27 13 CH Switzerland 26 14 AR Argentina 24 15 FR France 22 16 SE Sweden 21 17 HU Hungary 20 18 IT Italy 20 19 PT Portugal 20 20 NO Norway 19 21 FI Finland 18 22 NZ New Zealand 17 23 PH Philippines 17>

   Observação: seus resultados podem ser diferentes do que está acima, já que o conjunto de dados google_trends que está sendo consultado é atualizado com novos dados de maneira contínua.

5. Na próxima célula (abaixo da saída da célula anterior), insira o comando a seguir para executar a mesma consulta, mas desta vez salve os resultados em um novo DataFrame do Pandas chamado regions_by_country. Para isso, use um argumento com o comando mágico %%bigquery.

   %%bigquery regions_by_country
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code, country_name
   ORDER BY
     num_regions DESC;
   

   Observação: para mais informações sobre os argumentos disponíveis para o comando %%bigquery, consulte a documentação de comandos mágicos da biblioteca de cliente.

6. Clique em  Executar célula.

7. Na próxima célula, insira o comando a seguir para ver as primeiras linhas dos resultados da consulta que você acabou de ler:

   regions_by_country.head()
   

8. Clique em  Executar célula.

   O DataFrame regions_by_country do pandas está pronto para ser plotado.

Consultar dados usando a biblioteca de cliente do BigQuery

Nesta seção, você usará a biblioteca de cliente do BigQuery para Python diretamente para ler dados no notebook do Python.

A biblioteca de cliente oferece mais controle sobre suas consultas e permite usar configurações mais complexas para consultas e jobs. As integrações da biblioteca com o Pandas permitem combinar o poder do SQL declarativo com o código imperativo (Python) para ajudar a analisar, visualizar e transformar os dados.

Observação: é possível usar várias bibliotecas de visualização e de análise e conversão de dados para Python, como numpy, pandas, matplotlib e muitos outros. Várias dessas bibliotecas são criadas com base em um objeto DataFrame.

1. Na próxima célula, insira o seguinte código Python para importar a biblioteca de cliente do BigQuery para Python e inicializar um cliente:

   from google.cloud import bigquery
   
   client = bigquery.Client()
   

   O cliente do BigQuery é usado para enviar e receber mensagens da API BigQuery.

2. Clique em  Executar célula.

3. Na próxima célula, insira o código a seguir para recuperar a porcentagem de principais termos diários nos top_terms dos EUA que se sobrepõem ao longo do tempo por números de dias. A ideia é analisar os principais termos de cada dia e ver qual porcentagem deles se sobrepõe aos principais termos do dia anterior, 2 dias antes, 3 dias antes e assim por diante (para todos pares de datas durante aproximadamente um mês).

   sql = """
   WITH
     TopTermsByDate AS (
       SELECT DISTINCT refresh_date AS date, term
       FROM `bigquery-public-data.google_trends.top_terms`
     ),
     DistinctDates AS (
       SELECT DISTINCT date
       FROM TopTermsByDate
     )
   SELECT
     DATE_DIFF(Dates2.date, Date1Terms.date, DAY)
       AS days_apart,
     COUNT(DISTINCT (Dates2.date || Date1Terms.date))
       AS num_date_pairs,
     COUNT(Date1Terms.term) AS num_date1_terms,
     SUM(IF(Date2Terms.term IS NOT NULL, 1, 0))
       AS overlap_terms,
     SAFE_DIVIDE(
       SUM(IF(Date2Terms.term IS NOT NULL, 1, 0)),
       COUNT(Date1Terms.term)
       ) AS pct_overlap_terms
   FROM
     TopTermsByDate AS Date1Terms
   CROSS JOIN
     DistinctDates AS Dates2
   LEFT JOIN
     TopTermsByDate AS Date2Terms
     ON
       Dates2.date = Date2Terms.date
       AND Date1Terms.term = Date2Terms.term
   WHERE
     Date1Terms.date <= Dates2.date
   GROUP BY
     days_apart
   
   ORDER BY
     days_apart;
   """
   pct_overlap_terms_by_days_apart = client.query(sql).to_dataframe()
   
   pct_overlap_terms_by_days_apart.head()
   

   O SQL usado é encapsulado em uma string Python e transmitido para o método query() para executar uma consulta. O método to_dataframe aguarda a conclusão da consulta e faz o download dos resultados em um DataFrame de pandas usando a API BigQuery Storage.

4. Clique em  Executar célula.

   As primeiras linhas dos resultados da consulta são exibidas abaixo da célula de código.

      days_apart   num_date_pairs  num_date1_terms overlap_terms   pct_overlap_terms
    0          0             32               800            800            1.000000
    1          1             31               775            203            0.261935
    2          2             30               750             73            0.097333
    3          3             29               725             31            0.042759
    4          4             28               700             23            0.032857
   

   Observação: seus resultados podem ser diferentes do que está acima, já que o conjunto de dados google_trends que está sendo consultado é atualizado com novos dados de maneira contínua.

Para mais informações sobre o uso de bibliotecas de cliente do BigQuery, consulte o guia de início rápido Como usar bibliotecas de cliente.

Consultar dados usando a integração do BigQuery no Vertex AI Workbench

A integração do BigQuery fornece dois métodos adicionais para consultar dados. Esses métodos são diferentes do uso do comando mágico %%bigquery.

• O Editor de consultas na célula é um tipo de célula que pode ser usado nos arquivos de notebook.

• O Editor de consultas independente é aberto como uma guia separada no JupyterLab.

Na célula

Para usar o editor de consultas na célula para consultar dados em uma tabela do BigQuery, conclua as seguintes etapas:

1. No JupyterLab, abra um arquivo do notebook (IPYNB) ou crie um novo.

2. Para criar um editor de consultas na célula, clique na célula e, à direita da célula, clique no botão  Integração do BigQuery. Ou, em uma célula de marcação, digite #@BigQuery.

   A integração do BigQuery converte a célula em um editor de consultas.

3. Em uma nova linha abaixo de #@BigQuery, escreva sua consulta usando as declarações compatíveis e dialetos SQL do BigQuery. Se forem detectados erros na sua consulta, uma mensagem de erro será exibida no canto superior direito do editor de consultas. Se a consulta for válida, o número estimado de bytes a serem processados será exibido.

4. Clique em Enviar consulta. Os resultados da consulta são exibidos. Por padrão, os resultados da consulta são paginados em 100 linhas por página e limitados a 1.000 linhas no total, mas é possível alterar essas configurações na parte inferior da tabela de resultados. No editor de consultas, mantenha a consulta limitada apenas aos dados necessários para verificá-la. Você executará essa consulta novamente em uma célula do notebook, onde poderá ajustar o limite para recuperar o conjunto de resultados completo, se quiser.

   Observação: o texto e os resultados da consulta persistem após o fechamento e a reabertura do arquivo de notebook.

5. Clique em Consultar e carregar como o DataFrame para adicionar automaticamente uma nova célula que contenha um segmento de código que importe a biblioteca de cliente do BigQuery para Python. Depois, execute a consulta em um notebook. célula e armazena os resultados em um dataframe pandas chamado df.

Independente

Para usar o editor de consultas independente para consultar dados em uma tabela do BigQuery, conclua as seguintes etapas:

1. No JupyterLab, no painel BigQuery em Notebooks, clique com o botão direito do mouse em uma tabela e selecione Consultar tabela ou clique duas vezes em uma tabela para abrir uma description em uma guia separada e clique no link Query table.

2. Escreva sua consulta usando as declarações compatíveis e dialetos SQL do BigQuery. Se forem detectados erros na sua consulta, uma mensagem de erro será exibida no canto superior direito do editor de consultas. Se a consulta for válida, o número estimado de bytes a serem processados será exibido.

3. Clique em Enviar consulta. Os resultados da consulta são exibidos. Por padrão, os resultados da consulta são paginados em 100 linhas por página e limitados a 1.000 linhas no total, mas é possível alterar essas configurações na parte inferior da tabela de resultados. No editor de consultas, mantenha a consulta limitada apenas aos dados necessários para verificá-la. Você executará essa consulta novamente em uma célula do notebook, onde poderá ajustar o limite para recuperar o conjunto de resultados completo, se quiser.

4. Clique em Copiar código para o DataFrame para copiar um segmento de código que importa a biblioteca de cliente do BigQuery para Python, executa sua consulta em uma célula do notebook e armazena os resultados em Um DataFrame da pandas chamado df. Cole esse código em uma célula do notebook em que você quer executá-lo.

Acessar histórico de consultas e reutilizar consultas

Para ver seu histórico de consultas como uma guia no JupyterLab, siga estas etapas:

1. No menu de navegação do JupyterLab, clique em  BigQuery em Notebooks para abrir o painel BigQuery.

2. No painel do BigQuery, role para baixo e clique em Histórico de consultas.

   

   Uma lista de consultas será aberta em uma nova guia com as seguintes tarefas:

   • Para ver os detalhes de uma consulta, como o ID do job, quando a consulta foi executada e quanto tempo levou, clique na consulta.
   • Para revisar a consulta, executá-la novamente ou copiá-la no notebook para uso futuro, clique em Abrir consulta no editor.

Observação: se você executar consultas após abrir essa guia, clique em  Atualizar para ver as consultas mais recentes.

A seguir

• Para conferir exemplos de como visualizar os dados das tabelas do BigQuery, consulte Explorar e visualizar dados no BigQuery no JupyterLab.

• Para saber mais sobre como criar consultas para o BigQuery, consulte Como executar jobs de consulta interativos e em lote.

• Saiba como controlar o acesso a conjuntos de dados do BigQuery.