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 de estadísticas 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 infraestructura. BigQuery usa el lenguaje SQL conocido y un modelo de cobro en el que solo pagas 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 bq para cargar un archivo CSV local en una tabla de BigQuery nueva.

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

Habilita BigQuery

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

1. Accede a Google Cloud Console 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. Para poder usar BigQuery, debes crear un proyecto. Sigue las indicaciones para crear un proyecto nuevo.

Elige un nombre para el 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 del entorno de pruebas, puedes agregar una cuenta de facturación registrándote en la prueba gratuita de Google Cloud.

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 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. 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 de 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

Cómo ver las propiedades del conjunto de datos

Verifica que hayas creado el conjunto de datos viendo sus propiedades 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 de datos, incluidos JSON delimitado por saltos de línea, Avro y CSV. Para simplificar, usarás CSV.

Crea un archivo CSV

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

touch customer_transactions.csv

Abre el archivo CSV en el editor de código de Cloud Shell ejecutando el comando cloudshell edit, que abrirá una nueva ventana del navegador con un editor de código y un panel de Cloud Shell.

cloudshell edit customer_transactions.csv

Nota: El editor de código requiere cookies de terceros para funcionar, ya que 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

Haz clic en Archivo > Editar para guardar el archivo CSV.

5. Cargar datos

Usa el comando bq load para cargar tu 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 del 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 cargará. 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. (Usaste una lista separada por comas para simplificar).

Usaste el siguiente esquema en la tabla customer_transactions:

• Id:string: Es un identificador del cliente.
• Zip:string: Código postal de EE.UU.
• Ttime:timestamp: Fecha y hora en que se realizó la transacción
• Amount:numeric: Es el importe de una transacción (una columna numérica almacena datos en formato decimal, lo que resulta útil para los valores monetarios).
• Fdbk:float: Es la calificación de una encuesta de comentarios sobre la transacción.
• Sku:string: Es un identificador del elemento comprado.

Obtén los detalles de la tabla

Verifica que la tabla se haya cargado mostrando sus propiedades.

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 se cargaron tus datos, puedes consultarlos con la IU web de BigQuery, el comando bq o la API. Tus consultas pueden unir tus datos con cualquier conjunto de datos (o conjuntos de datos, siempre que estén en la misma ubicación) que tengas permiso para leer.

Ejecuta una consulta de SQL estándar que una tu conjunto de datos con el conjunto de datos de códigos postales de EE.UU. y sume 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úblicos y tu conjunto de datos privados. Obtén más información leyendo esta versión comentada de la misma búsqueda:

#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 las tablas 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 bq para interactuar con BigQuery.
• Unir tus datos y un conjunto de datos públicos con una consulta de 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
• Datos meteorológicos, delictivos y de otros tipos en TIL con BigQuery