Creación de particiones y agrupamiento en clústeres en BigQuery

1. Introducción

BigQuery es un almacén de datos empresarial de bajo costo, a escala de petabytes y completamente administrado para el análisis. BigQuery es sin servidores. No necesitas configurar ni administrar clústeres.

Un conjunto de datos de BigQuery reside en un proyecto de GCP y contiene una o más tablas. Puedes consultar estos conjuntos de datos con SQL.

En este codelab, usarás la IU web de BigQuery en la consola de GCP para comprender la partición y el agrupamiento en clústeres en BigQuery. La partición de tablas y el agrupamiento en clústeres de BigQuery ayudan a estructurar los datos para que coincidan con los patrones comunes de acceso a los datos. La partición y el agrupamiento en clústeres son clave para maximizar por completo el rendimiento y el costo de BigQuery cuando se consultan sobre un rango de datos específico. Como resultado, se analizan menos datos por consulta, y la reducción se determina antes de la hora de inicio de la consulta.

Nota: Además de la IU web de BigQuery, puedes usar la herramienta de línea de comandos de bq para realizar operaciones en los conjuntos de datos de BigQuery. Para obtener más información sobre la herramienta de línea de comandos de bq, consulta Herramienta de línea de comandos de bq.

Para obtener más información sobre BigQuery, consulta la documentación de BigQuery.

Qué aprenderás

• Cómo crear y consultar tablas particionadas y agrupadas
• Compara el rendimiento de las consultas con tablas particionadas y agrupadas

Requisitos

Para completar este lab, necesitarás lo siguiente:

• La versión más reciente de Google Chrome
• Una cuenta de facturación de Google Cloud Platform

2. Cómo prepararte

Para trabajar con BigQuery, debes crear un proyecto de GCP o seleccionar uno existente.

Cómo crear un proyecto

Para crear un proyecto nuevo, sigue estos pasos:

1. Si aún no tienes una cuenta de Google (Gmail o Google Apps), crea una.
2. Accede a la consola de Google Cloud Platform ( console.cloud.google.com) y crea un proyecto nuevo.
3. Si no tienes ningún proyecto, haz clic en el botón Crear proyecto:

De lo contrario, crea un proyecto nuevo desde el menú de selección de proyectos:

4. Ingresa un nombre de proyecto y selecciona Crear. Ten en cuenta que el ID del proyecto es un nombre único en todos los proyectos de Google Cloud.

3. Trabaja con conjuntos de datos públicos

BigQuery te permite trabajar con conjuntos de datos públicos, como BBC News, repos de GitHub, Stack Overflow y los conjuntos de datos de la Administración Nacional Oceánica y Atmosférica (NOAA) de EE.UU. No es necesario que cargues estos conjuntos de datos en BigQuery. Solo debes abrir los conjuntos de datos para explorarlos y consultarlos en BigQuery. En este codelab, trabajarás con el conjunto de datos públicos de Stack Overflow.

Explora el conjunto de datos de Stack Overflow

El conjunto de datos de Stack Overflow contiene información sobre publicaciones, etiquetas, insignias, comentarios, usuarios y más. Para explorar el conjunto de datos de Stack Overflow en la IU web de BigQuery, sigue estos pasos:

1. Abre el conjunto de datos de Stack Overflow. Se abrirá la IU web de BigQuery en GCP Console y mostrará información sobre el conjunto de datos de Stack Overflow.
2. En el panel de navegación , selecciona bigquery-public-data. El menú se expande para enumerar los conjuntos de datos públicos. Cada conjunto de datos consta de una o más tablas.
3. Desplázate hacia abajo y selecciona stackoverflow. El menú se expande para mostrar una lista de las tablas en el conjunto de datos de Stack Overflow.
4. Selecciona Badges para ver el esquema de la tabla de insignias. Anota los nombres de los campos en la tabla.
5. Sobre los nombres de los campos, haz clic en Vista previa para ver los datos de muestra de la tabla de insignias.

Para obtener más información sobre todos los conjuntos de datos públicos disponibles en BigQuery, consulta Conjuntos de datos públicos de Google BigQuery.

Consulta el conjunto de datos de Stack Overflow

