Acessar e visualizar dados no BigQuery de dentro do JupyterLab

Nesta página, mostramos alguns exemplos de como explorar e visualizar dados armazenados no BigQuery na interface do JupyterLab da sua instância de notebooks gerenciados do Vertex AI Workbench.

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.

ler dados do BigQuery;

Nas próximas duas seções, você vai ler os dados do BigQuery que vai usar para visualizar mais tarde. Essas etapas são idênticas àquelas em Consultar dados no BigQuery a partir do JupyterLab. Portanto, se você já as concluiu, pule para Ver um resumo dos dados em uma tabela do BigQuery.

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>

  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

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.

Receber um resumo dos dados em uma tabela do BigQuery

Nesta seção, você usará um atalho do notebook para ver estatísticas e visualizações resumidas de todos os campos de uma tabela do BigQuery. Essa pode ser uma maneira rápida de criar o perfil dos dados antes de explorar mais.

A biblioteca de cliente do BigQuery fornece um comando mágico, %bigquery_stats, que pode ser chamado com um nome de tabela específico para fornecer uma visão geral da tabela e estatísticas detalhadas em cada uma das colunas da tabela.

  1. Na próxima célula, insira o código a seguir para executar essa análise na tabela top_terms dos EUA:

    %bigquery_stats bigquery-public-data.google_trends.top_terms

  2. Clique em  Executar célula.

    Depois de ser executada por algum tempo, uma imagem aparece com várias estatísticas em cada uma das sete variáveis na tabela top_terms. A imagem a seguir mostra parte de alguns exemplos de saída:

    Visão geral dos principais termos internacionais de estatística.

Visualizar dados do BigQuery

Nesta seção, você usará os recursos de plotagem para visualizar os resultados das consultas executadas anteriormente no notebook do Jupyter.

  1. Na próxima célula, insira o código a seguir para usar o método DataFrame.plot() de pandas e criar um gráfico de barras que visualize os resultados da consulta que retorna o número de regiões por país:

    regions_by_country.plot(kind="bar", x="country_name", y="num_regions", figsize=(15, 10))

  2. Clique em  Executar célula.

    A gráfico é semelhante a:

    Resultados internacionais dos principais termos no país

  3. Na próxima célula, insira o código a seguir para usar o método DataFrame.plot() do pandas e criar um gráfico de dispersão que visualize os resultados da consulta para a porcentagem de sobreposição nos principais termos de pesquisa por dias de intervalo:

    pct_overlap_terms_by_days_apart.plot(
  kind="scatter",
  x="days_apart",
  y="pct_overlap_terms",
  s=len(pct_overlap_terms_by_days_apart["num_date_pairs"]) * 20,
  figsize=(15, 10)
  )

  4. Clique em  Executar célula.

    O gráfico é semelhante a: O tamanho de cada ponto reflete o número de pares de datas com quantos dias de intervalo entre os dados. Por exemplo, há mais pares com um dia de intervalo do que 30 dias de intervalo porque os principais termos de pesquisa aparecem diariamente em aproximadamente um mês.

    Gráfico de dias com os principais termos internacionais.

Para mais informações sobre a visualização de dados, consulte a documentação do pandas.

A seguir