Hva er ETL?
ETL er en prosess som trekker ut data fra flere kildesystemer, endrer dem (gjennom beregninger, sammenkoblinger og så videre), og deretter legger dem inn i datavarehussystemet. ETL står for Extract, Transform og Load.
Det er lett å tro at det å bygge et datavarehus er så enkelt som å hente data fra en rekke kilder og mate dem inn i en datavarehusdatabase. Dette er langt fra tilfelle, og det kreves en komplisert ETL-prosedyre. ETL-prosessen, som er teknisk kompleks, involverer aktiv deltakelse fra en rekke interessenter, inkludert utviklere, analytikere, testere og toppledere.
For å bevare verdien som beslutningsverktøy, må datavarehussystemet utvikles i takt med forretningsutviklingen. ETL er en vanlig (daglig, ukentlig, månedlig) prosess i et datavarehussystem som må være smidig, automatisert og riktig dokumentert.
Hvordan fungerer ETL?
Her vil vi lære hvordan ETL-prosessen fungerer trinn for trinn:
Trinn 1) Ekstraksjon
Data trekkes ut fra kildesystemet og plasseres i oppstillingsområdet under uttak. Hvis det kreves noen transformasjoner, utføres de i oppstillingsområdet slik at ytelsen til kildesystemet ikke blir skadet. Tilbakerulling vil være vanskelig hvis skadede data overføres direkte fra kilden til datavarehusdatabasen. Før du flytter utpakkede data inn i datavarehuset, kan de valideres i oppsamlingsområdet.
Datavarehus kan kombinere systemer med forskjellig maskinvare, databasestyringssystemer, operativsystemer og kommunikasjonsprotokoller. Datavarehus må kombinere systemer med ulike DBMS, maskinvare, operativsystemer og kommunikasjonsprotokoller. Kilder kan inkludere eldre programmer som stormaskiner, tilpassede applikasjoner, kontaktpunkter som minibanker og ringebrytere, tekstfiler, regneark, ERP, data fra leverandører og partnere, og så videre.
Derfor, før du trekker ut data og laster dem fysisk, kreves det et logisk datakart. Sammenhengen mellom kilder og måldata vises i dette datakartet.
Tre metoder for datautvinning:
- Delvis utvinning – Hvis kildesystemet varsler deg når en post endres, er det den enkleste måten å få tak i dataene på.
- Delvis utvinning (uten oppdateringsvarsel) – Ikke alle systemer kan levere et varsel når en oppdatering skjer; men de kan vise til postene som har blitt endret og gi utvinning av disse postene.
- Fullt utdrag – Enkelte systemer er ikke i stand til å avgjøre hvilke data som i det hele tatt er endret. I dette scenariet er den eneste måten å få dataene ut av systemet på å utføre et fullstendig uttrekk. Denne tilnærmingen krever at du har en sikkerhetskopi av det forrige utdraget i
samme format tilgjengelig for å identifisere endringene som er gjort.
Uavhengig av metoden som brukes, bør utvinning ikke ha innvirkning på ytelsen eller responstiden til kildesystemene. Dette er sanntids produksjonsdatabaser. Enhver nedgang eller låsing kan ha innvirkning på selskapets bunnlinje.
Trinn 2) Transformasjon
Dataene som hentes fra kildeserveren er rå og ubrukelige i sin opprinnelige tilstand. Som et resultat må det renses, kartlegges og transformeres. I virkeligheten er dette nøkkeltrinnet der ETL-prosessen tilfører verdi og transformerer data for å produsere meningsfulle BI-rapporter.
Det er et sentralt ETL-konsept der du bruker en samling funksjoner på uttrukket data. Direkte trekk or gå gjennom data er typen data som ikke krever noen transformasjon.
Du kan utføre tilpassede operasjoner på data under transformasjonstrinnet. Anta for eksempel at klienten ønsker en salgssum som ikke finnes i databasen. eller hvis for- og etternavnet i en tabell er i separate kolonner. Før lasting kan de settes sammen.
Følgende er noen eksempler på problemer med dataintegritet:
- Ulike stavemåter av samme individ, som Prashant, Parshant, og etc.
- Det er mange måter å representere et firmanavn på, for eksempel Google, Google Inc.
- Ulike navn, som Cleaveland og Cleveland, brukes.
- Det er mulig at flere kontonumre produseres av forskjellige applikasjoner for samme klient.
- Noen nødvendige datafiler er tomme.
Trinn 3) Lasting
Det siste stadiet i ETL-prosessen er å laste data inn i måldatavarehusdatabasen. Et stort datavolum lastes på relativt kort tid i et typisk datavarehus. Som et resultat bør lasteprosessen optimaliseres for ytelse.
Hvis det oppstår en belastningsfeil, bør gjenopprettingsprosedyrer settes på plass slik at operasjoner kan startes på nytt fra feilpunktet uten å kompromittere dataintegriteten. Datavarehusadministratorer må overvåke, fortsette og stoppe belastninger basert på serverytelse.
Typer lasting:
- Innledende belastning — fyller alt
datavarehus-tabellene - Inkrementell belastning – implementering pågår
endringer etter behov med jevne mellomrom - Full oppdatering — tømme innholdet
av en eller flere tabeller og laster dem på nytt med ferske data
Last bekreftelse
- Sjekk at nøkkelfeltdataene ikke mangler eller er null.
- Modelleringsvisninger basert på måltabeller bør testes.
- Undersøk de kombinerte verdiene3 og beregnede mål.
- Datasjekker i dimensjons- og historikktabellene.
- Undersøk BI-rapportene på den innlastede fakta- og dimensjonstabellen.
Sette opp ETL ved hjelp av PythonScript
Som et resultat må du utføre grunnleggende Extract Transform Load (ETL) fra flere databaser til et datavarehus for å kunne gjøre dataaggregering for business intelligence. Det er flere tilgjengelige ETL-pakker som du mente var overdrevne for din grunnleggende bruk.
Jeg skal vise deg hvordan du trekker ut data fra MySQL, SQL-server og firebird i denne artikkelen. Bruk Python 3.6, transformer dataene og last dem inn i SQL-server (datavarehus).
Først av alt må vi lage en katalog for prosjektet vårt:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
For å sette opp ETL ved hjelp av Python, må du generere følgende filer i prosjektkatalogen din.
- db_credentials.py: Bør ha all informasjonen som trengs for å koble til alle databaser. som databasepassord, portnummer osv.
- sql_queries.py: Alle vanlig brukte databasespørringer for å trekke ut og laste data i strengformat bør være tilgjengelige.
- etl.py: Koble til databasen og utfør de nødvendige spørringene ved å utføre alle nødvendige prosedyrer.
- main.py: Ansvarlig for å administrere flyten av operasjoner og utføre de essensielle operasjonene i en spesifisert rekkefølge.
I denne delen av sql_queries.py er dette stedet hvor vi skal lagre alle våre sql-spørringer for å trekke ut fra kildedatabaser og importere til måldatabasen vår (datavarehus)
Sett opp databaselegitimasjon og variabler
I variables.py oppretter du en variabel for å registrere navnet på basen.
datawarehouse_name = 'ditt_datavarehus_navn'
Konfigurer alle kilde- og måldatabasetilkoblingsstrengene og påloggingsinformasjonen i db_credentials.py som vist nedenfor. Lagre konfigurasjonen som en liste slik at vi kan gjenta den når det er nødvendig gjennom mange databaser senere.
fra variabler importer datawarehouse_name datawarehouse_name = 'ditt_datavarehus_navn' # sql-server (mål-db, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', ' database': '{}'.format(datawarehouse_name), 'user': 'ditt_db_brukernavn', 'passord': 'ditt_db_passord', 'autocommit': True, } # sql-server (kildedb) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'din_sql_server', 'database': 'db1', 'user': 'ditt_db_brukernavn', 'passord': 'ditt_db_passord', ' autocommit': True, } ] # mysql (kilde 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': 'ditt_passord_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (kilde db) fdb_db_config = [ { 'dsn' : "/din/sti/til/kilde.db", 'bruker': "ditt_brukernavn", 'passord': "ditt_passord", } ]
SQL-spørsmål
I denne delen av sql_queries.py er dette stedet hvor vi skal lagre alle sql-spørringene våre for å trekke ut fra kildedatabaser og importere til måldatabasen vår (datavarehus).
Vi må implementere ulike syntakser for hver database fordi vi jobber med flere dataplattformer. Vi kan gjøre dette ved å skille spørringene basert på databasetypen.
# eksempelspørringer, vil være forskjellige på tvers av forskjellige db-plattformer 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 = (''' VELG fbd_column_2, fbd_column_3, fbd_column_2 FROM fbd_table_2; ''') firebird_insert_2 = (''' SETT INN I tabell_1 (kolonne_2, kolonne 3, kolonne_1, kolonne?)? ) ''') sqlserver_extract = (''' VELG sqlserver_column_2, sqlserver_column_3, sqlserver_column_1 FROM sqlserver_table ''') sqlserver_insert = (''' SETTE INN I tabell (column_2, VALUE, kolonne_3,')?) ) mysql_extract = (''' VELG mysql_column_1, mysql_column_2, mysql_column_3 FROM mysql_table ''') mysql_insert = (''' SETT INN I tabell (column_1, column_2, column_3) 'Søker?, eksporterer? klasse SqlQuery: def __init__(selv, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # opprett forekomster for SqlQuery klasse fbd_query = SqlQuery(firebird_extract, firebird_extract, firebird_extract_firebird_extract_qlerts_extract SqlQuery_2servers , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # lagre som liste for iterasjon fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] = mysql_queries
Trekk ut transformasjonsbelastning
For å sette opp ETL ved hjelp av Python for de ovennevnte datakildene, trenger du følgende moduler:
# python-moduler import mysql.connector import pyodbc import fdb # variabler fra variabler import datawarehouse_name
Vi kan bruke to teknikker i dette: etl() og etl_process().
etl_process() er prosedyren for å etablere en databasekildetilkobling og kalle etl()-metoden basert på databaseplattformen.
Og i den andre metoden, som er etl()-metoden, kjører den uttrekksspørringen først, og lagrer deretter SQL-dataene i variable data og setter dem inn i den målrettede databasen, som er datavarehuset vårt. Datatransformasjon kan oppnås ved å endre datavariabelen til typen tuppel.
def etl(query, source_cnx, target_cnx): # trekk ut data fra kilden db source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # last inn data til lageret db hvis data: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('data lastet til lager db') target_cursor.close() else : print('data er tomme') def etl_process(queries, target_cnx, source_db_config, db_platform): # etablere kildedb-tilkobling hvis db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_config) elif db_plattform == 'sqlserver': source_cnx = pyodbc.connect(**source_db_config) elif db_platform == 'firebird': source_cnx = fdb.connect(**source_db_config) else: return 'Feil! ukjent db-plattform' # sløyfe gjennom sql-spørringer for spørring i spørringer: etl(query, source_cnx, target_cnx) # lukk kilde-db-tilkoblingen source_cnx.close()
Setter alt sammen
Nå, i neste trinn, kan vi gå over all legitimasjon i main.py og kjøre etl for alle databaser.
For det må 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 * # etl_queries fra variabler etl
Koden i denne filen er ansvarlig for å iterere over legitimasjon for å koble til databasen og utføre de nødvendige ETL ved hjelp av Python-operasjoner.
def main(): print('starting etl') # etablere tilkobling for måldatabase (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # loop gjennom legitimasjon # mysql for config i mysql_db_config: prøv: print("loading db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') bortsett fra Unntak som feil: print("etl for {} har feil".format(config['database'])) print ('feilmelding: {}'.format(error)) fortsett # sql-server for config i sqlserver_db_config: try: print("laster db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') bortsett fra Unntak som feil: print("etl for {} har feil".format(config['database'])) print('feilmelding: {}'.format(error)) fortsett # firebird for config i fbd_db_config: try: print("laster db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') bortsett fra Unntak som feil: print("etl for {} har feil".format(config ['database'])) print('feilmelding: {}'.format(error)) continue target_cnx.close() if __name__ == "__main__": main()
Skriv inn python main.py i terminalen din, og du har nettopp opprettet en ETL ved å bruke et rent python-skript.
ETL-verktøy
Det finnes flere datavarehusverktøy på markedet. Her er noen av de mest kjente eksemplene:
1. MarkLogic:
MarkLogic er et datavarehussystem som bruker en rekke forretningsmuligheter for å gjøre dataintegrasjon enklere og raskere. Den kan søke etter mange typer data, for eksempel dokumenter, relasjoner og metadata.
https://www.marklogic.com/product/getting-started/
2. Oracle:
Oracle er bransjens mest populære database. Det tilbyr et stort utvalg av datavarehusløsninger for både lokale og skytjenester. Det hjelper til med bedre kundeopplevelser ved å øke driftseffektiviteten.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift er en datavarehusløsning fra Amazon. Det er en enkel og kostnadseffektiv løsning for å analysere ulike typer data med standard SQL og eksisterende business intelligence-verktøy. Den gjør det også mulig å utføre komplekse spørringer på petabyte med strukturerte data.
https://aws.amazon.com/redshift/?nc2=h_m1
konklusjonen
Denne artikkelen ga deg en dyp forståelse av hva ETL er, samt en trinn-for-trinn-veiledning om hvordan du setter opp ETL i Python. Den ga deg også en liste over de beste verktøyene som de fleste organisasjoner i dag bruker for å bygge opp sine ETL-datapipelines.
De fleste organisasjoner i dag, derimot, har en enorm mengde data med en svært dynamisk struktur. Å lage en ETL-pipeline fra bunnen av for slike data er en vanskelig prosedyre siden organisasjoner må bruke et stort antall ressurser for å lage denne pipeline og deretter sikre at den kan holde tritt med det høye datavolumet og skjemaendringene.
Om forfatteren
Prashant Sharma
For øyeblikket følger jeg min bachelorgrad i teknologi (B.Tech) fra Vellore Institute of Technology. Jeg er veldig entusiastisk for programmering og dens virkelige applikasjoner, inkludert programvareutvikling, maskinlæring, dyp læring og datavitenskap.
Jeg håper du liker artikkelen. Hvis du vil komme i kontakt med meg, kan du koble til på:
eller for annen tvil, kan du send en mail til meg også
I slekt
- '
- "
- Logg inn
- aktiv
- Alle
- Amazon
- analytics
- søknader
- AREA
- Artikkel
- Automatisert
- Backup
- øke
- bygge
- Bygning
- virksomhet
- business intelligence
- ring
- Sjekker
- cleveland
- Cloud
- skytjenester
- kode
- Kommunikasjon
- Selskapet
- tilkobling
- fortsette
- Opprette
- Credentials
- dato
- dataintegrasjon
- datavitenskap
- datalager
- datavarehus
- Database
- databaser
- dyp læring
- utvikle
- utviklere
- Utvikling
- Enheter
- Dimensjon
- dokumenter
- sjåfør
- effektivitet
- etc
- gjennomføring
- ledere
- Erfaringer
- utdrag
- ekstrakter
- Failure
- Først
- flyten
- format
- fersk
- fullt
- veilede
- maskinvare
- her.
- Høy
- historie
- Hvordan
- Hvordan
- HTTPS
- identifisere
- Påvirkning
- importere
- Inkludert
- informasjon
- integrering
- Intelligens
- saker
- IT
- nøkkel
- stor
- LÆRE
- læring
- linje
- Liste
- laste
- maskinlæring
- ledelse
- kart
- marked
- Media
- Mest populær
- flytte
- navn
- varsling
- tall
- Tilbud
- drift
- operativsystemer
- Drift
- orakel
- rekkefølge
- organisasjoner
- Annen
- partnere
- Passord
- ytelse
- plattform
- Plattformer
- Populær
- produsert
- Produksjon
- Programmering
- programmer
- prosjekt
- trekke
- Python
- Raw
- sanntids
- Reality
- poster
- utvinning
- Relasjoner
- Rapporter
- Ressurser
- svar
- inntekter
- Vitenskap
- Tjenester
- sett
- Kort
- Enkelt
- So
- Software
- programvareutvikling
- Solutions
- SQL
- Scene
- Tilstand
- oppbevare
- butikker
- system
- Systemer
- Target
- tech
- teknikker
- Teknologi
- Kilden
- tid
- Transformation
- tutorial
- Oppdater
- verdi
- leverandører
- volum
- Warehouse
- lager
- ukentlig
- Arbeid
- virker