Análisis de desempeño con diferentes tipos de compresión

Al momento de crear el cluster, seleccione la versión Free Trial que otorga un nodo con 2 Slices, viendo que los datos de la columna venueseats de la tabla de tabla cartesian_venue tiene alrededor de mas de 30 millones de registros numéricos se creo una tabla con las codificaciones para este tipo de datos numéricos

la codificación lzo para los datos de la columna es la que ocupa menos bloques por slice, pero analizando la compresión de redshift para este tipo de datos de esta columna recomienda que la mejor compresión es zstd

y comparando el resultado entre ambas, disminuye en un bloque menos por slice en comparación a la compresión lzo, por lo que analyse compression es una herramienta bastante útil para optimizar

Les dejo mi solución al reto de la clase, decidí hacer desde cero la tabla cartesiana con solamente venueseats para practicar todo el proceso. Estoy pendiente a sus comentarios 😊

--RETO Venue Seats

--Creamos la tabla cartesiana
create  table cartesian_venueseats as (
select venueseats
from venue, listing);

--Creamos la lista con los 10 métodos de encodings
create table encoding_venueseats (
seatsraw integer ENCODE raw,
seatsaz64 integer ENCODE az64,
seatsbytedict integer ENCODE bytedict,
seatsdelta integer ENCODE delta,
seatsdelta32k integer ENCODE delta32k,
seatslzo integer ENCODE lzo,
seatsmostly8 integer ENCODE mostly8,
seatsmostly16 integer ENCODE mostly16,
seatsmostly32 bigint ENCODE mostly32,
seatsrunlength integer ENCODE runlength,
seatszstd integer ENCODE zstd
);

insert into encoding_venueseats
(
select venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats
from cartesian_venueseats
);

--Comprobamos que se insertaran nuestros datos
select * from encoding_venueseats limit 10;

--Buscamos el ID de la tabla, en mi caso es 101656
select * from stv_tbl_perm 
where name = 'encoding_venueseats';

--Agrupamos los bloqueses de cada método
select col, max(blocknum) from pg_catalog.stv_blocklist
where tbl = 101656
and col <= 10 
group by col;
--En mi análisis ganan zstd y runlength con un peso de 1

--comparamos nuestro análisis con el de redshift
analyze compression cartesian_venueseats;
--Nos recomienda zstd con una compresión esperada del 98,99%
--Tiembla Pied Piper! xD 

-- Crear una tabla con datos de venueseats de la tabla venue y listing
CREATE TABLE cartesian_venueseats AS (
    SELECT venueseats
    FROM venue, listing
);

-- Crear una tabla para encoding de venueseats
CREATE TABLE encoding_venueseats AS (
    SELECT
        seatsraw AS integer raw,
        seatsaz64 AS integer ENCODE az64,
        seatsbytedict AS integer ENCODE bytedict,
        seatsdelta AS integer ENCODE delta,
        seatsdelta32k AS integer ENCODE delta32k,
        seatslzo AS integer ENCODE lzo,
        seatsmostly8 AS integer ENCODE mostly8,
        seatsmostly16 AS integer ENCODE mostly16,
        seatsmostly32 AS bigint ENCODE mostly32,
        seatsrunlength AS integer ENCODE runlength,
        seatszstd AS integer ENCODE zstd
    FROM cartesian_venueseats
);

-- Mostrar la suma de los valores de seatsraw en encoding_venueseats
SELECT 'Suma de seatsraw:' AS descripcion, SUM(seatsraw) AS total
FROM encoding_venueseats;

-- Buscar en stv_tbl_perm
WITH tbl_info AS (
    SELECT oid FROM pg_tables WHERE tablename = 'encoding_venueseats'
)
SELECT col, MAX(blocknum)
FROM pg_catalog.stv_blocklist
WHERE tbl = (SELECT oid FROM tbl_info)
  AND col <= 0
GROUP BY col;

-- Realizar análisis de compresión en encoding_venueseats
ANALYZE COMPRESSION encoding_venueseats;

-- Agregar una columna calculada con el promedio de seatsraw
ALTER TABLE encoding_venueseats
    ADD COLUMN seatsraw_avg double precision;

