ETL est l'abréviation de Extract, Transform, and Load (extraction, transformation et chargement). Les ingénieurs et les professionnels des données utilisent couramment ce terme pour décrire le processus en plusieurs étapes qui consiste à transformer des données brutes en une forme qui peut être utilisée pour effectuer des analyses de données utiles qui permettent d'améliorer la prise de décision au sein de l'entreprise.
ETL Signification
La meilleure façon de décrire la signification de l'ETL est de prendre chaque lettre et de discuter de son rôle dans le processus ETL.
L'étape de l'extraction
La première phase du processus ETL consiste à extraire des données opérationnelles brutes à partir de nombreux systèmes sources. Si le jeu de données que vous chargez n'a pas besoin d'être actualisé plus d'une fois par jour et n'est pas trop volumineux, vous pouvez en extraire une copie quotidienne et écraser la table dans votre base de données cible. Les bases de données relationnelles disposent d'une fonction SQL appelée TRUNCATE TABLE, qui fixe le point le plus haut à zéro, ce qui a pour effet de vider instantanément la table. Cette méthode est plus efficace que l'exécution d'une opération SQL DELETE qui doit traiter chaque ligne et consommer une tonne de cycles de processeur . Cette approche est connue sous le nom d'extraction complète.
Si les données sources doivent être nettoyées ou transformées, ou si elles sont très volumineuses, il est préférable de n'extraire que les données les plus récentes, de les nettoyer et de les charger. Cette approche est connue sous le nom d'extraction partielle.
L'extraction la plus sophistiquée est connue sous le nom d'extraction partielle avec notification de mise à jour. Cette approche est utilisée lorsque votre base de données cible nécessite des mises à jour en temps quasi réel des modifications apportées aux données sources. Par exemple, s'il s'agit d'un système de négociation d'actions, le système source peut transmettre les modifications à plusieurs systèmes cibles abonnés. Supposons que l'application source soit un réseau de téléphonie mobile qui suit des milliers de transactions d'appel par seconde. Dans ce cas, l'extraction peut être effectuée en micro-lots qui interrogent le système source toutes les 5 secondes environ et appliquent ces changements au système cible en temps quasi réel.
De nombreux systèmes de base de données support déclencheurs qui se déclenchent lorsqu'un événement tel qu'une opération d'insertion, de suppression ou de mise à jour se produit. Ces déclencheurs peuvent être programmés pour mettre à jour un journal des modifications à partir duquel les systèmes en aval peuvent extraire des données. L'utilisation d'un système de messagerie tel qu'Amazon SNS vous permet d'envoyer des notifications directement à vos systèmes cibles pour les informer des mises à jour du système source. Ce type de relation asynchrone entre les systèmes de données source et cible garantit que les systèmes source opérationnels ne sont jamais bloqués parce qu'un système cible ne peut pas accueillir la mise à jour pour une raison quelconque.
Les relations entre les systèmes source et cible peuvent prendre la forme d'une simple connexion point à point, simple à mettre en place et à maintenir. Au fil du temps, les entreprises créent des centaines de connexions de ce type, qui deviennent trop lourdes à maintenir et à administrer. À ce stade, un outil ETL à la demande peut consolider la gestion de toutes les connexions, ce qui en fait un excellent investissement. Un aspect très important de l'extraction est la mémorisation de la source des données, afin que les systèmes puissent la cataloguer pour maintenir et auditer la trace de l'origine des données. La provenance des données (également appelée "lignée de données") est une métadonnées associée à des enregistrements qui détaillent l'origine, les modifications et les détails justifiant l'origine des sources de données. Ce type de suivi des sources permet de contrôler les doublons et de déterminer si les données sont dignes de confiance ou non.
L'extraction de données peut être aussi simple que l'importation d'une version CSV d'une feuille de calcul ou aussi complexe que l'utilisation d'un service web API. La plupart des applications d'entreprise fournissent des API permettant l'extraction à l'aide d'une application ou d'un outil ETL. Le format des données sources détermine souvent la manière dont elles peuvent être extraites. Un flux JSON, par exemple, nécessite l'utilisation d'une API de services web. Un fichier plat peut être lu de manière séquentielle. Une source de base de données dispose généralement d'un utilitaire d'exportation permettant de décharger les données dans un fichier plat. Par exemple, dans un système central, un jeu de données VSAM auquel on accède généralement par clé
L'étape de la transformation
Les données brutes provenant d'une source opérationnelle doivent généralement être manipulées et nettoyées pour pouvoir être utilisées de manière générale. La transformation des données a souvent lieu dans une base de données intermédiaire ou de transit. Les données brutes peuvent contenir des données excédentaires qu'il convient de filtrer. Elles peuvent présenter des lacunes qui peuvent être comblées par des valeurs par défaut ou des données interpolées. Les données en double peuvent être agrégées ou supprimées en fonction de l'utilisation prévue dans la base de données cible. Si des données connexes sont extraites de plusieurs sources, elles peuvent être fusionnées pour créer un enregistrement unique. Les valeurs des données peuvent être normalisées. Par exemple, différentes sources de données sur les clients peuvent les enregistrement différemment. Un système peut écrire l'État en toutes lettres, comme "New York", tandis que d'autres utilisent une abréviation telle que "NY". La normalisation de l'abréviation peut être effectuée lors de la transformation des données. La transformation peut inclure des opérations de qualité des données, telles que la vérification des valeurs dans plusieurs sources avant de les transmettre en aval en tant que valeur de confiance.
L'étape du chargement
Le chargement des données dans le système cible est la dernière étape du processus ETL. Le chargement des données peut être effectué à l'aide d'un utilitaire de chargement en masse, d'un streaming, d'une messagerie asynchrone ou d'un programme personnalisé. Les données existantes peuvent être entièrement écrasées, complétées ou partiellement mises à jour avec de nouvelles valeurs. La fréquence de mise à jour peut être effectuée par lots, par exemple pendant la nuit, périodiquement par micro-lots ou en continu dans un flux de données.
Historique de l'ETL
Dans les années 1970, les bases de données étaient chargées à l'aide d'un code personnalisé ou saisies par des employés chargés de la saisie des données. Les volumes étaient faibles par rapport aux normes actuelles et les taux d'erreur pouvaient être élevés en raison de l'erreur humaine. Les bases de données étaient principalement utilisées pour faire fonctionner les systèmes opérationnels ; toute analyse était effectuée par des personnes qui consultaient des imprimés.
Dans les années 1980, les mini-ordinateurs et les ordinateurs personnels pouvaient être utilisés pour créer des fichiers plats destinés à être chargés dans des systèmes analytiques à l'aide de programmes de chargement par lots. L'émergence des feuilles de calcul pour l'analyse des données a entraîné la normalisation des données dans des formats délimités tels que les formats de fichiers CSV pour le partage des données entre ordinateurs. Les réseaux étant lents, les données étaient souvent transportées sur des bandes magnétiques et des disquettes. L'analyse des données était effectuée à l'aide de progiciels statistiques tels que X11 et Box Jenkins pour l'analyse de régression multivariée, et les données étaient rapportées à l'aide de langages écrits en code 3GL tels que FORTRAN.
Les années 1990 ont vu l'essor de l'entrepôt de données, qui a entraîné le processus ETL. Les systèmes d'support décision ont été construits sur des bases de données fonctionnant sur diverses versions d'Unix et de Linux. Les fonctions d'entreposage de données de l'entreprise ou centralisées pouvaient être distribuées à des systèmes plus localisés avec des marts de données spécifiques à l'unité commerciale ou à la région. L'intégration des données se faisait généralement de point à point.
Dans les années 2000, l'ETL a été normalisé par des fournisseurs proposant des outils d'intégration de données qui aidaient les organisations à étendre le mouvement et le transport des données entre les systèmes et offraient des interfaces utilisateur graphiques de bureau (GUI) pour créer et gérer les processus ETL.
Dans les années 2010, le transfert de données basé sur l'internet et les applications SaaS sont devenus très populaires, tout comme les offres de publication et d'abonnement à l'échelle de l'internet sur les plateformes cloud public. Le niveau de sophistication des modèles d'ETL et d'intégration des données a augmenté avec l'avènement des modèles de données en étoile.
Aujourd'hui, l'ETL est omniprésent et le marché consolide les fonctions d'ETL et de gestion des bases de données dans des services intégrés basés sur le cloud.
Comment fonctionne l'ETL ?
Nous pouvons utiliser une application Customer360 comme exemple pour illustrer le fonctionnement de l'ETL. Les gestionnaires de comptes et les vendeurs utilisent la base de données cible pour vendre des services à de nouveaux clients, faire de la vente croisée de nouveaux services à des clients existants et renouveler des abonnements existants.
Avant d'appeler le client ou le client potentiel, l'équipe doit en savoir le plus possible sur lui pour montrer qu'elle s'intéresse suffisamment à lui en effectuant des recherches approfondies. Le système cible doit indiquer depuis combien de temps le client est là, combien il a dépensé et quels sont les services dont il bénéficie aujourd'hui et dans le passé. Il est essentiel de connaître les mesures de satisfaction telles que les résultats des enquêtes et les dossiers d'support en cours , car il est difficile de vendre à des clients mécontents.
Les données sources doivent provenir de systèmes tels que Salesforce, ServiceNow et SAP. Les données de Salesforce doivent être extraites en exportant un fichier CSV basé sur un rapport spécifique au client. ServiceNow fournit une API REST qu'un outil ETL peut interroger à partir d'un script de commande. SAP peut créer un fichier CSV qui peut être importé dans le dépôtun outil de BI, qui est souvent une édition Embarqué de SQL Server. Toutes les sources de données extraites par le processus ETL peuvent être utilisées pour créer un tableau de bord 360 pour les équipes de vente ou de gestion des comptes.
Pourquoi l'ETL est-il important ?
Le nombre de sources et de types de données d'entreprise augmente chaque année et le nombre de connexions nécessaires à ces systèmes augmente également. Le coût de l'administration et de la maintenance des connexions ponctuelles point à point devient rapidement prohibitif. Il est essentiel de disposer d'un outil qui vous permette d'établir des connexions plug-and-play avec n'importe quelle source de données et qui gère les transformations de données et les opérations de chargement. Une fois l'intégration en place, il est essentiel qu'elle puisse planifier les flux de données avec une bonne visibilité. En l'absence d'un processus ETL formel, une organisation consacrerait tout son temps et son budget à maintenir les connexions en fonctionnement plutôt qu'à de nouveaux projets qui augmentent les revenus de l'entreprise.
Quelle est la différence entre ETL et ELT ?
La notion d'extraction, de chargement et de transformation, ou ELT, est apparue plus récemment que l'ETL traditionnel. Les deux principales différences sont les suivantes :
- L'ETL charge des données transformées ou propres dans l'entrepôt de données cible. L'ELT, quant à lui, transforme les données brutes dans l'entrepôt de données.
- L'ETL utilise un magasin de données intermédiaire et un serveur pour transformer les données. L'ELT effectue des transformations de données dans l'entrepôt de données cible.
L'ETL peut être considéré comme plus lourd car il utilise plus de serveurs et de magasins de données. L'ELT apparaît donc comme un pipeline de données plus rationalisé. Le chargement et le déchargement des données peuvent être lents, de sorte que l'exécution de la transformation dans le même serveur peut être plus rapide.
Si l'entrepôt de données cible a besoin d'être nettoyé des données personnelles clés, le filtrage et l'utilisation d'un système de mise en scène rendent l'ETL intéressant.
Comment l'ETL est-il utilisé ?
Le processus ETL peut être géré à différents niveaux de sophistication :
- Le niveau de base consiste simplement à établir une connexion point à point entre chaque système source et cible. Cette approche n'est pas très évolutive, sa maintenance est lourde et elle n'est pas flexible.
- La centralisation de la gestion de l'ETL est plus évolutif car elle fournit un point de référence unique et utilise les meilleures pratiques pour construire de nouvelles intégrations.
- L'approche la plus avancée consiste à utiliser une solution d'intégration de données à la demande qui a la notion de bus d'intégration ou de hub qui maintient des métadonnées sur les sources de données pour se conformer et s'adapter à l'évolution des besoins de l'organisation.
Qu'est-ce qu'un pipeline ETL ?
Un pipeline ETL comprend la chaîne de processus utilisée pour obtenir des données à partir de sources brutes vers l'entrepôt de données cible. Un pipeline peut être exécuté par lots, micro-lots ou en temps réel ; il change au fur et à mesure qu'il se produit dans le système source.
Défis ETL
L'ETL est confronté à de nombreux défis, dont le principal est la complexité. De nombreux flux ETL contiennent plusieurs étapes qui sont difficiles à tester et à maintenir parce qu'elles support systèmes encore plus complexes. Une entreprise peut avoir des centaines de flux ETL qui doivent être contrôlés, administrés et maintenus. Les systèmes informatiques ne sont pas statiques. Ils ont besoin de correctifs logiciels continus pour la pile technologique supportant des applications qui deviennent rapidement obsolètes et doivent être mises à jour ou migrées pour maintenir la stabilité. Comme l'ETL s'étend sur plusieurs systèmes, sa complexité est encore plus grande.
Le volume et la diversité des sources de données connectées par une entreprise ne cessent de croître. Les sources peuvent contenir des données structurées et semi-structurées, des sources en temps réel, des fichiers plats, des CSV, des buckets S3 et des sources streaming . L'Internet des objets (IoT), les journaux de visite des sites web, les sentiments des réseaux sociaux et la vidéo créent de nouvelles catégories de contenu à exploiter pour obtenir des informations.
Les entrepôts de données collectent des données sources à partir de systèmes de production qui doivent être hautement disponibles, de sorte qu'ils offrent quelques fenêtres de maintenance incluses pour les tests du système.
Les flux ETL peuvent traverser plusieurs silos commerciaux, ce qui rend difficile la compréhension du flux approprié d'informations commerciales entre les unités commerciales. Les processus ETL complexes peuvent devenir peu fiables, voire perdre des données. Les données peuvent être bloquées parce qu'un format ou une valeur de données provoque des enregistrements d'exception dans les journaux d'erreurs. Tous ces journaux doivent être surveillés car ils peuvent conduire à des résultats inattendus dans l'entrepôt de données cible.
La connexion de systèmes disparates conçus comme des monolithes peut avoir des effets secondaires sur les données en aval lorsque des modifications sont apportées aux systèmes individuels.
Outils ETL Actian
Il existe une grande variété d'outils ETL. Certains ont été conçus pour se concentrer uniquement sur la résolution des problèmes d'ETL - de nombreux outils tels qu'Actian DataConnect s'adressent à un marché plus large de l'intégration des données.
Le DataFlow d'Actian est conçu pour fonctionner comme des flux parallèles, ce qui améliore considérablement le débit des opérations ETL telles que les filtres et les transformations de données. Le constructeur d'expressions DataFlow vous aide à construire des expressions pour les champs de dérivation, les lignes de filtrage, l'analyseur de qualité des données, les lignes de filtrage existantes et les nœuds de jointure. Cette boîte de dialogue fournit une liste des champs, opérateurs et fonctions disponibles que vous pouvez utiliser pour construire des expressions.
Dans de rares cas, les bases de données sont conçues pour support portabilité des données. Par exemple, le format de fichier de la base de données Actian Zen est le même sous Unix et Windows, ce qui facilite l'étape d'extraction, en particulier lorsqu'il s'agit de centaines d'ensembles de données de journaux IoT.
Actian DataConnect est un exemple de solution ETL complète. Actian DataConnect peut être utilisé pour concevoir et gérer des intégrations déployées dans des environnements en nuage, sur site ou hybrides. DataConnect prend en charge un large éventail de types de données et s'adapte à de grands volumes de données. Une interface utilisateur bureau permet aux utilisateurs de créer ou de modifier visuellement des cartes d'intégration, des artefacts, des règles et des programmes de travail, sans codage ni script. L'accent est mis sur la réutilisation et l'adaptabilité afin que les entreprises puissent intégrer de manière rentable des données et des applications diverses.
Actian X aide les entreprises avec l'ETL en évitant les efforts de développement de code personnalisé pour répondre à une seule exigence commerciale. Au fur et à mesure que les besoins d'intégration augmentent, les problèmes liés au code personnalisé commencent à s'accumuler : impossibilité de réutiliser le code, difficulté de le maintenir en raison d'une documentation insuffisante ou inexistante et du fait que le développeur initial n'est plus disponible. DataConnect for Actian X permet une approche organisée pour ajouter des fonctions d'intégration de données aux applications existantes de manière incrémentale.
La plateforme de données Actian est un entrepôt de données doté de capacités d'intégration natives intégrées. La plateforme de données Actian dispose de chargeurs directs pour extraire des données en vrac à partir de sources de données populaires telles que S3, ADLS et Hadoop. La plateforme de données Actian comprend également plus de 200 connecteurs et modèles permettant d'obtenir et de déplacer facilement des données à partir d'applications SaaS via ODBC, JDBC, .Net et Python.
Meilleures pratiques
Les conseils suivants sont tirés de la documentation Actian DataConnect sur les meilleures pratiques en matière d'intégration de données, qui s'appliquent également au sous-ensemble ETL :
Architecte de la solution - Lorsque vous démarrez un nouveau projet d'intégration, identifiez d'abord les problèmes commerciaux pour lesquels vous devez fournir des solutions. En vous conformant à la méthodologie Agile, créez le document d'exigences, mais ne consacrez que le temps nécessaire pour que l'équipe comprenne les objectifs et les résultats requis. Veillez à mettre à jour ce document au fur et à mesure que vous progressez.
Concevoir pour la réutilisation - Il existe plusieurs façons de créer des intégrations organisées et faciles à comprendre. Cela donne plus de clarté aux membres de l'équipe en fournissant des modèles familiers et reconnaissables à mesure que la taille du projet ou le nombre de projets augmente. Pendant la phase de conception, mettez en place des espaces de travail collaboratifs, utilisez des conventions de dénomination, faites correspondre les processus métier aux flux de données, créez des modèles et réutilisez les connexions dans les pipelines de données ETL.
La création de modèles est également bénéfique car elle permet de documenter visuellement les étapes de l'ETL.
Les techniques courantes à prendre en compte consistent à rendre les scripts réutilisables grâce au passage de paramètres, à l'utilisation de constantes, à l'utilisation du parallélisme dans la mesure du possible et à la validation des données. En outre, il faut tenir compte des fuseaux horaires et de la localisation.
Il est important de tester les processus ETL à l'aide d'échantillons de données pendant le développement et, plus important encore, d'effectuer un test système en parallèle avec le système existant jusqu'à ce que vous soyez sûr de pouvoir passer au nouveau processus.
Pendant la production, utilisez un gestionnaire de journaux pour surveiller les exceptions afin de pouvoir détecter les effets secondaires involontaires des modifications apportées par le système à votre pipeline ETL.
Visitez le site Web d'Actian pour en savoir plus sur notre gamme complète de produits et de solutions de données.