Cómo usar BigQuery con Python

1. Descripción general

BigQuery es el almacén de datos de estadísticas de bajo costo, a escala de petabytes y completamente administrado de Google. BigQuery es NoOps, no se debe administrar ninguna infraestructura ni se necesita un administrador de base de datos, por lo que puedes enfocarte en el análisis de datos para encontrar estadísticas valiosas, usar el lenguaje SQL que conoces y aprovechar nuestro modelo de pago por uso.

En este codelab, usarás las bibliotecas cliente de Google Cloud para Python con el fin de 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 iniciaste Cloud Shell, aparecerá una pantalla intermedia (mitad inferior de la página) que describe en qué consiste. Si ese es el caso, haz clic en Continuar (y no volverás a verlo). 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 esta función es compatible con la función de autocompletado con tabulador. Para obtener más información, consulta la descripción general de la herramienta de línea de comandos de gcloud.

3. Ejecuta el siguiente comando en Cloud Shell para confirmar que el comando de 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 debe estar habilitada de forma predeterminada en todos los proyectos de Google Cloud. Puedes verificar si esto es así 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

...

Si 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 comprueba 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, configura 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 lo siguiente:

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 nueva cuenta de servicio. 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 utiliza la biblioteca cliente de BigQuery para Python, que se aborda en el siguiente paso, para encontrar tus credenciales. La variable de entorno se debe establecer en la ruta de acceso completa del archivo JSON de credenciales que creaste, con el siguiente código:

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

Puedes leer más sobre cómo 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 los documentos 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 del 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 del 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 Python de BigQuery:

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

Ya tienes todo listo para programar con la API de BigQuery.

7. Consultar las obras de Shakespeare

Un conjunto de datos públicos es un conjunto de datos que se almacena en BigQuery y está disponible para el público en general. Hay muchos otros conjuntos de datos públicos disponibles para consultar. Si bien algunos conjuntos de datos se alojan en Google, la mayoría están 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 están contenidas en el 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. Muestra la cantidad de veces que cada palabra aparece 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, crea en Cloud Shell una aplicación de Python sencilla 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 desde 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.

En Cloud Shell, ejecuta la app:

python3 app.py

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

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.

• Comprueba que la variable de entorno de credenciales esté definida: echo $GOOGLE_APPLICATION_CREDENTIALS
• Deberías ver la ruta 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, revisa el paso Autentica las solicitudes a la API.

8. Consultar el conjunto de datos de GitHub

Para familiarizarte más con BigQuery, ahora realizará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:

Abrir 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 en busca de los mensajes de confirmación más comunes.

En Cloud Shell, ejecuta la app:

python3 app.py

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

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 las opciones de consulta. BigQuery también realiza un seguimiento de las estadísticas sobre 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 consultas.

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:,}')

Algunos aspectos que debes tener en cuenta sobre el código. Primero, para inhabilitar el almacenamiento en caché, se introduce QueryJobConfig y se establece use_query_cache como falso. Segundo, accediste a las estadísticas sobre la consulta desde el objeto de trabajo.

En Cloud Shell, ejecuta la app:

python3 app.py

Como antes, deberías ver una lista de mensajes de confirmación y sus casos. Además, también deberías ver algunas estadísticas sobre la consulta al final:

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, incluidos 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 a una tabla de BigQuery un archivo JSON almacenado en Cloud Storage. El archivo JSON se encuentra en gs://cloud-samples-data/bigquery/us-states/us-states.json.

Si te interesa el contenido del archivo JSON, puedes usar la herramienta de línea de comandos de 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 lo 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 unos minutos a estudiar cómo el código carga el archivo JSON y crea una tabla con un esquema en un conjunto de datos.

En Cloud Shell, ejecuta la app:

python3 app.py

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

Para verificar que se haya creado el conjunto de datos, dirígete a la consola de BigQuery. Deberías ver un conjunto de datos y una tabla nuevos. 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.