Consulta datos en BigQuery desde JupyterLab

En esta página, se muestra cómo consultar los datos almacenados en BigQuery desde la interfaz de JupyterLab o desde tu instancia de Vertex AI Workbench.

Métodos para consultar datos de BigQuery en archivos de notebook (IPYNB)

Si deseas consultar datos de BigQuery desde un archivo del notebook de JupyterLab, puedes usar el comando mágico %%bigquery y la biblioteca cliente de BigQuery para Python.

Las instancias de Vertex AI Workbench también incluyen una integración de BigQuery que te permite explorar y consultar datos desde la interfaz de JupyterLab.

En esta página, se describe cómo usar cada uno de estos métodos.

Antes de comenzar

Si aún no lo hiciste, crea una instancia de Vertex AI Workbench.

Roles obligatorios

Para garantizar que la cuenta de servicio de tu instancia tenga los permisos necesarios para consultar datos en BigQuery, pídele a tu administrador que le otorgue a la cuenta de servicio de tu instancia el rol de IAM del consumidor de Service Usage (roles/serviceusage.serviceUsageConsumer) en el proyecto. Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

Es posible que tu administrador también pueda otorgar a la cuenta de servicio de tu instancia los permisos necesarios mediante roles personalizados o con otros roles predefinidos.

Abre JupyterLab

  1. En la consola de Google Cloud, ve a la página Instancias.

    Ir a Instancias

  2. Junto al nombre de la instancia de Vertex AI Workbench, haz clic en Abrir JupyterLab.

    Tu instancia de Vertex AI Workbench abre JupyterLab.

Explora recursos de BigQuery

La integración de BigQuery proporciona un panel para navegar por los recursos de BigQuery a los que tienes acceso.

  1. En el menú de navegación de JupyterLab, haz clic en BigQuery BigQuery in Notebooks.

    En el panel BigQuery, se enumeran los proyectos y conjuntos de datos disponibles, en los que puedes realizar tareas de la siguiente manera:

    • Para ver la descripción de un conjunto de datos, haz doble clic en el nombre del conjunto de datos.
    • Para mostrar las tablas, vistas y modelos de un conjunto de datos, expande el conjunto de datos.
    • Para abrir una descripción resumida como una pestaña en JupyterLab, haz doble clic en una tabla, una vista o un modelo.

    Nota: En la descripción del resumen de una tabla, haz clic en la pestaña Vista previa para obtener una vista previa de sus datos. En la siguiente imagen, se muestra una vista previa de la tabla international_top_terms que se encuentra en el conjunto de datos google_trends en el proyecto bigquery-public-data:

    Lista internacional de términos principales.

Consulta datos con el comando mágico %%bigquery

En esta sección, escribirás SQL directamente en las celdas de notebook y leerás datos de BigQuery en el notebook de Python.

Los comandos automáticos que usan un carácter de porcentaje único o doble (% o %%) te permiten usar una sintaxis mínima para interactuar con BigQuery dentro del notebook. La biblioteca cliente de BigQuery para Python se instala automáticamente en una instancia de Vertex AI Workbench. En segundo plano, el comando mágico %%bigquery usa la biblioteca cliente de BigQuery para Python a fin de ejecutar la consulta determinada, convertir los resultados en un DataFrame de Pandas, guardar los resultados en una variable y, de forma opcional, guardarlos. y, luego, mostrar los resultados.

Nota: A partir de la versión 1.26.0 del paquete google-cloud-bigquery de Python, se usa la API de BigQuery Storage de forma predeterminada para descargar los resultados de los comandos mágicos %%bigquery.

  1. Para abrir un archivo de notebook, selecciona Archivo > Nuevo > Notebook.

  2. En el cuadro de diálogo Seleccionar Kernel, selecciona Python 3 y haz clic en Seleccionar.

    Se abrirá tu archivo IPYNB nuevo.

  3. Para obtener la cantidad de regiones por país en el conjunto de datos international_top_terms, ingresa la siguiente sentencia:

    %%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. Haz clic en  Ejecutar celda.

    El resultado es similar a este:

    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. En la celda a continuación (que se encuentra debajo del resultado de la celda anterior), ingresa el siguiente comando para ejecutar la misma consulta, pero esta vez guarda los resultados en un DataFrame de Pandas nuevo llamado regions_by_country. Debes proporcionar ese nombre mediante un argumento con el comando automático %%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;
    

    Nota: Si deseas obtener más información sobre los argumentos disponibles para el comando %%bigquery, consulta la documentación de comandos mágicos de la biblioteca cliente.

  6. Haz clic en  Ejecutar celda.

  7. En la siguiente celda, ingresa el siguiente comando para ver las primeras filas de los resultados de la consulta que acabas de leer:

    regions_by_country.head()
    
  8. Haz clic en  Ejecutar celda.

    El DataFrame de Pandas regions_by_country está listo para trazar.

Consulta datos mediante la biblioteca cliente de BigQuery directamente

En esta sección, usarás la biblioteca cliente de BigQuery para Python directamente a fin de leer datos en el notebook de Python.

La biblioteca cliente te brinda más control sobre tus consultas y te permite usar opciones de configuración más complejas para consultas y trabajos. Las integraciones de la biblioteca con Pandas te permiten combinar el rendimiento de SQL declarativo con el código imperativo (Python) para ayudarte a analizar, visualizar y transformar tus datos.