Explorar un conjunto de datos es una buena manera de entender los datos con los que estás trabajando, pero la consulta de conjuntos de datos es donde BigQuery realmente se destaca. En esta sección, se explica cómo ejecutar consultas de BigQuery. No necesitas saber nada de SQL en este punto. Puedes copiar y pegar las siguientes consultas.

Para ejecutar una consulta, completa los siguientes pasos:

1. Cerca de la parte superior derecha de GCP Console, selecciona Redactar consulta nueva.
2. En el área de texto del Editor de consultas, copia y pega la siguiente consulta en SQL. BigQuery valida la consulta y la IU web muestra una marca de verificación verde debajo del área de texto para indicar que la sintaxis es válida.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Selecciona Run. La consulta devuelve el número de publicaciones o preguntas de Stack Overflow que se publicaron cada año.

Nota: Esta consulta usa el nombre completo de la tabla para la tabla posts_questions, incluido el nombre del proyecto, el nombre del conjunto de datos y el nombre de la tabla: bigquery-public-data.stackoverflow.posts_questions.

4. Crear una tabla nueva

En la sección anterior, consultaste conjuntos de datos públicos que BigQuery pone a tu disposición. En esta sección, crearás una tabla nueva en BigQuery a partir de una tabla existente. Crearás una tabla nueva con datos muestreados de la tabla posts_questions del conjunto de datos públicos de Stack Overflow y, luego, la consultarás.

Crea un conjunto de datos nuevo

Para crear y cargar datos de tablas en BigQuery, primero debes crear un conjunto de datos de BigQuery que contenga los datos. Para ello, sigue estos pasos:

1. En el panel de navegación de la consola de GCP, selecciona el nombre del proyecto creado como parte de la configuración.
2. En el panel de detalles a la derecha, selecciona Crear conjunto de datos.

3. En el diálogo Crear conjunto de datos, en ID del conjunto de datos, escribe stackoverflow. Deja los valores predeterminados en el resto de la configuración y haz clic en OK.

Cómo crear una tabla nueva con las publicaciones de StackOverflow de 2018

Ahora que creaste un conjunto de datos de BigQuery, puedes crear una tabla nueva en BigQuery. Para crear una tabla con datos de una tabla existente, consultarás el conjunto de datos de publicaciones de Stack Overflow de 2018 y escribirás los resultados en una tabla nueva completando los siguientes pasos:

1. Cerca de la parte superior derecha de GCP Console, selecciona Redactar consulta nueva.

2. En el área de texto del Editor de consultas, copia y pega la siguiente consulta en SQL para crear una tabla nueva, que es una instrucción de DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Selecciona Run. La consulta crea una nueva tabla questions_2018 en el conjunto de datos stackoverflow de tu proyecto con los datos resultantes de la ejecución de una consulta en el conjunto de datos de Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Nota: Este paso tarda aproximadamente 2 min en crear una tabla nueva. Espera antes de continuar con el siguiente paso.

Consulta la nueva tabla con publicaciones de Stack Overflow de 2018

Ahora que creaste una tabla de BigQuery, ejecutemos una consulta para mostrar las publicaciones de Stack Overflow con preguntas y títulos, junto con algunas otras estadísticas, como la cantidad de respuestas, comentarios, vistas y favoritos. Completa los siguientes pasos:

1. Cerca de la parte superior derecha de GCP Console, selecciona Redactar consulta nueva.
2. En el área de texto del Editor de consultas, copia y pega la siguiente consulta en SQL.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Selecciona Run. La consulta muestra preguntas de Stack Overflow creadas en el mes de enero de 2018 que están etiquetadas como android, junto con la pregunta y otras estadísticas.
4. De forma predeterminada, BigQuery almacena en caché los resultados de las consultas. Ejecuta la misma consulta y verás que BigQuery tardó mucho menos en devolver los resultados porque muestra resultados de la caché.
5. Vuelve a ejecutar la misma consulta, pero esta vez con el almacenamiento en caché de BigQuery inhabilitado. Inhabilitaremos la caché para que el resto del lab compare el rendimiento con tablas particionadas y agrupadas en clústeres, que ejecutaremos en las siguientes secciones. En el editor de consultas, haz clic en Más y selecciona Configuración de consulta.
6. En Preferencia de caché, desmarca Usar resultados almacenados en caché.
7. En los resultados de la consulta, deberías ver el tiempo que tardó en completarse y el volumen de datos procesados para obtener los resultados.

5. Crea y consulta una tabla particionada

