Kaj je ETL?
ETL je proces, ki ekstrahira podatke iz več izvornih sistemov, jih spremeni (z izračuni, združevanjem itd.) in jih nato postavi v sistem Data Warehouse. ETL pomeni ekstrahiranje, preoblikovanje in nalaganje.
Zlahka je verjeti, da je izgradnja podatkovnega skladišča tako preprosta kot črpanje podatkov iz številnih virov in njihovo vnašanje v podatkovno bazo podatkovnega skladišča. To še zdaleč ni tako in potreben je zapleten postopek ETL. Postopek ETL, ki je tehnično zapleten, vključuje aktivno sodelovanje različnih zainteresiranih strani, vključno z razvijalci, analitiki, preizkuševalci in višjimi vodstvenimi delavci.
Da bi ohranil svojo vrednost kot orodje za odločanje, se mora sistem podatkovnega skladišča razvijati v skladu s poslovnim razvojem. ETL je reden (dnevni, tedenski, mesečni) proces sistema podatkovnega skladišča, ki mora biti agilen, avtomatiziran in ustrezno dokumentiran.
Kako deluje ETL?
Tukaj bomo spoznali, kako poteka postopek ETL korak za korakom:
Korak 1) Ekstrakcija
Podatki se ekstrahirajo iz izvornega sistema in med ekstrakcijo postavijo v uprizoritveno območje. Če so potrebne kakršne koli transformacije, se izvedejo v uprizoritvenem območju, tako da zmogljivost izvornega sistema ni škodovana. Povratek bo otežen, če se poškodovani podatki prenesejo neposredno iz vira v bazo podatkov skladišča podatkov. Preden ekstrahirane podatke premaknete v podatkovno skladišče, jih je mogoče preveriti v uprizoritvenem območju.
Podatkovna skladišča lahko združujejo sisteme z različno strojno opremo, sistemi za upravljanje baz podatkov, operacijskimi sistemi in komunikacijskimi protokoli. Podatkovna skladišča morajo združevati sisteme z različnimi DBMS, strojno opremo, operacijskimi sistemi in komunikacijskimi protokoli. Viri lahko vključujejo podedovane programe, kot so veliki računalniki, prilagojene aplikacije, naprave za stike, kot so bankomati in klicna stikala, besedilne datoteke, preglednice, ERP, podatke prodajalcev in partnerjev itd.
Zato je pred ekstrahiranjem podatkov in njihovim fizičnim nalaganjem potreben logični zemljevid podatkov. Povezava med viri in ciljnimi podatki je prikazana na tej podatkovni karti.
Tri metode pridobivanja podatkov:
- Delna ekstrakcija – Če vas izvorni sistem opozori, ko je zapis spremenjen, je to najenostavnejši način za pridobitev podatkov.
- Delno ekstrahiranje (brez obvestila o posodobitvi) – Vsi sistemi ne morejo poslati obvestila, ko pride do posodobitve; lahko pa navedejo zapise, ki so bili spremenjeni, in zagotovijo ekstrakcijo teh zapisov.
- Popolni izvleček – Nekateri sistemi ne morejo ugotoviti, kateri podatki so bili sploh spremenjeni. V tem scenariju je edini način, da pridobite podatke iz sistema, tako da izvedete popoln izvleček. Ta pristop zahteva varnostno kopijo prejšnjega izvlečka v
isti format pri roki, da prepoznate opravljene spremembe.
Ne glede na izbrano metodo ekstrakcija ne bi smela vplivati na zmogljivost ali odzivni čas izvornih sistemov. To so produkcijske baze podatkov v realnem času. Vsaka upočasnitev ali zaklepanje lahko vpliva na rezultat podjetja.
2. korak) Preoblikovanje
Podatki, pridobljeni iz izvornega strežnika, so surovi in neuporabni v prvotnem stanju. Posledično ga je treba očistiti, preslikati in preoblikovati. V resnici je to ključni korak, kjer postopek ETL dodaja vrednost in preoblikuje podatke, da ustvari smiselna poročila poslovne inteligence.
To je ključni koncept ETL, pri katerem uporabite zbirko funkcij za ekstrahirane podatke. Neposredna poteza or preiti skozi data je vrsta podatkov, ki ne zahteva nobene transformacije.
Med korakom preoblikovanja lahko nad podatki izvajate prilagojene operacije. Denimo, da stranka želi vsoto prihodkov od prodaje, ki ne obstaja v bazi podatkov. ali če sta imena in priimki v tabeli v ločenih stolpcih. Pred nalaganjem jih je mogoče povezati.
Sledi nekaj primerov težav z integriteto podatkov:
- Različno črkovanje istega posameznika, na primer Prashant, Parshant itd.
- Obstaja veliko načinov za predstavitev imena podjetja, na primer Google, Google Inc.
- Uporabljajo se različna imena, kot sta Cleaveland in Cleveland.
- Možno je, da različne aplikacije za isto stranko ustvarijo več številk računov.
- Nekatere datoteke s potrebnimi podatki so ostale prazne.
3. korak) Nalaganje
Zadnja stopnja v procesu ETL je nalaganje podatkov v podatkovno bazo ciljnega skladišča podatkov. V tipičnem podatkovnem skladišču se v razmeroma kratkem času naloži velika količina podatkov. Posledično mora biti postopek nalaganja optimiziran za zmogljivost.
V primeru okvare obremenitve je treba vzpostaviti postopke za obnovitev, tako da se lahko operacije znova zaženejo od točke okvare, ne da bi bila ogrožena celovitost podatkov. Skrbniki podatkovnega skladišča morajo spremljati, nadaljevati in ustaviti nalaganje na podlagi zmogljivosti strežnika.
Vrste nakladanja:
- Začetna obremenitev — polnjenje vsega
tabele Data Warehouse - Inkrementalna obremenitev — izvajanje v teku
po potrebi redno spreminjati - Popolna osvežitev — čiščenje vsebine
ene ali več tabel in njihovo ponovno nalaganje s svežimi podatki
Preverjanje obremenitve
- Preverite, ali podatki o ključnem polju ne manjkajo ali so ničelni.
- Preizkusiti je treba poglede modeliranja, ki temeljijo na ciljnih tabelah.
- Preglejte kombinirane vrednosti3 in izračunane mere.
- Preverjanje podatkov v dimenzijskih in zgodovinskih tabelah.
- Preglejte poročila BI o naloženi tabeli dejstev in dimenzij.
Nastavitev ETL z uporabo PythonScript
Posledično morate izvesti osnovno nalaganje izvlečka transformacije (ETL) iz več baz podatkov v podatkovno skladišče, da lahko izvedete združevanje podatkov za poslovno inteligenco. Na voljo je več paketov ETL, za katere ste mislili, da so pretirani za vaš osnovni primer uporabe.
V tem članku vam bom pokazal, kako ekstrahirati podatke iz MySQL, SQL-strežnika in firebirda. S Python 3.6 transformirajte podatke in jih naložite v SQL-strežnik (skladišče podatkov).
Najprej moramo ustvariti imenik za naš projekt:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Če želite nastaviti ETL z uporabo Pythona, boste morali ustvariti naslednje datoteke v imeniku vašega projekta.
- db_credentials.py: Imeti mora vse informacije, potrebne za povezavo z vsemi zbirkami podatkov. kot so geslo baze podatkov, številka vrat itd.
- sql_queries.py: Na voljo morajo biti vse pogosto uporabljene poizvedbe baze podatkov za ekstrahiranje in nalaganje podatkov v obliki niza.
- etl.py: Povežite se z bazo podatkov in opravite potrebne poizvedbe z izvajanjem vseh potrebnih postopkov.
- main.py: Odgovoren za vodenje poteka operacij in izvajanje bistvenih operacij v določenem vrstnem redu.
V tem razdelku sql_queries.py je to mesto, kjer bomo shranili vse naše poizvedbe sql za ekstrahiranje iz izvornih baz podatkov in uvoz v našo ciljno bazo podatkov (podatkovno skladišče).
Nastavite poverilnice in spremenljivke zbirke podatkov
V variables.py ustvarite spremenljivko za zapis imena baze.
datawarehouse_name = 'your_datawarehouse_name'
Konfigurirajte vse izvorne in ciljne povezovalne nize ter poverilnice v db_credentials.py, kot je prikazano spodaj. Shranite konfiguracijo kot seznam, da jo bomo lahko pozneje, kadar koli bo potrebno, ponovili skozi številne baze podatkov.
iz spremenljivk 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', ' baza podatkov': '{}'.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' : "/your/path/to/source.db", 'user': "your_username", 'password': "your_password", } ]
Poizvedbe SQL
V tem razdelku sql_queries.py je to mesto, kjer bomo shranili vse naše poizvedbe sql za ekstrahiranje iz izvornih baz podatkov in uvoz v našo ciljno bazo podatkov (podatkovno skladišče).
Za vsako bazo podatkov moramo implementirati različne sintakse, ker delamo z več podatkovnimi platformami. To lahko storimo tako, da ločimo poizvedbe glede na vrsto baze podatkov.
# primeri poizvedb bodo različni na različnih platformah db firebird_extract = (''' IZBERI fbd_column_1, fbd_column_2, fbd_column_3 IZ fbd_table; ''') firebird_insert = ('''' VSTAVI V tabelo (column_1, column_2, column_3) VALUES (?, ?, ?) ''') firebird_extract_2 = (''' IZBERI fbd_column_1, fbd_column_2, fbd_column_3 IZ fbd_table_2; ''') firebird_insert_2 = (''' VSTAVI V tabelo_2 (stolpec_1, stolpec_2, stolpec_3) VREDNOSTI (?, ?, ? ) ''') sqlserver_extract = ('''' IZBERI sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 IZ sqlserver_table ''') sqlserver_insert = ('''' VSTAVI V tabelo (stolpec_1, stolpec_2, stolpec_3) VREDNOSTI (?, ?, ?) ''' ) mysql_extract = ('''' IZBERI mysql_column_1, mysql_column_2, mysql_column_3 IZ mysql_table ''') mysql_insert = ('''' VSTAVI V tabelo (stolpec_1, stolpec_2, stolpec_3) VREDNOSTI (?, ?, ?) ''') # izvozne poizvedbe class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # ustvari primerke za razred 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) # shrani kot seznam za iteracijo fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Ekstrahiraj transformacijsko obremenitev
Za nastavitev ETL z uporabo Pythona za zgoraj omenjene vire podatkov boste potrebovali naslednje module:
# moduli python import mysql.connector import pyodbc import fdb # spremenljivke iz spremenljivk import datawarehouse_name
Pri tem lahko uporabimo dve tehniki: etl() in etl_process().
etl_process() je postopek za vzpostavitev povezave z izvorom baze podatkov in klic metode etl(), ki temelji na platformi baze podatkov.
In v drugi metodi, ki je metoda etl(), najprej zažene poizvedbo za izvleček, nato shrani podatke SQL v spremenljive podatke in jih vstavi v ciljno zbirko podatkov, ki je naše podatkovno skladišče. Preoblikovanje podatkov je mogoče doseči s spreminjanjem podatkovne spremenljivke vrste tuple.
def etl(query, source_cnx, target_cnx): # ekstrahiraj podatke iz izvorne baze podatkov source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # naloži podatke v skladišče db if podatki: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('podatki naloženi v skladišče db') target_cursor.close() else : print('podatki so prazni') def etl_process(queries, target_cnx, source_db_config, db_platform): # vzpostavi povezavo izvorne db 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 'Napaka! neprepoznana platforma db' # zanka po poizvedbah sql za poizvedbo v poizvedbah: etl(query, source_cnx, target_cnx) # zapri izvorno povezavo db source_cnx.close()
Združevanje vsega skupaj
Zdaj lahko v naslednjem koraku preletimo vse poverilnice v main.py in izvedemo etl za vse baze podatkov.
Za to moramo uvoziti vse zahtevane spremenljivke in metode:
# spremenljivke iz db_credentials uvoz datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config iz sql_queries uvoz fbd_queries, sqlserver_queries, mysql_queries iz spremenljivk uvoz * # metode iz etl import etl_process
Koda v tej datoteki je odgovorna za ponavljanje poverilnic, da se povežete z bazo podatkov in izvedete potrebne operacije ETL z uporabo Pythona.
def main(): print('starting etl') # vzpostavitev povezave za ciljno bazo podatkov (sql-strežnik) target_cnx = pyodbc.connect(**datawarehouse_db_config) # pomik po poverilnicah # mysql za konfiguracijo v mysql_db_config: poskusi: print("nalaganje db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') razen Izjema kot napaka: print("etl za {} ima napako".format(config['database'])) print ('sporočilo o napaki: {}'.format(error)) continue # sql-server for config v sqlserver_db_config: poskusi: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') razen Izjema kot napaka: print("etl za {} ima napako".format(config['database'])) print('sporočilo o napaki: {}'.format(error)) continue # firebird for config in fbd_db_config: poskusi: print("nalaganje db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') razen Izjema kot napaka: print("etl za {} ima napako".format(config ['baza podatkov'])) print('sporočilo o napaki: {}'.format(error)) continue target_cnx.close() if __name__ == "__main__": main()
V svoj terminal vnesite python main.py in pravkar ste ustvarili ETL s čistim skriptom python.
Orodja ETL
Na trgu obstaja več orodij za shranjevanje podatkov. Tukaj je nekaj najbolj znanih primerov:
1. MarkLogic:
MarkLogic je sistem za shranjevanje podatkov, ki uporablja niz poslovnih zmogljivosti za lažjo in hitrejšo integracijo podatkov. Poizveduje lahko po številnih vrstah podatkov, kot so dokumenti, razmerja in metapodatki.
https://www.marklogic.com/product/getting-started/
2. Oracle:
Oracle je najbolj priljubljena zbirka podatkov v industriji. Ponuja široko paleto rešitev Data Warehouse za lokalne storitve in storitve v oblaku. Pomaga pri boljši izkušnji strank s povečanjem operativne učinkovitosti.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift je Amazonova rešitev za shranjevanje podatkov. Je preprosta in stroškovno učinkovita rešitev za analiziranje različnih vrst podatkov s standardnim SQL in obstoječimi orodji za poslovno inteligenco. Omogoča tudi izvajanje kompleksnih poizvedb na petabajtih strukturiranih podatkov.
https://aws.amazon.com/redshift/?nc2=h_m1
zaključek
Ta članek vam je dal poglobljeno razumevanje, kaj je ETL, kot tudi vadnico po korakih o tem, kako nastaviti svoj ETL v Pythonu. Dal vam je tudi seznam najboljših orodij, ki jih večina današnjih organizacij uporablja za izgradnjo svojih podatkovnih cevovodov ETL.
Večina današnjih organizacij pa ima na drugi strani ogromno količino podatkov z zelo dinamično strukturo. Ustvarjanje cevovoda ETL iz nič za takšne podatke je težak postopek, saj bodo morale organizacije uporabiti veliko virov, da bi ustvarile ta cevovod in nato zagotovile, da bo lahko sledil velikemu obsegu podatkov in spremembam sheme.
O avtorju
Prashant Sharma
Trenutno študiram diplomo tehnologije (B.Tech) na tehnološkem inštitutu Vellore. Zelo sem navdušen nad programiranjem in njegovimi resničnimi aplikacijami, vključno z razvojem programske opreme, strojnim učenjem, globokim učenjem in podatkovno znanostjo.
Upam, da vam je članek všeč. Če se želite povezati z mano, se lahko povežete na:
ali za kakršne koli druge dvome, lahko pošlji mail tudi meni
Podobni
- '
- "
- Račun
- aktivna
- vsi
- Amazon
- analitika
- aplikacije
- OBMOČJE
- članek
- Avtomatizirano
- backup
- povečanje
- izgradnjo
- Building
- poslovni
- Poslovna inteligenca
- klic
- Pregledi
- cleveland
- Cloud
- storitev v oblaku
- Koda
- Komunikacija
- podjetje
- povezava
- naprej
- Ustvarjanje
- Mandatno
- datum
- integracija podatkov
- znanost o podatkih
- podatkovno skladišče
- skladišča podatkov
- Baze podatkov
- baze podatkov
- globoko učenje
- Razvoj
- Razvijalci
- Razvoj
- naprave
- Dimenzije
- Dokumenti
- voznik
- učinkovitosti
- itd
- izvedba
- vodstvo
- Doživetja
- pridobivanje
- Izvlečki
- Napaka
- prva
- Pretok
- format
- sveže
- polno
- vodi
- strojna oprema
- tukaj
- visoka
- zgodovina
- Kako
- Kako
- HTTPS
- identificirati
- vpliv
- uvoz
- Vključno
- Podatki
- integracija
- Intelligence
- Vprašanja
- IT
- Ključne
- velika
- UČITE
- učenje
- vrstica
- Seznam
- obremenitev
- strojno učenje
- upravljanje
- map
- Tržna
- mediji
- Najbolj popularni
- premikanje
- Imena
- Obvestilo
- številke
- Ponudbe
- deluje
- Operacijski sistemi
- operacije
- Oracle
- Da
- organizacije
- Ostalo
- partnerji
- Geslo
- performance
- platforma
- Platforme
- Popular
- Proizvedeno
- proizvodnja
- Programiranje
- programi
- Projekt
- vlečenje
- Python
- Surovi
- v realnem času
- Reality
- evidence
- okrevanje
- Razmerja
- Poročila
- viri
- Odgovor
- prihodki
- Znanost
- Storitve
- nastavite
- Kratke Hlače
- Enostavno
- So
- Software
- Razvoj programske opreme
- rešitve
- SQL
- Stage
- Država
- trgovina
- trgovine
- sistem
- sistemi
- ciljna
- tech
- tehnike
- Tehnologija
- Vir
- čas
- Preoblikovanje
- Navodila
- Nadgradnja
- vrednost
- prodajalci
- Obseg
- Skladišče
- skladiščenje
- Tedenski
- delo
- deluje