```aca resolviendo el ejercicio en el 2023,ademas le agregue otra detalles, feliz con el curso

Este curso es una joya !!!

-- Homework
select distinct venueseats from venue;

create table encoding_venue_seats (
	venue_seats_raw integer encode raw,
	venue_seats_az64 integer encode az64,
	venue_seats_bytedict integer encode bytedict,
	venue_seats_lzo integer encode lzo,
	venue_seats_runlength integer encode runlength,
	venue_seats_mostly8 integer encode mostly8,
	venue_seats_mostly16 integer encode mostly16,
	venue_seats_mostly32 bigint encode mostly32,
	venue_seats_zstd integer encode zstd
)

insert into encoding_venue_seats (
	select venueseats, venueseats, venueseats, venueseats, 
			venueseats, venueseats, venueseats, venueseats,
			venueseats
	from cartesian_venue
)

select * from encoding_venue_seats limit 20;
select count(0) from encoding_venue_seats ;
-- Review the encoding size

-- Check identifier of the table - 110055
select * from stv_tbl_perm where name = 'encoding_venue_seats';

select col, max(blocknum) 
from pg_catalog.stv_blocklist 
where tbl = 110055 and col <= 8
group by col;


analyze compression cartesian_venue;

-- Result zstd is the most properly algorithm to compress seats data

-- Homework
select distinct venueseats from venue;

create table encoding_venue_seats (
	venue_seats_raw integer encode raw,
	venue_seats_az64 integer encode az64,
	venue_seats_bytedict integer encode bytedict,
	venue_seats_lzo integer encode lzo,
	venue_seats_runlength integer encode runlength,
	venue_seats_mostly8 integer encode mostly8,
	venue_seats_mostly16 integer encode mostly16,
	venue_seats_mostly32 bigint encode mostly32,
	venue_seats_zstd integer encode zstd
)

insert into encoding_venue_seats (
	select venueseats, venueseats, venueseats, venueseats, 
			venueseats, venueseats, venueseats, venueseats,
			venueseats
	from cartesian_venue
)

select * from encoding_venue_seats limit 20;
select count(0) from encoding_venue_seats ;
-- Review the encoding size

-- Check identifier of the table - 110055
select * from stv_tbl_perm where name = 'encoding_venue_seats';

select col, max(blocknum) 
from pg_catalog.stv_blocklist 
where tbl = 110055 and col <= 8
group by col;


analyze compression cartesian_venue;

-- Result zstd is the most properly algorithm

Me encantó la referencia a Sillicon Valley, ya con eso no se me olvida xD

Hola me volvio a ganar Zstandard 🤔

--RETO

SELECT venueseats
FROM venue
LIMIT 10;

SELECT venueseats ,count(0)
FROM venue
GROUP BY venueseats;

CREATE TABLE encoding_seats (
seatsraw integer encode raw,
seatsaz64 integer encode az64,
seatsbytedict integer encode bytedict,
seatsdelta int encode delta,
seatsdelta32k int encode delta32k,
seatslzo integer encode lzo,
seatsmostly8 int encode mostly8,
seatsmostly16 int encode mostly16,
seatsmostly32 bigint encode mostly32,
seatsrunlength integer encode runlength ,
seatszstd integer encode zstd
);

INSERT INTO encoding_seats 
(
SELECT venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats
FROM cartesian_venue 
);

SELECT *
FROM encoding_seats
LIMIT 10;

SELECT * 
FROM pg_catalog.stv_tbl_perm --101.540
WHERE name = 'encoding_seats';

SELECT *
FROM pg_catalog.stv_blocklist sb 
LIMIT 10;

SELECT col, max(blocknum)
FROM pg_catalog.stv_blocklist sb 
WHERE tbl = 101540 
AND col <= 11
GROUP BY col;

SELECT *
FROM pg_catalog.stv_blocklist sb --273851
WHERE tbl = 101540 
AND col = 0;

SELECT *
FROM pg_catalog.stv_blocklist sb --19'442.197
WHERE tbl = 101540 
AND col = 11;

ANALYZE compression cartesian_venue;

me base en esta tabla:
tabla de amazon

estos fueron los resultados:
11 0
10 3
5 4
2 19
9 34
6 40
1 40
3 45
4 45
7 51
0 70
8 73

Estoy interpretando mal, ¿Que consejos me dan?

Resultados de compresión de la columna venueseats de la tabla venue:

Ambiente Redshift:

• 1 nodo
• Espacio de almacenamiento 160 GB

Tipo de dato de columna: integer (4 bytes)
Compresión óptima: zstd
Números de blues sin comprmir: 142
Número de bloques comprimido: 8

Imagen en el enlace: Resultado compresión redshift

Distribución de una tabla en los slices de los nodos definidos

Este es el resultado del reto

--Creando la tabla con los encoders
create table encoding_seats(
	seatsraw integer encode raw,
	seatsaz64 integer encode az64,
	seatsbytedict integer encode bytedict,
	seatsdelta int encode delta,
	seatsdelta32k int encode delta32k,
	seatsmostly8 int encode mostly8,
	seatsmostly16 int encode mostly16,
	seatszstd integer encode zstd
)

--Insertando los datos a la tabla
insert into encoding_seats(
	select venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats, venueseats
	from cartesian_venue
);

--Obtener el número de la tabla
select * from stv_tbl_perm
where name = 'encoding_seats'; 

--Haciendo un test para acceder a la tabla
select * from pg_catalog.stv_blocklist 
where tbl = 109836;

--Conociendo el numero de bloques por cada columna de la tabla
select col,max(blocknum) from pg_catalog.stv_blocklist 
where tbl = 109836
and col <= 7
group by col;

¡¡Mas que excelente Curso!!
Tengo una duda , cuando hacemos la compresión hay riesgo de perdida de datos o que el dato pueda tener algún cambio en su estructura al momento de exportarlo a un archivo?

CREATE table encoding_seats(
	seatsraw integer encode raw,
	seatsaz64 integer encode az64,
	seatsdelta integer encode delta,
	seatsdelta32k integer encode delta32k,
	seatslzo integer encode lzo,
	seatszstd integer encode zstd,
	seatsmostly8 integer encode mostly8,
	seatsmostly16 integer encode MOSTLY16,
	seatsmostly32 bigint encode MOSTLY32
);

0 70
1 40
2 45
3 45
4 4 --> lzo
5 3 --> zstd
6 40
7 51
8 73

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

SELECT * FROM encoding_venue limit 10;
SELECT * FROM stv_tbl_perm
WHERE name = 'encodig_venue';--101395

SELECT col,max(blocknum) FROM pg_catalog.stv_blocklist
WHERE tlb = 101395
AND col <= 6
GROUP BY col ;```

