Extraer, transformar, cargar

El procesamiento ETL implica la extracción de datos del sistema o sistemas de origen. En muchos casos, este representa el aspecto más importante de ETL, ya que la extracción correcta de datos sienta las bases para el éxito de los procesos posteriores. La mayoría de los proyectos de almacenamiento de datos combinan datos de diferentes sistemas de origen. Cada sistema independiente también puede utilizar una organización y/o formato de datos diferente . ^{[ 5 ]} Los formatos comunes de origen de datos incluyen bases de datos relacionales , bases de datos de archivos planos , XML y JSON , pero también pueden incluir estructuras de bases de datos no relacionales como IBM Information Management System u otras estructuras de datos como Virtual Storage Access Method (VSAM) o Indexed Sequential Access Method (ISAM) , o incluso formatos obtenidos de fuentes externas mediante métodos como un rastreador web o el raspado de datos . ^{[ 5 ]} La transmisión del origen de datos extraído y la carga sobre la marcha en la base de datos de destino es otra forma de realizar ETL cuando no se requiere almacenamiento de datos intermedio. ^{[ 6 ]}

Una parte intrínseca de la extracción implica la validación de datos para confirmar si los datos extraídos de las fuentes tienen los valores correctos/esperados en un dominio determinado (como un patrón/valor predeterminado o una lista de valores). Si los datos no cumplen las reglas de validación, se rechazan total o parcialmente. ^{[ 7 ]} Idealmente, los datos rechazados se notifican al sistema de origen para su posterior análisis, con el fin de identificar y rectificar registros incorrectos o realizar la preparación de datos .

En la etapa de transformación de datos , se aplica una serie de reglas o funciones a los datos extraídos para prepararlos para su carga en el destino final. ^{[ 5 ] [ 6 ]}

Una función importante de la transformación es la limpieza de datos , cuyo objetivo es transferir únicamente datos "correctos" al destino. El desafío cuando diferentes sistemas interactúan radica en la interconexión y comunicación entre los sistemas relevantes. Los conjuntos de caracteres disponibles en un sistema pueden no estarlo en otros. ^{[ 8 ]}

En otros casos, puede ser necesario uno o más de los siguientes tipos de transformación para satisfacer las necesidades comerciales y técnicas del servidor o del almacén de datos: ^{[ 9 ]}

• Seleccionar solo ciertas columnas para cargar (o descartar las columnas nulas ). Por ejemplo, si los datos de origen tienen tres columnas (también llamadas "atributos"): número de matrícula, edad y salario, la selección podría tomar solo número de matrícula y salario. O bien, el mecanismo de selección podría ignorar todos los registros donde no se encuentre el salario (salario = nulo).
• Traducción de valores codificados. Por ejemplo, si el sistema de origen codifica "masculino" como "1" y "femenino" como "2", pero el almacén codifica "masculino" como "M" y "femenino" como "F".
• Codificación de valores de formato libre. Por ejemplo, asignar "Male" a "M".
• Derivando un nuevo valor calculado. Por ejemplo, sale_amount = qty * unit_price.
• Ordenar o clasificar los datos según una lista de columnas para mejorar el rendimiento de la búsqueda.
• Combinar datos de múltiples fuentes ( por ejemplo , búsqueda, fusión) y eliminar los datos duplicados.
• Agregación. Por ejemplo, consolidación: resumen de varias filas de datos: ventas totales para cada tienda, para cada región, etc.
• Generando valores de clave subrogada .
• Transposición o pivoteo (convertir varias columnas en varias filas o viceversa).
• Dividir una columna en varias columnas. Por ejemplo, convertir una lista separada por comas , especificada como una cadena de texto en una columna, en valores individuales en columnas diferentes.
• Desagrupación de columnas repetidas.
• Consultar y validar los datos relevantes en tablas o archivos de referencia.
• Aplicar cualquier forma de validación de datos. Una validación fallida puede resultar en el rechazo total, parcial o absoluto de los datos, y por lo tanto, algunos, ninguno o todos los datos se transfieren al siguiente paso según el diseño de la regla y el manejo de excepciones; muchas de las transformaciones anteriores pueden generar excepciones, por ejemplo, cuando una traducción de código analiza un código desconocido en los datos extraídos.

