Wat is ETL?
ETL is een proces dat gegevens uit meerdere bronsystemen haalt, deze wijzigt (door middel van berekeningen, aaneenschakelingen, enzovoort) en deze vervolgens in het Data Warehouse-systeem plaatst. ETL staat voor Extract, Transform en Load.
Het is gemakkelijk te geloven dat het bouwen van een datawarehouse net zo eenvoudig is als het ophalen van gegevens uit verschillende bronnen en deze invoeren in een datawarehouse-database. Dit is verre van het geval en er is een ingewikkelde ETL-procedure voor nodig. Het ETL-proces, dat technisch complex is, omvat actieve deelname van verschillende belanghebbenden, waaronder ontwikkelaars, analisten, testers en senior executives.
Om zijn waarde als besluitvormingsinstrument te behouden, moet het datawarehouse-systeem zich synchroon met de bedrijfsontwikkelingen ontwikkelen. ETL is een regelmatig (dagelijks, wekelijks, maandelijks) proces van een datawarehouse-systeem dat flexibel, geautomatiseerd en goed gedocumenteerd moet zijn.
Hoe werkt ETL?
Hier zullen we stap voor stap leren hoe het ETL-proces werkt:
Stap 1) Extractie
Gegevens worden uit het bronsysteem geëxtraheerd en tijdens de extractie in het verzamelgebied geplaatst. Als er transformaties nodig zijn, worden deze uitgevoerd in het verzamelgebied, zodat de prestaties van het bronsysteem niet worden geschaad. Terugdraaien zal moeilijk zijn als beschadigde gegevens rechtstreeks van de bron naar de datawarehouse-database worden overgebracht. Voordat geëxtraheerde gegevens naar het datawarehouse worden verplaatst, kunnen deze worden gevalideerd in het verzamelgebied.
Datawarehouses kunnen systemen combineren met verschillende hardware, databasebeheersystemen, besturingssystemen en communicatieprotocollen. Datawarehouses moeten systemen combineren met ongelijksoortige DBMS, hardware, besturingssystemen en communicatieprotocollen. Bronnen kunnen oude programma's zijn, zoals mainframes, aangepaste toepassingen, apparaten met contactpunten zoals geldautomaten en oproepschakelaars, tekstbestanden, spreadsheets, ERP, gegevens van leveranciers en partners, enzovoort.
Dus voordat gegevens worden geëxtraheerd en fysiek worden geladen, is een logische gegevenskaart vereist. Het verband tussen bronnen en doelgegevens wordt weergegeven in deze gegevenskaart.
Drie methoden voor gegevensextractie:
- Gedeeltelijke extractie – Als het bronsysteem u waarschuwt wanneer een record wordt gewijzigd, is dat de eenvoudigste manier om de gegevens te verkrijgen.
- Gedeeltelijke extractie (zonder updatemelding) – Niet alle systemen kunnen een melding geven wanneer er een update plaatsvindt; maar ze kunnen de records aangeven die zijn gewijzigd en extractie van die records bieden.
- Volledig uittreksel – Bepaalde systemen kunnen helemaal niet vaststellen welke gegevens zijn gewijzigd. In dit scenario is een volledige extractie de enige manier om de gegevens uit het systeem te halen. Deze aanpak vereist een back-up van het vorige uittreksel in het
hetzelfde formaat bij de hand om de aangebrachte wijzigingen te identificeren.
Ongeacht de gekozen methode, extractie mag geen invloed hebben op de prestaties of responstijd van de bronsystemen. Dit zijn realtime productiedatabases. Elke vertraging of blokkering kan van invloed zijn op de bedrijfsresultaten.
Stap 2) Transformatie
De gegevens die van de bronserver worden opgehaald, zijn onbewerkt en onbruikbaar in de oorspronkelijke staat. Als gevolg hiervan moet het worden opgeschoond, in kaart gebracht en getransformeerd. In werkelijkheid is dit de belangrijkste stap waarin het ETL-proces waarde toevoegt en gegevens transformeert om zinvolle BI-rapporten te produceren.
Het is een belangrijk ETL-concept waarin u een verzameling functies toepast op geëxtraheerde gegevens. Directe verhuizing or door data is het type gegevens dat geen transformatie vereist.
Tijdens de transformatiestap kunt u aangepaste bewerkingen op gegevens uitvoeren. Stel dat de klant een omzetsom wil die niet in de database voorkomt. of als de voor- en achternaam in een tabel in aparte kolommen staan. Voordat ze worden geladen, kunnen ze worden samengevoegd.
Hieronder volgen enkele voorbeelden van problemen met gegevensintegriteit:
- Verschillende spellingen van dezelfde persoon, zoals Prashant, Parshant en etc.
- Er zijn veel manieren om een bedrijfsnaam weer te geven, zoals Google, Google Inc.
- Er worden verschillende namen gebruikt, zoals Cleaveland en Cleveland.
- Het is mogelijk dat meerdere rekeningnummers worden aangemaakt door verschillende applicaties voor dezelfde klant.
- Sommige benodigde gegevensbestanden zijn leeg gelaten.
Stap 3) Laden
De laatste fase in het ETL-proces is het laden van gegevens in de beoogde datawarehouse-database. In een typisch datawarehouse wordt in relatief korte tijd een grote hoeveelheid data geladen. Als gevolg hiervan moet het laadproces worden geoptimaliseerd voor prestaties.
In het geval van een laadfout moeten er herstelprocedures worden ingevoerd, zodat de operaties kunnen worden hervat vanaf het punt van de storing zonder de gegevensintegriteit in gevaar te brengen. Datawarehouse-beheerders moeten ladingen bewaken, voortzetten en stoppen op basis van serverprestaties.
Soorten laden:
- Eerste lading - alles vullen
de datawarehouse-tabellen - Incrementele belasting — lopende uitvoering
regelmatige aanpassingen indien nodig - Volledig vernieuwen — het opruimen van de inhoud
van een of meer tabellen en deze opnieuw laden met nieuwe gegevens
Verificatie laden
- Controleer of de sleutelveldgegevens niet ontbreken of null zijn.
- Het modelleren van weergaven op basis van doeltabellen moet worden getest.
- Onderzoek de gecombineerde waarden3 en berekende maten.
- Gegevenscontroles in de dimensie- en historietabellen.
- Onderzoek de BI-rapporten op de geladen feiten- en dimensietabel.
ETL instellen met behulp van PythonScript
Als gevolg hiervan moet u de basis Extract Transform Load (ETL) van verschillende databases naar een datawarehouse uitvoeren om gegevensaggregatie voor bedrijfsinformatie uit te voeren. Er zijn verschillende ETL-pakketten beschikbaar waarvan u dacht dat ze te veel waren voor uw basisgebruik.
In dit artikel laat ik je zien hoe je gegevens kunt extraheren uit MySQL, SQL-server en firebird. Transformeer de gegevens met behulp van Python 3.6 en laad deze in SQL-server (datawarehouse).
Allereerst moeten we een map voor ons project maken:
python_etl |__main.py |__db_credentials.py |__variabelen.py |__sql_queries.py |__etl.py
Om ETL in te stellen met behulp van Python, moet u de volgende bestanden in uw projectdirectory genereren.
- db_credentials.py: Moet alle informatie hebben die nodig is om verbinding te maken met alle databases. zoals databasewachtwoord, poortnummer, enz.
- sql_queries.py: Alle veelgebruikte databasequery's voor het extraheren en laden van gegevens in String-indeling moeten beschikbaar zijn.
- etl.py: Maak verbinding met de database en voer de benodigde query's uit door alle noodzakelijke procedures uit te voeren.
- hoofd.py: Verantwoordelijk voor het beheer van de stroom van operaties en het uitvoeren van de essentiële operaties in een bepaalde volgorde.
In dit gedeelte van sql_queries.py is dit de plaats waar we al onze sql-query's gaan opslaan om uit brondatabases te extraheren en te importeren in onze doeldatabase (datawarehouse).
Stel databasereferenties en variabelen in
Maak in variables.py een variabele om de naam van de basis op te nemen.
datawarehouse_name = 'uw_datawarehouse_name'
Configureer al uw bron- en doeldatabaseverbindingsreeksen en referenties in db_credentials.py zoals hieronder weergegeven. Sla de configuratie op als een lijst, zodat we deze later wanneer nodig door vele databases kunnen herhalen.
from variables import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (doel-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 (source db) fdb_db_config = [ { 'dsn' : "/uw/pad/naar/bron.db", 'user': "uw_gebruikersnaam", 'wachtwoord': "u r_wachtwoord", } ]
SQL-query's
In dit gedeelte van sql_queries.py is dit de plaats waar we al onze sql-query's gaan opslaan om uit brondatabases te extraheren en te importeren in onze doeldatabase (datawarehouse).
We moeten voor elke database verschillende syntaxis implementeren omdat we met meerdere dataplatforms werken. We kunnen dit doen door de queries te scheiden op basis van het databasetype.
# voorbeeldquery's, zullen verschillend zijn op verschillende db-platforms 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 VAN fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (column_1, column_2, column_3) WAARDEN (?, ?, ?) ) ''') sqlserver_extract = (''' SELECT sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 VAN sqlserver_table ''') sqlserver_insert = (''' INSERT INTO table (column_1, column_2, column_3) WAARDEN (?, ?, ?) ''' ) mysql_extract = (''' SELECT mysql_column_1, mysql_column_2, mysql_column_3 VAN mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') # export queries class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # cre ate instances for SqlQuery class 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) # store as list for iteration fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Pak Transform Load uit
Om ETL in te stellen met behulp van Python voor de bovengenoemde gegevensbronnen, hebt u de volgende modules nodig:
# python-modules importeer mysql.connector importeer pyodbc importeer fdb # variabelen uit variabelen import datawarehouse_name
We kunnen hierbij twee technieken gebruiken: etl() en etl_process().
etl_process() is de procedure voor het tot stand brengen van een databasebronverbinding en het aanroepen van de etl()-methode op basis van het databaseplatform.
En bij de tweede methode, de methode etl(), wordt eerst de extractiequery uitgevoerd, vervolgens worden de SQL-gegevens in de variabele gegevens opgeslagen en in de beoogde database, ons datawarehouse, ingevoegd. Gegevenstransformatie kan worden bereikt door de gegevensvariabele van het type tupel te wijzigen.
def etl(query, source_cnx, target_cnx): # haal gegevens uit source db source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # laad gegevens in magazijn db if data: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('data geladen naar magazijn db') target_cursor.close() else : print('data is leeg') def etl_process(queries, target_cnx, source_db_config, db_platform): # breng bron db-verbinding tot stand als 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! niet-herkend db-platform' # doorloop sql-query's voor query in queries: etl(query, source_cnx, target_cnx) # sluit de source db-verbinding source_cnx.close()
Alles bij elkaar zetten
Nu, in de volgende stap, kunnen we alle inloggegevens in main.py doorlopen en de etl voor alle databases uitvoeren.
Daarvoor moeten we alle vereiste variabelen en methoden importeren:
# variabelen van db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config van sql_queries import fbd_queries, sqlserver_queries, mysql_queries van variabelen import * # methoden van etl import etl_process
De code in dit bestand is verantwoordelijk voor het herhalen van inloggegevens om verbinding te maken met de database en de noodzakelijke ETL-bewerkingen uit te voeren met behulp van Python.
def main(): print('starting etl') # breng verbinding tot stand voor doeldatabase (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # doorloop referenties # mysql voor configuratie in mysql_db_config: probeer: print("laden db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') behalve Uitzondering als fout: print("etl voor {} heeft fout".format(config['database'])) print ('foutmelding: {}'.format(error)) doorgaan # sql-server voor configuratie in sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') behalve Uitzondering als fout: print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) continue # firebird for config in fbd_db_config: probeer: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') behalve Uitzondering als fout: print("etl voor {} heeft fout".format(config ['database'])) print('foutmelding: {}'.fo rmat(error)) doorgaan target_cnx.close() if __name__ == "__main__": main()
Typ in je terminal python main.py en je hebt zojuist een ETL gemaakt met een puur python-script.
ETL-hulpmiddelen
Er zijn verschillende Data Warehousing-tools op de markt. Hier zijn enkele van de meest bekende voorbeelden:
1. Mark Logic:
MarkLogic is een datawarehousing-systeem dat een scala aan zakelijke mogelijkheden gebruikt om data-integratie eenvoudiger en sneller te maken. Het kan vele soorten gegevens opvragen, zoals documenten, relaties en metagegevens.
https://www.marklogic.com/product/getting-started/
2. Orakel:
Oracle is de populairste database in de branche. Het biedt een grote verscheidenheid aan datawarehouse-oplossingen voor zowel on-premises als cloudservices. Het helpt bij betere klantervaringen door de operationele efficiëntie te vergroten.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift is een datawarehousing-oplossing van Amazon. Het is een eenvoudige en kosteneffectieve oplossing voor het analyseren van verschillende soorten gegevens met standaard SQL en bestaande tools voor bedrijfsinformatie. Het maakt ook de uitvoering van complexe queries op petabytes aan gestructureerde data mogelijk.
https://aws.amazon.com/redshift/?nc2=h_m1
Conclusie
Dit artikel heeft u een goed begrip gegeven van wat ETL is, evenals een stapsgewijze zelfstudie over het instellen van uw ETL in Python. Het gaf je ook een lijst met de beste tools die de meeste organisaties tegenwoordig gebruiken om hun ETL-datapijplijnen op te bouwen.
De meeste organisaties hebben tegenwoordig daarentegen een enorme hoeveelheid data met een zeer dynamische structuur. Het creëren van een ETL-pijplijn vanaf het begin voor dergelijke gegevens is een moeilijke procedure, aangezien organisaties een groot aantal bronnen zullen moeten gebruiken om deze pijplijn te maken en er vervolgens voor moeten zorgen dat deze het grote gegevensvolume en de schemawijzigingen kan bijhouden.
Over de auteur
Prashant Sharma
Momenteel volg ik mijn Bachelor of Technology (B.Tech) aan het Vellore Institute of Technology. Ik ben erg enthousiast over programmeren en de echte toepassingen ervan, waaronder softwareontwikkeling, machine learning, deep learning en datawetenschap.
Ik hoop dat je het artikel leuk vindt. Als je met mij wilt verbinden, kun je verbinding maken op:
of voor andere twijfels, u kunt: stuur een mail ook voor mij
Verwant
- '
- "
- Account
- actieve
- Alles
- Amazone
- analytics
- toepassingen
- GEBIED
- dit artikel
- geautomatiseerde
- backup
- het stimuleren
- bouw
- Gebouw
- bedrijfsdeskundigen
- business intelligence
- Bellen
- Controles
- cleveland
- Cloud
- cloud-diensten
- code
- Communicatie
- afstand
- versterken
- voortzetten
- Wij creëren
- Geloofsbrieven
- gegevens
- gegevens integratie
- data science
- datawarehouse
- data warehouses
- Database
- databanken
- diepgaand leren
- ontwikkelen
- ontwikkelaars
- Ontwikkeling
- systemen
- Afmeting
- documenten
- bestuurder
- doeltreffendheid
- etc
- uitvoering
- leidinggevende
- Ervaringen
- extractie
- extracten
- Storing
- Voornaam*
- stroom
- formaat
- vers
- vol
- Kopen Google Reviews
- gids
- Hardware
- hier
- Hoge
- geschiedenis
- Hoe
- How To
- HTTPS
- identificeren
- Impact
- importeren
- Inclusief
- informatie
- integratie
- Intelligentie
- problemen
- IT
- sleutel
- Groot
- LEARN
- leren
- Lijn
- Lijst
- laden
- machine learning
- management
- kaart
- Markt
- Media
- Meest populair
- beweging
- namen
- notificatie
- nummers
- Aanbod
- werkzaam
- besturingssystemen
- Operations
- orakel
- bestellen
- organisaties
- Overige
- partners
- Wachtwoord
- prestatie
- platform
- platforms
- Populair
- geproduceerd
- Productie
- Programming
- Programma's
- project
- trekken
- Python
- Rauw
- real-time
- Realiteit
- archief
- na een training
- Relaties
- Rapporten
- Resources
- antwoord
- inkomsten
- Wetenschap
- Diensten
- reeks
- Bermuda's
- Eenvoudig
- So
- Software
- software development
- Oplossingen
- SQL
- Stadium
- Land
- shop
- winkels
- system
- Systems
- doelwit
- tech
- technieken
- Technologie
- De Bron
- niet de tijd of
- Transformatie
- zelfstudie
- bijwerken
- waarde
- vendors
- volume
- Magazijn
- Warehousing
- per week
- Mijn werk
- Bedrijven