Nota: Puedes usar varias bibliotecas de análisis, transformación y visualización de datos de Python, como numpy, pandas, matplotlib y muchas opciones más. Varias de estas bibliotecas se basan en un objeto DataFrame.

  1. En la celda siguiente, ingresa el siguiente código de Python a fin de importar la biblioteca cliente de BigQuery para Python e inicializar un cliente:

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

    El cliente de BigQuery se usa para enviar y recibir mensajes de la API de BigQuery.

  2. Haz clic en  Ejecutar celda.

  3. En la siguiente celda, ingresa el siguiente código para recuperar el porcentaje de términos principales diarios en los top_terms de EE.UU. que se superponen en el tiempo según la cantidad de días de diferencia. La idea es ver los términos principales de cada día y ver qué porcentaje de ellos se superpone con los términos principales del día anterior, 2 días antes, 3 días antes, etcétera (para todos pares de fechas durante aproximadamente un mes).

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

    El SQL que se usa se encapsula en una cadena de Python y, luego, se pasa al método query() para ejecutar una consulta. El método to_dataframe espera a que finalice la consulta y descarga los resultados en un DataFrame de Pandas a través de la API de BigQuery Storage.

  4. Haz clic en  Ejecutar celda.

    Las primeras filas de los resultados de la consulta aparecen debajo de la celda del 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 obtener más información sobre el uso de las bibliotecas cliente de BigQuery, consulta la guía de inicio rápido Usa bibliotecas cliente.

Consulta datos a través de la integración de BigQuery en Vertex AI Workbench

La integración de BigQuery proporciona dos métodos adicionales para consultar datos. Estos métodos son diferentes del uso del comando mágico %%bigquery.

  • El Editor de consultas en la celda es un tipo de celda que puedes usar dentro de los archivos del notebook.

  • El Editor de consultas independiente se abre como una pestaña separada en JupyterLab.

En celda

Para usar el editor de consultas en celda y consultar datos en una tabla de BigQuery, completa los siguientes pasos:

  1. En JupyterLab, abre un archivo de notebook (IPYNB) o crea uno nuevo.

  2. Para crear un editor de consultas en celda, haz clic en la celda y, luego, a la derecha de ella, haz clic en el botón  Integración de BigQuery. O bien, en una celda de Markdown, ingresa #@BigQuery.

    La integración en BigQuery convierte la celda en un editor de consultas en celda.

  3. En una línea nueva debajo de #@BigQuery, escribe tu consulta mediante las declaraciones compatibles y los dialectos de SQL de BigQuery. Si se detectan errores en tu consulta, aparecerá un mensaje de error en la esquina superior derecha del editor de consultas. Si la consulta es válida, aparece la cantidad estimada de bytes que se procesarán.

  4. Haz clic en Enviar consulta. Aparecerán los resultados de tu consulta. De forma predeterminada, los resultados de la consulta se paginan en 100 filas por página y se limitan a 1,000 filas en total, pero puedes cambiar esta configuración en la parte inferior de la tabla de resultados. En el editor de consultas, mantén la consulta limitada solo con los datos que necesitas para verificar tu consulta. Volverás a ejecutar esta consulta en una celda de notebook, en la que puedes ajustar el límite para recuperar el conjunto completo de resultados si lo deseas.

  5. Puedes hacer clic en Consulta y carga como DataFrame para agregar de manera automática una celda nueva que contenga un segmento de código que importa la biblioteca cliente de BigQuery para Python, ejecute tu consulte en una celda de notebook y almacene los resultados en un DataFrame de Pandas llamado df.

Independiente

Para usar el editor de consultas independiente a fin de consultar datos en una tabla de BigQuery, completa los siguientes pasos:

  1. En JupyterLab, en el panel BigQuery en notebooks, haz clic con el botón derecho en una tabla, selecciona Consultar tabla o haz doble clic en una tabla para abrir una descripción en una pestaña separada y, luego, haz clic en el vínculo Tabla de consultas.

  2. Escribe tu consulta con las declaraciones compatibles y dialectos SQL de BigQuery. Si se detectan errores en tu consulta, aparecerá un mensaje de error en la esquina superior derecha del editor de consultas. Si la consulta es válida, aparece la cantidad estimada de bytes que se procesarán.

  3. Haz clic en Enviar consulta. Aparecerán los resultados de tu consulta. De forma predeterminada, los resultados de la consulta se paginan en 100 filas por página y se limitan a 1,000 filas en total, pero puedes cambiar esta configuración en la parte inferior de la tabla de resultados. En el editor de consultas, mantén la consulta limitada solo con los datos que necesitas para verificar tu consulta. Volverás a ejecutar esta consulta en una celda de notebook, en la que puedes ajustar el límite para recuperar el conjunto completo de resultados si lo deseas.

  4. Puedes hacer clic en Copiar código para DataFrame a fin de copiar un segmento de código que importa la biblioteca cliente de BigQuery para Python, ejecuta tu consulta en una celda de notebook y almacena los resultados en un DataFrame de Pandas llamado df. Pega este código en una celda del notebook en la que desees ejecutarlo.

Visualiza el historial de consultas y reutiliza las consultas

Para ver el historial de consultas como una pestaña en JupyterLab, realiza los siguientes pasos:

  1. En el menú de navegación de JupyterLab, haz clic en BigQuery BigQuery in Notebooks (BigQuery en notebooks) para abrir el panel BigQuery.

  2. En el panel BigQuery, desplázate hacia abajo y haz clic en BigQuery.

    Historial de consultas destacado en la parte inferior del panel de navegación izquierdo

    Se abre una lista de tus consultas en una pestaña nueva en la que puedes realizar tareas como las siguientes:

    • Para ver los detalles de una consulta, como su ID de trabajo, cuándo se ejecutó la consulta y cuánto tiempo tardó, haz clic en la consulta.
    • Si quieres revisar la consulta, volver a ejecutarla o copiarla en el notebook para usarla más adelante, haz clic en Abrir consulta en el editor.

¿Qué sigue?