Descripción general

BigQuery es la base de datos analítica de bajo costo, no-ops y completamente administrada de Google. Con BigQuery, puedes consultar muchos terabytes de datos sin tener que administrar infraestructuras y sin necesitar un administrador de base de datos. BigQuery usa SQL y puede aprovechar el modelo de pago por uso. BigQuery te permite enfocarte en el análisis de datos para buscar estadísticas valiosas.

En este lab, nos enfocaremos en cómo diseñar un almacén de datos para mejorar el rendimiento de las consultas. Además, compararás un esquema relacional tradicional, al que se le aplicaron operaciones JOIN, con un esquema desnormalizado. También usarás el plan de ejecución de consultas de BigQuery para evaluar de forma cuantitativa las compensaciones de rendimiento.

Actividades

En este lab, aprenderás a realizar estas tareas:

• Cargar un archivo de valores separados por comas (CSV) en una tabla de BigQuery con la IU web
• Cargar un archivo de notación de objeto de JavaScript (JSON) en una tabla de BigQuery con la interfaz de línea de comandos (CLI)
• Transformar datos y unir (JOIN) tablas con la IU web
• Almacenar los resultados de la consulta en una tabla de destino
• Consultar una tabla de destino con la IU web para confirmar que los datos se transformaron y cargaron de forma correcta

Configuración y requisitos

En cada lab, recibirá un proyecto de Google Cloud y un conjunto de recursos nuevos por tiempo limitado y sin costo adicional.

1. Accede a Qwiklabs desde una ventana de incógnito.

2. Ten en cuenta el tiempo de acceso del lab (por ejemplo, 1:15:00) y asegúrate de finalizarlo en el plazo asignado.
   No existe una función de pausa. Si lo necesita, puede reiniciar el lab, pero deberá hacerlo desde el comienzo.

3. Cuando esté listo, haga clic en Comenzar lab.

4. Anote las credenciales del lab (el nombre de usuario y la contraseña). Las usarás para acceder a la consola de Google Cloud.

5. Haga clic en Abrir Google Console.

6. Haga clic en Usar otra cuenta, copie las credenciales para este lab y péguelas en el mensaje emergente que aparece.
   Si usa otras credenciales, se generarán errores o incurrirá en cargos.

7. Acepta las condiciones y omite la página de recursos de recuperación.

Activa Google Cloud Shell

Google Cloud Shell es una máquina virtual que cuenta con herramientas para desarrolladores. Ofrece un directorio principal persistente de 5 GB y se ejecuta en Google Cloud.

Google Cloud Shell proporciona acceso de línea de comandos a tus recursos de Google Cloud.

1. En la consola de Cloud, en la barra de herramientas superior derecha, haz clic en el botón Abrir Cloud Shell.

   

2. Haz clic en Continuar.

El aprovisionamiento y la conexión al entorno demorarán unos minutos. Cuando te conectes, habrás completado la autenticación, y el proyecto estará configurado con tu PROJECT_ID. Por ejemplo:

gcloud es la herramienta de línea de comandos de Google Cloud. Viene preinstalada en Cloud Shell y es compatible con el completado de línea de comando.

• Puedes solicitar el nombre de la cuenta activa con este comando:

Resultado:

Resultado de ejemplo:

• Puedes solicitar el ID del proyecto con este comando:

Resultado:

Resultado de ejemplo:

Abra BigQuery en Console

1. En Google Cloud Console, seleccione el menú de navegación > BigQuery.

Se abrirá el cuadro de mensaje Te damos la bienvenida a BigQuery en Cloud Console, que contiene un vínculo a la guía de inicio rápido y enumera las actualizaciones de la IU.

2. Haga clic en Listo.

Tarea 1. Crea un nuevo conjunto de datos para almacenar tus tablas

En tu proyecto de BigQuery, crea un nuevo conjunto de datos titulado liquor_sales.

1. En el panel Explorador, haz clic en el ícono Ver acciones que se encuentra junto al ID del proyecto y elige Crear un conjunto de datos.

Se abrirá el diálogo Crear un conjunto de datos.