La fase de carga transfiere los datos al destino final, que puede ser cualquier almacén de datos, desde un simple archivo plano delimitado hasta un almacén de datos . Este proceso varía considerablemente según los requisitos de la organización. Algunos almacenes de datos pueden sobrescribir la información existente con información acumulativa; la actualización de los datos extraídos se realiza frecuentemente a diario, semanalmente o mensualmente. Otros almacenes de datos (o incluso otras partes del mismo) pueden agregar nuevos datos de forma histórica a intervalos regulares, por ejemplo, cada hora. Para comprender esto, consideremos un almacén de datos que debe mantener registros de ventas del último año. Este almacén sobrescribe cualquier dato con más de un año de antigüedad con datos más recientes. Sin embargo, la entrada de datos para cualquier período de un año se realiza de forma histórica. El momento y el alcance para reemplazar o agregar datos son decisiones estratégicas de diseño que dependen del tiempo disponible y las necesidades del negocio . Los sistemas más complejos pueden mantener un historial y un registro de auditoría de todos los cambios realizados en los datos cargados en el almacén de datos. A medida que la fase de carga interactúa con una base de datos, se aplican las restricciones definidas en el esquema de la base de datos, así como en los disparadores que se activan al cargar los datos (por ejemplo, unicidad, integridad referencial , campos obligatorios), lo que también contribuye al rendimiento general de la calidad de los datos del proceso ETL.

• Por ejemplo, una institución financiera podría tener información sobre un cliente en varios departamentos, y cada departamento podría tenerla organizada de forma diferente. El departamento de membresía podría listar al cliente por nombre, mientras que el departamento de contabilidad podría listarlo por número. ETL puede agrupar todos estos elementos de datos y consolidarlos en una presentación uniforme, por ejemplo, para almacenarlos en una base de datos o un almacén de datos.
• Otra forma en que las empresas utilizan ETL es para transferir información de forma permanente a otra aplicación. Por ejemplo, la nueva aplicación podría usar otro proveedor de bases de datos y, muy probablemente, un esquema de base de datos muy diferente. ETL se puede usar para transformar los datos a un formato compatible con la nueva aplicación.
• Un ejemplo sería un sistema de recuperación de gastos y costos como los que utilizan contadores , consultores y bufetes de abogados . Los datos generalmente se incorporan al sistema de control de tiempo y facturación , aunque algunas empresas también pueden utilizar los datos brutos para generar informes de productividad de los empleados para el departamento de Recursos Humanos o informes de uso de equipos para el departamento de Gestión de Instalaciones.

Un ciclo ETL real puede constar de pasos de ejecución adicionales, por ejemplo:

1. Inicio del ciclo (pre-ETL: Asegúrese de que todos los sistemas relevantes para ETL estén disponibles, sean funcionales y no presenten problemas que puedan detener repentinamente el proceso una hora después de iniciado, como suscripciones válidas de computación/memoria/almacenamiento B2B y claves API, un presupuesto satisfactorio para el tiempo/dinero estimado y la ausencia de tareas de mayor prioridad).
2. Crear datos de referencia (es decir, vincularlos ( no copiarlos en papel ) a bases de datos históricas fiables).
3. Extracto (de fuentes de datos)
4. Validar (garantizar que los datos puedan ser útiles en algún momento ; que los datos no sean completamente basura).
5. Transformar ( limpiar , aplicar reglas de negocio , comprobar la integridad de los datos , crear agregados o desagregaciones)
6. Etapa (cargar en tablas de preparación , si se utilizan)
7. Informes de auditoría (por ejemplo, sobre el cumplimiento de las normas empresariales. Además, en caso de fallo, ayuda a diagnosticar/reparar).
8. Publicar (en las tablas de destino)
9. Archivo (Compresión extrema y eficiente en espacio para datos antiguos que se usan muy poco. En ciertas situaciones, puede haber pérdida de datos ).

El rango de valores o la calidad de los datos en un sistema operativo puede superar las expectativas de los diseñadores al momento de especificar las reglas de validación y transformación. El análisis de datos de una fuente durante el proceso permite identificar las condiciones de los datos que deben gestionarse mediante las especificaciones de las reglas de transformación, lo que conlleva una modificación de las reglas de validación implementadas explícita e implícitamente en el proceso ETL.

