Carga y consulta datos con la herramienta de línea de comandos de bq para BigQuery

1. Introducción

BigQuery es la base de datos analítica de bajo costo, no-ops y completamente administrada de Google. Con BigQuery, puedes consultar terabytes de datos sin un administrador de base de datos ni una infraestructura. BigQuery utiliza el conocido SQL y un modelo de cobro de pago por lo que usas. BigQuery te permite enfocarte en el análisis de datos para buscar estadísticas valiosas. En este codelab, usarás la herramienta de línea de comandos de bq para cargar un archivo CSV local en una tabla nueva de BigQuery.

Qué aprenderás

• Cómo usar la herramienta de línea de comandos de bq para BigQuery
• Cómo cargar archivos de datos locales en una tabla de BigQuery

Requisitos

• Un proyecto de Google Cloud
• Un navegador, como Google Chrome

2. Prepárate

Habilitar BigQuery

Si aún no tienes una Cuenta de Google, debes crear una.

1. Accede a la consola de Google Cloud y navega a BigQuery. También puedes abrir la IU web de BigQuery directamente si ingresas la siguiente URL en tu navegador.

https://console.cloud.google.com/bigquery

2. Acepta las Condiciones del Servicio.
3. Antes de poder usar BigQuery, debes crear un proyecto. Sigue las indicaciones para crear un proyecto nuevo.

Elige un nombre de proyecto y toma nota del ID del proyecto.

El ID del proyecto es un nombre único en todos los proyectos de Google Cloud. Se mencionará más adelante en este codelab como PROJECT_ID.

En este codelab, se usan recursos de BigQuery dentro de los límites de la zona de pruebas de BigQuery. No se requiere una cuenta de facturación. Si más adelante deseas quitar los límites de la zona de pruebas, puedes registrarte para la prueba gratuita de Google Cloud para agregar una cuenta de facturación.

Cloud Shell

Usarás Cloud Shell, un entorno de línea de comandos que se ejecuta en Google Cloud.

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. Crea un conjunto de datos

Crea un conjunto de datos que contenga tus tablas.

¿Qué es un conjunto de datos?

Un conjunto de datos de BigQuery es una colección de tablas. Todas las tablas en un conjunto de datos se almacenan en la misma ubicación de datos. También puedes adjuntar controles de acceso personalizados para limitar el acceso a un conjunto de datos y sus tablas.

Crea un conjunto de datos

En Cloud Shell, usa el comando bq mk para crear un conjunto de datos llamado “bq_load_codelab”.

bq mk bq_load_codelab

Ver las propiedades del conjunto de datos

Verifica que creaste el conjunto de datos; para ello, visualiza las propiedades del conjunto de datos con el comando bq show.

bq show bq_load_codelab

Debería ver un resultado similar al siguiente:

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      [e-mail]
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. Crea el archivo de datos

BigQuery puede cargar datos desde varios formatos, incluidos JSON delimitado por saltos de línea, Avro y CSV. Para simplificar, usarás CSV.

Cómo crear un archivo CSV

En Cloud Shell, crea un archivo CSV vacío.

touch customer_transactions.csv

Para abrir el archivo CSV en el editor de código de Cloud Shell, ejecuta el comando de edición de cloudshell, que abrirá una nueva ventana del navegador con un editor de código y el panel de Cloud Shell.

cloudshell edit customer_transactions.csv

Nota: El editor de código requiere cookies de terceros para funcionar porque se carga desde un dominio diferente al de la consola de Google Cloud.

En el editor de código, ingresa algunos valores separados por comas para cargarlos en BigQuery.

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

Guarda el archivo CSV haciendo clic en Archivo > Editar

5. Cargar datos

Usa el comando bq load para cargar el archivo CSV en una tabla de BigQuery.

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

Usaste las siguientes opciones:

• --source_format=CSV usa el formato de datos CSV cuando analiza el archivo de datos.
• --skip_leading_rows=1 omite la primera línea en el archivo CSV porque es una fila de encabezado.
• Bq_load_codelab.customer_transactions—the first positional argument— define en qué tabla se deben cargar los datos.
• ./customer_transactions.csv, el segundo argumento posicional, define qué archivo se carga. Además de los archivos locales, el comando bq load puede cargar archivos desde Cloud Storage con gs://my_bucket/path/to/file URIs.
• Un esquema, que se puede definir en un archivo de esquema JSON o como una lista separada por comas. (Utilizaste una lista separada por comas para que sea más simple).

Utilizaste el siguiente esquema en la tabla customer_transactions:

• Id:string: Un identificador de cliente
• Zip:string: Es un código postal de EE.UU.
• Ttime:timestamp: Es la fecha y hora en que se realizó la transacción.
• Amount:numeric: El importe de una transacción (una columna numérica almacena datos en formato decimal, lo que es útil para valores monetarios)
• Fdbk:float: La calificación de una encuesta de comentarios sobre la transacción
• Sku:string: Es un identificador para el artículo comprado.

Obtén los detalles de la tabla

Muestra sus propiedades para verificar que la tabla se cargó.

bq show bq_load_codelab.customer_transactions

Resultado:

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. Consulta los datos

Ahora que tus datos están cargados, puedes consultarlos con la IU web de BigQuery, el comando de bq o la API. Tus consultas pueden unir tus datos con cualquier conjunto de datos (o conjunto de datos, siempre que se encuentren en la misma ubicación) para el que tengas permiso para leer.

Ejecuta una consulta en SQL estándar que una tu conjunto de datos con el conjunto de datos de códigos postales de EE.UU. y resuma las transacciones por estado de EE.UU. Usa el comando bq query para ejecutar la consulta.

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

Ese comando debería mostrar un resultado similar al siguiente:

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

La consulta que ejecutaste usó un conjunto de datos público y tu conjunto de datos privado. Para obtener más información, lee esta versión comentada de la misma consulta:

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. Limpia

Borra el conjunto de datos que creaste con el comando bq rm. Usa la marca -r para quitar cualquier tabla que contenga.

bq rm -r bq_load_codelab

8. ¡Felicitaciones!

Subiste una tabla a BigQuery y la consultaste.

Temas abordados

• Usa la herramienta de línea de comandos de bq para interactuar con BigQuery.
• Unir tus datos y un conjunto de datos públicos con una consulta en BigQuery.

¿Qué sigue?

Conoce más sobre:

• La herramienta de línea de comandos de bq
• Otras formas de cargar datos en BigQuery.
• Otros conjuntos de datos públicos disponibles a través de BigQuery.
• Clima, delitos y otros tipos de datos en TIL con BigQuery.