En la sección anterior, creaste una tabla nueva en BigQuery con datos de la tabla posts_questions mediante el conjunto de datos públicos de Stack Overflow. Consultamos este conjunto de datos con el almacenamiento en caché inhabilitado y observamos el rendimiento de las consultas. En esta sección, crearás una nueva tabla particionada a partir de la tabla posts_questions del mismo conjunto de datos públicos de Stack Overflow y observarás el rendimiento de la consulta.

Las tablas particionadas son tablas especiales que se dividen en segmentos, denominados particiones, que facilitan la administración y la consulta de los datos. Por lo general, puedes dividir tablas grandes en varias particiones más pequeñas con el tiempo de transferencia de datos, la columna TIMESTAMP/DATE o una columna INTEGER. Crearemos una tabla particionada DATE.

Obtén más información sobre las tablas particionadas aquí.

Crea una nueva tabla particionada con publicaciones de StackOverflow de 2018

Para crear una tabla particionada con datos de una tabla o consulta existente, consultarás el conjunto de datos de publicaciones de Stackoverflow de 2018 y escribirás los resultados en una tabla nueva. Para ello, sigue estos pasos:

1. Cerca de la parte superior derecha de GCP Console, selecciona Redactar consulta nueva.

2. En el área de texto del Editor de consultas, copia y pega la siguiente consulta en SQL para crear una tabla nueva, que es una instrucción de DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Selecciona Run. La consulta crea una nueva tabla questions_2018_partitioned en el conjunto de datos stackoverflow de tu proyecto con los datos resultantes de la ejecución de una consulta en el conjunto de datos de Stack Overflow de BigQuery bigquery-public-data.stackoverflow.posts_questions

Nota: Este paso tarda aproximadamente 2 min en crear una tabla nueva. Espera antes de continuar con el siguiente paso.

Consulta la tabla particionada con publicaciones de Stack Overflow de 2018

Ahora que creaste una tabla particionada de BigQuery, ejecutemos la misma consulta, esta vez en la tabla particionada, para mostrar publicaciones de Stack Overflow con preguntas y títulos junto con algunas otras estadísticas, como la cantidad de respuestas, comentarios, vistas y favoritos. Completa los siguientes pasos:

1. Cerca de la parte superior derecha de GCP Console, selecciona Redactar consulta nueva.
2. En el área de texto del Editor de consultas, copia y pega la siguiente consulta en SQL.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Selecciona Ejecutar con el almacenamiento en caché de BigQuery inhabilitado (consulta la sección anterior para inhabilitar la caché de BigQuery). La consulta muestra preguntas de Stack Overflow creadas en el mes de enero de 2018 que están etiquetadas como android, junto con la pregunta y otras estadísticas.
4. En los resultados de la consulta, deberías ver el tiempo que tardó en completarse y el volumen de datos procesados para obtener los resultados.

Deberías ver que el rendimiento de la consulta con la tabla particionada es mejor que el de la tabla no particionada, ya que BigQuery reduce las particiones, es decir, analiza solo las particiones necesarias que procesan menos datos y se ejecutan más rápido. Esto optimiza los costos y el rendimiento de las consultas.

6. Cómo crear y consultar una tabla agrupada

En la sección anterior, creaste una tabla particionada en BigQuery con datos de la tabla posts_questions en el conjunto de datos públicos de Stack Overflow. Consultamos esta tabla con el almacenamiento en caché inhabilitado y observamos el rendimiento de las consultas con tablas particionadas y no particionadas. En esta sección, crearás una nueva tabla agrupada en clústeres a partir de la tabla posts_questions del mismo conjunto de datos públicos de Stack Overflow y observarás el rendimiento de la consulta.

Cuando una tabla se agrupa en clústeres en BigQuery, los datos de la tabla se organizan automáticamente según el contenido de una o más columnas del esquema de la tabla. Las columnas que especificas se usan para colocar los datos relacionados. Cuando los datos se escriben en una tabla agrupada en clústeres, BigQuery los ordena mediante los valores de las columnas de agrupamiento en clústeres. Estos valores se usan con el fin de organizar los datos en diferentes bloques en el almacenamiento de BigQuery. El orden de las columnas agrupadas determina el orden de clasificación de los datos. Cuando se agregan datos nuevos a una tabla o partición específica, BigQuery realiza un reagrupamiento en clústeres automático en segundo plano para restablecer la propiedad de orden de la tabla o partición.

