Vad är ETL?
ETL är en process som extraherar data från flera källsystem, ändrar den (genom beräkningar, sammanlänkningar och så vidare) och sedan lägger in den i Data Warehouse-systemet. ETL står för Extract, Transform och Load.
Det är lätt att tro att det är lika enkelt att bygga ett datalager som att hämta data från många källor och mata in det i en databas. Detta är långt ifrån fallet, och ett komplicerat ETL-förfarande krävs. ETL-processen, som är tekniskt komplex, involverar aktivt deltagande från en mängd olika intressenter, inklusive utvecklare, analytiker, testare och ledande befattningshavare.
För att behålla sitt värde som beslutsverktyg måste datalagersystemet utvecklas i takt med affärsutvecklingen. ETL är en regelbunden (daglig, veckovis, månatlig) process för ett datalagersystem som måste vara smidigt, automatiserat och korrekt dokumenterat.
Hur fungerar ETL?
Här kommer vi att lära oss hur ETL-processen fungerar steg för steg:
Steg 1) Extraktion
Data extraheras från källsystemet och placeras i uppställningsområdet under extraktion. Om några transformationer krävs utförs de i mellanställningsområdet så att källsystemets prestanda inte skadas. Återställning kommer att vara svårt om skadad data överförs direkt från källan till Data Warehouse-databasen. Innan extraherade data flyttas in i datavarehuset kan de valideras i mellanrumsområdet.
Datalager kan kombinera system med olika hårdvara, databashanteringssystem, operativsystem och kommunikationsprotokoll. Datalager måste kombinera system med olika DBMS, hårdvara, operativsystem och kommunikationsprotokoll. Källor kan inkludera äldre program som stordatorer, anpassade applikationer, kontaktpunkter som bankomater och samtalsväxlar, textfiler, kalkylblad, ERP, data från leverantörer och partners och så vidare.
Innan man extraherar data och laddar den fysiskt krävs alltså en logisk datakarta. Sambandet mellan källor och måldata visas i denna datakarta.
Tre dataextraktionsmetoder:
- Partiell extraktion – Om källsystemet varnar dig när en post ändras, är det det enklaste sättet att få fram data.
- Partiell extraktion (utan uppdateringsmeddelande) – Alla system kan inte skicka ett meddelande när en uppdatering sker; men de kan indikera för posterna som har ändrats och ge extraktion av dessa poster.
- Fullständigt extrakt – Vissa system är oförmögna att avgöra vilka data som har ändrats överhuvudtaget. I det här scenariot är det enda sättet att få ut data från systemet att utföra ett fullständigt extrakt. Detta tillvägagångssätt kräver att du har en säkerhetskopia av det tidigare utdraget i
samma format till hands för att identifiera de ändringar som har gjorts.
Oavsett vilken metod som används bör extraktion inte ha någon inverkan på källsystemens prestanda eller svarstid. Dessa är produktionsdatabaser i realtid. Varje avmattning eller låsning kan ha en inverkan på företagets resultat.
Steg 2) Transformation
Data som hämtas från källservern är rå och oanvändbar i sitt ursprungliga tillstånd. Som ett resultat måste det rengöras, kartläggas och omvandlas. I verkligheten är detta nyckelsteget där ETL-processen tillför värde och transformerar data för att producera meningsfulla BI-rapporter.
Det är ett centralt ETL-koncept där du tillämpar en samling funktioner på extraherade data. Direkt drag or passera genom data är den typ av data som inte kräver någon transformation.
Du kan utföra anpassade operationer på data under transformationssteget. Anta till exempel att kunden vill ha en summa av försäljningsintäkter som inte finns i databasen. eller om för- och efternamnen i en tabell finns i separata kolumner. Innan de laddas kan de sammanfogas.
Följande är några exempel på dataintegritetsproblem:
- Olika stavningar av samma individ, som Prashant, Parshant, och etc.
- Det finns många sätt att representera ett företagsnamn, som Google, Google Inc.
- Olika namn, som Cleaveland och Cleveland, används.
- Det är möjligt att flera kontonummer produceras av olika applikationer för samma kund.
- Vissa datafiler lämnas tomma.
Steg 3) Laddar
Det sista steget i ETL-processen är att ladda data till måldatabasen. En stor mängd data laddas på relativt kort tid i ett typiskt datalager. Som ett resultat bör laddningsprocessen optimeras för prestanda.
Om ett belastningsfel inträffar bör återställningsprocedurer införas så att verksamheten kan starta om från felpunkten utan att äventyra dataintegriteten. Data Warehouse-administratörer måste övervaka, fortsätta och stoppa laddningar baserat på serverns prestanda.
Typer av laddning:
- Initial belastning — fyller allt
Data Warehouse-tabellerna - Inkrementell belastning — Genomförande pågår
ändringar efter behov på regelbunden basis - Fullständig uppdatering — rensa innehållet
av en eller flera tabeller och ladda om dem med färska data
Ladda verifiering
- Kontrollera att nyckelfältsdata inte saknas eller är null.
- Modelleringsvyer baserade på måltabeller bör testas.
- Undersök de kombinerade värdena3 och beräknade mått.
- Datakontroller i dimensions- och historiktabellerna.
- Granska BI-rapporterna på den laddade fakta- och dimensionstabellen.
Konfigurera ETL med PythonScript
Som ett resultat måste du utföra grundläggande Extract Transform Load (ETL) från flera databaser till ett datalager för att kunna göra dataaggregering för business intelligence. Det finns flera tillgängliga ETL-paket som du trodde var överdrivna för ditt grundläggande användningsfall.
Jag visar dig hur du extraherar data från MySQL, SQL-server och firebird i den här artikeln. Använd Python 3.6, transformera data och ladda den till SQL-server (data warehouse).
Först och främst måste vi skapa en katalog för vårt projekt:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
För att ställa in ETL med Python måste du generera följande filer i din projektkatalog.
- db_credentials.py: Bör ha all information som behövs för att ansluta till alla databaser. som databaslösenord, portnummer, etc.
- sql_queries.py: Alla vanliga databasfrågor för att extrahera och ladda data i String-format bör vara tillgängliga.
- etl.py: Anslut till databasen och utför de nödvändiga frågorna genom att utföra alla nödvändiga procedurer.
- main.py: Ansvarig för att hantera flödet av operationer och utföra de väsentliga operationerna i en specificerad ordning.
I det här avsnittet av sql_queries.py är det här platsen där vi ska lagra alla våra sql-frågor för att extrahera från källdatabaser och importera till vår måldatabas (datalager)
Ställ in databasuppgifter och variabler
Skapa en variabel i variables.py för att registrera namnet på basen.
datawarehouse_name = 'ditt_datawarehouse_name'
Konfigurera alla dina käll- och måldatabasanslutningssträngar och autentiseringsuppgifter i db_credentials.py som visas nedan. Spara konfigurationen som en lista så att vi kan iterera den när det behövs genom många databaser senare.
från variabler importera datawarehouse_name datawarehouse_name = 'ditt_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 (källa db) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'din_sql_server', 'databas': 'db1', 'user': 'ditt_db_användarnamn', 'lösenord': 'ditt_db_lösenord', ' autocommit': True, } ] # mysql (källa db) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'databas': 'db_1', } , { 'user': 'your_user_2', 'password': 'your_password_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (källa db) fdb_db_config = [ { 'dsn' : "/din/sökväg/till/källa.db", 'user': "ditt_användarnamn", 'lösenord': "du r_lösenord", } ]
SQL-frågor
I det här avsnittet av sql_queries.py är det här platsen där vi ska lagra alla våra sql-frågor för att extrahera från källdatabaser och importera till vår måldatabas (datalager).
Vi måste implementera olika syntaxer för varje databas eftersom vi arbetar med flera dataplattformar. Vi kan göra detta genom att separera frågorna baserat på databastypen.
# exempelfrågor, kommer att vara olika på olika db-plattformar 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 (kolumn_3,)_2, column ?, ?) ''') firebird_extract_1 = (''' VÄLJ fbd_column_2, fbd_column_3, fbd_column_2 FRÅN fbd_table_2; ''') firebird_insert_2 = (''' INFOGA I tabell_1 (kolumn_2, kolumn)? 3, kolumn_1, kolumn? ) ''') sqlserver_extract = (''' VÄLJ sqlserver_column_2, sqlserver_column_3, sqlserver_column_1 FRÅN sqlserver_table ''') sqlserver_insert = (''' INFOGA I tabell (kolumn, kolumn, kolumn)? (kolumn_2, kolumn)? ) mysql_extract = (''' VÄLJ mysql_column_3, mysql_column_1, mysql_column_2 FRÅN mysql_table ''') mysql_insert = (''' INFOGA I tabell (kolumn_3, kolumn_1, kolumn_2) 'VÄRDE, exportera? 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_3 = 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]
Extrahera Transform Load
För att ställa in ETL med Python för de ovan nämnda datakällorna behöver du följande moduler:
# pythonmoduler import mysql.connector import pyodbc import fdb # variabler från variabler import datawarehouse_name
Vi kan använda två tekniker i detta: etl() och etl_process().
etl_process() är proceduren för att upprätta en databaskällanslutning och anropa etl()-metoden baserad på databasplattformen.
Och i den andra metoden, som är etl()-metoden, kör den först utdragsfrågan, lagrar sedan SQL-data i variabeldata och infogar den i måldatabasen, som är vårt datalager. Datatransformation kan åstadkommas genom att ändra datavariabeln för typen tupel.
def etl(query, source_cnx, target_cnx): # extrahera data från källdb source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # ladda data till lagerdb om data: target_cursor = target_cnx.cursor() target_cursor.execute("ANVÄND {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('data laddad till lagerdb') target_cursor.close() else : print('data är tom') def etl_process(queries, target_cnx, source_db_config, db_platform): # upprätta source db-anslutning om 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! okänd db-plattform' # loop igenom sql-frågor för fråga i frågor: etl(query, source_cnx, target_cnx) # stäng käll-db-anslutningen source_cnx.close()
Att sätta ihop allt
Nu, i nästa steg, kan vi loopa över alla referenser i main.py och köra etl för alla databaser.
För det måste vi importera alla nödvändiga variabler och metoder:
# variabler från db_credentials importera datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config från sql_queries importera fbd_queries, sqlserver_queries, mysql_queries från variabler importera * # etl-metoder från variabler import
Koden i den här filen är ansvarig för att iterera över autentiseringsuppgifter för att ansluta till databasen och utföra nödvändiga ETL med Python-operationer.
def main(): print('starting etl') # upprätta anslutning för måldatabas (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # loop igenom autentiseringsuppgifter # mysql för config i mysql_db_config: try: print("loading db: " + config['databas']) etl_process(mysql_queries, target_cnx, config, 'mysql') förutom Undantag som fel: print("etl för {} har fel".format(config['databas'])) print ('felmeddelande: {}'.format(error)) fortsätt # sql-server för config i sqlserver_db_config: try: print("laddar db: " + config['databas']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') förutom Undantag som fel: print("etl för {} har fel".format(config['databas'])) print('felmeddelande: {}'.format(error)) fortsätt # firebird för config i fbd_db_config: try: print("laddar db: " + config['databas']) etl_process(fbd_queries, target_cnx, config, 'firebird') förutom Undantag som fel: print("etl för {} har fel".format(config ['databas'])) print('felmeddelande: {}'.fo rmat(error)) fortsätt target_cnx.close() om __name__ == "__main__": main()
I din terminal skriver du python main.py och du har precis skapat en ETL med ett rent python-skript.
ETL-verktyg
Det finns flera Data Warehousing-verktyg på marknaden. Här är några av de mest kända exemplen:
1. MarkLogic:
MarkLogic är ett datalagersystem som använder en rad affärsmöjligheter för att göra dataintegration enklare och snabbare. Det kan söka efter många typer av data, såsom dokument, relationer och metadata.
https://www.marklogic.com/product/getting-started/
2. Oracle:
Oracle är branschens mest populära databas. Det erbjuder ett stort utbud av Data Warehouse-lösningar för både lokala och molntjänster. Det bidrar till bättre kundupplevelser genom att öka den operativa effektiviteten.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift är en datalagerlösning från Amazon. Det är en enkel och kostnadseffektiv lösning för att analysera olika typer av data med standard SQL och befintliga business intelligence-verktyg. Det möjliggör också exekvering av komplexa frågor på petabyte av strukturerad data.
https://aws.amazon.com/redshift/?nc2=h_m1
Slutsats
Den här artikeln gav dig en djup förståelse av vad ETL är, samt en steg-för-steg handledning om hur du ställer in din ETL i Python. Det gav dig också en lista över de bästa verktygen som de flesta organisationer nuförtiden använder för att bygga upp sina ETL-datapipelines.
De flesta organisationer nuförtiden har å andra sidan en enorm mängd data med en mycket dynamisk struktur. Att skapa en ETL-pipeline från grunden för sådan data är en svår procedur eftersom organisationer kommer att behöva använda ett stort antal resurser för att skapa denna pipeline och sedan säkerställa att den kan hänga med i den höga datavolymen och schemaändringarna.
Om författaren
Prashant Sharma
För närvarande läser jag mina kandidatexamen i teknik (B.Tech) från Vellore Institute of Technology. Jag är mycket entusiastisk över programmering och dess verkliga applikationer inklusive mjukvaruutveckling, maskininlärning, Deep Learning och datavetenskap.
Jag hoppas att du gillar artikeln. Om du vill komma i kontakt med mig kan du ansluta på:
eller för andra tvivel kan du skicka ett mail till mig också
Relaterad
- '
- "
- Konto
- aktiv
- Alla
- amason
- analytics
- tillämpningar
- OMRÅDE
- Artikeln
- Automatiserad
- säkerhetskopiering
- öka
- SLUTRESULTAT
- Byggnad
- företag
- business intelligence
- Ring
- Kontroller
- Cleveland
- cloud
- molntjänster
- koda
- Kommunikation
- företag
- anslutning
- fortsätta
- Skapa
- referenser
- datum
- dataintegration
- datavetenskap
- datalagret
- datalager
- Databas
- databaser
- djupt lärande
- utveckla
- utvecklare
- Utveckling
- enheter
- Dimensionera
- dokument
- chaufför
- effektivitet
- etc
- utförande
- befattningshavare
- Erfarenheter
- extraktion
- extrakt
- Misslyckande
- Förnamn
- flöda
- format
- färsk
- full
- styra
- hårdvara
- här.
- Hög
- historia
- Hur ser din drömresa ut
- How To
- HTTPS
- identifiera
- Inverkan
- importera
- Inklusive
- informationen
- integrering
- Intelligens
- problem
- IT
- Nyckel
- Large
- LÄRA SIG
- inlärning
- linje
- Lista
- läsa in
- maskininlärning
- ledning
- karta
- marknad
- Media
- Mest populär
- flytta
- namn
- anmälan
- nummer
- Erbjudanden
- drift
- operativsystem
- Verksamhet
- orakel
- beställa
- organisationer
- Övriga
- partner
- Lösenord
- prestanda
- plattform
- Plattformar
- Populära
- producerad
- Produktion
- Programmering
- Program
- projektet
- dra
- Python
- Raw
- realtid
- Verkligheten
- register
- återvinning
- Förhållanden
- Rapport
- Resurser
- respons
- intäkter
- Vetenskap
- Tjänster
- in
- Kort
- Enkelt
- So
- Mjukvara
- mjukvaruutveckling
- Lösningar
- SQL
- Etapp
- Ange
- lagra
- lagrar
- system
- System
- Målet
- tech
- tekniker
- Teknologi
- källan
- tid
- Transformation
- handledning
- Uppdatering
- värde
- försäljare
- volym
- Warehouse
- Lagring
- vecka
- Arbete
- fungerar