Mikä on ETL?
ETL on prosessi, joka poimii tietoja useista lähdejärjestelmistä, muuttaa sitä (laskutoimitusten, ketjutusten ja niin edelleen) ja lisää sen sitten tietovarastojärjestelmään. ETL tarkoittaa Extract, Transform ja Load.
On helppo uskoa, että tietovaraston rakentaminen on yhtä helppoa kuin tietojen hakeminen useista lähteistä ja syöttäminen tietovarastotietokantaan. Tämä ei ole kaukana, ja tarvitaan monimutkainen ETL-menettely. ETL-prosessi, joka on teknisesti monimutkainen, edellyttää aktiivista osallistumista useilta sidosryhmiltä, mukaan lukien kehittäjät, analyytikot, testaajat ja ylimmät johtajat.
Säilyttääkseen arvonsa päätöksenteon työkaluna tietovarastojärjestelmän tulee kehittyä tahdissa liiketoiminnan kehityksen kanssa. ETL on säännöllinen (päivittäinen, viikoittainen, kuukausittainen) tietovarastojärjestelmän prosessi, jonka on oltava ketterä, automatisoitu ja asianmukaisesti dokumentoitu.
Miten ETL toimii?
Täällä opimme kuinka ETL-prosessi toimii vaihe vaiheelta:
Vaihe 1) Uutto
Tiedot poimitaan lähdejärjestelmästä ja sijoitetaan esitysalueelle poiminnan aikana. Jos muunnoksia tarvitaan, ne suoritetaan lavastusalueella, jotta lähdejärjestelmän suorituskyky ei vahingoitu. Palauttaminen on vaikeaa, jos vahingoittuneet tiedot siirretään suoraan lähteestä Tietovaraston tietokantaan. Ennen kuin poimitut tiedot siirretään tietovarastoon, ne voidaan validoida välitysalueella.
Tietovarastot voivat yhdistää järjestelmiä erilaisiin laitteistoihin, tietokantojen hallintajärjestelmiin, käyttöjärjestelmiin ja tietoliikenneprotokolliin. Tietovarastojen on yhdistettävä järjestelmiä, joissa on erilaisia DBMS-, laitteisto-, käyttöjärjestelmiä ja viestintäprotokollia. Lähteitä voivat olla vanhat ohjelmat, kuten keskustietokoneet, mukautetut sovellukset, yhteyspistelaitteet, kuten pankkiautomaatit ja puhelun kytkimet, tekstitiedostot, laskentataulukot, ERP, tiedot toimittajilta ja kumppaneilta ja niin edelleen.
Siksi ennen tietojen poimimista ja fyysistä lataamista tarvitaan looginen tietokartta. Tässä tietokartassa näkyy lähteiden ja kohdetietojen välinen yhteys.
Kolme tiedonpoistomenetelmää:
- Osittainen poisto – Jos lähdejärjestelmä hälyttää tietueen muuttamisesta, se on yksinkertaisin tapa saada tiedot.
- Osittainen purku (ilman päivitysilmoitusta) – Kaikki järjestelmät eivät voi toimittaa ilmoitusta päivityksen tapahtuessa. mutta he voivat ilmoittaa tietueille, jotka on muutettu, ja toimittaa niistä poimia.
- Täysi ote – Tietyt järjestelmät eivät pysty määrittämään, mitä tietoja on ylipäätään muutettu. Tässä skenaariossa ainoa tapa saada tiedot ulos järjestelmästä on suorittaa täydellinen purku. Tämä lähestymistapa vaatii varmuuskopion edellisestä otteesta
samassa muodossa, jotta voidaan tunnistaa tehdyt muutokset.
Riippumatta käytetystä menetelmästä purkamisen ei pitäisi vaikuttaa lähdejärjestelmien suorituskykyyn tai vasteaikaan. Nämä ovat reaaliaikaisia tuotantotietokantoja. Mikä tahansa hidastuminen tai lukittuminen saattaa vaikuttaa yhtiön tulokseen.
Vaihe 2) Muunnos
Lähdepalvelimelta haettu data on raakaa ja käyttökelvotonta alkuperäisessä tilassaan. Tämän seurauksena se on puhdistettava, kartoitettava ja muutettava. Todellisuudessa tämä on avainvaihe, jossa ETL-prosessi tuo lisäarvoa ja muuntaa tietoja merkityksellisten BI-raporttien tuottamiseksi.
Se on keskeinen ETL-konsepti, jossa voit käyttää funktiokokoelmaa poimittuihin tietoihin. Suora liike or kulkea sen läpia on datatyyppi, joka ei vaadi mitään muuntamista.
Voit suorittaa mukautettuja toimintoja datalle muunnosvaiheen aikana. Oletetaan esimerkiksi, että asiakas haluaa myyntitulojen summan, jota ei ole tietokannassa. tai jos taulukon etu- ja sukunimet ovat erillisissä sarakkeissa. Ennen lataamista ne voidaan ketjuttaa.
Seuraavassa on esimerkkejä tietojen eheysongelmista:
- Saman henkilön eri kirjoitusasut, kuten Prashant, Parshant jne.
- Yrityksen nimeä voidaan esittää monella tapaa, kuten Google, Google Inc.
- Käytetään erilaisia nimiä, kuten Cleaveland ja Cleveland.
- On mahdollista, että samalle asiakkaalle eri sovellukset tuottavat useita tilinumeroita.
- Jotkut tarvittavat tiedot jätetään tyhjiksi.
Vaihe 3) Lataus
ETL-prosessin viimeinen vaihe on tietojen lataaminen kohdetietovarastotietokantaan. Suuri määrä dataa ladataan suhteellisen lyhyessä ajassa tyypilliseen tietovarastoon. Tämän seurauksena latausprosessi tulisi optimoida suorituskyvyn kannalta.
Kuormahäiriön sattuessa tulee ottaa käyttöön palautusmenettelyt, jotta toiminnot voidaan käynnistää uudelleen virhepisteestä vaarantamatta tietojen eheyttä. Data Warehouse -järjestelmänvalvojien on valvottava, jatkettava ja pysäytettävä latauksia palvelimen suorituskyvyn perusteella.
Lataustyypit:
- Alkukuorma - täyttää kaikki
Tietovaraston taulukot - Inkrementaalinen kuormitus — täytäntöönpano käynnissä
muutoksia tarpeen mukaan säännöllisesti - Täysi päivitys - sisällön tyhjentäminen
yhden tai useamman taulukon ja lataamalla ne uudelleen tuoreilla tiedoilla
Lataa vahvistus
- Tarkista, että avainkentän tiedot eivät ole puuttuvia tai tyhjiä.
- Kohdetaulukoihin perustuvia mallinnusnäkymiä tulisi testata.
- Tarkastele yhdistettyjä arvoja3 ja laskettuja mittoja.
- Tietojen tarkistukset ulottuvuus- ja historiataulukoissa.
- Tutki ladatun tieto- ja dimensiotaulukon BI-raportteja.
ETL:n määrittäminen PythonScriptin avulla
Tämän seurauksena sinun on suoritettava ETL (Extract Transform Load) -perustoiminto useista tietokannoista tietovarastoon, jotta voit koota tietoja liiketoimintatiedon saamiseksi. Saatavilla on useita ETL-paketteja, joiden uskot olevan liikaa peruskäyttöön.
Näytän sinulle, kuinka voit purkaa tietoja MySQL:stä, SQL-palvelimesta ja Firebirdistä tässä artikkelissa. Muunna tiedot Python 3.6:lla ja lataa ne SQL-palvelimeen (tietovarastoon).
Ensinnäkin meidän on luotava hakemisto projektillemme:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Jos haluat määrittää ETL:n Pythonilla, sinun on luotava seuraavat tiedostot projektihakemistoosi.
- db_credentials.py: Pitäisi olla kaikki tiedot, joita tarvitaan yhteyden muodostamiseen kaikkiin tietokantoihin. kuten tietokannan salasana, portin numero jne.
- sql_queries.py: Kaikkien yleisesti käytettyjen tietokantakyselyiden poimimiseen ja lataamiseen merkkijonomuodossa pitäisi olla saatavilla.
- etl.py: Yhdistä tietokantaan ja suorita tarvittavat kyselyt suorittamalla kaikki tarvittavat toimenpiteet.
- main.py: Vastaat toimintojen hallinnasta ja olennaisten toimintojen suorittamisesta määrätyssä järjestyksessä.
Tässä sql_queries.py-osiossa tämä on paikka, johon tallennamme kaikki sql-kyselymme lähdetietokannoista poimimista ja kohdetietokantaan (tietovarastoon) tuomista varten.
Määritä tietokannan tunnistetiedot ja muuttujat
Luo muuttuja variables.py:ssä kannan nimen tallentamiseksi.
datawarehouse_name = 'tietovaraston_nimi'
Määritä kaikki lähde- ja kohdetietokannan yhteysmerkkijonot ja valtuustiedot tiedostossa db_credentials.py alla kuvatulla tavalla. Tallenna kokoonpano luettelona, jotta voimme toistaa sitä tarvittaessa useiden tietokantojen kautta myöhemmin.
muuttujista import datawarehouse_name datawarehouse_name = 'tietovaraston_nimi' # sql-server (kohde db, tietovarasto) datawarehouse_db_config = { 'Trusted_Connection': 'kyllä', 'ohjain': '{SQL-palvelin}', 'palvelin': 'sq'ltapalvelin tietokanta': '{}'.format(tietovaraston_nimi), 'user': 'oma_db_käyttäjänimesi', 'salasana': 'oma_db_salasana', 'autocommit': True, } # sql-server (lähdedb) sqlserver_db_configrusted = [_Connection'T ': 'kyllä', 'ohjain': '{SQL-palvelin}', 'palvelin': 'oma_sql_palvelin', 'tietokanta': 'db1', 'käyttäjä': 'your_db_username', 'salasana': 'your_db_password', ' autocommit': True, } ] # mysql (lähde db) mysql_db_config = [ { 'käyttäjä': 'käyttäjäsi_1', 'salasana': 'salasanasi_1', 'isäntä': 'db_connection_string_1', 'tietokanta', 'tietokanta' , { 'user': 'your_user_1', 'password': 'your_password_2', 'host': 'db_connection_string_2', 'tietokanta': 'db_2', }, ] # firebird (lähde db) fdb_db_'confign [' {confign : "/oma/polku/lähde.db", 'käyttäjä': "käyttäjänimesi", 'salasana': "sinä r_salasana", } ]
SQL-kyselyt
Tässä sql_queries.py-osiossa tämä on paikka, johon tallennamme kaikki sql-kyselymme lähdetietokannoista poimimista ja kohdetietokantaan (tietovarastoon) tuomista varten.
Meidän on otettava käyttöön erilaisia syntakseja jokaiselle tietokannalle, koska työskentelemme useiden tietoalustojen kanssa. Voimme tehdä tämän erottamalla kyselyt tietokantatyypin perusteella.
# esimerkkikyselyt ovat erilaisia eri tietokanta-alustoissa firebird_extract = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO taulukko (sarake_1, sarake_2, sarake_3?) " ) ''') sqlserver_extract = (''' SELECT sqlserver_column_2, sqlserver_column_1, sqlserver_column_2 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO, sarake, ''3 sarake, _2 taulukko, sarake'n? ) mysql_extract = (''' SELECT mysql_column_2, mysql_column_2, mysql_column_1 FROM mysql_table ''') mysql_insert = (''' INSERT INTO -taulukko (sarake_2, sarake_3, sarake_1) ARVOJA, kyselyitä) (?) luokka 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_3, firebird_insert_1) 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]
Pura muunnoskuorma
ETL:n määrittäminen Pythonilla edellä mainituille tietolähteille tarvitset seuraavat moduulit:
# python-moduulit tuonti mysql.connector tuonti pyodbc tuonti fdb # muuttujat muuttujista tuonti tietovaraston_nimi
Voimme käyttää tässä kahta tekniikkaa: etl() ja etl_process().
etl_process() on menetelmä tietokantalähdeyhteyden muodostamiseksi ja etl()-menetelmän kutsumiseksi tietokantaalustaan perustuen.
Ja toisessa menetelmässä, joka on etl()-menetelmä, se suorittaa ensin poimintakyselyn, sitten tallentaa SQL-tiedot muuttujatietoihin ja lisää ne kohdetietokantaan, joka on tietovarastomme. Datamuunnos voidaan suorittaa muuttamalla tyyppituplen datamuuttujaa.
def etl(kysely, lähde_cnx, kohde_cnx): # poimi tiedot lähteestä db source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # lataa tiedot varastoon db if data: target_cursor = target_cnx.cursor() target_cursor.execute("KÄYTÄ {}".format(tietovaraston_nimi)) target_cursor.executemany(query.load_query, data) print('data ladattu varastoon db') target_cursor.close() else : print('data on tyhjä') def etl_process(queries, target_cnx, source_db_config, db_platform): # muodosta lähdedb-yhteys 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 'Virhe! tuntematon db-alusta' # silmukka sql-kyselyjen läpi kyselyille kyselyissä: etl(kysely, lähde_cnx, kohde_cnx) # sulje lähdetietokantayhteys source_cnx.close()
Kaikki yhteen
Nyt, seuraavassa vaiheessa, voimme silmukan ylittää kaikki main.py:n valtuustiedot ja suorittaa etl:n kaikille tietokannoille.
Tätä varten meidän on tuotava kaikki vaaditut muuttujat ja menetelmät:
# muuttujat osoitteesta db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config from sql_queries import fbd_queries, sqlserver_queries, mysql_queries from variables import * # method etl_process import
Tämän tiedoston koodi on vastuussa valtuustietojen iteroinnista, jotta se voi muodostaa yhteyden tietokantaan ja suorittaa tarvittavat ETL-koodit Python-toimintojen avulla.
def main(): print('starting etl') # muodosta yhteys kohdetietokantaan (sql-palvelin) target_cnx = pyodbc.connect(**datawarehouse_db_config) # silmukka valtuustietojen läpi # mysql for config mysql_db_config: try: print("lataus db: " + config['tietokanta']) etl_process(mysql_queries, target_cnx, config, 'mysql') paitsi Poikkeus virheenä: print("etl for {} has error".format(config['tietokanta'])) print ('virheilmoitus: {}'.format(error)) jatka # sql-server for config in sqlserver_db_config: try: print("loading db: " + config['tietokanta']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') paitsi Poikkeus virheenä: print("etl for {} has error".format(config['tietokanta'])) print('error message: {}'.format(error)) jatka # firebird for config in fbd_db_config: try: print("loading db: " + config['tietokanta']) etl_process(fbd_queries, target_cnx, config, 'firebird') paitsi Poikkeus virheenä: print("etl for {} has error".format(config ['tietokanta'])) print('virhesanoma: {}'.fo rmat(error)) jatka target_cnx.close() if __name__ == "__main__": main()
Kirjoita päätteeseen python main.py ja olet juuri luonut ETL:n puhtaalla python-skriptillä.
ETL-työkalut
Markkinoilla on useita Data Warehousing -työkaluja. Tässä on joitain tunnetuimpia esimerkkejä:
1. MarkLogic:
MarkLogic on tietovarastojärjestelmä, joka käyttää erilaisia liiketoimintaominaisuuksia tehdäkseen tietojen integroinnista helpompaa ja nopeampaa. Se voi kysyä monenlaisia tietoja, kuten asiakirjoja, suhteita ja metatietoja.
https://www.marklogic.com/product/getting-started/
2. Oracle:
Oracle on alan suosituin tietokanta. Se tarjoaa laajan valikoiman Data Warehouse -ratkaisuja sekä paikan päällä että pilvipalveluihin. Se auttaa parantamaan asiakaskokemuksia tehostamalla toimintaa.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift on Amazonin tietovarastoratkaisu. Se on yksinkertainen ja kustannustehokas ratkaisu erilaisten tietojen analysointiin vakio-SQL:llä ja olemassa olevilla business intelligence -työkaluilla. Se mahdollistaa myös monimutkaisten kyselyjen suorittamisen strukturoidun datan petatavuille.
https://aws.amazon.com/redshift/?nc2=h_m1
Yhteenveto
Tämä artikkeli antoi sinulle syvällisen ymmärryksen siitä, mitä ETL on, sekä vaiheittaisen opetusohjelman ETL:n määrittämisestä Pythonissa. Se antoi sinulle myös luettelon hienoimmista työkaluista, joita useimmat organisaatiot käyttävät nykyään ETL-tietoputkien rakentamiseen.
Useimmilla organisaatioilla on nykyään valtava määrä dataa, jonka rakenne on erittäin dynaaminen. ETL-putken luominen tyhjästä tällaisille tiedoille on vaikea prosessi, koska organisaatioiden on käytettävä suuri määrä resursseja tämän putkiston luomiseksi ja sen jälkeen varmistettava, että se pysyy suuren datamäärän ja kaaviomuutosten mukana.
Author
Prashant Sharma
Tällä hetkellä suoritan tekniikan kandidaattini (B.Tech) Vellore Institute of Technologysta. Olen erittäin innostunut ohjelmoinnista ja sen todellisista sovelluksista, mukaan lukien ohjelmistokehitys, koneoppiminen, syväoppiminen ja datatiede.
Toivottavasti pidät artikkelista. Jos haluat olla yhteydessä minuun, voit ottaa yhteyttä:
tai jos sinulla on muita epäilyksiä, voit lähetä postia myös minulle
liittyvä
- '
- "
- Tili
- aktiivinen
- Kaikki
- Amazon
- Analytics
- sovellukset
- ALUE
- artikkeli
- Automatisoitu
- Varmuuskopiointi
- lisäämällä
- rakentaa
- Rakentaminen
- liiketoiminta
- bisnesvaisto
- soittaa
- Tarkastukset
- Cleveland
- pilvi
- pilvipalvelut
- koodi
- Viestintä
- yritys
- liitäntä
- jatkaa
- Luominen
- Valtakirja
- tiedot
- datan integraatio
- tietojenkäsittely
- tietovarasto
- tietovarastot
- tietokanta
- tietokannat
- syvä oppiminen
- kehittää
- kehittäjille
- Kehitys
- Laitteet
- Ulottuvuus
- asiakirjat
- kuljettaja
- tehokkuus
- jne.
- teloitus
- johtajat
- Elämykset
- uuttaminen
- otteet
- Epäonnistuminen
- Etunimi
- virtaus
- muoto
- tuore
- koko
- ohjaavat
- Tarvikkeet
- tätä
- Korkea
- historia
- Miten
- Miten
- HTTPS
- tunnistaa
- Vaikutus
- tuovan
- Mukaan lukien
- tiedot
- integraatio
- Älykkyys
- kysymykset
- IT
- avain
- suuri
- OPPIA
- oppiminen
- linja
- Lista
- kuormitus
- koneoppiminen
- johto
- kartta
- markkinat
- Media
- Suosituin
- liikkua
- nimet
- ilmoituksen
- numerot
- Tarjoukset
- toiminta
- käyttöjärjestelmät
- Operations
- oraakkeli
- tilata
- organisaatioiden
- Muut
- kumppani
- Salasana
- suorituskyky
- foorumi
- Platforms
- Suosittu
- valmistettu
- tuotanto
- Ohjelmointi
- Ohjelmat
- projekti
- vetämällä
- Python
- raaka
- reaaliaikainen
- Todellisuus
- asiakirjat
- elpyminen
- Ihmissuhteet
- Raportit
- Esittelymateriaalit
- vastaus
- tulot
- tiede
- Palvelut
- setti
- Lyhyt
- Yksinkertainen
- So
- Tuotteemme
- ohjelmistokehitys
- Ratkaisumme
- SQL
- Vaihe
- Osavaltio
- verkkokaupasta
- varastot
- järjestelmä
- järjestelmät
- Kohde
- teknologia
- tekniikat
- Elektroniikka
- Lähde
- aika
- Muutos
- oppitunti
- Päivitykset
- arvo
- myyjät
- tilavuus
- Varasto
- Varastointi
- viikoittain
- Referenssit
- toimii