Los almacenes de datos suelen estar compuestos por diversas fuentes de datos con diferentes formatos y propósitos. Por ello, el proceso ETL es fundamental para integrar todos los datos en un entorno estándar y homogéneo.

El análisis de diseño ^{[ 10 ]} debe establecer la escalabilidad de un sistema ETL a lo largo de su vida útil, incluyendo la comprensión de los volúmenes de datos que deben procesarse dentro de los acuerdos de nivel de servicio . El tiempo disponible para extraer datos de los sistemas de origen puede variar, lo que puede significar que la misma cantidad de datos deba procesarse en menos tiempo. Algunos sistemas ETL deben escalar para procesar terabytes de datos para actualizar almacenes de datos con decenas de terabytes de datos. El aumento de los volúmenes de datos puede requerir diseños que puedan escalar desde lotes diarios hasta microlotes de varios días, e integrarse con colas de mensajes o captura de cambios de datos en tiempo real para la transformación y actualización continuas.

Las claves únicas desempeñan un papel fundamental en todas las bases de datos relacionales, ya que vinculan todos los elementos. Una clave única es una columna que identifica una entidad determinada, mientras que una clave foránea es una columna en otra tabla que hace referencia a una clave primaria. Las claves pueden estar compuestas por varias columnas, en cuyo caso se denominan claves compuestas. En muchos casos, la clave primaria es un número entero generado automáticamente que no tiene significado para la entidad comercial representada, sino que existe únicamente para los fines de la base de datos relacional; comúnmente se la conoce como clave subrogada .

Dado que normalmente se cargan varias fuentes de datos en el almacén, las claves son un aspecto importante a considerar. Por ejemplo: los clientes pueden estar representados en varias fuentes de datos, con su número de la Seguridad Social como clave principal en una, su número de teléfono en otra y una clave sustituta en la tercera. Sin embargo, un almacén de datos puede requerir la consolidación de toda la información del cliente en una sola dimensión .

Una forma recomendada de abordar la preocupación consiste en agregar una clave subrogada de almacén, que se utiliza como clave foránea de la tabla de hechos. ^{[ 11 ]}

Normalmente, las actualizaciones se producen en los datos de origen de una dimensión, y deben reflejarse en el almacén de datos.

Si se requiere la clave principal de los datos de origen para generar informes, la dimensión ya contiene esa información para cada fila. Si los datos de origen utilizan una clave subrogada, el almacén de datos debe mantener un registro de ella, aunque nunca se utilice en consultas o informes; esto se logra creando una tabla de búsqueda que contiene la clave subrogada del almacén de datos y la clave original. ^{[ 12 ]} De esta manera, la dimensión no se contamina con claves subrogadas de diversos sistemas de origen, a la vez que se conserva la capacidad de actualización.

La tabla de búsqueda se utiliza de diferentes maneras según la naturaleza de los datos de origen. Hay 5 tipos a considerar; ^{[ 12 ]} aquí se incluyen tres:

Tipo 1

La fila de dimensión simplemente se actualiza para que coincida con el estado actual del sistema de origen; el almacén de datos no captura el historial; la tabla de búsqueda se utiliza para identificar la fila de dimensión que se debe actualizar o sobrescribir.

Tipo 2

Se agrega una nueva fila de dimensión con el nuevo estado del sistema de origen; se asigna una nueva clave subrogada; la clave de origen ya no es única en la tabla de búsqueda.

Registro completo

Se agrega una nueva fila de dimensión con el nuevo estado del sistema de origen, mientras que la fila de dimensión anterior se actualiza para reflejar que ya no está activa y el momento de su desactivación.

Los proveedores de ETL evalúan el rendimiento de sus sistemas de registro a varios TB (terabytes) por hora (o ~1 GB por segundo) utilizando potentes servidores con múltiples CPU, múltiples discos duros, múltiples conexiones de red Gigabit y mucha memoria.

En la práctica, la parte más lenta de un proceso ETL suele ocurrir en la fase de carga de la base de datos. Las bases de datos pueden funcionar lentamente porque deben gestionar la concurrencia, el mantenimiento de la integridad y los índices. Por lo tanto, para un mejor rendimiento, puede ser conveniente emplear:

• Utilice el método de extracción de ruta directa o la descarga masiva siempre que sea posible (en lugar de consultar la base de datos) para reducir la carga en el sistema de origen y, al mismo tiempo, obtener una extracción de alta velocidad.
• La mayor parte del procesamiento de transformación se realiza fuera de la base de datos.
• Operaciones de carga a granel siempre que sea posible

