Qu'est-ce que l'ETL?
ETL est un processus qui extrait des données de plusieurs systèmes sources, les modifie (via des calculs, des concaténations, etc.), puis les place dans le système Data Warehouse. ETL signifie Extraire, Transformer et Charger.
Il est facile de croire que créer un entrepôt de données est aussi simple que d'extraire des données de nombreuses sources et de les introduire dans une base de données d'entrepôt de données. C’est loin d’être le cas et une procédure ETL compliquée est nécessaire. Le processus ETL, qui est techniquement complexe, implique la participation active de diverses parties prenantes, notamment des développeurs, des analystes, des testeurs et des cadres supérieurs.
Pour conserver sa valeur d’outil d’aide à la décision, le système d’entrepôt de données doit évoluer en phase avec les évolutions métiers. ETL est un processus régulier (quotidien, hebdomadaire, mensuel) d'un système d'entrepôt de données qui doit être agile, automatisé et correctement documenté.
Comment fonctionne l'ETL?
Ici, nous allons apprendre comment fonctionne le processus ETL étape par étape :
Étape 1) Extraction
Les données sont extraites du système source et placées dans la zone de transit lors de l'extraction. Si des transformations sont nécessaires, elles sont effectuées dans la zone de transfert afin que les performances du système source ne soient pas affectées. La restauration sera difficile si les données endommagées sont transférées directement de la source vers la base de données de l'entrepôt de données. Avant de déplacer les données extraites dans l'entrepôt de données, elles peuvent être validées dans la zone de préparation.
Les entrepôts de données peuvent combiner des systèmes avec différents matériels, systèmes de gestion de bases de données, systèmes d'exploitation et protocoles de communication. Les entrepôts de données doivent combiner des systèmes avec des SGBD, du matériel, des systèmes d'exploitation et des protocoles de communication disparates. Les sources peuvent inclure des programmes existants tels que des ordinateurs centraux, des applications personnalisées, des appareils de point de contact tels que des guichets automatiques et des commutateurs d'appel, des fichiers texte, des feuilles de calcul, des ERP, des données provenant de fournisseurs et de partenaires, etc.
Ainsi, avant d’extraire des données et de les charger physiquement, une carte logique des données est requise. La connexion entre les sources et les données cibles est affichée dans cette carte de données.
Trois méthodes d'extraction de données :
- Extraction partielle – Si le système source vous avertit lorsqu'un enregistrement est modifié, c'est le moyen le plus simple d'obtenir les données.
- Extraction partielle (sans notification de mise à jour) – Tous les systèmes ne peuvent pas envoyer une notification lorsqu'une mise à jour se produit ; mais ils peuvent indiquer les enregistrements qui ont été modifiés et permettre l'extraction de ces enregistrements.
- Extrait complet – Certains systèmes sont incapables de déterminer quelles données ont été modifiées. Dans ce scénario, la seule façon d’extraire les données du système est d’effectuer une extraction complète. Cette approche nécessite d'avoir une sauvegarde de l'extrait précédent dans le
même format afin d'identifier les changements qui ont été effectués.
Quelle que soit la méthode adoptée, l’extraction ne devrait pas avoir d’impact sur les performances ou le temps de réponse des systèmes sources. Ce sont des bases de données de production en temps réel. Tout ralentissement ou blocage pourrait avoir un impact sur les résultats de l'entreprise.
Étape 2) Transformation
Les données récupérées du serveur source sont brutes et inutilisables dans leur état d'origine. En conséquence, il doit être nettoyé, cartographié et transformé. En réalité, il s’agit de l’étape clé dans laquelle le processus ETL ajoute de la valeur et transforme les données afin de produire des rapports BI significatifs.
Il s'agit d'un concept ETL clé dans lequel vous appliquez un ensemble de fonctions aux données extraites. Déménagement direct or passer par làa est le type de données qui ne nécessite aucune transformation.
Vous pouvez exécuter des opérations personnalisées sur les données pendant l'étape de transformation. Par exemple, supposons que le client souhaite une somme de revenus de ventes qui n'existe pas dans la base de données. ou si le prénom et le nom d'un tableau sont dans des colonnes séparées. Avant le chargement, ils peuvent être concaténés.
Voici quelques exemples de problèmes d’intégrité des données :
- Différentes orthographes du même individu, telles que Prashant, Parshant, etc.
- Il existe de nombreuses façons de représenter le nom d'une entreprise, comme Google, Google Inc.
- Divers noms, tels que Cleaveland et Cleveland, sont utilisés.
- Il est possible que plusieurs numéros de compte soient générés par différentes applications pour le même client.
- Certains fichiers de données nécessaires sont laissés vides.
Étape 3) Chargement
La dernière étape du processus ETL consiste à charger les données dans la base de données de l'entrepôt de données cible. Un grand volume de données est chargé dans un laps de temps relativement court dans un entrepôt de données typique. En conséquence, le processus de chargement doit être optimisé pour les performances.
En cas de panne de charge, des procédures de récupération doivent être mises en place afin que les opérations puissent redémarrer à partir du point de panne sans compromettre l'intégrité des données. Les administrateurs de Data Warehouse doivent surveiller, poursuivre et arrêter les chargements en fonction des performances du serveur.
Types de chargement:
- Charge initiale — remplissant tout
les tables de l'entrepôt de données - Charge incrémentielle — mise en œuvre en cours
modifications au besoin sur une base régulière - Rafraîchissement complet — effacer le contenu
d'une ou plusieurs tables et en les rechargeant avec de nouvelles données
Vérification de la charge
- Vérifiez que les données du champ clé ne sont pas manquantes ou nulles.
- Les vues de modélisation basées sur des tables cibles doivent être testées.
- Examinez les valeurs combinées3 et les mesures calculées.
- Vérification des données dans les tables de dimension et d'historique.
- Examinez les rapports BI sur la table de faits et de dimensions chargée.
Configuration d'ETL à l'aide de PythonScript
Par conséquent, vous devez exécuter ETL (Extract Transform Load) de base à partir de plusieurs bases de données vers un entrepôt de données afin de procéder à l'agrégation de données à des fins de business intelligence. Il existe plusieurs packages ETL disponibles que vous jugez excessifs pour votre cas d'utilisation de base.
Je vais vous montrer comment extraire des données de MySQL, SQL Server et Firebird dans cet article. À l'aide de Python 3.6, transformez les données et chargez-les dans le serveur SQL (entrepôt de données).
Tout d'abord, nous devons créer un répertoire pour notre projet :
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Pour configurer ETL à l'aide de Python, vous devrez générer les fichiers suivants dans le répertoire de votre projet.
- db_credentials.py : Doit disposer de toutes les informations nécessaires pour se connecter à toutes les bases de données. tels que le mot de passe de la base de données, le numéro de port, etc.
- sql_queries.py : Toutes les requêtes de base de données couramment utilisées pour extraire et charger des données au format String doivent être disponibles.
- etl.py : Connectez-vous à la base de données et effectuez les requêtes nécessaires en effectuant toutes les procédures nécessaires.
- main.py : Responsable de la gestion du flux des opérations et de l’exécution des opérations essentielles dans un ordre spécifié.
Dans cette section de sql_queries.py, c'est l'endroit où nous allons stocker toutes nos requêtes SQL pour les extraire des bases de données sources et les importer dans notre base de données cible (entrepôt de données).
Configurer les informations d'identification et les variables de la base de données
Dans variables.py, créez une variable pour enregistrer le nom de la base.
datawarehouse_name = 'votre_datawarehouse_name'
Configurez toutes vos chaînes de connexion et informations d'identification aux bases de données source et cible dans db_credentials.py comme indiqué ci-dessous. Enregistrez la configuration sous forme de liste afin que nous puissions la parcourir ultérieurement chaque fois que nécessaire dans de nombreuses bases de données.
à partir de variables import datawarehouse_name datawarehouse_name = 'votre_nom_datawarehouse' # sql-server (base de données cible, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection' : 'oui', 'driver' : '{SQL Server}', 'server' : 'datawarehouse_sql_server', ' base de données' : '{}'.format(datawarehouse_name), 'user' : 'votre_nom_db_username', 'password' : 'votre_db_password', 'autocommit' : True, } # sql-server (base de données source) sqlserver_db_config = [ { 'Trusted_Connection ' : 'oui', 'pilote' : '{SQL Server}', 'serveur' : 'votre_serveur_sql', 'base de données' : 'db1', 'utilisateur' : 'votre_nom_db_utilisateur', 'mot de passe' : 'votre_mot de passe_db', ' autocommit' : True, } ] # mysql (base de données source) mysql_db_config = [ { 'user' : 'votre_utilisateur_1', 'password' : 'votre_password_1', 'host' : 'db_connection_string_1', 'database' : 'db_1', } , { 'user' : 'votre_utilisateur_2', 'password' : 'votre_password_2', 'host' : 'db_connection_string_2', 'database' : 'db_2', }, ] # firebird (base de données source) fdb_db_config = [ { 'dsn' : "/votre/chemin/vers/source.db", 'utilisateur' : "votre_nom d'utilisateur", 'mot de passe' : "votre_mot de passe", } ]
Requêtes SQL
Dans cette section de sql_queries.py, c'est l'endroit où nous allons stocker toutes nos requêtes SQL pour les extraire des bases de données sources et les importer dans notre base de données cible (entrepôt de données).
Nous devons implémenter différentes syntaxes pour chaque base de données car nous travaillons avec plusieurs plates-formes de données. Nous pouvons le faire en séparant les requêtes en fonction du type de base de données.
# exemples de requêtes, seront différents selon les différentes plates-formes de base de données firebird_extract = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') firebird_extract_2 = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (column_1, column_2, column_3) VALEURS (?, ?, ? ) ''') sqlserver_extract = (''' SELECT sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALEURS (?, ?, ?) ''' ) mysql_extract = (''' SELECT mysql_column_1, mysql_column_2, mysql_column_3 FROM mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') # exportation de requêtes classe SqlQuery : def __init__(self, extract_query, load_query) : self.extract_query = extract_query self.load_query = load_query # créer des instances pour la classe SqlQuery fbd_query = SqlQuery(firebird_extract, firebird_insert) fbd_query_2 = SqlQuery(firebird_extract_2, firebird_insert_2) sqlserver_query = SqlQuery( sqlserver_extract , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # stocker sous forme de liste pour l'itération fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Extraire la charge de transformation
Pour configurer ETL à l'aide de Python pour les sources de données mentionnées ci-dessus, vous aurez besoin des modules suivants :
#modules python importer mysql.connector importer pyodbc importer fdb # variables à partir de variables importer datawarehouse_name
Nous pouvons utiliser deux techniques pour cela : etl() et etl_process().
etl_process() est la procédure permettant d'établir une connexion source de base de données et d'appeler la méthode etl() basée sur la plate-forme de base de données.
Et dans la deuxième méthode qui est la méthode etl(), elle exécute d'abord la requête d'extraction, puis stocke les données SQL dans les données variables et les insère dans la base de données ciblée, qui est notre entrepôt de données. La transformation des données peut être réalisée en modifiant la variable de données du type tuple.
def etl(query, source_cnx, target_cnx): # extraire les données de la base de données source source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # charger les données dans la base de données de l'entrepôt si données : target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('données chargées dans la base de données de l'entrepôt') target_cursor.close() else : print('data is empty') def etl_process(queries, target_cnx, source_db_config, db_platform): # établir une connexion à la base de données source si db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_config) elif db_platform == 'sqlserver' : source_cnx = pyodbc.connect(**source_db_config) elif db_platform == 'firebird' : source_cnx = fdb.connect(**source_db_config) else : return 'Erreur ! plate-forme de base de données non reconnue # boucle dans les requêtes SQL pour les requêtes dans les requêtes : etl(query, source_cnx, target_cnx) # ferme la connexion à la base de données source source_cnx.close()
Tout mettre ensemble
Maintenant, à l'étape suivante, nous pouvons parcourir toutes les informations d'identification dans main.py et exécuter l'etl pour toutes les bases de données.
Pour cela, nous devons importer toutes les variables et méthodes requises :
# variables depuis db_credentials importer datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config depuis sql_queries importer fbd_queries, sqlserver_queries, mysql_queries depuis variables importer * # méthodes depuis etl importer etl_process
Le code de ce fichier est responsable de l'itération sur les informations d'identification afin de se connecter à la base de données et d'exécuter les opérations ETL utilisant Python nécessaires.
def main(): print('starting etl') # établir une connexion pour la base de données cible (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # parcourir les informations d'identification # mysql pour la configuration dans mysql_db_config : try: print("loading db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') sauf exception en tant qu'erreur : print("etl for {} has error".format(config['database'])) print ("message d'erreur : {}'.format(erreur)) continue # serveur sql pour la configuration dans sqlserver_db_config : try : print("chargement de la base de données : " + config['base de données']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') sauf exception en tant qu'erreur : print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) continue # firebird pour la configuration dans fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') except Exception as error: print("etl for {} has error".format(config ['base de données'])) print('message d'erreur : {}'.format(erreur)) continue target_cnx.close() if __name__ == "__main__": main()
Dans votre terminal, tapez python main.py et vous venez de créer un ETL en utilisant un script python pur.
Outils ETL
Il existe plusieurs outils de Data Warehousing sur le marché. Voici quelques-uns des exemples les plus célèbres :
1. MarkLogic :
MarkLogic est un système d'entreposage de données qui utilise un éventail de fonctionnalités commerciales pour rendre l'intégration des données plus facile et plus rapide. Il peut interroger de nombreux types de données, telles que des documents, des relations et des métadonnées.
https://www.marklogic.com/product/getting-started/
2.Oracle :
Oracle est la base de données la plus populaire du secteur. Il offre une grande variété de solutions d'entrepôt de données pour les services sur site et cloud. Il contribue à améliorer l’expérience client en améliorant l’efficacité opérationnelle.
https://www.oracle.com/index.html
3. Amazon Redshift :
Redshift est une solution d'entreposage de données d'Amazon. Il s'agit d'une solution simple et rentable pour analyser différents types de données avec SQL standard et les outils de business intelligence existants. Il permet également l'exécution de requêtes complexes sur des pétaoctets de données structurées.
https://aws.amazon.com/redshift/?nc2=h_m1
Conclusion
Cet article vous a donné une compréhension approfondie de ce qu'est l'ETL, ainsi qu'un didacticiel étape par étape sur la façon de configurer votre ETL en Python. Il vous a également donné une liste des meilleurs outils que la plupart des organisations utilisent aujourd'hui pour créer leurs pipelines de données ETL.
En revanche, la plupart des organisations disposent aujourd’hui d’une quantité massive de données avec une structure très dynamique. Créer un pipeline ETL à partir de zéro pour de telles données est une procédure difficile car les organisations devront utiliser un grand nombre de ressources pour créer ce pipeline, puis s'assurer qu'il peut suivre le volume élevé de données et les modifications de schéma.
A propos de l'auteur
Prashant Sharma
Actuellement, je poursuis mon baccalauréat en technologie (B.Tech) du Vellore Institute of Technology. Je suis très passionné par la programmation et ses applications réelles, notamment le développement de logiciels, l'apprentissage automatique, le Deep Learning et la science des données.
J'espère que l'article vous plaira. Si vous souhaitez me contacter, vous pouvez vous connecter sur :
ou pour tout autre doute, vous pouvez envoyer un mail à moi aussi
Services Connexes
- '
- "
- Compte
- infection
- Tous
- Amazon
- analytique
- applications
- Réservé
- article
- Automatisation
- sauvegarde
- stimuler
- construire
- Développement
- la performance des entreprises
- l'intelligence d'entreprise
- Appelez-nous
- Contrôles
- cleveland
- le cloud
- services de cloud computing
- code
- Communication
- Société
- connexion
- continuer
- La création
- Lettres de créance
- données
- intégration de données
- science des données
- entrepôt de données
- entrepôts de données
- Base de données
- bases de données
- l'apprentissage en profondeur
- développer
- mobiles
- Développement
- Compatibles
- Dimension
- INSTITUTIONNELS
- driver
- efficace
- etc
- exécution
- cadres
- Expériences
- extraction
- Extraits
- Échec
- Prénom
- flux
- le format
- à la main
- plein
- guide
- Matériel
- ici
- Haute
- Histoire
- Comment
- How To
- HTTPS
- identifier
- Impact
- l'importation
- Y compris
- d'information
- l'intégration
- Intelligence
- vous aider à faire face aux problèmes qui vous perturbent
- IT
- clés / KEY :
- gros
- APPRENTISSAGE
- apprentissage
- Gamme
- Liste
- charge
- machine learning
- gestion
- Localisation
- Marché
- Médias
- Le Plus Populaire
- Bougez
- noms
- déclaration
- numéros
- Offres Speciales
- d'exploitation
- systèmes d'exploitation
- Opérations
- oracle
- de commander
- organisations
- Autre
- partenaires,
- Mot de Passe
- performant
- plateforme
- Plateformes
- Populaire
- Produit
- Vidéo
- Programmation
- Programmes
- Projet
- tirant
- Python
- raw
- en temps réel
- Réalité
- Articles
- récupération
- Les relations
- Rapports
- Ressources
- réponse
- de revenus
- Sciences
- Services
- set
- Shorts
- étapes
- So
- Logiciels
- développement de logiciels
- Solutions
- SQL
- Étape
- Région
- Boutique
- STORES
- combustion propre
- Système
- Target
- technologie
- techniques
- Technologie
- La Source
- fiable
- De La Carrosserie
- tutoriel
- Mises à jour
- Plus-value
- fournisseurs
- le volume
- Entrepots
- Entreposage
- hebdomadaire
- Activités:
- vos contrats