Código de creación:

CREATE table encoding_venueseats(
seatsraw integer ENCODE raw,
seatsaz64 integer ENCODE az64,
seatsbytedict integer ENCODE bytedict,
seatsdelta integer ENCODE delta,
seatsdelta32k integer ENCODE delta32k,
seatslzo integer ENCODE lzo,
seatsmostly8 integer ENCODE mostly8,
seatsmostly16 integer ENCODE mostly16,
seatsmostly32 bigint ENCODE mostly32,
seatsrunlength integer ENCODE runlength,
seatszstd integer ENCODE zstd);

INSERT into encoding_venueseats 
(
SELECT venueseats, venueseats, venueseats, venueseats, venueseats, venueseats,
	   venueseats, venueseats, venueseats, venueseats, venueseats
FROM cartesian_venue 
);

y resultados:

0	70 raw
1	40 az64
2	19 bytedict
3	45 delta
4	45 delta32k
5	4 lzo
6	40 mostly8
7	51 mostly16
8	73 mostly32
9	34 runlength
10	3 zstd

analyze compression

Realiza un análisis de compresión y produce un informe con la codificación de compresión sugerida para las tablas analizadas. Para cada columna, el informe incluye un cálculo de la reducción potencial de espacio en disco en comparación con la codificación actual.

interesante