¿Cómo optimizar la integridad referencial en un data warehouse?
La creación de un data warehouse eficiente y efectivo requiere más que adquirir técnicas avanzadas. La integridad referencial y las restricciones adecuadas son fundamentales para asegurar que la información guardada tenga coherencia y consistencia. En este sentido, las llaves primarias y foráneas resultan esenciales. Una llave primaria garantiza que ningún dato se repita en una fila; por su parte, una llave foránea establece vínculos restrictivos. Esto asegura, por ejemplo, que no puedas asignar un país inventado a una persona si dicho país no existe en la tabla de países. Estas restricciones no solo mantienen la integridad de los datos, sino que mejoran el rendimiento de las consultas al aprovechar el plano de ejecución (Xplain plan). Así que, siempre que sea posible, implementa estas herramientas en tu data warehouse para asegurar un mejor rendimiento en las consultas.
¿Por qué es importante unificar formatos de fechas en bases de datos?
Interactuar con campos de tipo fecha puede ser complicado. La data errónea y variaciones en el formato presentan desafíos constantes para quienes trabajan en ciencia de datos. Sin embargo, estandarizar estos formatos es vital. Transformar las fechas para almacenarlas como tipo date o timestamp facilita su manipulación y asegura que estén optimizadas para las operaciones necesarias. Date cuenta de que integrar un formato unificado para las fechas impulsa tanto el almacenamiento eficaz como el acceso rápido a la información necesaria.
¿Cómo afecta el tamaño de los datos a la compresión de bases de datos?
Tradicionalmente, establecer una longitud fija para columnas aseguraba un menor uso de bytes, fundamental dado el alto costo de almacenamiento. Hoy, esta práctica ha cambiado con la evolución de tecnologías y reducción de costos. No obstante, definir el tamaño adecuado para los datos sigue siendo crucial, especialmente para algoritmos de compresión como Mustley. La precisión en el tipo de dato y su longitud facilita una mejor compresión y optimiza el uso de almacenamiento, lo cual es esencial en ambientes como Repsheat.
¿Cuál es la clave para el diseño eficiente de un diccionario de datos?
Un diccionario de datos bien diseñado es indispensable para la legibilidad y el mantenimiento de bases de datos. Añadir comentarios detallados a las tablas y columnas sobre su uso y propósito mejora la comprensión del modelo de datos, no solo para ti, sino también para cualquier persona que lo aborde en el futuro. Un diccionario claro y comprensible previene malentendidos, facilita modificaciones futuras, y tiene un impacto inesperado pero sumamente positivo en la productividad y eficiencia del equipo.
¿Cómo evaluar el rendimiento de tablas en Repsheat?
Repsheat ofrece herramientas precisas para medir el rendimiento de las tablas. Usando Queries documentadas por la plataforma, puedes obtener un análisis detallado de cada tabla: su ID, tamaño en megabytes, y detalles críticos como si cuentan con llave de distribución o columnes ordenadas y comprimidas. Este examen revela si hay sesgo en la distribución de los datos y proporciona un porcentaje de llenado de datos, lo que es crucial para entender cómo se distribuye la información en tus tablas. Cuanto más altos sean estos valores, más eficaz será la distribución de los datos por segmentos, algo esencial en un entorno de data warehouse.
-- Consulta para evaluar diseño de tablas en RepsheatSELECT tabla_id, tamaño_mb, llave_distribucion, columnas_ordenamiento, columnas_comprimidas, sesgo_datos, porcentaje_lleno
FROM tablas_repsheet;
Aprovechar estas funcionalidades y consejos puede marcar la diferencia al diseñar y gestionar bases de datos complejas. Implementa estas buenas prácticas y evalúa el rendimiento constantemente sobre la base de herramientas integradas. Esta dedicación se traduce en una base de datos no solo eficiente, sino también escalable a largo plazo. Si estás en camino de convertirte en un especialista en data warehousing, integrar estas lecciones te impulsará hacia el éxito. ¡Sigue aprendiendo y perfeccionando tus habilidades!
select
schema schemaname,"table" tablename, table_id tableid, size size_in_mb,case when diststyle not in('EVEN','ALL') then 1else0 end has_dist_key,case when sortkey1 is not null then 1else0 end has_sort_key,case when encoded ='Y' then 1else0 end has_col_encoding,cast(max_blocks_per_slice - min_blocks_per_slice asFLOAT)/greatest(NVL(min_blocks_per_slice,0)::int,1) ratio_skew_across_slices,cast(100* dist_slice asFLOAT)/( select
COUNT(distinct slice)from stv_slices) pct_slices_populated
from svv_table_info ti
join( select
tbl,MIN(c) min_blocks_per_slice,MAX(c) max_blocks_per_slice,COUNT(distinct slice) dist_slice
from( select
b.tbl, b.slice,COUNT(*)as c
fromSTV_BLOCKLIST b
group by
b.tbl, b.slice) where
tbl in( selectselect
schema schemaname,"table" tablename, table_id tableid, size size_in_mb,case when diststyle not in('EVEN','ALL') then 1else0 end has_dist_key,case when sortkey1 is not null then 1else0 end has_sort_key,case when encoded ='Y' then 1else0 end has_col_encoding,cast(max_blocks_per_slice - min_blocks_per_slice asFLOAT)/greatest(NVL(min_blocks_per_slice,0)::int,1) ratio_skew_across_slices,cast(100* dist_slice asFLOAT)/( select
COUNT(distinct slice)from stv_slices) pct_slices_populated
from svv_table_info ti
join( select
tbl,MIN(c) min_blocks_per_slice,MAX(c) max_blocks_per_slice,COUNT(distinct slice) dist_slice
from( select
b.tbl, b.slice,COUNT(*)as c
fromSTV_BLOCKLIST b
group by
b.tbl, b.slice) where
tbl in( select
table_id
from svv_table_info) group by
tbl) iq on
iq.tbl= ti.table_id; table_id
from svv_table_info) group by
tbl) iq on
iq.tbl= ti.table_id;
Nova versão da query, a postada acima não estava funcionando:
SELECT SCHEMA schemaname,
"table" tablename,
table_id tableid,
size size_in_mb,
CASE
WHEN diststyle ilike 'KEY%' THEN 1
ELSE 0
END has_dist_key,
CASE
WHEN sortkey1 IS NOT NULL THEN 1
ELSE 0
END has_sort_key,
CASE
WHEN encoded = 'Y' THEN 1
ELSE 0
END has_col_encoding,
ROUND(100CAST(max_blocks_per_slice - min_blocks_per_slice AS FLOAT) / GREATEST(NVL (min_blocks_per_slice,0)::int,1),2) ratio_skew_across_slices,
ROUND(CAST(100dist_slice AS FLOAT) /(SELECT COUNT(DISTINCT slice) FROM stv_slices where type = 'D'),2) pct_slices_populated
FROM svv_table_info ti
JOIN (SELECT tbl,
MIN(c) min_blocks_per_slice,
MAX(c) max_blocks_per_slice,
COUNT(DISTINCT slice) dist_slice
FROM (SELECT b.tbl,
b.slice,
COUNT(*) AS c
FROM STV_BLOCKLIST b
GROUP BY b.tbl,
b.slice)
WHERE tbl IN (SELECT table_id FROM svv_table_info)
GROUP BY tbl) iq ON iq.tbl = ti.table_id
ORDER BY SCHEMA, "table";
Muy interesante hasta ahora, aunque me ha costado entender del todo varios puntos. No descarto tomar el curso una segunda vez cuando lo termine.
Profe muchisimas gracias por este curso, estoy aplicando todo lo que me indicas para llevar buenas prácticas en mi empresa. Un saludo
me alegro mucho, espero puedas mejorar el poder analítico en tu empresa!!
Buenas Prácticas para Diseñar Tablas en Amazon Redshift
Diseñar tablas en Amazon Redshift implica optimizar tanto el almacenamiento como el rendimiento de las consultas. Esto requiere una planificación cuidadosa en cuanto a la distribución de datos, el ordenamiento, la compresión y otros aspectos clave. A continuación, se presentan las mejores prácticas para maximizar la eficiencia.
1. Selección del Estilo de Distribución
El estilo de distribución define cómo se almacenan los datos en los nodos del clúster. Una distribución adecuada minimiza el movimiento de datos entre nodos durante las consultas.
a. KEY
Usa una columna común en las consultas para distribuir los datos.
Ideal para tablas con combinaciones frecuentes.
Ejemplo:CREATE TABLE ventas (
venta_id INT,
cliente_id INT,
fecha DATE
)
DISTKEY (cliente_id);
b. ALL
Replica la tabla en todos los nodos.
Útil para tablas pequeñas utilizadas en muchas combinaciones.
Ideal para tablas grandes con accesos no predecibles.
Ejemplo:CREATE TABLE logs (
log_id INT,
mensaje VARCHAR(255),
fecha DATE
)
DISTSTYLE EVEN;
2. Definición de Llaves de Ordenamiento (SORTKEY)
Las llaves de ordenamiento optimizan el escaneo de datos en disco, reduciendo el tiempo de ejecución de consultas.
a. Llave Compuesta (COMPOUND)
Ordena físicamente por las columnas definidas, en el orden especificado.
Útil para consultas que filtran por la primera columna.
Ejemplo:CREATE TABLE ventas (
venta_id INT,
fecha DATE,
cliente_id INT
)
COMPOUND SORTKEY (fecha, cliente_id);
b. Llave Entrelazada (INTERLEAVED)
Distribuye el ordenamiento entre varias columnas.
Ideal para consultas con filtros en cualquier combinación de columnas.
Ejemplo:CREATE TABLE ventas (
venta_id INT,
fecha DATE,
cliente_id INT
)
INTERLEAVED SORTKEY (fecha, cliente_id);
3. Uso de Compresión (ENCODE)
Aplica compresión para reducir el espacio en disco y mejorar el rendimiento. Redshift selecciona automáticamente el mejor algoritmo al analizar los datos.
a. Compresión Manual
Especifica el algoritmo si conoces el tipo de datos.
Ejemplo:CREATE TABLE ventas (
venta_id INT ENCODE delta,
fecha DATE ENCODE lzo,
cliente_id INT ENCODE zstd
);
b. Analiza y Optimiza
Usa el comando ANALYZE COMPRESSION para determinar los mejores algoritmos.ANALYZE COMPRESSION ventas;
4. Partición de Tablas Grandes
Divide tablas muy grandes en subconjuntos manejables mediante claves de partición lógica, como fechas.
Ejemplo:
Crear tablas mensuales basadas en la fecha.
CREATE TABLE ventas_2025_01 (
venta_id INT,
fecha DATE,
cliente_id INT
)
DISTKEY (cliente_id)
SORTKEY (fecha);
5. Mantenimiento Regular
a. VACUUM
Reorganiza datos fragmentados y mejora la velocidad de lectura.VACUUM FULL ventas;
b. ANALYZE
Actualiza las estadísticas de la tabla para optimizar el planificador de consultas.ANALYZE ventas;
c. DEEP COPY
Realiza una copia profunda de tablas críticas para reorganizar los datos.CREATE TABLE ventas_copy AS SELECT * FROM ventas;
DROP TABLE ventas;
ALTER TABLE ventas_copy RENAME TO ventas;
6. Índices y Optimización de Consultas
Aunque Redshift no utiliza índices tradicionales, puedes optimizar las consultas con:
Llaves de ordenamiento: Para reducir el escaneo de datos.
Proyección de columnas: Selecciona solo las columnas necesarias.SELECT cliente_id, fecha FROM ventas WHERE fecha = '2025-01-01';
7. Evitar Buenas Prácticas Comunes en Bases de Datos Relacionales
Redshift no es una base de datos relacional tradicional; evita:
Uso excesivo de normalización: Prioriza tablas desnormalizadas para evitar combinaciones costosas.
Inserciones individuales frecuentes: Utiliza cargas masivas con COPY.COPY ventas
FROM 's3://mi-bucket/ventas.csv'
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole'
CSV;
8. Uso de Monitoreo y Ajuste
Monitorea las consultas para identificar cuellos de botella.
**Consultas más lentas:**SELECT query, starttime, endtime, substring
FROM svl_qlog
WHERE aborted = 0
ORDER BY endtime - starttime DESC
LIMIT 10;
**Tablas más usadas:**SELECT tbl, sum(scan_count) AS scans
FROM svv_table_info
GROUP BY tbl
ORDER BY scans DESC;
Conclusión
El diseño eficiente de tablas en Redshift requiere una planificación que tenga en cuenta la carga de trabajo y los patrones de consulta. Aplicando estas buenas prácticas, puedes garantizar un rendimiento óptimo y una experiencia más fluida con Redshift.
En Redshift es más eficiente hacer cruces de tablas históricas(filtrando por el mes de análisis) que el mismo cruce por una tabla que sólo tenga la información del mes de análisis?
Depende mucho de como vas a filtrar la tabla para consultarla, en lo personal si tengo una tabla con columnas de negocio ya definidas previamente para lo que requiero, solo la dejo general con una columna de tiempo y con sort_key por esa columna, para filtrar por fechas facilmente y de manera veloz.
El colocar constraints (PK y FK) actualmente en Redshift (2021) veo que no impide esa restricción de autenticidad en los registros, es sólo representativo el añadírselos...¿aún así se considera buena práctica o recomendable?
es recomendable solo si estamos en un modelo dimensional, estrella o copo de nieve, de ser mas un datalake no hay necesidad
Tengo una duda, al momento que ingreso al link me envía a otra pagina supongo que cambio la documentación link, sin embargo ejecute el codigo pero me aparece el siguiente error:
SQL Error [500310] [42601]: Amazon Invalid operation: syntax error at or near "schema" Position: 864;
En la que considero es la nueva documentación aparece otro código dejo el link, lo ejecute una vez y perfecto, pero no me dejo ver bien puesto que es una vista.
Entonces ¿El código de la clase continua trabajando si es así que parte tengo que revisar?
¿El link de lo que llamo "nueva documentación" es correcto o me estoy equivocando?
Que grande el profe contestando estás preguntas, bastante tiempo después de que salió el curso y que ResShift se ha actualizado.
Muchas gracias
que tiene mejor rendimiento en Redshift ¿una tabla con muchas columnas o muchas tablas que tengan una columna en común?
depende de las caracteristicas que tengas y como la piensas manejar... si puedes dividir las tablas por una representacion especifica de negocio y sus respectivas columnas es mejor asi, porque puedes darle compresion o ordenamientos y distribución a esa tabla sin afectar el resto y consultarla aparte, en mi caso tengo una tabla por cada evento en platzi que ocurre en la pagina, y si solo me interesa ese evento, voy a esa tabla en especifico, pero ya teniendo esa tabla no se justifica crear tablas aparte por cada año o mes, sino en esa misma tabla tener toda la historia.
Es bueno resaltar que redshift permite definir restricciones más son solo informativas:
"Las restricciones de unicidad, clave principal y clave externa son solo informativas, Amazon Redshift no las aplica al rellenar una tabla. Por ejemplo, si inserta datos en una tabla con dependencias, la inserción puede realizarse correctamente aunque infrinja la restricción. No obstante, las claves principales y externas se utilizan como sugerencias de planificación y deben estar declaradas si el proceso de Extract, Transform, Load (ETL, Extracción, transformación y carga) o algún otro proceso de su aplicación exige su integridad."
