Was ist ETL?
ETL ist ein Prozess, der Daten aus mehreren Quellsystemen extrahiert, sie ändert (durch Berechnungen, Verkettungen usw.) und sie dann in das Data Warehouse-System einfügt. ETL steht für Extrahieren, Transformieren und Laden.
Es ist leicht zu glauben, dass der Aufbau eines Data Warehouse so einfach ist wie das Abrufen von Daten aus zahlreichen Quellen und das Einspeisen in eine Data Warehouse-Datenbank. Dies ist bei weitem nicht der Fall, und es ist ein kompliziertes ETL-Verfahren erforderlich. Der technisch komplexe ETL-Prozess erfordert die aktive Teilnahme einer Vielzahl von Interessengruppen, darunter Entwickler, Analysten, Tester und Führungskräfte.
Um seinen Wert als Entscheidungshilfe zu erhalten, muss sich das Data-Warehouse-System synchron mit den Geschäftsentwicklungen entwickeln. ETL ist ein regelmäßiger (täglicher, wöchentlicher, monatlicher) Prozess eines Data-Warehouse-Systems, der agil, automatisiert und ordnungsgemäß dokumentiert sein muss.
Wie funktioniert ETL?
Hier lernen wir Schritt für Schritt, wie der ETL-Prozess funktioniert:
Schritt 1) Extraktion
Daten werden aus dem Quellsystem extrahiert und während der Extraktion in den Staging-Bereich gestellt. Falls Transformationen erforderlich sind, werden diese im Staging-Bereich durchgeführt, damit die Performance des Quellsystems nicht beeinträchtigt wird. Ein Rollback wird schwierig, wenn beschädigte Daten direkt von der Quelle in die Data Warehouse-Datenbank übertragen werden. Bevor extrahierte Daten in das Data Warehouse verschoben werden, können sie im Bereitstellungsbereich validiert werden.
Data Warehouses können Systeme mit unterschiedlicher Hardware, Datenbankverwaltungssystemen, Betriebssystemen und Kommunikationsprotokollen kombinieren. Data Warehouses müssen Systeme mit unterschiedlichen DBMS, Hardware, Betriebssystemen und Kommunikationsprotokollen kombinieren. Zu den Quellen können ältere Programme wie Mainframes, angepasste Anwendungen, Point-of-Contact-Geräte wie Geldautomaten und Rufschalter, Textdateien, Tabellenkalkulationen, ERP, Daten von Anbietern und Partnern usw. gehören.
Daher ist vor dem Extrahieren von Daten und dem physischen Laden dieser Daten eine logische Datenzuordnung erforderlich. Die Verbindung zwischen Quell- und Zieldaten wird in dieser Datenlandkarte dargestellt.
Drei Datenextraktionsmethoden:
- Teilextraktion – Wenn das Quellsystem Sie benachrichtigt, wenn ein Datensatz geändert wird, ist dies der einfachste Weg, um die Daten zu erhalten.
- Teilextraktion (ohne Update-Benachrichtigung) – Nicht alle Systeme können eine Benachrichtigung liefern, wenn ein Update auftritt; Sie können jedoch auf die Datensätze hinweisen, die geändert wurden, und eine Extraktion dieser Datensätze bereitstellen.
- Vollständiger Extrakt – Bestimmte Systeme können nicht feststellen, welche Daten überhaupt geändert wurden. In diesem Szenario besteht die einzige Möglichkeit, die Daten aus dem System zu bekommen, darin, einen vollständigen Extrakt durchzuführen. Dieser Ansatz erfordert eine Sicherung des vorherigen Extrakts in der
gleichen Format zur Verfügung, um die vorgenommenen Änderungen zu identifizieren.
Unabhängig von der angewandten Methode sollte die Extraktion keinen Einfluss auf die Leistung oder Antwortzeit der Quellsysteme haben. Dies sind Echtzeit-Produktionsdatenbanken. Jede Verlangsamung oder Sperrung kann sich auf das Endergebnis des Unternehmens auswirken.
Schritt 2) Umwandlung
Die vom Quellserver abgerufenen Daten sind im Originalzustand roh und unbrauchbar. Infolgedessen muss es bereinigt, abgebildet und transformiert werden. In Wirklichkeit ist dies der entscheidende Schritt, bei dem der ETL-Prozess einen Mehrwert schafft und Daten transformiert, um aussagekräftige BI-Berichte zu erstellen.
Es ist ein Schlüsselkonzept von ETL, bei dem Sie eine Sammlung von Funktionen auf extrahierte Daten anwenden. Direkter Umzug or passieren data ist der Datentyp, der keine Transformation erfordert.
Sie können während des Transformationsschritts benutzerdefinierte Operationen für Daten ausführen. Angenommen, der Kunde möchte eine Umsatzsumme, die nicht in der Datenbank vorhanden ist. oder wenn die Vor- und Nachnamen in einer Tabelle in getrennten Spalten stehen. Vor dem Laden können sie verkettet werden.
Im Folgenden finden Sie einige Beispiele für Datenintegritätsprobleme:
- Unterschiedliche Schreibweisen derselben Person, wie Prashant, Parshant usw.
- Es gibt viele Möglichkeiten, einen Firmennamen darzustellen, z. B. Google, Google Inc.
- Es werden verschiedene Namen wie Cleaveland und Cleveland verwendet.
- Es ist möglich, dass mehrere Kontonummern von verschiedenen Anwendungen für denselben Kunden erstellt werden.
- Einige Dateien, die für Daten benötigt werden, bleiben leer.
Schritt 3) Laden
Die letzte Phase des ETL-Prozesses besteht darin, Daten in die Ziel-Data-Warehouse-Datenbank zu laden. In einem typischen Data Warehouse wird eine große Datenmenge in relativ kurzer Zeit geladen. Daher sollte der Ladevorgang auf Performance optimiert werden.
Beim Auftreten eines Lastausfalls sollten Wiederherstellungsverfahren eingerichtet werden, damit der Betrieb ab dem Ausfallpunkt wieder aufgenommen werden kann, ohne die Datenintegrität zu gefährden. Data Warehouse-Administratoren müssen Ladevorgänge basierend auf der Serverleistung überwachen, fortsetzen und stoppen.
Ladearten:
- Anfängliche Ladung - alles füllen
die Data Warehouse-Tabellen - Inkrementelle Last — Umsetzung läuft
Anpassungen nach Bedarf regelmäßig - Vollständige Aktualisierung — Löschen des Inhalts
einer oder mehrerer Tabellen und Neuladen mit neuen Daten
Überprüfung laden
- Stellen Sie sicher, dass die Schlüsselfelddaten nicht fehlen oder null sind.
- Die Modellierung von Views basierend auf Zieltabellen sollte getestet werden.
- Untersuchen Sie die kombinierten Werte3 und berechneten Maße.
- Datenprüfungen in den Dimensions- und Verlaufstabellen.
- Untersuchen Sie die BI-Berichte zur geladenen Fakten- und Dimensionstabelle.
ETL mit PythonScript einrichten
Infolgedessen müssen Sie grundlegendes Extract Transform Load (ETL) von mehreren Datenbanken in ein Data Warehouse ausführen, um die Datenaggregation für Business Intelligence durchzuführen. Es sind mehrere ETL-Pakete verfügbar, die Sie für Ihren grundlegenden Anwendungsfall für übertrieben hielten.
In diesem Artikel zeige ich Ihnen, wie Sie Daten aus MySQL, SQL-Server und Firebird extrahieren. Transformieren Sie die Daten mit Python 3.6 und laden Sie sie in den SQL-Server (Data Warehouse).
Zunächst müssen wir ein Verzeichnis für unser Projekt erstellen:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Um ETL mit Python einzurichten, müssen Sie die folgenden Dateien in Ihrem Projektverzeichnis generieren.
- db_credentials.py: Sollte über alle Informationen verfügen, die zum Herstellen einer Verbindung zu allen Datenbanken erforderlich sind. wie Datenbankkennwort, Portnummer usw.
- sql_queries.py: Alle gängigen Datenbankabfragen zum Extrahieren und Laden von Daten im String-Format sollten verfügbar sein.
- etl.py: Stellen Sie eine Verbindung zur Datenbank her und führen Sie die erforderlichen Abfragen durch, indem Sie alle erforderlichen Verfahren ausführen.
- main.py: Verantwortlich für die Verwaltung des Betriebsablaufs und die Ausführung der wesentlichen Vorgänge in einer bestimmten Reihenfolge.
In diesem Abschnitt von sql_queries.py ist dies der Ort, an dem wir alle unsere SQL-Abfragen zum Extrahieren aus Quelldatenbanken und Importieren in unsere Zieldatenbank (Data Warehouse) speichern werden.
Richten Sie Datenbank-Anmeldeinformationen und -Variablen ein
Erstellen Sie in variables.py eine Variable, um den Namen der Basis aufzuzeichnen.
datawarehouse_name = 'ihr_datawarehouse_name'
Konfigurieren Sie alle Ihre Quell- und Zieldatenbank-Verbindungszeichenfolgen und Anmeldeinformationen in db_credentials.py wie unten gezeigt. Speichern Sie die Konfiguration als Liste, damit wir sie später bei Bedarf durch viele Datenbanken iterieren können.
from variables import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (target db, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', ' database': '{}'.format(datawarehouse_name), 'user': 'your_db_username', 'password': 'your_db_password', 'autocommit': True, } # sql-server (source db) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'your_sql_server', 'database': 'db1', 'user': 'your_db_username', 'password': 'your_db_password', ' autocommit': True, } ] # mysql (source db) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'database': 'db_1', } , { 'user': 'your_user_2', 'password': 'your_password_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (Quelldatenbank) fdb_db_config = [ { 'dsn' : "/Ihr/Pfad/zur/Quelle.db", 'Benutzer': "Ihr_Benutzername", 'Passwort': "Ihr_Passwort", } ]
SQL-Abfragen
In diesem Abschnitt von sql_queries.py ist dies der Ort, an dem wir alle unsere SQL-Abfragen zum Extrahieren aus Quelldatenbanken und Importieren in unsere Zieldatenbank (Data Warehouse) speichern werden.
Da wir mit mehreren Datenplattformen arbeiten, müssen wir für jede Datenbank verschiedene Syntaxen implementieren. Wir können dies tun, indem wir die Abfragen basierend auf dem Datenbanktyp trennen.
# Beispielabfragen, werden auf verschiedenen DB-Plattformen unterschiedlich sein 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) VALUES (?, ?, ? ) ''') 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) VALUES (?, ?, ?) ''' ). class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # Instanzen für SqlQuery erstellen class fbd_query = SqlQuery(firebird_extract, firebird_insert) fbd_query_1 = SqlQuery(firebird_extract_2, firebird_insert_3) sqlserver_query = SqlQuery(sqlserver_extract , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # als Liste für Iteration speichern fbd_queries = [fbdquery, fbd_query_1] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Transformationslast extrahieren
Um ETL mit Python für die oben genannten Datenquellen einzurichten, benötigen Sie die folgenden Module:
# Python-Module import mysql.connector import pyodbc import fdb # variables from variables import datawarehouse_name
Wir können dabei zwei Techniken verwenden: etl() und etl_process().
etl_process() ist die Prozedur zum Herstellen einer Datenbankquellverbindung und Aufrufen der etl()-Methode basierend auf der Datenbankplattform.
Und in der zweiten Methode, der etl()-Methode, führt sie zuerst die Extraktionsabfrage aus, speichert dann die SQL-Daten in den variablen Daten und fügt sie in die Zieldatenbank ein, die unser Data Warehouse ist. Eine Datentransformation kann erreicht werden, indem die Datenvariable des Typ-Tupels geändert wird.
def etl(query, source_cnx, target_cnx): # Daten aus Quelldatenbank extrahieren source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # Daten in Warehouse-Datenbank laden if Daten: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('Daten in Warehouse-Datenbank geladen') target_cursor.close() sonst : print('data is empty') def etl_process(queries, target_cnx, source_db_config, db_platform): # Verbindung zur Quelldatenbank herstellen if 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 'Error! nicht erkannte Datenbankplattform' # Durchlaufen von SQL-Abfragen für Abfragen in Abfragen: etl(query, source_cnx, target_cnx) # Schließen der Quelldatenbankverbindung source_cnx.close()
Alles zusammenfügen
Jetzt können wir im nächsten Schritt alle Anmeldeinformationen in main.py durchlaufen und das etl für alle Datenbanken ausführen.
Dazu müssen wir alle erforderlichen Variablen und Methoden importieren:
# Variablen aus db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config aus sql_queries import fbd_queries, sqlserver_queries, mysql_queries aus Variablen import * # Methoden aus etl import etl_process
Der Code in dieser Datei ist für das Iterieren über Anmeldeinformationen verantwortlich, um eine Verbindung zur Datenbank herzustellen und die erforderlichen ETL-Operationen mit Python auszuführen.
def main(): print('starting etl') # Verbindung zur Zieldatenbank herstellen (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # Anmeldeinformationen durchlaufen # mysql für config in mysql_db_config: try: print("loading db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') außer Ausnahme als Fehler: print("etl for {} has error".format(config['database'])) print ('Fehlermeldung: {}'.format(error)) Continue # sql-server for config in sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') außer Ausnahme als Fehler: print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) Continue # firebird for config in fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') außer Exception as error: print("etl for {} has error".format(config ['Datenbank'])) print('Fehlermeldung: {}'.format(error)) Continue target_cnx.close() if __name__ == "__main__": main()
Geben Sie in Ihrem Terminal python main.py ein und Sie haben gerade eine ETL mit einem reinen Python-Skript erstellt.
ETL-Tools
Es gibt mehrere Data-Warehousing-Tools auf dem Markt. Hier sind einige der bekanntesten Beispiele:
1. MarkLogic:
MarkLogic ist ein Data-Warehousing-System, das eine Reihe von Geschäftsfunktionen nutzt, um die Datenintegration einfacher und schneller zu machen. Es kann viele Arten von Daten abfragen, z. B. Dokumente, Beziehungen und Metadaten.
https://www.marklogic.com/product/getting-started/
2. Orakel:
Oracle ist die beliebteste Datenbank der Branche. Es bietet eine große Auswahl an Data Warehouse-Lösungen sowohl für lokale als auch für Cloud-Dienste. Es trägt zu besseren Kundenerlebnissen bei, indem es die betriebliche Effizienz steigert.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift ist eine Data-Warehousing-Lösung von Amazon. Es ist eine einfache und kostengünstige Lösung zur Analyse verschiedener Arten von Daten mit Standard-SQL und vorhandenen Business-Intelligence-Tools. Es ermöglicht auch die Ausführung komplexer Abfragen auf Petabytes strukturierter Daten.
https://aws.amazon.com/redshift/?nc2=h_m1
Zusammenfassung
Dieser Artikel gab Ihnen ein tiefes Verständnis dafür, was ETL ist, sowie eine Schritt-für-Schritt-Anleitung zum Einrichten Ihrer ETL in Python. Es gab Ihnen auch eine Liste der besten Tools, die die meisten Unternehmen heutzutage zum Aufbau ihrer ETL-Datenpipelines verwenden.
Andererseits verfügen die meisten Organisationen heutzutage über eine riesige Datenmenge mit einer hochdynamischen Struktur. Das Erstellen einer ETL-Pipeline von Grund auf für solche Daten ist ein schwieriges Verfahren, da Unternehmen eine große Anzahl von Ressourcen verwenden müssen, um diese Pipeline zu erstellen und dann sicherzustellen, dass sie mit dem hohen Datenvolumen und den Schemaänderungen Schritt halten kann.
Über den Autor
Prashant Sharma
Derzeit verfolge ich meinen Bachelor of Technology (B.Tech) vom Vellore Institute of Technology. Ich bin sehr begeistert von der Programmierung und ihren realen Anwendungen, einschließlich Softwareentwicklung, maschinellem Lernen, Deep Learning und Data Science.
Ich hoffe, Ihnen gefällt der Artikel. Wenn Sie sich mit mir verbinden möchten, können Sie sich verbinden unter:
oder für andere Zweifel können Sie eine Mail senden bei mir auch
Verbunden
- '
- "
- Konto
- aktiv
- Alle
- Amazon
- Analytik
- Anwendungen
- Bereich
- Artikel
- Automatisiert
- Sicherungskopie
- Stärkung
- bauen
- Building
- Geschäft
- Business Intelligence
- rufen Sie uns an!
- Schecks
- Cleveland
- Cloud
- Cloud-Services
- Code
- Kommunikation
- Unternehmen
- Verbindung
- fortsetzen
- Erstellen
- Referenzen
- technische Daten
- Datenintegration
- Datenwissenschaft
- Data Warehouse
- Data Warehouse
- Datenbase
- Datenbanken
- tiefe Lernen
- entwickeln
- Entwickler
- Entwicklung
- Geräte
- Abmessungen
- Unterlagen
- Fahrer
- Effizienz
- etc
- Ausführung
- Führungskräfte
- Erfahrungen
- Extraktion
- KONZENTRAT
- Scheitern
- Vorname
- Fluss
- Format
- frisch
- voller
- Guide
- Hardware
- hier
- High
- Geschichte
- Ultraschall
- Hilfe
- HTTPS
- identifizieren
- Impact der HXNUMXO Observatorien
- Einfuhr
- Einschließlich
- Information
- Integration
- Intelligenz
- Probleme
- IT
- Wesentliche
- grosse
- LERNEN
- lernen
- Line
- Liste
- Belastung
- Maschinelles Lernen
- Management
- Karte
- Markt
- Medien
- Am beliebtesten
- schlauer bewegen
- Namen
- Benachrichtigung
- Zahlen
- Angebote
- die
- Betriebssysteme
- Einkauf & Prozesse
- Orakel
- Auftrag
- Organisationen
- Andere
- Passwort
- Leistung
- Plattform
- Plattformen
- Beliebt
- Produziert
- Produktion
- Programmierung
- Programme
- Projekt
- Ziehen
- Python
- Roh
- Echtzeit
- Realität
- Aufzeichnungen
- Erholung
- Beziehungen
- Meldungen
- Downloads
- Antwort
- Einnahmen
- Wissenschaft
- Leistungen
- kompensieren
- Short
- Einfacher
- So
- Software
- Software-Entwicklung
- Lösungen
- SQL
- Stufe
- Bundesstaat
- speichern
- Läden
- System
- Systeme und Techniken
- Target
- Tech
- Techniken
- Technologie
- Die Quelle
- Zeit
- Transformation
- Lernprogramm
- Aktualisierung
- Wert
- Anbieter
- Volumen
- Warehouse
- Lagerung
- wöchentlich
- Arbeiten
- Werk