Aun así, incluso utilizando operaciones masivas, el acceso a la base de datos suele ser el cuello de botella en el proceso ETL. Algunos métodos comunes utilizados para aumentar el rendimiento son:

• Tablas de partición (e índices): intente que las particiones tengan un tamaño similar (preste atención a nulllos valores que puedan distorsionar la partición).
• Realizar todas las validaciones en la capa ETL antes de la carga: deshabilitar la comprobación de integridad ( disable constraint...) en las tablas de la base de datos de destino durante la carga.
• Deshabilitar los disparadores ( disable trigger...) en las tablas de la base de datos de destino durante la carga: simular su efecto como un paso separado.
• Generar identificadores en la capa ETL (no en la base de datos).
• Elimine los índices (en una tabla o partición) antes de la carga y vuelva a crearlos después de la carga (SQL: drop index... ; create index...).
• Utilice la carga masiva en paralelo siempre que sea posible; funciona bien cuando la tabla está particionada o no hay índices (Nota: intentar realizar cargas en paralelo en la misma tabla (partición) suele provocar bloqueos, si no en las filas de datos, en los índices).
• Si es necesario realizar inserciones, actualizaciones o eliminaciones, determine qué filas deben procesarse de qué manera en la capa ETL y, a continuación, procese estas tres operaciones en la base de datos por separado; a menudo se puede realizar una carga masiva para las inserciones, pero las actualizaciones y eliminaciones suelen realizarse a través de una API (usando SQL ).

La decisión de realizar ciertas operaciones dentro o fuera de la base de datos puede implicar una compensación. Por ejemplo, eliminar duplicados distinctpuede ser lento en la base de datos; por lo tanto, conviene hacerlo fuera. Por otro lado, si usar distinctreduce significativamente (100 veces) el número de filas a extraer, entonces conviene eliminar los duplicados lo antes posible en la base de datos antes de descargar los datos.

Una fuente común de problemas en ETL es la gran cantidad de dependencias entre las tareas. Por ejemplo, la tarea "B" no puede comenzar mientras la tarea "A" no haya terminado. Generalmente, se puede lograr un mejor rendimiento visualizando todos los procesos en un gráfico, intentando reducirlo aprovechando al máximo el paralelismo y acortando al máximo las secuencias de procesamiento consecutivo. Asimismo, la partición de tablas grandes y sus índices puede ser de gran ayuda.

Otro problema común surge cuando los datos se distribuyen entre varias bases de datos y el procesamiento se realiza secuencialmente en ellas. En ocasiones, se recurre a la replicación de bases de datos para copiar los datos entre ellas, lo que puede ralentizar considerablemente todo el proceso. La solución habitual consiste en reducir el grafo de procesamiento a solo tres capas:

• Fuentes
• Capa ETL central
• Objetivos

Este enfoque permite que el procesamiento aproveche al máximo el paralelismo. Por ejemplo, si necesita cargar datos en dos bases de datos, puede ejecutar las cargas en paralelo (en lugar de cargarlos primero en la primera y luego replicarlos en la segunda).

En ocasiones, el procesamiento debe realizarse de forma secuencial. Por ejemplo, se necesitan datos dimensionales (de referencia) antes de poder obtener y validar las filas de las tablas de "hechos" principales .

Algunas implementaciones de software ETL incluyen procesamiento paralelo . Esto permite utilizar diversos métodos para mejorar el rendimiento general de ETL al trabajar con grandes volúmenes de datos.

Las aplicaciones ETL implementan tres tipos principales de paralelismo:

• Datos: Al dividir un único archivo secuencial en archivos de datos más pequeños para proporcionar acceso paralelo.
• Pipeline : permite la ejecución simultánea de varios componentes en el mismo flujo de datos , por ejemplo, buscar un valor en el registro 1 al mismo tiempo que se agregan dos campos en el registro 2.
• Componente: La ejecución simultánea de múltiples procesos en diferentes flujos de datos en el mismo trabajo, por ejemplo, ordenar un archivo de entrada mientras se eliminan los duplicados en otro archivo.

Los tres tipos de paralelismo suelen operar de forma combinada en un mismo trabajo o tarea.