Obtén más información para trabajar con tablas agrupadas aquí.

Crea una nueva tabla agrupada en clústeres con las publicaciones de Stack Overflow, 2018

En esta sección, crearás una nueva tabla particionada en creation_date y agrupada en clústeres en la columna tags según el patrón de acceso a la consulta. Para crear una tabla agrupada con datos de una tabla o consulta existente, consultarás la tabla de publicaciones de Stack Overflow de 2018 y escribirás los resultados en una tabla nueva completando los siguientes pasos:

1. Cerca de la parte superior derecha de GCP Console, selecciona Redactar consulta nueva.

2. En el área de texto del Editor de consultas, copia y pega la siguiente consulta en SQL para crear una tabla nueva, que es una instrucción de DDL.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Selecciona Ejecutar. La consulta crea una nueva tabla questions_2018_clustered en el conjunto de datos stackoverflow de tu proyecto con los datos resultantes de la ejecución de una consulta en la tabla de Stack Overflow de BigQuery bigquery-public-data.stackoverflow.posts_questions. La tabla nueva se particionará en creación_fecha y se agrupará en clústeres en la columna de etiquetas.

Nota: Este paso tarda aproximadamente 2 min en crear una tabla nueva. Espera antes de continuar con el siguiente paso.

Consulta tablas agrupadas en clústeres con las publicaciones de Stack Overflow de 2018

Ahora que creaste una tabla de BigQuery agrupada en clústeres, volvamos a ejecutar la misma consulta, esta vez en la tabla particionada y agrupada, para mostrar publicaciones de Stack Overflow con preguntas y títulos, junto con otras estadísticas, como la cantidad de respuestas, comentarios, vistas y favoritos. Completa los siguientes pasos:

1. Cerca de la parte superior derecha de GCP Console, selecciona Redactar consulta nueva.
2. En el área de texto del Editor de consultas, copia y pega la siguiente consulta en SQL.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Selecciona Ejecutar con el almacenamiento en caché de BigQuery inhabilitado (consulta la sección anterior para inhabilitar la caché de BigQuery). La consulta muestra preguntas de Stack Overflow creadas en el mes de enero de 2018 que están etiquetadas como android, junto con la pregunta y otras estadísticas.
4. En los resultados de la consulta, deberías ver el tiempo que tardó en completarse y el volumen de datos procesados para obtener los resultados.

Con una tabla particionada y agrupada en clústeres, la consulta analizó menos datos que una tabla particionada o una no particionada. La forma en que se organizan los datos mediante la partición y el agrupamiento en clústeres minimiza la cantidad de datos que analizan los trabajadores de ranuras, lo que mejora el rendimiento de las consultas y optimiza los costos.

7. Realice una limpieza

A menos que planees seguir trabajando con tu conjunto de datos de Stackoverflow, debes borrarlo y borrar el proyecto que creaste para este codelab.

Borra el conjunto de datos de BigQuery

Sigue estos pasos para borrar el conjunto de datos de BigQuery:

1. Selecciona el conjunto de datos stackoverflow del panel de navegación izquierdo en BigQuery .
2. En el panel de detalles, selecciona Borrar conjunto de datos.
3. En el diálogo Borrar conjunto de datos, ingresa stackoverflow y selecciona Delete para confirmar que deseas borrar el conjunto de datos.

Borra el proyecto

Para borrar el proyecto de GCP que creaste para este codelab, sigue estos pasos:

1. En el menú de navegación de GCP, seleccione IAM y administración.
2. En el panel de navegación, seleccione Configuración.
3. En el panel de detalles, confirma que tu proyecto actual sea el que creaste para este codelab y selecciona Cerrar.
4. En el diálogo Cerrar proyecto, ingresa el ID de tu proyecto (no el nombre) y selecciona Cerrar para confirmar.

¡Felicitaciones! Ya aprendiste

• Cómo usar la IU web de BigQuery para crear una tabla nueva a partir de tablas existentes
• Cómo crear y consultar tablas particionadas y agrupadas
• Cómo la partición y el agrupamiento en clústeres optimizan el rendimiento y los costos de las consultas

Ten en cuenta que no tuviste que configurar ni administrar clústeres para trabajar con conjuntos de datos.