Cómo usar BigQuery con Python

1. Descripción general

BigQuery es el almacén de datos de estadísticas de bajo costo, completamente administrado y con escala de petabytes de Google. BigQuery es NoOps: no se debe administrar ninguna infraestructura y no necesitas un administrador de base de datos, de manera que puedes enfocarte en el análisis de datos para buscar estadísticas valiosas, usar el lenguaje SQL que conoces y sacar provecho de nuestro modelo prepago.

En este codelab, usarás las bibliotecas cliente de Google Cloud para Python para consultar conjuntos de datos públicos de BigQuery con Python.

Qué aprenderás

• Cómo usar Cloud Shell
• Cómo habilitar la API de BigQuery
• Cómo autenticar solicitudes a la API
• Cómo instalar la biblioteca cliente de Python
• Cómo consultar las obras de Shakespeare
• Cómo consultar el conjunto de datos de GitHub
• Cómo ajustar el almacenamiento en caché y las estadísticas de visualización

Requisitos

• Un proyecto de Google Cloud
• Un navegador, como Chrome o Firefox
• Conocimientos de Python

Encuesta

¿Cómo usarás este instructivo?

Leer Leer y completar los ejercicios

¿Cómo calificarías tu experiencia en Python?

Principiante Intermedio Avanzado

¿Cómo calificarías tu experiencia en el uso de los servicios de Google Cloud?

Principiante Intermedio Avanzado

2. Configuración y requisitos

Configuración del entorno de autoaprendizaje

1. Accede a Google Cloud Console y crea un proyecto nuevo o reutiliza uno existente. Si aún no tienes una cuenta de Gmail o de Google Workspace, debes crear una.

• El Nombre del proyecto es el nombre visible de los participantes de este proyecto. Es una string de caracteres que no se utiliza en las API de Google y se puede actualizar en cualquier momento.
• El ID del proyecto debe ser único en todos los proyectos de Google Cloud y es inmutable (no se puede cambiar después de configurarlo). Cloud Console genera automáticamente una string única, que, por lo general, no importa cuál sea. En la mayoría de los codelabs, debes hacer referencia al ID del proyecto (suele ser PROJECT_ID). Por lo tanto, si no te gusta, genera otro aleatorio o prueba con uno propio y comprueba si está disponible. Después de crear el proyecto, este ID se “congela” y no se puede cambiar.
• Además, hay un tercer valor, el Número de proyecto, que usan algunas API. Obtén más información sobre estos tres valores en la documentación.

Precaución: El ID de un proyecto debe ser único a nivel global y ninguna otra persona puede usarlo después de seleccionarlo. Eres el único usuario de ese ID, por lo que, si se borra un proyecto, ese ID no se puede volver a utilizar.

Nota: Si usas una cuenta de Gmail, puedes configurar la ubicación predeterminada como Sin organización. Si usas una cuenta de Google Workspace, elige una ubicación adecuada para tu organización.

2. A continuación, deberás habilitar la facturación en Cloud Console para usar las API o los recursos de Cloud. Ejecutar este codelab no debería costar mucho, tal vez nada. Si quieres cerrar los recursos para no se te facture más allá de este instructivo, sigue las instrucciones de “limpieza” que se encuentran al final del codelab. Los usuarios nuevos de Google Cloud son aptos para participar en el programa Prueba gratuita de USD 300.

Inicia Cloud Shell

Si bien Google Cloud y Spanner se pueden operar de manera remota desde tu laptop, en este codelab usarás Google Cloud Shell, un entorno de línea de comandos que se ejecuta en la nube.

Activar Cloud Shell

1. En la consola de Cloud, haz clic en Activar Cloud Shell.

Si nunca has iniciado Cloud Shell, aparecerá una pantalla intermedia (mitad inferior de la página) en la que se describirá qué es. Si ese es el caso, haz clic en Continuar (y no volverás a verla). Así es como se ve la pantalla única:

El aprovisionamiento y la conexión a Cloud Shell solo tomará unos minutos.

Esta máquina virtual está cargada con todas las herramientas de desarrollo que necesitas. Ofrece un directorio principal persistente de 5 GB y se ejecuta en Google Cloud, lo que permite mejorar considerablemente el rendimiento de la red y la autenticación. Gran parte de tu trabajo en este codelab, si no todo, se puede hacer simplemente con un navegador o tu Chromebook.

Una vez conectado a Cloud Shell, debería ver que ya se autenticó y que el proyecto ya se configuró con tu ID del proyecto.

2. En Cloud Shell, ejecuta el siguiente comando para confirmar que está autenticado:

gcloud auth list

Resultado del comando

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Nota: gcloud es la herramienta de línea de comandos potente y unificada de Google Cloud. Ya está preinstalada en Cloud Shell. Notarás que admite el autocompletado con la tecla Tab. Para obtener más información, consulta la descripción general de la herramienta de línea de comandos de gcloud.

3. En Cloud Shell, ejecuta el siguiente comando para confirmar que el comando gcloud conoce tu proyecto:

gcloud config list project

Resultado del comando

[core]
project = <PROJECT_ID>

De lo contrario, puedes configurarlo con el siguiente comando:

gcloud config set project <PROJECT_ID>

Resultado del comando

Updated property [core/project].

3. Habilita la API

La API de BigQuery debería estar habilitada de forma predeterminada en todos los proyectos de Google Cloud. Puedes verificar si esto es cierto con el siguiente comando en Cloud Shell: Deberías aparecer en la lista de BigQuery:

gcloud services list

Deberías ver BigQuery en la lista:

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

En caso de que la API de BigQuery no esté habilitada, puedes usar el siguiente comando en Cloud Shell para habilitarla:

gcloud services enable bigquery.googleapis.com

Nota: En caso de error, vuelve al paso anterior y verifica la configuración.

4. Autentica solicitudes a la API

Para realizar solicitudes a la API de BigQuery, debes usar una cuenta de servicio. Una cuenta de servicio pertenece a tu proyecto y la biblioteca cliente de Python de Google Cloud la usa para realizar solicitudes a la API de BigQuery. Al igual que cualquier otra cuenta de usuario, una cuenta de servicio está representada por una dirección de correo electrónico. En esta sección, usarás el SDK de Cloud para crear una cuenta de servicio y, luego, crear las credenciales que necesitarás para autenticarte como la cuenta de servicio.

Primero, establece una variable de entorno PROJECT_ID:

export PROJECT_ID=$(gcloud config get-value core/project)

A continuación, crea una cuenta de servicio nueva para acceder a la API de BigQuery con el siguiente comando:

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

A continuación, crea credenciales que tu código de Python usará para acceder como tu cuenta de servicio nueva. Crea estas credenciales y guárdalas como un archivo JSON ~/key.json con el siguiente comando:

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

Por último, configura la variable de entorno GOOGLE_APPLICATION_CREDENTIALS, que la biblioteca cliente de Python de BigQuery, que se explica en el siguiente paso, usa para encontrar tus credenciales. La variable de entorno se debe establecer con la ruta de acceso completa del archivo JSON de credenciales que creaste, con el siguiente comando:

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

Puedes obtener más información para autenticar la API de BigQuery.

5. Configurar el control de acceso

BigQuery usa Identity and Access Management (IAM) para administrar el acceso a los recursos. BigQuery tiene varios roles predefinidos (usuario, propietario de datos, visualizador de datos, etc.) que puedes asignar a la cuenta de servicio que creaste en el paso anterior. Puedes leer más sobre el Control de acceso en la documentación de BigQuery.

Antes de consultar conjuntos de datos públicos, debes asegurarte de que la cuenta de servicio tenga, al menos, el rol roles/bigquery.user. En Cloud Shell, ejecuta el siguiente comando para asignar el rol de usuario a la cuenta de servicio:

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

Puedes ejecutar el siguiente comando para verificar que la cuenta de servicio tenga el rol de usuario:

gcloud projects get-iam-policy $PROJECT_ID

Deberías ver lo siguiente:

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. Instala la biblioteca cliente

Instala la biblioteca cliente de BigQuery para Python:

pip3 install --user --upgrade google-cloud-bigquery

Ahora puedes escribir código con la API de BigQuery.

7. Consultar las obras de Shakespeare

Un conjunto de datos públicos es cualquier conjunto de datos que se almacena en BigQuery y está disponible para el público en general. Existen muchos otros conjuntos de datos públicos disponibles que puedes consultar. Si bien Google aloja algunos conjuntos de datos, la mayoría son alojados por terceros. Para obtener más información, consulta la página Conjuntos de datos públicos.

Además de los conjuntos de datos públicos, BigQuery proporciona una cantidad limitada de tablas de muestra que puedes consultar. Estas tablas son parte del conjunto de datos bigquery-public-data:samples. La tabla shakespeare del conjunto de datos samples contiene un índice de palabras de las obras de Shakespeare. Indica la cantidad de veces que aparece cada palabra en cada corpus.

En este paso, consultarás la tabla shakespeare.

Nota: Puedes ver los detalles de la tabla shakespeare en la consola de BigQuery aquí.

Primero, en Cloud Shell, crea una aplicación de Python simple que usarás para ejecutar las muestras de la API de Translation.

mkdir bigquery-demo
cd bigquery-demo
touch app.py

Abre el editor de código en la parte superior derecha de Cloud Shell:

Navega al archivo app.py dentro de la carpeta bigquery-demo y reemplaza el código por lo siguiente.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

Tómate uno o dos minutos para estudiar el código y ver cómo se consulta la tabla.

De regreso en Cloud Shell, ejecuta la app:

python3 app.py

Deberías ver una lista de palabras y sus ocurrencias:

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

Nota: Si recibes un error PermissionDenied (403), verifica los pasos que seguiste durante el paso Autentica solicitudes a la API.

