Hvad er ETL?
ETL er en proces, der udtrækker data fra flere kildesystemer, ændrer dem (gennem beregninger, sammenkædninger og så videre) og derefter sætter dem ind i Data Warehouse-systemet. ETL står for Extract, Transform og Load.
Det er let at tro, at opbygningen af et datavarehus er så simpelt som at trække data fra adskillige kilder og indlæse dem i en datavarehusdatabase. Dette er langt fra tilfældet, og en kompliceret ETL-procedure er påkrævet. ETL-processen, som er teknisk kompleks, involverer aktiv deltagelse fra en række forskellige interessenter, herunder udviklere, analytikere, testere og ledende medarbejdere.
For at bevare sin værdi som beslutningsværktøj skal datavarehussystemet udvikle sig i takt med forretningsudviklingen. ETL er en regelmæssig (daglig, ugentlig, månedlig) proces i et datavarehussystem, der skal være agilt, automatiseret og korrekt dokumenteret.
Hvordan virker ETL?
Her lærer vi, hvordan ETL-processen fungerer trin for trin:
Trin 1) Ekstraktion
Data udtrækkes fra kildesystemet og placeres i iscenesættelsesområdet under ekstraktion. Hvis der kræves transformationer, udføres de i iscenesættelsesområdet, så kildesystemets ydeevne ikke skades. Rollback vil være vanskelig, hvis beskadigede data overføres direkte fra kilden til Data warehouse-databasen. Før du flytter udtrukne data ind i datavarehuset, kan de valideres i staging-området.
Datavarehuse kan kombinere systemer med forskellig hardware, databasestyringssystemer, operativsystemer og kommunikationsprotokoller. Datavarehuse skal kombinere systemer med forskellige DBMS, hardware, operativsystemer og kommunikationsprotokoller. Kilder kan omfatte ældre programmer såsom mainframes, tilpassede applikationer, point-of-contact-enheder såsom pengeautomater og opkaldskontakter, tekstfiler, regneark, ERP, data fra leverandører og partnere og så videre.
Før udtrækning af data og indlæsning af dem fysisk, kræves der således et logisk datakort. Sammenhængen mellem kilder og måldata er vist i dette datakort.
Tre dataudtræksmetoder:
- Delvis udvinding – Hvis kildesystemet advarer dig, når en post er ændret, er det den nemmeste måde at få dataene på.
- Delvis udtrækning (uden opdateringsmeddelelse) – Ikke alle systemer kan levere en meddelelse, når der sker en opdatering; men de kan indikere til de poster, der er blevet ændret, og give udtræk af disse poster.
- Fuld uddrag – Visse systemer er ude af stand til at afgøre, hvilke data der overhovedet er blevet ændret. I dette scenarie er den eneste måde at få dataene ud af systemet på at udføre et fuldt udtræk. Denne tilgang kræver at have en sikkerhedskopi af det tidligere uddrag i
samme format til rådighed for at identificere de ændringer, der er foretaget.
Uanset den anvendte metode, bør ekstraktion ikke have en indvirkning på kildesystemernes ydeevne eller responstid. Disse er produktionsdatabaser i realtid. Enhver opbremsning eller låsning kan have en indvirkning på virksomhedens bundlinje.
Trin 2) Transformation
De data, der hentes fra kildeserveren, er rå og ubrugelige i sin oprindelige tilstand. Som et resultat skal det renses, kortlægges og transformeres. I virkeligheden er dette nøgletrinet i, hvor ETL-processen tilføjer værdi og transformerer data for at producere meningsfulde BI-rapporter.
Det er et centralt ETL-koncept, hvor du anvender en samling funktioner på udtrukne data. Direkte flytning or passere data er den type data, der ikke kræver nogen transformation.
Du kan udføre tilpassede operationer på data under transformationstrinnet. Antag for eksempel, at kunden ønsker en salgssum, der ikke findes i databasen. eller hvis for- og efternavne i en tabel er i separate kolonner. Før læsning kan de sammenkædes.
Følgende er nogle eksempler på problemer med dataintegritet:
- Forskellige stavemåder af det samme individ, såsom Prashant, Parshant osv.
- Der er mange måder at repræsentere et virksomhedsnavn på, såsom Google, Google Inc.
- Forskellige navne, såsom Cleaveland og Cleveland, bruges.
- Det er muligt, at flere kontonumre produceres af forskellige applikationer for den samme kunde.
- Nogle data nødvendige filer efterlades tomme.
Trin 3) Indlæsning
Det sidste trin i ETL-processen er at indlæse data i måldata warehouse-databasen. En stor mængde data indlæses på relativt kort tid i et typisk datavarehus. Som et resultat bør belastningsprocessen optimeres til ydeevne.
Hvis der opstår en belastningsfejl, bør der indføres gendannelsesprocedurer, så operationer kan genstartes fra fejlpunktet uden at kompromittere dataintegriteten. Data Warehouse-administratorer skal overvåge, fortsætte og stoppe belastninger baseret på serverydeevne.
Typer af indlæsning:
- Indledende belastning — fylder det hele
Data Warehouse-tabellerne - Inkrementel belastning — implementering igangværende
ændringer efter behov med jævne mellemrum - Fuld opdatering — rydning af indholdet
af en eller flere tabeller og genindlæse dem med friske data
Indlæs bekræftelse
- Kontroller, at nøglefeltdataene ikke mangler eller er nul.
- Modelleringsvisninger baseret på måltabeller bør testes.
- Undersøg de kombinerede værdier3 og beregnede mål.
- Datatjek i dimensions- og historietabellerne.
- Undersøg BI-rapporterne på den indlæste fakta- og dimensionstabel.
Opsætning af ETL ved hjælp af PythonScript
Som et resultat skal du udføre grundlæggende Extract Transform Load (ETL) fra flere databaser til et datavarehus for at kunne foretage dataaggregering til business intelligence. Der er flere tilgængelige ETL-pakker, som du mente var overdrevne til din grundlæggende brug.
Jeg viser dig, hvordan du udtrækker data fra MySQL, SQL-server og firebird i denne artikel. Brug Python 3.6 til at transformere dataene og indlæse dem i SQL-server (data warehouse).
Først og fremmest skal vi oprette en mappe til vores projekt:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
For at konfigurere ETL ved hjælp af Python, skal du generere følgende filer i din projektmappe.
- db_credentials.py: Bør have alle de nødvendige oplysninger for at oprette forbindelse til alle databaser. såsom databaseadgangskode, portnummer osv.
- sql_queries.py: Alle almindeligt anvendte databaseforespørgsler til at udtrække og indlæse data i String-format bør være tilgængelige.
- etl.py: Opret forbindelse til databasen og udfør de nødvendige forespørgsler ved at udføre alle nødvendige procedurer.
- main.py: Ansvarlig for at styre flowet af operationer og udføre de væsentlige operationer i en specificeret rækkefølge.
I denne sektion af sql_queries.py er dette stedet, hvor vi skal gemme alle vores sql-forespørgsler til udtrækning fra kildedatabaser og import til vores måldatabase (data warehouse)
Opsætning af databaselegitimationsoplysninger og variabler
I variables.py skal du oprette en variabel for at registrere navnet på basen.
datawarehouse_name = 'dit_datawarehouse_name'
Konfigurer alle dine kilde- og måldatabaseforbindelsesstrenge og legitimationsoplysninger i db_credentials.py som vist nedenfor. Gem konfigurationen som en liste, så vi kan gentage den, når det er nødvendigt, gennem mange databaser senere.
fra variabler importer datawarehouse_name datawarehouse_name = 'dit_datawarehouse_name' # sql-server (mål-db, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', ' database': '{}'.format(datawarehouse_name), 'user': 'dit_db_brugernavn', 'adgangskode': 'dit_db_adgangskode', 'autocommit': Sand, } # sql-server (kilde db) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'din_sql_server', 'database': 'db1', 'user': 'dit_db_brugernavn', 'adgangskode': 'din_db_adgangskode', ' autocommit': Sandt, } ] # mysql (kilde db) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'database': 'db_1', } , { 'user': 'din_bruger_2', 'adgangskode': 'dit_adgangskode_2', 'vært': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (kilde db) fdb_db_config = [ { 'dsn' : "/din/sti/til/kilde.db", 'bruger': "dit_brugernavn", 'adgangskode': "dit_adgangskode", } ]
SQL-forespørgsler
I denne sektion af sql_queries.py er dette stedet, hvor vi skal gemme alle vores sql-forespørgsler til udtrækning fra kildedatabaser og import til vores måldatabase (data warehouse).
Vi er nødt til at implementere forskellige syntakser for hver database, fordi vi arbejder med flere dataplatforme. Vi kan gøre dette ved at adskille forespørgslerne baseret på databasetypen.
# eksempelforespørgsler, vil være forskellige på tværs af forskellige db-platforme 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 (kolonne_3,)_2, column ?, ?) ''') firebird_extract_1 = (''' VÆLG fbd_column_2, fbd_column_3, fbd_column_2 FRA fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_1 (column_column_2)_3, column_1, column? ) ''') sqlserver_extract = (''' VÆLG sqlserver_column_2, sqlserver_column_3, sqlserver_column_1 FRA sqlserver_table ''') sqlserver_insert = (''' INSERT INTO table (column_2, VALUE, kolonne_3,')? ) mysql_extract = (''' VÆLG mysql_column_1, mysql_column_2, mysql_column_3 FRA mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1, column_2, column_3) 'Sporting?) 'S? klasse SqlQuery: def __init__(selv, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # opret forekomster for SqlQuery klasse fbd_query = SqlQuery(firebird_extract, firebird_insert) =firebird_extract_query_qlerts(firebird_extract_query_q_2 , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # gem som liste til iteration fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] =mysql_qlqueries
Udtræk transformationsbelastning
For at konfigurere ETL ved hjælp af Python til de ovennævnte datakilder, skal du bruge følgende moduler:
# python-moduler import mysql.connector import pyodbc import fdb # variabler fra variable import datawarehouse_name
Vi kan bruge to teknikker i dette: etl() og etl_process().
etl_process() er proceduren til at etablere en databasekildeforbindelse og kalde etl()-metoden baseret på databaseplatformen.
Og i den anden metode, som er etl()-metoden, kører den først udtræksforespørgslen, gemmer derefter SQL-dataene i de variable data og indsætter dem i den målrettede database, som er vores datavarehus. Datatransformation kan opnås ved at ændre datavariablen af typen tuple.
def etl(forespørgsel, source_cnx, target_cnx): # udtræk data fra kilden db source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # indlæs data i lageret db hvis data: target_cursor = target_cnx.cursor() target_cursor.execute("BRUG {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('data indlæst til lager db') target_cursor.close() else : print('data er tomme') def etl_process(queries, target_cnx, source_db_config, db_platform): # opret kilde-db-forbindelse hvis 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: returner 'Fejl! unrecognized db platform' # loop gennem sql-forespørgsler til forespørgsel i forespørgsler: etl(query, source_cnx, target_cnx) # luk kilde-db-forbindelsen source_cnx.close()
At sætte alt sammen
Nu, i det næste trin, kan vi sløjfe over alle legitimationsoplysninger i main.py og udføre etl for alle databaser.
Til det skal vi importere alle nødvendige variabler og metoder:
# variabler fra db_credentials importer datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config fra sql_queries importer fbd_queries, sqlserver_queries, mysql_queries fra variabler etl. importer metode fra variabler etl.
Koden i denne fil er ansvarlig for at iterere over legitimationsoplysninger for at oprette forbindelse til databasen og udføre de nødvendige ETL ved hjælp af Python-operationer.
def main(): print('starting etl') # opret forbindelse til måldatabase (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # loop gennem legitimationsoplysninger # mysql for config i mysql_db_config: prøv: print("loading db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') undtagen Undtagelse som fejl: print("etl for {} har fejl".format(config['database'])) print ('fejlmeddelelse: {}'.format(error)) fortsæt # sql-server for config i sqlserver_db_config: prøv: print("indlæser db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') undtagen Undtagelse som fejl: print("etl for {} har fejl".format(config['database'])) print('fejlmeddelelse: {}'.format(error)) fortsæt # firebird for config i fbd_db_config: try: print("indlæser db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') undtagen Undtagelse som fejl: print("etl for {} har fejl".format(config ['database'])) print('fejlmeddelelse: {}'.format(error)) fortsæt target_cnx.close() if __name__ == "__main__": main()
I din terminal skal du skrive python main.py, og du har lige oprettet en ETL ved hjælp af et rent python-script.
ETL værktøjer
Der er flere data warehousing værktøjer på markedet. Her er nogle af de mest berømte eksempler:
1. MarkLogic:
MarkLogic er et data warehousing system, der bruger en række forretningsmuligheder til at gøre dataintegration nemmere og hurtigere. Det kan forespørge på mange slags data, såsom dokumenter, relationer og metadata.
https://www.marklogic.com/product/getting-started/
2. Oracle:
Oracle er branchens mest populære database. Det tilbyder et stort udvalg af Data Warehouse-løsninger til både lokale og cloud-tjenester. Det hjælper med bedre kundeoplevelser ved at øge den operationelle effektivitet.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift er en data warehousing-løsning fra Amazon. Det er en enkel og omkostningseffektiv løsning til at analysere forskellige slags data med standard SQL og eksisterende business intelligence-værktøjer. Det muliggør også udførelse af komplekse forespørgsler på petabytes af strukturerede data.
https://aws.amazon.com/redshift/?nc2=h_m1
Konklusion
Denne artikel gav dig en dyb forståelse af, hvad ETL er, samt en trin-for-trin vejledning om, hvordan du opsætter din ETL i Python. Det gav dig også en liste over de bedste værktøjer, som de fleste organisationer i dag bruger til at opbygge deres ETL-datapipelines.
De fleste organisationer i dag har på den anden side en enorm mængde data med en meget dynamisk struktur. At skabe en ETL-pipeline fra bunden til sådanne data er en hård procedure, da organisationer bliver nødt til at bruge et stort antal ressourcer for at skabe denne pipeline og derefter sikre, at den kan følge med den høje datamængde og skemaændringer.
Om forfatteren
Prashant Sharma
I øjeblikket forfølger jeg mine bachelorer i teknologi (B.Tech) fra Vellore Institute of Technology. Jeg er meget begejstret for programmering og dets rigtige applikationer, herunder softwareudvikling, maskinlæring, Deep Learning og datavidenskab.
Jeg håber, du kan lide artiklen. Hvis du vil oprette forbindelse til mig, kan du oprette forbindelse på:
eller for enhver anden tvivl, kan du send en mail også til mig
Relaterede
- '
- "
- Konto
- aktiv
- Alle
- Amazon
- analytics
- applikationer
- OMRÅDE
- artikel
- Automatiseret
- backup
- fremme
- bygge
- Bygning
- virksomhed
- business intelligence
- ringe
- Kontrol
- cleveland
- Cloud
- cloud-tjenester
- kode
- Kommunikation
- selskab
- tilslutning
- fortsæt
- Oprettelse af
- Legitimationsoplysninger
- data
- dataintegration
- datalogi
- datalager
- datavarehuse
- Database
- databaser
- dyb læring
- udvikle
- udviklere
- Udvikling
- Enheder
- Dimension
- dokumenter
- driver
- effektivitet
- etc.
- udførelse
- ledere
- Oplevelser
- udvinding
- Uddrag
- Manglende
- Fornavn
- flow
- format
- frisk
- fuld
- vejlede
- Hardware
- link.
- Høj
- historie
- Hvordan
- How To
- HTTPS
- identificere
- KIMOs Succeshistorier
- importere
- Herunder
- oplysninger
- integration
- Intelligens
- spørgsmål
- IT
- Nøgle
- stor
- LÆR
- læring
- Line (linje)
- Liste
- belastning
- machine learning
- ledelse
- kort
- Marked
- Medier
- Mest Populære
- bevæge sig
- navne
- underretning
- numre
- Tilbud
- drift
- operativsystemer
- Produktion
- oracle
- ordrer
- organisationer
- Andet
- partnere
- Adgangskode
- ydeevne
- perron
- Platforme
- Populær
- produceret
- produktion
- Programmering
- Programmer
- projekt
- trækker
- Python
- Raw
- realtid
- Reality
- optegnelser
- opsving
- Relationer
- Rapporter
- Ressourcer
- svar
- indtægter
- Videnskab
- Tjenester
- sæt
- Kort
- Simpelt
- So
- Software
- softwareudvikling
- Løsninger
- SQL
- Stage
- Tilstand
- butik
- forhandler
- systemet
- Systemer
- mål
- tech
- teknikker
- Teknologier
- The Source
- tid
- Transformation
- tutorial
- Opdatering
- værdi
- leverandører
- bind
- Warehouse
- Warehousing
- ugentlig
- Arbejde
- virker