2. Define el ID del conjunto de datos en liquor_sales. Deja las otras opciones con sus valores predeterminados y haz clic en Crear un conjunto de datos.

En el panel izquierdo, verás una tabla liquor_sales en tu proyecto.

Tarea 2. Carga y consulta datos relacionales

En esta sección, medirás el rendimiento de las consultas de datos relacionales en BigQuery.

BigQuery admite consultas JOIN de gran tamaño y el rendimiento de los JOIN es bueno. Sin embargo, BigQuery es un almacén de datos en columnas. El máximo rendimiento se consigue en conjuntos de datos desnormalizados. Dado que el almacenamiento de BigQuery es de bajo costo y escalable, es una buena práctica desnormalizar y unir previamente los conjuntos de datos en tablas homogéneas. En otras palabras, se intercambian recursos informáticos por recursos de almacenamiento (estos últimos son más eficaces y rentables).

En esta sección, harás lo siguiente:

• Cargar un conjunto de tablas de un esquema relacional (en 3ª forma normal)
• Ejecutar consultas en las tablas relacionales
• Observar el rendimiento de las consultas para compararlo con el rendimiento de las mismas consultas en una tabla de un esquema desnormalizado que contiene la misma información

Se cargan tablas que tienen un esquema relacional. El esquema relacional consta de las siguientes tablas:

Este es un diagrama del esquema relacional.

Crea la tabla de ventas

1. En la sección Explorador, haz clic en el ícono Ver acciones, situado junto al conjunto de datos liquor_sales, elige Abrir, y, a continuación, haz clic en Crear tabla.

2. En la página Crear tabla de la sección Origen, haz lo siguiente:

• En Crear tabla desde, elige Google Cloud Storage.
• Escribe la ruta de acceso al nombre del bucket de Google Cloud Storage:

• En Formato de archivo, elige CSV.

3. En la sección Destino, configura lo siguiente:

• En Nombre de la tabla, escribe sales.
• Deja el resto de los campos de destino en sus valores predeterminados.

4. En la sección Esquema, configura lo siguiente:

• Haz clic en Editar como texto.
• Copia y pega el siguiente esquema:

5. Haz clic en Opciones avanzadas para visualizar y configurar estas opciones:

• En Delimitador de campos, verifica que la opción Coma esté seleccionada.
• Dado que sales.csv contiene una única fila de encabezado, establece las Filas del encabezado que se omitirán, en 1.
• Marca Saltos de línea en secciones entrecomilladas.
• Acepta los valores predeterminados restantes y haz clic en Crear tabla.

BigQuery generará un trabajo de carga para crear la tabla y subir los datos a ella (esto puede tardar algunos segundos).

6. Haz clic en Historial personal para seguir el progreso del trabajo.

Crea las tablas restantes

Crea las tablas restantes en el esquema relacional con la línea de comandos de Cloud Shell.

1. Crea la tabla de category:

2. Crea la tabla convenience_store:

3. Crea la tabla county:

4. Crea la tabla item:

5. Crea la tabla store:

6. Crea la tabla vendor:

7. Ve a la IU web de BigQuery. Confirma que ves las nuevas tablas cargadas en tu conjunto de datos liquor_sales. Actualiza el navegador si es necesario.

Consulta datos relacionales

A continuación, usa el Editor de consultas para consultar los datos.

1. Bajo el cuadro de código Editor de consultas, haz clic en Más > Configuración de consulta.

2. En la sección Administración de recursos, Preferencia de caché, desmarca la casilla de verificación Usar resultados almacenados en caché y haz clic en Guardar. Si tienes que ejecutar la consulta más de una vez, no debes usar los resultados almacenados en caché.

3. En la ventana Editor de consultas, escribe la siguiente consulta en las tablas relacionales y haz clic en Ejecutar:

4. En la parte inferior, en la sección Resultados de la consulta, haz clic en la pestaña Resultados. Observa la hora de finalización de la consulta. A continuación, se muestra un ejemplo (el tiempo de ejecución puede variar).

