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

1. Introducción

BigQuery es un almacén de datos empresariales completamente administrado, de bajo costo y a escala de petabytes para análisis. BigQuery es sin servidores. No es necesario 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 GCP Console para comprender el particionado y la agrupación en clústeres en BigQuery. La partición y el agrupamiento en clústeres de tablas de BigQuery ayudan a estructurar tus datos para que coincidan con los patrones de acceso a datos comunes. La partición y el agrupamiento en clústeres son fundamentales para maximizar por completo el rendimiento y el costo de BigQuery cuando se consulta un rango de datos específico. Esto permite analizar menos datos por consulta, y la poda 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 bq para realizar operaciones en 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.

Crea 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 Google Cloud Platform Console ( console.cloud.google.com) y crea un proyecto nuevo.
3. Si no tienes ningún proyecto, haz clic en el botón para crear uno:

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

4. Ingresa un nombre para el 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, incluidos los de BBC News, los repositorios 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 mucho 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. La IU web de BigQuery se abre en GCP Console y muestra 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 mostrar una lista de los conjuntos de datos públicos. Cada conjunto de datos incluye 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. Ten en cuenta los nombres de los campos de la tabla.
5. Sobre los nombres de los campos, haz clic en Vista previa para ver datos de muestra de la tabla badges.

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 comprender los datos con los que trabajas, pero consultar conjuntos de datos es donde BigQuery realmente se destaca. En esta sección, se explica cómo ejecutar consultas de BigQuery. En este punto, no necesitas saber nada de SQL. Puedes copiar y pegar las siguientes consultas.

Para ejecutar una consulta, completa los siguientes pasos:

1. Cerca de la parte superior derecha de la consola de GCP, 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 la cantidad de publicaciones o preguntas de Stack Overflow que se publicaron cada año.

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

4. Cómo 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, consultarás la tabla.

Crea un conjunto de datos nuevo

Para crear y cargar datos de tablas en BigQuery, primero crea un conjunto de datos de BigQuery para almacenar los datos. Para ello, completa los siguientes pasos:

1. En el panel de navegación de GCP Console, selecciona el nombre del proyecto que se creó 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 las demás configuraciones predeterminadas en su lugar y haz clic en Aceptar.

Crea una tabla nueva con publicaciones de Stack Overflow 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. Para ello, completa los siguientes pasos:

1. Cerca de la parte superior derecha de la consola de GCP, 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 tabla nueva questions_2018 en el conjunto de datos stackoverflow de tu proyecto con los datos que se obtienen 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 alrededor de 2 minutos en crear una tabla nueva. Espera antes de continuar con el siguiente paso.

Consulta la tabla nueva 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 la consola de GCP, 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 devuelve las preguntas de Stack Overflow creadas en enero de 2018 que están etiquetadas como android junto con la pregunta y algunas 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 los devolvió desde la caché.
5. Vuelve a ejecutar la misma consulta, pero esta vez con el almacenamiento en caché de BigQuery inhabilitado. Inhabilitaremos la caché durante el resto del lab para que la comparación del rendimiento con las tablas particionadas y agrupadas, que se ejecutarán en las siguientes secciones, sea justa. 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 que se procesaron 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 usando 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 la consulta. En esta sección, crearás una nueva tabla particionada a partir de la misma tabla posts_questions del 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 las tablas grandes en muchas particiones más pequeñas usando el tiempo de transferencia de datos, una columna TIMESTAMP/DATE o una columna INTEGER. Crearemos una tabla particionada por DATE.

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

Crea una tabla particionada nueva con publicaciones de Stack Overflow de 2018

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

1. Cerca de la parte superior derecha de la consola de GCP, 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 tabla nueva questions_2018_partitioned en el conjunto de datos stackoverflow de tu proyecto con los datos que resultan de ejecutar una consulta en el conjunto de datos de Stack Overflow de BigQuery bigquery-public-data.stackoverflow.posts_questions.

Nota: Este paso tarda alrededor de 2 minutos 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 devolver 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 la consola de GCP, 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 devuelve las preguntas de Stack Overflow creadas en enero de 2018 que están etiquetadas como android junto con la pregunta y algunas otras estadísticas.
4. En los resultados de la consulta, deberías ver el tiempo que tardó en completarse y el volumen de datos que se procesaron para obtener los resultados.

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

6. Crea y consulta una tabla agrupada

En la sección anterior, creaste una tabla particionada en BigQuery con datos de la tabla posts_questions del conjunto de datos públicos de Stack Overflow. Consultamos esta tabla con el almacenamiento en caché inhabilitado y observamos el rendimiento de la consulta con tablas particionadas y no particionadas. En esta sección, crearás una nueva tabla agrupada a partir de la misma tabla posts_questions del 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 en el esquema de la tabla. Las columnas que especificas se usan para colocar datos relacionados. Cuando los datos se escriben en una tabla agrupada en clústeres, BigQuery los ordena según 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 en clústeres determina la clasificación de los datos. Cuando se agregan datos nuevos a una tabla o una 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 tabla agrupada nueva con publicaciones de Stack Overflow de 2018

En esta sección, crearás una tabla nueva 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. Para ello, completa los siguientes pasos:

1. Cerca de la parte superior derecha de la consola de GCP, 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 tabla nueva questions_2018_clustered en el conjunto de datos stackoverflow de tu proyecto con los datos que resultan de ejecutar una consulta en la tabla de Stack Overflow de BigQuerybigquery-public-data.stackoverflow.posts_questions. La tabla nueva está particionada en creation_date y agrupada en clústeres en la columna de etiquetas.

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

Consulta la tabla agrupada en clústeres con publicaciones de Stack Overflow de 2018

Ahora que creaste una tabla agrupada de BigQuery, volvamos a ejecutar la misma consulta, esta vez en la tabla particionada y agrupada, para devolver 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 la consola de GCP, 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 devuelve las preguntas de Stack Overflow creadas en enero de 2018 que están etiquetadas como android junto con la pregunta y algunas otras estadísticas.
4. En los resultados de la consulta, deberías ver el tiempo que tardó en completarse y el volumen de datos que se procesaron para obtener los resultados.

Con una tabla particionada y agrupada en clústeres, la consulta analizó menos datos que una tabla particionada o una tabla sin particiones. La forma en que se organizan los datos mediante la partición y la agrupación 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. Realiza una limpieza

A menos que planees seguir trabajando con tu conjunto de datos de Stack Overflow, 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 en el panel de navegación izquierdo de 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 Borrar 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 el proyecto actual es el que creaste para este codelab y selecciona Cerrar.
4. En el diálogo Cerrar el proyecto, ingresa el ID de tu proyecto (no el nombre) y selecciona Cerrar para confirmar.

¡Felicitaciones! Ahora ya sabes

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