Compresión de Datos en SQL: Evaluación y Comparación de Algoritmos

Clase 11 de 33 • Curso de AWS Redshift para Manejo de Big Data

Resumen

¿Cómo aplicar algoritmos de compresión a una columna en Big Data?

En el mundo del Big Data, la compresión de datos es fundamental para optimizar el almacenamiento y mejorar el rendimiento de las consultas. En esta clase del curso de Reptimit para Big Data, exploramos cómo aplicar algoritmos de compresión a columnas y evaluar su eficacia. Preparémonos para experimentar con SQL y sacarle el máximo provecho a estos algoritmos.

¿Cómo comenzamos con el código?

Lo primero que hicimos fue asegurarnos de tener datos suficientes. Usamos la tabla venue, que contiene datos numéricos y de texto, aunque apenas cuenta con 202 filas. Evaluamos que comprimir tan pocos registros no tiene mucho sentido. Por lo tanto, buscamos hacer el experimento más interesante cruzando esta tabla con la tabla listening, que tiene aproximadamente 192,000 registros.

¿Por qué es crucial evitar cartesianos en SQL?

Unir tablas sin una llave de relación específica puede resultar en un producto cartesiano, generando una explosión de registros innecesaria. Tal práctica se considera una de las peores en SQL. Sin embargo, para nuestro experimento, creamos una tabla llamada cartesian_venue que contiene 38,884,394 registros, resultando del cruce cartesiano. Este proceso se completó en cuestión de segundos, evidenciando la potencia de las bases de datos orientadas a columnas.

¿Cómo creamos y populamos tablas con diferentes codificaciones?

En este paso, comenzamos creando una tabla llamada encoding_venue con diferentes tipos de codificación para la columna venue_name. Los tipos de codificación utilizados incluyen:

Row: Sin ninguna codificación.
By Dig: Codificación de diccionario.
LZ0: Compresión basada en Lempel-Ziv.
Run Length: Compresión basada en longitudes de ejecución.
Text 255 y Text 32K: Para manejo de texto con distintos niveles de compresión.
Standard ZSTD: Compresión estándar.

Aquí es como se visualiza el código SQL para la creación de la tabla:

CREATE TABLE encoding_venue (
    name_row VARCHAR(100) ENCODE ROW,
    name_by_dig VARCHAR(100) ENCODE BY DIG,
    name_lz0 VARCHAR(100) ENCODE LZO,
    name_run_length VARCHAR(100) ENCODE RUNLENGTH,
    name_text_255 VARCHAR(255),
    name_text_32K VARCHAR(32000),
    name_standard_zstd VARCHAR(100) ENCODE ZSTD
);

Insertamos los datos de la tabla cartesian_venue en encoding_venue, aprovechando la ventaja que supone que todas las operaciones se realizan sobre los mismos bloques de datos columnares.

¿Qué descubrimos acerca de la compresión de datos?

Al final de la implementación, verificamos que cada columna en encoding_venue contiene los mismos 38,884,394 registros. Esto nos permitió comparar cómo cada tipo de compresión afecta el almacenamiento y el rendimiento. Así, destacamos el potencial de las bases de datos orientadas a columnas para manejar grandes volúmenes de datos de manera eficiente.

Este proceso es un testimonio del poder de la compresión y cómo puede transformar la gestión de Big Data. Recuerda, la práctica y la experimentación son herramientas esenciales al explorar nuevas tecnologías. Adelante, continúa perfeccionando tus habilidades en Big Data y SQL. Si surge alguna duda, siempre puedes acudir a los comentarios. ¡No pierdas la motivación!

Martin DAVILA

student•

SELECT * FROM venue limit 10;
SELECT count(0) FROM listing;

CREATE table cartesian_venue as ()
SELECT venueid, venuename , venuecity , venuestate ,
FROM venue, listing;

CREATE table encoding_venue(
nameraw varchar(100) encode raw,
namebytedict varchar(100) encode bytedict,
namelzo varchar(100) encode lzo,
namerunlength varchar(100) encode runlength,
nametext255 varchar(100) encode text255,
nametext32k varchar(100) encode text32k,
namezstd varchar(100) encode zstd,

);

