En informática , extraer, transformar y cargar ( ETL ) es un proceso de tres fases en el que los datos se extraen de una fuente de entrada, se transforman (incluida la limpieza ) y se cargan en un contenedor de datos de salida. Los datos se pueden recopilar de una o más fuentes y también se pueden enviar a uno o más destinos. El procesamiento ETL se ejecuta normalmente mediante aplicaciones de software , pero también lo pueden realizar manualmente los operadores del sistema. El software ETL normalmente automatiza todo el proceso y se puede ejecutar de forma manual o en programaciones recurrentes, ya sea como trabajos individuales o agregados en un lote de trabajos.
Un sistema ETL correctamente diseñado extrae datos de los sistemas de origen y aplica estándares de validez y tipo de datos, y garantiza que se ajuste estructuralmente a los requisitos del resultado. Algunos sistemas ETL también pueden entregar datos en un formato listo para presentación, de modo que los desarrolladores de aplicaciones puedan crear aplicaciones y los usuarios finales puedan tomar decisiones. [1]
El proceso ETL se utiliza a menudo en el almacenamiento de datos . [2] Los sistemas ETL suelen integrar datos de múltiples aplicaciones (sistemas), normalmente desarrollados y respaldados por diferentes proveedores o alojados en hardware informático independiente. Los sistemas separados que contienen los datos originales suelen ser gestionados y operados por diferentes partes interesadas . Por ejemplo, un sistema de contabilidad de costos puede combinar datos de nómina, ventas y compras.
La extracción de datos implica extraer datos de fuentes homogéneas o heterogéneas; la transformación de datos procesa los datos limpiándolos y transformándolos en un formato/estructura de almacenamiento adecuado para fines de consulta y análisis; finalmente, la carga de datos describe la inserción de datos en la base de datos de destino final, como un almacén de datos operativo , un data mart , un lago de datos o un almacén de datos. [3] [4]
El procesamiento ETL implica la extracción de datos de los sistemas de origen. En muchos casos, esto representa el aspecto más importante de la ETL, ya que la extracción correcta de los datos establece 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 . Los formatos de origen de datos comunes 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 por medios como un rastreador web o el raspado de datos . La transmisión de la fuente de datos extraída y la carga sobre la marcha a la base de datos de destino es otra forma de realizar ETL cuando no se requiere almacenamiento de datos intermedio.
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 superan las reglas de validación, se rechazan total o parcialmente. Lo ideal es que los datos rechazados se devuelvan al sistema de origen para un análisis posterior a fin de identificar y rectificar registros incorrectos o realizar una manipulación de datos .
En la etapa de transformación de datos , se aplican una serie de reglas o funciones a los datos extraídos con el fin de prepararlos para su carga en el destino final.
Una función importante de la transformación es la limpieza de datos , cuyo objetivo es pasar únicamente datos "adecuados" al destinatario. El desafío cuando interactúan diferentes sistemas está en la interconexión y comunicación entre los sistemas relevantes. Los conjuntos de caracteres que pueden estar disponibles en un sistema pueden no estarlo en otros.
En otros casos, es posible que se requiera uno o más de los siguientes tipos de transformación para satisfacer las necesidades comerciales y técnicas del servidor o almacén de datos:
La fase de carga carga los datos en el destino final, que puede ser cualquier almacén de datos, incluido un archivo plano delimitado simple o un almacén de datos . Dependiendo de los requisitos de la organización, este proceso varía ampliamente. Algunos almacenes de datos pueden sobrescribir la información existente con información acumulativa; la actualización de los datos extraídos se realiza con frecuencia de forma diaria, semanal o mensual. Otros almacenes de datos (o incluso otras partes del mismo almacén de datos) pueden agregar nuevos datos en forma histórica a intervalos regulares, por ejemplo, cada hora. Para comprender esto, considere un almacén de datos que se requiere para mantener registros de ventas del último año. Este almacén de datos sobrescribe cualquier dato anterior a un año con datos más nuevos. Sin embargo, la entrada de datos para cualquier ventana de un año se realiza de manera histórica. El momento y el alcance para reemplazar o agregar son opciones de diseño estratégicas que dependen del tiempo disponible y las necesidades comerciales . Los sistemas más complejos pueden mantener un historial y un registro de auditoría de todos los cambios 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 activadores activados durante la carga de datos (por ejemplo, unicidad, integridad referencial , campos obligatorios), que también contribuyen al rendimiento general de la calidad de los datos del proceso ETL.
Un ciclo ETL de la vida real puede constar de pasos de ejecución adicionales, por ejemplo:
Los procesos ETL pueden implicar una complejidad considerable y pueden surgir problemas operativos importantes con sistemas ETL mal diseñados.
El rango de valores de datos o la calidad de los datos en un sistema operativo pueden exceder las expectativas de los diseñadores en el momento en que se especifican las reglas de validación y transformación. La creación de perfiles de datos de una fuente durante el análisis de datos puede identificar las condiciones de los datos que deben gestionarse mediante las especificaciones de reglas de transformación, lo que lleva a una modificación de las reglas de validación implementadas de manera explícita e implícita en el proceso ETL.
Los almacenes de datos suelen estar compuestos por una variedad de fuentes de datos con diferentes formatos y propósitos. Por ello, la extracción, transformación y carga (ETL) es un proceso clave para reunir todos los datos en un entorno estándar y homogéneo.
El análisis de diseño [5] debe establecer la escalabilidad de un sistema ETL a lo largo de su vida útil, lo que incluye comprender los volúmenes de datos que deben procesarse dentro de los acuerdos de nivel de servicio . El tiempo disponible para extraer de los sistemas de origen puede cambiar, lo que puede significar que la misma cantidad de datos puede tener que procesarse en menos tiempo. Algunos sistemas ETL tienen que escalar para procesar terabytes de datos para actualizar los 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 para la integración con colas de mensajes o captura de datos modificados en tiempo real para una transformación y actualización continuas.
Las claves únicas desempeñan un papel importante en todas las bases de datos relacionales, ya que unen todo. Una clave única es una columna que identifica una entidad determinada, mientras que una clave externa es una columna de otra tabla que hace referencia a una clave principal. Las claves pueden comprender varias columnas, en cuyo caso son claves compuestas. En muchos casos, la clave principal es un entero generado automáticamente que no tiene ningún significado para la entidad comercial que se representa, sino que existe únicamente para el propósito de la base de datos relacional; comúnmente se la conoce como clave sustituta .
Como suele haber más de una fuente de datos que se carga en el almacén, las claves son un aspecto importante que debe abordarse. Por ejemplo, los clientes pueden estar representados en varias fuentes de datos, con su número de Seguro Social como clave principal en una fuente, su número de teléfono en otra y un sustituto en la tercera. Sin embargo, un almacén de datos puede requerir la consolidación de toda la información del cliente en una dimensión .
Una forma recomendada de abordar este problema consiste en agregar una clave sustituta del almacén, que se utiliza como clave externa de la tabla de hechos. [6]
Normalmente, se producen actualizaciones en los datos de origen de una dimensión, lo que obviamente debe reflejarse en el almacén de datos.
Si la clave principal de los datos de origen es necesaria para la elaboración de informes, la dimensión ya contiene esa información para cada fila. Si los datos de origen utilizan una clave sustituta, el almacén debe realizar un seguimiento de ella aunque nunca se utilice en consultas o informes; esto se hace creando una tabla de búsqueda que contiene la clave sustituta del almacén y la clave de origen. [7] De esta manera, la dimensión no se contamina con claves sustitutas de varios sistemas de origen, mientras que se conserva la capacidad de actualización.
La tabla de búsqueda se utiliza de distintas maneras según la naturaleza de los datos de origen. Hay 5 tipos a tener en cuenta; [7] aquí se incluyen tres:
Los proveedores de ETL evalúan sus sistemas de registro a varios TB (terabytes) por hora (o ~1 GB por segundo) utilizando servidores potentes con múltiples CPU, múltiples discos duros, múltiples conexiones de red gigabit y mucha memoria.
En la vida real, 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 tienen que ocuparse de la concurrencia, el mantenimiento de la integridad y los índices. Por lo tanto, para un mejor rendimiento, puede tener sentido emplear:
Aun así, incluso con operaciones masivas, el acceso a la base de datos suele ser el cuello de botella en el proceso ETL. Algunos métodos comunes que se utilizan para aumentar el rendimiento son:
nulllos valores que pueden sesgar la partición)disable constraint...) en las tablas de la base de datos de destino durante la cargadisable trigger...) en las tablas de la base de datos de destino durante la carga: simular su efecto como un paso separadodrop index... ; create index...)La decisión de realizar determinadas operaciones en la base de datos o fuera de ella puede implicar un equilibrio. Por ejemplo, la eliminación de duplicados mediante el uso de la función distinctpuede resultar lenta en la base de datos; por lo tanto, tiene sentido realizarla fuera de ella. Por otro lado, si el uso de la función distinctreduce significativamente (x100) la cantidad de filas que se deben extraer, entonces tiene sentido 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 una gran cantidad de dependencias entre los trabajos ETL. Por ejemplo, el trabajo "B" no puede comenzar mientras el trabajo "A" no haya terminado. Por lo general, se puede lograr un mejor rendimiento visualizando todos los procesos en un gráfico e intentando reducir el gráfico haciendo el máximo uso del paralelismo y haciendo que las "cadenas" de procesamiento consecutivo sean lo más cortas posible. Nuevamente, la partición de tablas grandes y sus índices puede ser de gran ayuda.
Otro problema común ocurre cuando los datos se encuentran distribuidos entre varias bases de datos y el procesamiento se realiza en ellas de forma secuencial. En ocasiones, la replicación de bases de datos puede ser un método para copiar datos entre bases de datos, lo que puede ralentizar significativamente todo el proceso. La solución habitual es reducir el gráfico de procesamiento a solo tres capas:
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 cargar en la primera y luego replicarlas en la segunda).
A veces, 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 en paralelo . Esto permite una serie de métodos para mejorar el rendimiento general de ETL cuando se trabaja con grandes volúmenes de datos.
Las aplicaciones ETL implementan tres tipos principales de paralelismo:
Los tres tipos de paralelismo suelen operar combinados en un mismo trabajo o tarea.
Una dificultad adicional es asegurarse de que los datos que se cargan sean relativamente consistentes. Debido a que las distintas bases de datos de origen pueden tener diferentes ciclos de actualización (algunas pueden actualizarse cada pocos minutos, mientras que otras pueden tardar días o semanas), puede ser necesario un sistema ETL para retener ciertos datos hasta que todas las fuentes estén sincronizadas. Del mismo modo, cuando es necesario conciliar un almacén con el contenido de un sistema de origen o con el libro mayor, se hace necesario establecer puntos de sincronización y conciliación.
Los procedimientos de almacenamiento de datos suelen subdividir un gran proceso ETL en partes más pequeñas que se ejecutan de forma secuencial o en paralelo. Para realizar un seguimiento de los flujos de datos, tiene sentido etiquetar cada fila de datos con "row_id" y etiquetar cada parte del proceso con "run_id". En caso de fallo, disponer de estos identificadores ayuda a revertir y volver a ejecutar la parte fallida.
Las mejores prácticas también requieren puntos de control , que son estados en los que se completan determinadas fases del proceso. Una vez que se llega a un punto de control, es una buena idea escribir todo en el disco, limpiar algunos archivos temporales, registrar el estado, etc.
Un marco ETL establecido puede mejorar la conectividad y la escalabilidad . [ cita requerida ] Una buena herramienta ETL debe poder comunicarse con las diferentes bases de datos relacionales y leer los diversos formatos de archivo utilizados en una organización. Las herramientas ETL han comenzado a migrar a la integración de aplicaciones empresariales , o incluso a los sistemas de bus de servicios empresariales , que ahora cubren mucho más que solo la extracción, transformación y carga de datos. Muchos proveedores de ETL ahora tienen capacidades de perfilado de datos , calidad de datos y metadatos . Un caso de uso común para las herramientas ETL incluye la conversión de archivos CSV a formatos legibles por bases de datos relacionales. Una traducción típica de millones de registros se facilita mediante herramientas ETL que permiten a los usuarios ingresar archivos/fuentes de datos similares a CSV e importarlos a una base de datos con la menor cantidad de código posible.
Las herramientas ETL suelen ser utilizadas por una amplia gama de profesionales: desde estudiantes de informática que buscan importar rápidamente grandes conjuntos de datos hasta arquitectos de bases de datos a cargo de la gestión de cuentas de empresas. Las herramientas ETL se han convertido en una herramienta práctica en la que se puede confiar para obtener el máximo rendimiento. En la mayoría de los casos, las herramientas ETL contienen una interfaz gráfica de usuario que ayuda a los usuarios a transformar los datos de forma cómoda, utilizando un mapeador de datos visual, en lugar de tener que escribir grandes programas para analizar archivos y modificar tipos de datos.
Si bien las herramientas ETL tradicionalmente han sido para desarrolladores y personal de TI, la firma de investigación Gartner escribió que la nueva tendencia es brindar estas capacidades a los usuarios comerciales para que puedan crear conexiones e integraciones de datos por sí mismos cuando sea necesario, en lugar de recurrir al personal de TI. [8] Gartner se refiere a estos usuarios no técnicos como Integradores Ciudadanos. [9]
En las aplicaciones de procesamiento de transacciones en línea (OLTP), los cambios de 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 en un formato común y cargarlos en un lago de datos o almacén. [1]
La virtualización de datos se puede utilizar para mejorar el procesamiento ETL. La aplicación de la virtualización de datos a la ETL permitió resolver las tareas ETL más comunes de migración de datos e integración de aplicaciones para múltiples fuentes de datos dispersas. La ETL virtual opera con la representación abstracta de los objetos o entidades recopilados de la variedad de 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 de concentrador y radios ubicada centralmente . Una colección de este tipo 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 la memoria o ser persistente. Al utilizar 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 casi en tiempo real.
Extraer, cargar y transformar (ELT) es una variante de ETL en la que los datos extraídos se cargan primero en el sistema de destino. [10] La arquitectura de la canalización de análisis también debe considerar dónde limpiar y enriquecer los datos [10], así como también cómo ajustar las dimensiones. [1] Algunos de los beneficios de un proceso ELT incluyen la velocidad y la capacidad de manejar más fácilmente datos estructurados y no estructurados. [11]
El libro de Ralph Kimball y Joe Caserta The Data Warehouse ETL Toolkit (Wiley, 2004), que se utiliza como libro de texto para cursos que enseñan procesos ETL en almacenamiento de datos, abordó esta cuestión. [12]
Los almacenes de datos basados en la nube, como Amazon Redshift , Google BigQuery , Microsoft Azure Synapse Analytics y Snowflake Inc., han podido proporcionar una potencia informática altamente 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 más y almacenarse en un almacén de datos. [13]
La mayoría de las herramientas de integración de datos se inclinan por el ETL, mientras que el ELT es popular en los dispositivos de bases de datos y almacenes 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 cambios en los datos, por ejemplo, la quema de tokens) antes de extraerlos y cargarlos en otro almacén de datos. [14]