Cargar CSVs en la tabla Redshift, donde algunos CSVs carecen de algunas columnas.
Objetivo
Cargar todos los datos de archivos CSV en un bucket de S3 en una tabla de Redshift.
Problema
Algunos archivos carecen de un subconjunto de columnas.
Ejemplo
En el mundo real, mi bucket recibe nuevos CSVs diariamente, pero considera este ejemplo más simple.
Supongamos que tengo una tabla fruit:
| id | name | val1 | val2 |
|---|---|---|---|
| INTEGER | VARCHAR | VARCHAR | INTEGER |
Supongamos que tengo 2 CSVs.
test1.csv:
| id | name | val1 | val2 |
|---|---|---|---|
| 1 | manzana | abc | 123 |
| 2 | naranja | def | 456 |
test2.csv:
| id | name | val2 |
|---|---|---|
| 3 | plátano | 421 |
| 4 | pera | 68 |
Nótese que el archivo test2.csv carece de la columna val1.
Pregunta
¿Existe alguna forma de cargar arbitrariamente muchos CSVs en una tabla de Redshift, donde algunos archivos pueden carecer de cualquier subconjunto de columnas (que no sean clave primaria), utilizando la funcionalidad nativa de Redshift y SQL? ¿Alguno(s) de los otros servicios de AWS puede(n) completar la tarea? ¿O debo recurrir a Python en este punto? ¿Es este un trabajo para psycopg2 y/o SQLAlchemy?
Lo que he leído
Usaría COPY, pero según la documentación parece que no admite mi escenario. Una lista de columnas debe coincidir con el orden de los nombres de columna en los datos de origen, pero si falta alguna columna, entonces ese orden no es constante, por lo que creo que esta funcionalidad no puede ayudarme. Las columnas faltantes no siempre están al final del conjunto de datos, por lo que creo que el parámetro FILLRECORD de COPY no me ayudará.
La idea de crear una tabla temporal o externa es interesante, pero no estoy seguro si admite mi escenario. ¿No me encontraría con los mismos problemas?
Actualización: Soluciones que encontré
La solución más simple utilizando AWS es AppFlow. Confirmé que cargará datos desde un CSV incluso si las columnas definidas en la tabla de destino están ausentes en un archivo de origen. Establecí los valores predeterminados como NULL al crear la tabla en Redshift.
Si se desea hacer algo más complicado con AWS Glue y Python, confirmé que Pandas o SQLAlchemy cargarán CSVs con columnas faltantes. AWS Data Wrangler también debería poder hacerlo, pero no lo he probado.
davy.ai
Solución para cargar CSVs con columnas faltantes en Redshift
Objetivo
El objetivo es cargar datos de archivos CSV en un bucket de S3 en una tabla de Redshift, incluso si algunos archivos carecen de un subconjunto de columnas.
Problema
La funcionalidad
COPYde Redshift no admite mi escenario, ya que la lista de columnas debe coincidir con el orden de los nombres de columna en los datos fuente. Además, las columnas faltantes no siempre están al final del conjunto de datos, por lo que el parámetroFILLRECORDdeCOPYno ayudará.Solución
Existen algunas soluciones a este problema utilizando servicios de AWS:
NULLal crear la tabla en Redshift.AWS Glue: AWS Glue es una solución más complicada que implica el uso de Python, Pandas o SQLAlchemy para cargar CSVs con columnas faltantes. AWS Glue tiene un potente motor ETL (extracción, transformación, carga) que puede analizar los archivos CSV y asignarlos a las columnas de la tabla de destino. Esta solución requiere una configuración más completa, pero ofrece más control sobre el procesamiento de datos.
AWS Data Wrangler: AWS Data Wrangler es otra opción que ofrece funcionalidad similar a AWS Glue pero requiere menos configuración. Puede cargar CSVs con columnas faltantes directamente en Redshift y también ofrece funcionalidad adicional para la limpieza y procesamiento de datos.
Conclusión
AppFlow es la solución más sencilla para cargar CSVs con columnas faltantes en Redshift, mientras que AWS Glue y AWS Data Wrangler ofrecen más control y funcionalidad pero requieren una configuración más completa. Todas estas soluciones pueden manejar arbitrariamente muchos CSVs y admiten la carga de datos desde buckets de S3, lo que las hace ideales para manejar tuberías de datos a gran escala.