En informática , extraer, transformar, 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 normalmente se ejecuta mediante aplicaciones de software , pero los operadores del sistema también pueden realizarlo manualmente . El software ETL normalmente automatiza todo el proceso y puede ejecutarse manualmente o en programas recurrentes, ya sea como trabajos individuales o agregados en un lote de trabajos.
Un sistema ETL diseñado correctamente extrae datos de los sistemas fuente y aplica estándares de tipo y validez 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 para 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 comúnmente integran datos de múltiples aplicaciones (sistemas), generalmente desarrollados y respaldados por diferentes proveedores o alojados en hardware informático separado. Los sistemas separados que contienen los datos originales frecuentemente son administrados 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 datos limpiándolos y transformándolos en un formato/estructura de almacenamiento adecuado con 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 operativos , un mercado de datos , un lago de datos o un almacén de datos. [3] [4]
El procesamiento ETL implica extraer los datos de los sistemas de origen. En muchos casos, esto 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 fuente. Cada sistema independiente también puede utilizar una organización y/o formato de datos diferente . Los formatos de fuentes 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 el método de acceso al almacenamiento virtual (VSAM) o el secuencial indexado. Método de acceso (ISAM) , o incluso formatos obtenidos de fuentes externas mediante medios como un rastreador web o extracción de datos . La transmisión por secuencias de la fuente de datos extraída 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.
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/predeterminado o una lista de valores). Si los datos no cumplen las reglas de validación, se rechazan total o parcialmente. Lo ideal es que los datos rechazados se devuelvan al sistema de origen para su posterior análisis 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 para 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 sólo datos "adecuados" al objetivo. El desafío cuando interactúan diferentes sistemas está en la interfaz y comunicación de 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 técnicas y comerciales 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 simple archivo plano delimitado 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 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 entender 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 de más de un año con datos más nuevos. Sin embargo, el ingreso 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 de las necesidades comerciales . Los sistemas más complejos pueden mantener un historial y un seguimiento 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.
El ciclo ETL típico de la vida real consta de los siguientes pasos de ejecución:
Los procesos ETL pueden implicar una complejidad considerable y pueden ocurrir problemas operativos importantes con sistemas ETL diseñados incorrectamente.
El rango de valores de datos o la calidad de los datos en un sistema operativo puede exceder las expectativas de los diseñadores en el momento en que se especifican las reglas de validación y transformación. El perfilado de datos de una fuente durante el análisis de datos puede identificar las condiciones de los datos que deben gestionarse mediante especificaciones de reglas de transformación, lo que lleva a una modificación de las reglas de validación implementadas explícita e implícitamente en el proceso ETL.
Los almacenes de datos normalmente se ensamblan a partir de una variedad de fuentes de datos con diferentes formatos y propósitos. Como tal, 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, incluida 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 de los sistemas de origen puede cambiar, lo que puede significar que la misma cantidad de datos deba procesarse en menos tiempo. Algunos sistemas ETL tienen que escalar para procesar terabytes de datos y actualizar almacenes de datos con decenas de terabytes de datos. Los volúmenes crecientes de datos pueden requerir diseños que puedan escalar desde lotes diarios hasta micro lotes de varios días hasta la integración con colas de mensajes o la captura de datos modificados en tiempo real para una transformación y actualización continuas.
Los proveedores de ETL comparan sus sistemas de registro con 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 cuidar la concurrencia, el mantenimiento de la integridad y los índices. Por lo tanto, para un mejor rendimiento, puede tener sentido emplear:
Aún 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:
nulllos valores que pueden sesgar la partición)disable constraint...) en las tablas de la base de datos de destino durante la carga.disable trigger...) en las tablas de la base de datos de destino durante la carga: simule su efecto como un paso separadodrop index... ; create index...)Realizar ciertas operaciones en la base de datos o fuera de ella puede implicar una compensación. Por ejemplo, eliminar duplicados usando distinctpuede ser lento en la base de datos; por tanto, tiene sentido hacerlo al aire libre. Por otro lado, si el uso de distinct(x100) reduce significativamente el número de filas que se extraerán, entonces tiene sentido eliminar las duplicaciones 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 trabajos de ETL. Por ejemplo, el trabajo "B" no puede comenzar mientras el trabajo "A" no haya finalizado. Por lo general, se puede lograr un mejor rendimiento visualizando todos los procesos en un gráfico e intentando reducir el gráfico aprovechando al máximo el 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 distribuyen entre varias bases de datos y el procesamiento se realiza en esas bases de datos de forma secuencial. A veces, la replicación de bases de datos puede utilizarse como método para copiar datos entre bases de datos; puede ralentizar significativamente todo el proceso. La solución común 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 cargarlos en la primera y luego replicarlos 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 principales de "hechos" .
Un desarrollo reciente [update]en el software ETL es la implementación del procesamiento paralelo . Ha permitido una serie de métodos para mejorar el rendimiento general de ETL cuando se trata de grandes volúmenes de datos.
Las aplicaciones ETL implementan tres tipos principales de paralelismo:
Los tres tipos de paralelismo suelen operar combinados en un solo trabajo o tarea.
Una dificultad adicional surge al asegurarse de que los datos que se cargan sean relativamente consistentes. Debido a que varias 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), es posible que se requiera que un sistema ETL retenga ciertos datos hasta que todas las fuentes estén sincronizadas. Del mismo modo, cuando es posible que sea necesario conciliar un almacén con el contenido de un sistema fuente 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 falla, tener estos ID ayuda a revertir y volver a ejecutar la pieza fallida.
Las mejores prácticas también exigen puntos de control , que son estados en los que se completan determinadas fases del proceso. Una vez en un punto de control, es una buena idea escribir todo en el disco, limpiar algunos archivos temporales, registrar el estado, etc.
A partir de 2010 [update], la virtualización de datos había comenzado a avanzar en el procesamiento ETL. La aplicación de la virtualización de datos a 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. Virtual ETL opera con la representación abstracta de los objetos o entidades recopilados de una 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 persistentemente en una arquitectura radial 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 hacerse persistente. Al utilizar un repositorio de metadatos persistente, las herramientas ETL pueden pasar de proyectos únicos a middleware persistente, realizando la armonización y elaboración de perfiles de datos de manera consistente y casi en tiempo real.
Las claves únicas juegan 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 número entero generado automáticamente que no tiene significado para la entidad comercial que se representa, sino que existe únicamente para los fines de la base de datos relacional, comúnmente conocida como clave sustituta .
Como normalmente se carga más de una fuente de datos en el almacén, las claves son una preocupación importante que hay que abordar. 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 sola dimensión .
Una forma recomendada de abordar el problema consiste en agregar una clave sustituta de almacén, que se utiliza como clave externa de la tabla de hechos. [6]
Por lo general, las actualizaciones se producen en los datos de origen de una dimensión, que obviamente deben reflejarse en el almacén de datos.
Si se requiere la clave principal de los datos de origen para los 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; se realiza 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 sustitutos de varios sistemas fuente, mientras se preserva 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; [7] aquí se incluyen tres:
Un marco ETL establecido puede mejorar la conectividad y la escalabilidad . [ cita necesaria ] Una buena herramienta ETL debe poder comunicarse con las diferentes bases de datos relacionales y leer los distintos formatos de archivos utilizados en una organización. Las herramientas ETL han comenzado a migrar hacia la integración de aplicaciones empresariales , o incluso hacia 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 creación de perfiles de datos , calidad de datos y metadatos . Un caso de uso común de las herramientas ETL incluye la conversión de archivos CSV a formatos legibles por bases de datos relacionales. Las herramientas ETL facilitan una traducción típica de millones de registros que permiten a los usuarios ingresar fuentes/archivos 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 la empresa, las herramientas ETL se han convertido en una herramienta conveniente en la que se puede confiar para obtener el máximo rendimiento. . En la mayoría de los casos, las herramientas ETL contienen una GUI que ayuda a los usuarios a transformar datos cómodamente, utilizando un mapeador de datos visual, en lugar de escribir programas grandes para analizar archivos y modificar tipos de datos.
Si bien las herramientas ETL han sido tradicionalmente para desarrolladores y personal de TI, la firma de investigación Gartner escribió que la nueva tendencia es proporcionar estas capacidades a los usuarios empresariales para que ellos mismos puedan crear conexiones e integraciones de datos cuando sea necesario, en lugar de recurrir al personal de TI. [8] Gartner se refiere a estos usuarios no técnicos como Citizen Integrators. [9]
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 para el proceso de análisis también deberá considerar dónde limpiar y enriquecer los datos [10], así como 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 el almacenamiento de datos, abordó este tema. [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 carga previa y replicar datos sin procesar en sus almacenes de datos, donde pueden transformarlos según sea necesario utilizando SQL .
Después de haber utilizado ELT, los datos pueden procesarse más y almacenarse en un centro de datos. [13]
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 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, quema de tokens) antes de extraerlos y cargarlos en otro almacén de datos. [14]