Esto se comparará con la hora de consulta de un conjunto de datos acoplado en secciones posteriores.

Tarea 3. Carga y consulta datos acoplados

En esta sección, se desnormalizan los esquemas y se analizan las ventas de bebidas alcohólicas del estado de Iowa con los datos acoplados. La ejecución de esta consulta en los datos acoplados debería ser más rápida que en los datos relacionales. Deberás anotar la hora para comparar y confirmarlo.

Un esquema desnormalizado acopla todos los datos relacionales en una única fila. Por ejemplo, en el esquema desnormalizado, county_number, county, store, name, address, city, zipcode, store_location, county_number y cstore son campos que contienen todos los campos de las tablas County, Store y Convenience_store.

En el siguiente diagrama, se muestra el esquema desnormalizado.

Crea la tabla iowa_sales_denorm

1. En el panel izquierdo, elige el conjunto de datos liquor_sales y haz clic en Crear tabla a la derecha.

Se abrirá el diálogo Crear tabla.

2. En la sección Origen, configura lo siguiente:

• En Crear tabla desde, elige Google Cloud Storage.
• Escribe la ruta de acceso al nombre del bucket de Google Cloud Storage:

• En Formato de archivo, elige CSV.

3. Configura la sección Destino de la siguiente manera:

• En Nombre de la tabla, escribe iowa_sales_denorm.
• Deja el resto de los campos de destino en sus valores predeterminados.

4. En la sección Esquema, configura lo siguiente:

• Haz clic en Editar como texto.
• Copia y pega el siguiente esquema:

5. Configura la sección Opciones avanzadas, de la siguiente manera:

• En Delimitador de campos, verifica que la opción Coma esté seleccionada.
• Dado que iowa_sales_denorm.csv contiene una única fila de encabezado, para Filas del encabezado que se omitirán, escribe 1.
• Marca Saltos de línea en secciones entrecomilladas.
• Acepta los valores predeterminados restantes y haz clic en Crear tabla.

BigQuery generará un trabajo de carga para crear la tabla y subir los datos a ella (esto puede tardar algunos segundos).

6. Haz clic en Historial personal para seguir el progreso del trabajo.

7. Escribe y ejecuta la siguiente consulta en la tabla con un esquema desnormalizado (esta consulta produce los mismos resultados que la consulta de la sección anterior):

8. En la parte inferior, en la sección Resultados de la consulta, haz clic en la pestaña Resultados y anota la hora de finalización de la consulta. Esto se comparará con la hora de consulta de un conjunto de datos acoplado en secciones posteriores.

9. Toma nota del tiempo que tarda en ejecutarse la consulta restando la hora de inicio de la hora de finalización.

Observa que la consulta correspondiente a la tabla con esquema desnormalizado se ejecuta un poco más rápido y tiene una sintaxis más sencilla. Siempre que sea posible, une previamente los conjuntos de datos en tablas homogéneas para optimizar el rendimiento de BigQuery.

Compara el rendimiento de la consulta con los detalles de la ejecución

1. Elige HISTORIAL DEL PROYECTO.

2. Haz clic en el primer trabajo de consulta que ejecutaste en el esquema relacional normalizado y, a continuación, haz clic en ABRIR COMO CONSULTA NUEVA.

3. Elige Detalles de la ejecución.

El plan de ejecución tiene dos secciones principales:

• Tiempo promedio y máximo de los trabajadores por tipo de trabajo y por etapa

• Comparativas de rendimiento de alto nivel

  • Tiempo transcurrido: Es el tiempo total de procesamiento de la consulta.
  • Tiempo de ranura consumido: Se refiere al tiempo que se tardaría en procesar la consulta si no se procesara en paralelo en varias máquinas.
  • Bytes redistribuidos: Es una redistribución automática de datos en la memoria para el procesamiento paralelo masivo.
  • Bytes volcados al disco: Si los datos no se pueden procesar en memoria, indica cuánto se volcó al disco persistente (por lo general, la culpa es del sesgo de los datos).

1. En primer lugar, compara los tiempos de las comparativas entre cada una de las consultas que ejecutamos.