• Verifica que la variable de entorno de las credenciales esté definida: echo $GOOGLE_APPLICATION_CREDENTIALS
• Deberías ver la ruta de acceso completa a tu archivo de credenciales: /home/$USER/key.json
• Luego, verifica que se hayan creado las credenciales: cat $GOOGLE_APPLICATION_CREDENTIALS
• Deberías ver algo similar a lo siguiente: {"type": "service_account","project_id": "PROJECT_ID",...}

Si algo no es correcto, vuelve al paso Cómo autenticar solicitudes a la API.

8. Consultar el conjunto de datos de GitHub

Para familiarizarte más con BigQuery, ahora ejecutarás una consulta en el conjunto de datos públicos de GitHub. Encontrarás los mensajes de confirmación más comunes en GitHub. También usarás la consola web de BigQuery para obtener una vista previa y ejecutar consultas ad hoc.

Para ver cómo se ven los datos, abre el conjunto de datos de GitHub en la IU web de BigQuery:

Abre la tabla github_repos

Haz clic en el botón Vista previa para ver cómo se ven los datos:

Navega al archivo app.py dentro de la carpeta bigquery_demo y reemplaza el código por lo siguiente.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

Tómate uno o dos minutos para estudiar el código y ver cómo se consulta la tabla para obtener los mensajes de confirmación más comunes.

De regreso en Cloud Shell, ejecuta la app:

python3 app.py

Deberías ver una lista de los mensajes de confirmación y sus ocurrencias:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

Nota: Este conjunto de datos se actualiza periódicamente, por lo que tus resultados pueden variar.

9. Almacenamiento en caché y estadísticas

BigQuery almacena en caché los resultados de las consultas. Como resultado, las consultas posteriores tardan menos tiempo. Es posible inhabilitar el almacenamiento en caché con opciones de consulta. BigQuery también hace un seguimiento de las estadísticas sobre las consultas, como la hora de creación, la hora de finalización y el total de bytes procesados.

En este paso, inhabilitarás el almacenamiento en caché y también mostrarás estadísticas sobre las búsquedas.

Navega al archivo app.py dentro de la carpeta bigquery_demo y reemplaza el código por lo siguiente.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

Hay algunas cosas que debes tener en cuenta sobre el código. Primero, se inhabilita el almacenamiento en caché con la introducción de QueryJobConfig y la configuración de use_query_cache en falso. En segundo lugar, accediste a las estadísticas sobre la consulta desde el objeto de trabajo.

De regreso en Cloud Shell, ejecuta la app:

python3 app.py

Al igual que antes, deberías ver una lista de los mensajes de confirmación y sus ocurrencias. Además, al final, también deberías ver algunas estadísticas sobre la búsqueda:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. Carga datos en BigQuery

Si deseas consultar tus propios datos, debes cargarlos en BigQuery. BigQuery admite la carga de datos desde muchas fuentes, como Cloud Storage, otros servicios de Google y otras fuentes legibles. Incluso puedes transmitir tus datos con inserciones de transmisión. Para obtener más información, consulta la página Carga datos en BigQuery.

En este paso, cargarás un archivo JSON almacenado en Cloud Storage en una tabla de BigQuery. El archivo JSON se encuentra en gs://cloud-samples-data/bigquery/us-states/us-states.json

Si tienes curiosidad sobre el contenido del archivo JSON, puedes usar la herramienta de línea de comandos gsutil para descargarlo en Cloud Shell:

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

Puedes ver que contiene la lista de estados de EE.UU., y cada estado es un documento JSON en una línea separada:

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

Para cargar este archivo JSON en BigQuery, navega al archivo app.py dentro de la carpeta bigquery_demo y reemplaza el código por el siguiente.

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

Dedica un minuto o dos a estudiar cómo el código carga el archivo JSON y crea una tabla con un esquema en un conjunto de datos.

De regreso en Cloud Shell, ejecuta la app:

python3 app.py

Se crean un conjunto de datos y una tabla en BigQuery.

Para verificar que se creó el conjunto de datos, ve a la consola de BigQuery. Deberías ver un nuevo conjunto de datos y una nueva tabla. Cambia a la pestaña de vista previa de la tabla para ver tus datos:

11. ¡Felicitaciones!

Aprendiste a usar BigQuery con Python.

Realiza una limpieza

Para evitar que se generen cargos en tu cuenta de Google Cloud por los recursos que usaste en este instructivo, sigue estos pasos:

• En la consola de Cloud, ve a la página Administrar recursos.
• En la lista de proyectos, selecciona el tuyo y haz clic en Borrar.
• En el diálogo, escribe el ID del proyecto y, luego, haz clic en Cerrar para borrarlo.

Más información

• Google BigQuery: https://cloud.google.com/bigquery/docs/
• Python en Google Cloud: https://cloud.google.com/python/
• Bibliotecas cliente de Cloud para Python: https://googleapis.github.io/google-cloud-python/

Licencia

Este trabajo cuenta con una licencia Atribución 2.0 Genérica de Creative Commons.