Una dificultad adicional radica en garantizar la coherencia de los datos que se cargan. Dado que las distintas bases de datos de origen pueden tener ciclos de actualización diferentes (algunas se actualizan cada pocos minutos, mientras que otras pueden tardar días o semanas), es posible que se requiera un sistema ETL para retener ciertos datos hasta que todas las fuentes estén sincronizadas. Del mismo modo, cuando un almacén de datos debe conciliarse con el contenido de un sistema de origen o con el libro mayor, resulta necesario establecer puntos de sincronización y conciliación.

Los procedimientos de almacenamiento de datos suelen subdividir un proceso ETL grande en partes más pequeñas que se ejecutan de forma secuencial o en paralelo. Para realizar un seguimiento del flujo de datos, es útil etiquetar cada fila de datos con "row_id" y cada parte del proceso con "run_id". En caso de fallo, estos identificadores permiten revertir y volver a ejecutar la parte que falló.

Las buenas prácticas también recomiendan establecer puntos de control , que son estados en los que se completan ciertas fases del proceso. Una vez en un punto de control, es buena idea guardar toda la información en el disco, eliminar algunos archivos temporales, registrar el estado, etc.

En las aplicaciones de procesamiento de transacciones en línea (OLTP), los cambios de las instancias OLTP individuales se detectan y registran en una instantánea, o lote, de actualizaciones. Se puede utilizar una instancia ETL para recopilar periódicamente todos estos lotes, transformarlos a un formato común y cargarlos en un lago de datos o almacén de datos. ^{[ 1 ]}

La virtualización de datos puede utilizarse para optimizar el procesamiento ETL. Su aplicación a ETL ha permitido resolver las tareas más comunes de migración de datos e integración de aplicaciones para múltiples fuentes de datos dispersas. El ETL virtual opera con la representación abstracta de los objetos o entidades recopilados de diversas fuentes de datos relacionales, semiestructuradas y no estructuradas . Las herramientas ETL pueden aprovechar el modelado orientado a objetos y trabajar con representaciones de entidades almacenadas de forma persistente en una arquitectura centralizada de tipo hub-and-spoke . Esta colección, que contiene representaciones de las entidades u objetos recopilados de las fuentes de datos para el procesamiento ETL, se denomina repositorio de metadatos y puede residir en memoria o ser persistente. Mediante el uso de un repositorio de metadatos persistente, las herramientas ETL pueden pasar de proyectos puntuales a middleware persistente, realizando la armonización y el perfilado de datos de forma consistente y prácticamente en tiempo real.

La extracción, carga y transformación (ELT) es una variante de ETL en la que los datos extraídos se cargan primero en el sistema de destino. ^{[ 15 ]} La arquitectura para la canalización analítica también debe considerar dónde limpiar y enriquecer los datos ^{[ 15 ]} así como cómo conformar las dimensiones. ^{[ 1 ]} Algunos de los beneficios de un proceso ELT incluyen la velocidad y la capacidad de manejar más fácilmente tanto datos no estructurados como estructurados. ^{[ 16 ]}

El libro de Ralph Kimball y Joe Caserta , The Data Warehouse ETL Toolkit (Wiley, 2004), que se utiliza como libro de texto en cursos que enseñan procesos ETL en almacenamiento de datos, abordó este tema. ^{[ 17 ]}

Los almacenes de datos en la nube, como Amazon Redshift , Google BigQuery , Microsoft Azure Synapse Analytics y Snowflake , ofrecen una gran capacidad de procesamiento escalable. Esto permite a las empresas prescindir de las transformaciones de precarga y replicar los datos sin procesar en sus almacenes de datos, donde pueden transformarlos según sea necesario mediante SQL .

Después de haber utilizado ELT, los datos pueden procesarse aún más y almacenarse en un almacén de datos. ^{[ 18 ]}

La mayoría de las herramientas de integración de datos se inclinan hacia ETL, mientras que ELT es popular en dispositivos de bases de datos y almacenamiento de datos. De manera similar, es posible realizar TEL (Transformar, Extraer, Cargar), donde los datos se transforman primero en una cadena de bloques (como una forma de registrar los cambios en los datos, por ejemplo, la quema de tokens) antes de extraerlos y cargarlos en otro almacén de datos. ^{[ 19 ]}