2. A continuación, compara el tipo de trabajo al que los trabajadores dedicaron más tiempo.

Consulta 1. Detalles de ejecución del esquema relacional

Consulta 2. Detalles de ejecución del esquema desnormalizado

Observaciones:

• La consulta desnormalizada (nº 2) es más rápida y usa menos tiempo de ranura para obtener el mismo resultado.
• La consulta relacional (nº 1) tiene muchas más etapas de entrada y emplea la mayor parte del tiempo de trabajo en unir los conjuntos de datos.
• La consulta desnormalizada (nº 2) es la que más tiempo dedica a la lectura de los datos de entrada y a la salida de los resultados. El tiempo empleado en agregaciones y uniones es mínimo.
• Ninguna de las dos consultas dio como resultado bytes volcados al disco, lo que sugiere que es probable que nuestros conjuntos de datos no estén sesgados (o sean lo suficientemente grandes como para que un trabajador individual los vuelque de la memoria).

Evita los antipatrones del rendimiento

Ahora que ya sabes sobre el diseño eficaz de esquemas de bases de datos, es hora de practicar la optimización de algunas consultas mal escritas.

La siguiente consulta se ejecuta demasiado lento, ¿qué puedes hacer para corregirla?

1. Copia y pega la siguiente consulta en el Editor de consultas y ejecútala para obtener una comparativa.

Objetivo: Contabilizar todas las organizaciones sin fines de lucro de Estados Unidos que presentaron declaraciones de impuestos en papel (no de forma electrónica) en 2015.

¿Qué puedes hacer para mejorar el rendimiento?

2. Compáralo con la solución siguiente:

3. Ejecuta tu versión actualizada y controla el tiempo.

4. Borra los datos del Editor de consultas.

Esta nueva consulta se ejecuta de forma lenta. Ejecuta la consulta para obtener una comparativa y detenla a los 30 segundos si no se completa.

Objetivo: Unir la tabla de presentaciones de declaraciones de impuestos con la tabla de nombres de organizaciones. Para esto, usa el número de identificación del empleador (ein) como campo de enlace, además de obtener los nombres de todas las organizaciones que presentaron declaraciones en 2015.

5. Agrega esta consulta en el Editor de consultas y, a continuación, haz clic en Ejecutar:

6. Corrige la consulta anterior. Sugerencia: Recuerda la condición del campo JOIN correcta para nuestro esquema.

Compáralo con la solución siguiente.

7. Agrega esta consulta en el Editor de consultas y, a continuación, haz clic en Ejecutar:

8. Ejecuta tu versión actualizada y controla el tiempo.

¿Ves alguna mejora? ¿Con qué rapidez se ejecuta la consulta?

Lecciones aprendidas

¡Felicitaciones!

Con esto, se concluye este lab práctico sobre el Diseño de esquemas efectivos en BigQuery y rendimiento de consultas. Cargaste archivos CVS y JSON en tablas de BigQuery; transformaste datos y uniste tablas; almacenaste resultados de consultas; y luego confirmaste que los datos se transformaron y cargaron de forma correcta.

Finalice su lab

Cuando haya completado el lab, haga clic en Finalizar lab. Google Cloud Skills Boost quitará los recursos que usó y limpiará la cuenta.

Tendrá la oportunidad de calificar su experiencia en el lab. Seleccione la cantidad de estrellas que corresponda, ingrese un comentario y haga clic en Enviar.

La cantidad de estrellas indica lo siguiente:

• 1 estrella = Muy insatisfecho
• 2 estrellas = Insatisfecho
• 3 estrellas = Neutral
• 4 estrellas = Satisfecho
• 5 estrellas = Muy satisfecho

Puede cerrar el cuadro de diálogo si no desea proporcionar comentarios.

Para enviar comentarios, sugerencias o correcciones, use la pestaña Asistencia.

Copyright 2020 Google LLC. All rights reserved. Google y el logotipo de Google son marcas de Google LLC. Los demás nombres de productos y empresas pueden ser marcas de las respectivas empresas a las que estén asociados.