INSERT INTO encoding_venue
(
SELECT venuename,venuename,venuename,venuename,venuename,venuename,venuename
FROM cartesian_venue
);```

JAVIER SANTIAGO SALGADO

student•

sentí mucha adrenalina al ejecutar ese producto cartesiano :smirk:

SELECT * FROM venue LIMIT 10;
SELECT COUNT(0) FROM venue;
SELECT COUNT(*) FROM listing;
SELECT COUNT(*) FROM cartesian_venue;

SELECT venueid, venuename, venuecity, venuestate, venueseats
FROM venue
LIMIT 10;

CREATE TABLE cartesian_venue as(
SELECT venueid, venuename, venuecity, venuestate, venueseats
FROM venue, listing);

SELECT *
FROM cartesian_venue
LIMIT 10;

CREATE TABLE encoding_venue (
nameraw varchar(100) encode raw,
namebytedict varchar(100) encode bytedict,
namelzo varchar(100) encode lzo,
namerunlength varchar(100) encode runlength,
nametext255 varchar(100) encode text255,
nametext32k varchar(100) encode text32k,
namezstd varchar(100) encode zstd
);

INSERT INTO encoding_venue 
(
SELECT venuename, venuename, venuename, venuename, venuename, venuename, venuename
FROM cartesian_venue 
);

SELECT * FROM encoding_venue LIMIT 10;

SELECT COUNT(0)
FROM encoding_venue;

Carlos Javier Guevara Contreras

student•

Una codificación de compresión especifica el tipo de compresión que se aplica a una columna de valores de datos a medida que se añaden filas a una tabla.

Si no se especifica una compresión en la instrucción CREATE TABLE o ALTER TABLE, Amazon Redshift asigna automáticamente la codificación de compresión de la siguiente manera:

A las columnas que están definidas como claves de ordenación se les asigna una compresión RAW.

A las columnas que están definidas como tipos de datos BOOLEAN, REAL o DOUBLE PRECISION se les asigna una compresión RAW.

Las columnas que se definen como tipos de datos SMALLINT, INTEGER, BIGINT, DECIMAL, CHAR, VARCHAR, DATE, TIMESTAMP o TIMESTAMPTZ tienen asignada la compresión AZ64.

Las columnas que se definen como tipos de datos CHAR o VARCHAR tienen asignada la compresión LZO.

Felipe Andres Torres Haro

student•

Me sorprendiio la velocidad, en serio... muy sorprendente

Elver Tobo

student•

Y no son solo 38.8M de datos, es eso multiplicado por 7 columnas PUFFF!!

Raúl Ortega Larsen

student•

Eso es cerca de 280 millones de registros... en verdad impresionante!

Joel Eduardo Gaspar

student•

Eso fue mas rápido que El Rayo McQueen!

Mario Alexander Vargas Celis

student•

Aplicar algoritmos de compresión en Amazon Redshift implica configurar cómo se almacenan las columnas de una tabla para optimizar el uso del almacenamiento y mejorar el rendimiento de las consultas. A continuación, se explica cómo aplicar los algoritmos de compresión en Redshift paso a paso:

1. Crear una tabla con algoritmos de compresión

Puedes definir la compresión en el momento de la creación de una tabla utilizando el parámetro ENCODE en cada columna.

Ejemplo:

CREATE TABLE mi_tabla ( id INT ENCODE RAW, -- Sin compresión nombre VARCHAR(255) ENCODE BYTEDICT, -- Diccionario de bytes fecha TIMESTAMP ENCODE DELTA, -- Compresión por diferencias estado CHAR(1) ENCODE RUNLENGTH, -- Compresión por longitud de ejecución comentario TEXT ENCODE ZSTD -- Algoritmo ZSTD para texto largo );

2. Aplicar compresión en tablas existentes

Si ya tienes una tabla creada sin compresión o con compresión ineficiente, puedes aplicar algoritmos usando ALTER TABLE.

Ejemplo:

ALTER TABLE mi_tabla ALTER COLUMN nombre ENCODE BYTEDICT;

⚠️ Nota: Si la tabla tiene datos, deberás recrearla o usar herramientas específicas para evitar pérdida de datos.

3. Analizar compresión con ANALYZE COMPRESSION

El comando ANALYZE COMPRESSION evalúa los datos existentes y sugiere los mejores algoritmos de compresión para cada columna.

Ejemplo:

ANALYZE COMPRESSION mi_tabla;

Salida esperada: Un informe con sugerencias para las columnas. Por ejemplo:

columna1: DELTA columna2: BYTEDICT columna3: ZSTD

4. Recargar datos con compresión automática

El comando COPY puede determinar automáticamente los algoritmos de compresión adecuados al cargar datos.

Ejemplo:

COPY mi_tabla FROM 's3://mi-bucket/mi-dataset.csv' IAM_ROLE 'arn:aws:iam::123456789012:role/MiRole' CSV;

Esto analizará los datos y aplicará la compresión más eficiente.

5. Recrear la tabla para optimizar la compresión

Una opción más avanzada consiste en:

Crear una nueva tabla con las configuraciones de compresión sugeridas.
Transferir los datos de la tabla original a la nueva tabla.
Renombrar la nueva tabla como la tabla original.

Ejemplo:

CREATE TABLE mi_tabla_nueva ( id INT ENCODE RAW, nombre VARCHAR(255) ENCODE BYTEDICT, fecha TIMESTAMP ENCODE DELTA, estado CHAR(1) ENCODE RUNLENGTH, comentario TEXT ENCODE ZSTD );

INSERT INTO mi_tabla_nueva SELECT * FROM mi_tabla;

DROP TABLE mi_tabla;

ALTER TABLE mi_tabla_nueva RENAME TO mi_tabla;

6. Validar el uso de compresión

Puedes verificar qué algoritmo se está usando en cada columna consultando la vista del sistema SVV_TABLE_INFO o PG_TABLE_DEF.

Ejemplo:

SELECT "column", encoding FROM pg_table_def WHERE tablename = 'mi_tabla';

7. Mejoras y beneficios

Reducción de almacenamiento: La compresión puede reducir significativamente el espacio en disco, especialmente para datos repetitivos o categóricos.
Consultas más rápidas: Menos datos físicos que leer significa consultas más rápidas.
Costos más bajos: Al utilizar menos almacenamiento, puedes optimizar costos en tu clúster Redshift.

Mejores prácticas

Siempre analiza los datos con ANALYZE COMPRESSION antes de aplicar compresión manualmente.
Usa compresión automática durante cargas masivas con COPY.
Optimiza las columnas más grandes o aquellas que se usan frecuentemente en consultas.

Karla Verónica Álvarez Vázquez

student•

Topics Compression encodings:

Raw encoding AZ64 encoding Byte-dictionary encoding Delta encoding LZO encoding Mostly encoding Runlength encoding Text255 and Text32k encodings Zstandard encoding

Compresión de Datos en SQL: Evaluación y Comparación de Algoritmos

Primeros pasos en la arquitectura no transaccional

Manejo de Big Data con Reptiit en Amazon AWS

Data Warehouse y Modelo Dimensional en Amazon Repsheet

Bases de Datos Columnares: Eficiencia en Consultas Analíticas

Procesamiento de Datos con Repsheet y Clústeres SQL

Configura tu entorno de trabajo para Redshift

Configuración de IAM y S3 en AWS para Repsheet

Configuración de Clúster en Amazon Repsheet para Big Data

Conexión y Configuración de Repsheet con Clientes Externos

Carga de Datos a Redshift desde Amazon S3: Paso a Paso

Cómo diseñar tu base de datos para mejorar su desempeño

Compresión de Datos en Repsheet: Algoritmos y Aplicaciones

Algoritmos de Compresión de Datos: Musley y Otros Métodos Eficientes