Ce este ETL?
ETL este un proces care extrage date din mai multe sisteme surse, le modifică (prin calcule, concatenări și așa mai departe) și apoi le introduce în sistemul Data Warehouse. ETL înseamnă Extract, Transform, and Load.
Este ușor de crezut că construirea unui depozit de date este la fel de simplă ca extragerea datelor din numeroase surse și introducerea lor într-o bază de date a depozitului de date. Acest lucru este departe de a fi cazul și este necesară o procedură ETL complicată. Procesul ETL, care este complex din punct de vedere tehnic, implică participarea activă a unei varietăți de părți interesate, inclusiv dezvoltatori, analiști, testeri și directori seniori.
Pentru a-și păstra valoarea ca instrument de luare a deciziilor, sistemul de depozit de date trebuie să se dezvolte în sincron cu evoluțiile afacerii. ETL este un proces regulat (zilnic, săptămânal, lunar) al unui sistem de depozit de date care trebuie să fie agil, automatizat și documentat corespunzător.
Cum funcționează ETL?
Aici vom afla cum funcționează procesul ETL pas cu pas:
Pasul 1) Extracție
Datele sunt extrase din sistemul sursă și plasate în zona de pregătire în timpul extracției. Dacă sunt necesare transformări, acestea sunt efectuate în zona de staging, astfel încât performanța sistemului sursă să nu fie afectată. Rollback-ul va fi dificil dacă datele deteriorate sunt transferate direct de la sursă în baza de date a depozitului de date. Înainte de a muta datele extrase în Depozitul de date, acestea pot fi validate în zona de staging.
Depozitele de date pot combina sisteme cu diferite hardware, sisteme de gestionare a bazelor de date, sisteme de operare și protocoale de comunicare. Depozitele de date trebuie să combine sisteme cu SGBD, hardware, sisteme de operare și protocoale de comunicație disparate. Sursele pot include programe vechi, cum ar fi mainframe, aplicații personalizate, dispozitive de la punctul de contact, cum ar fi ATM-uri și comutatoare de apel, fișiere text, foi de calcul, ERP, date de la furnizori și parteneri și așa mai departe.
Astfel, înainte de a extrage datele și de a le încărca fizic, este necesară o hartă logică a datelor. Legătura dintre surse și datele țintă este afișată în această hartă de date.
Trei metode de extragere a datelor:
- Extracție parțială – Dacă sistemul sursă vă avertizează când o înregistrare este modificată, acesta este cel mai simplu mod de a obține datele.
- Extragere parțială (fără notificare de actualizare) – Nu toate sistemele pot livra o notificare atunci când are loc o actualizare; dar, ei pot indica înregistrările care au fost modificate și pot oferi extragerea acelor înregistrări.
- Extras complet – Anumite sisteme sunt incapabile să determine care date au fost modificate. În acest scenariu, singura modalitate de a scoate datele din sistem este efectuarea unei extrageri complete. Această abordare necesită o copie de rezervă a extrasului anterior în fișierul
același format la îndemână pentru a identifica modificările care au fost efectuate.
Indiferent de metoda adoptată, extracția nu ar trebui să aibă un impact asupra performanței sau timpului de răspuns al sistemelor sursă. Acestea sunt baze de date de producție în timp real. Orice încetinire sau blocare ar putea avea un impact asupra profitului companiei.
Pasul 2) Transformare
Datele preluate de pe serverul sursă sunt brute și inutilizabile în starea inițială. Ca rezultat, trebuie curățat, mapat și transformat. În realitate, acesta este pasul cheie în care procesul ETL adaugă valoare și transformă datele pentru a produce rapoarte BI semnificative.
Este un concept cheie ETL în care aplicați o colecție de funcții datelor extrase. Mișcare directă or trece prin data este tipul de date care nu necesită nicio transformare.
Puteți executa operațiuni personalizate asupra datelor în timpul etapei de transformare. De exemplu, să presupunem că clientul dorește un venit din suma vânzărilor care nu există în baza de date. sau dacă numele și prenumele dintr-un tabel sunt în coloane separate. Înainte de încărcare, acestea pot fi concatenate.
Următoarele sunt câteva exemple de probleme de integritate a datelor:
- Ortografii diferite ale aceluiași individ, cum ar fi Prashant, Parshant și etc.
- Există multe modalități de a reprezenta numele unei companii, cum ar fi Google, Google Inc.
- Sunt folosite diferite nume, cum ar fi Cleaveland și Cleveland.
- Este posibil ca mai multe numere de cont să fie produse de aplicații diferite pentru același client.
- Unele fișiere de date necesare sunt lăsate necompletate.
Pasul 3) Încărcare
Etapa finală a procesului ETL este încărcarea datelor în baza de date a depozitului de date țintă. Un volum mare de date este încărcat într-o perioadă relativ scurtă de timp într-un depozit de date tipic. Ca rezultat, procesul de încărcare ar trebui optimizat pentru performanță.
În cazul unei erori de încărcare, procedurile de recuperare ar trebui să fie implementate, astfel încât operațiunile să poată reporni din punctul de defecțiune fără a compromite integritatea datelor. Administratorii Data Warehouse trebuie să monitorizeze, să continue și să oprească încărcările pe baza performanței serverului.
Tipuri de încărcare:
- Încărcare inițială — umplerea tuturor
tabelele Data Warehouse - Încărcare incrementală — implementare în curs de desfășurare
modificările necesare în mod regulat - Reîmprospătare completă — curățarea conținutului
a unuia sau mai multor tabele și reîncărcarea acestora cu date noi
Verificarea încărcării
- Verificați dacă datele câmpului cheie nu lipsesc sau nu sunt nule.
- Vederile de modelare bazate pe tabelele țintă ar trebui testate.
- Examinați valorile combinate3 și măsurile calculate.
- Verificări de date în tabelele de dimensiuni și istoric.
- Examinați rapoartele BI pe tabelul de fapte și dimensiuni încărcate.
Configurarea ETL folosind PythonScript
Ca rezultat, trebuie să executați Extract Transform Load (ETL) de bază din mai multe baze de date într-un depozit de date pentru a face agregarea datelor pentru business intelligence. Există mai multe pachete ETL disponibile pe care le considerați excesive pentru cazul dvs. de utilizare de bază.
Vă voi arăta cum să extrageți date din MySQL, SQL-server și firebird în acest articol. Folosind Python 3.6, transformați datele și încărcați-le în SQL-server (depozitul de date).
În primul rând, trebuie să creăm un director pentru proiectul nostru:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Pentru a configura ETL folosind Python, va trebui să generați următoarele fișiere în directorul de proiect.
- db_credentials.py: Ar trebui să aibă toate informațiile necesare pentru a vă conecta la toate bazele de date. cum ar fi parola bazei de date, numărul portului etc.
- sql_queries.py: Toate interogările de bază de date utilizate în mod obișnuit pentru extragerea și încărcarea datelor în format String ar trebui să fie disponibile.
- etl.py: Conectați-vă la baza de date și efectuați interogările necesare efectuând toate procedurile necesare.
- main.py: Responsabil cu gestionarea fluxului de operațiuni și executarea operațiunilor esențiale într-o ordine specificată.
În această secțiune a sql_queries.py, acesta este locul în care vom stoca toate interogările noastre sql pentru extragerea din bazele de date sursă și importarea în baza noastră de date țintă (depozitul de date)
Configurați acreditările și variabilele bazei de date
În variables.py, creați o variabilă pentru a înregistra numele bazei.
datawarehouse_name = 'numele_de_datawarehouse_dvs'
Configurați toate șirurile și acreditările de conectare la baza de date sursă și țintă în db_credentials.py, așa cum se arată mai jos. Salvați configurația ca o listă, astfel încât să o putem repeta oricând este necesar prin multe baze de date mai târziu.
din variabile import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (target db, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'da', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', '' baza de date': '{}'.format(datawarehouse_name), 'user': 'your_db_username', 'parola': 'your_db_password', 'autocommit': True, } # sql-server (sursă db) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'your_sql_server', 'database': 'db1', 'user': 'your_db_username', 'parola': 'your_db_parola', ' autocommit': adevărat, } ] # mysql (sursă 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 (sursă db) fdb_db_config = [ { 'dsn' : „/your/path/to/source.db”, „user”: „your_username”, „parola”: „your_parola”, } ]
Interogări SQL
În această secțiune a sql_queries.py, acesta este locul în care vom stoca toate interogările noastre sql pentru extragerea din bazele de date sursă și importarea în baza noastră de date țintă (depozitul de date).
Trebuie să implementăm diverse sintaxe pentru fiecare bază de date, deoarece lucrăm cu mai multe platforme de date. Putem face acest lucru prin separarea interogărilor în funcție de tipul bazei de date.
# exemple de interogări, vor fi diferite în diferite platforme db firebird_extract = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO table (coloana_1, coloana, 2, coloana S_3?) ?, ?) ''') firebird_extract_2 = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (coloana_1, coloana ?, VALUES, coloana?) ) ''') sqlserver_extract = (''' SELECT sqlserver_column_2, sqlserver_column_3, sqlserver_column_1 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO coloana, coloana , coloana , _'''' , coloana , VALOR_2 ? ) mysql_extract = (''' SELECT mysql_column_3, mysql_column_1, mysql_column_2 FROM mysql_table ''') mysql_insert = (''' INSERT INTO table (coloana_3, coloana_1, coloana_2), interogări de export VALORI (''', ?) #?) Clasa SqlQuery: def __init __ (self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # creează instanțe pentru clasa sqlquery fbd_query = sqlquery (firesbird_extry, firebird_insert) fbd_query_3 = sqlquery (foc? , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # stocați ca listă pentru iterație fbd_queries = [fbdquery, fbd_query_1] sqlserver_queries = [sqlserver_query] = mysqlserver_query]_qlries
Extrageți sarcina de transformare
Pentru a configura ETL folosind Python pentru sursele de date menționate mai sus, veți avea nevoie de următoarele module:
# module python import mysql.connector import pyodbc import fdb # variabile din variabile import datawarehouse_name
Putem folosi două tehnici în acest sens: etl() și etl_process().
etl_process() este procedura pentru stabilirea unei conexiuni la sursa bazei de date și apelarea metodei etl() bazată pe platforma bazei de date.
Și în a doua metodă, care este metoda etl(), rulează mai întâi interogarea de extragere, apoi stochează datele SQL în datele variabile și le inserează în baza de date vizată, care este depozitul nostru de date. Transformarea datelor poate fi realizată prin modificarea variabilei de date a tuplului de tip.
def etl(query, source_cnx, target_cnx): # extrageți date din DB sursă source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # încărcați datele în warehouse db dacă date: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('date loaded to warehouse db') target_cursor.close() else : print('datele sunt goale') def etl_process(interogări, target_cnx, source_db_config, db_platform): # stabilește conexiunea sursă 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: returnează 'Eroare! platformă db nerecunoscută' # parcurge interogări sql pentru interogări în interogări: etl(query, source_cnx, target_cnx) # închide conexiunea sursă db source_cnx.close()
Punând totul laolaltă
Acum, în pasul următor, putem trece peste toate acreditările din main.py și putem executa etl-ul pentru toate bazele de date.
Pentru asta trebuie să importam toate variabilele și metodele necesare:
# variabile din db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config din sql_queries import fbd_queries, sqlserver_queries, mysql_queries din variabile import * # și metode din import_process etl
Codul din acest fișier este responsabil pentru repetarea acreditărilor pentru a se conecta la baza de date și a executa operațiunile ETL necesare folosind Python.
def main(): print('pornire etl') # stabiliți conexiunea pentru baza de date țintă (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # treceți în buclă prin acreditări # mysql pentru config în mysql_db_config: încercați: print("încărcare db: " + config['bază de date']) etl_process(mysql_queries, target_cnx, config, 'mysql') cu excepția excepției ca eroare: print("etl pentru {} are eroare".format(config['bază de date'])) print ('mesaj de eroare: {}'.format(error)) continua # sql-server pentru config în sqlserver_db_config: try: print("se încarcă db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') cu excepția excepției ca eroare: print("etl pentru {} are eroare".format(config['bază de date'])) print('mesaj de eroare: {}'.format(eroare)) continua # firebird pentru config în fbd_db_config: încercați: print("se încarcă db: " + config['bază de date']) etl_process(fbd_queries, target_cnx, config, 'firebird') cu excepția ca eroare: print("etl pentru {} are eroare".format(config). ['bază de date'])) print('mesaj de eroare: {}'.format(eroare)) continue target_cnx.close() if __name__ == "__main__": main()
În terminalul dvs., tastați python main.py și tocmai ați creat un ETL folosind un script python pur.
Instrumente ETL
Există mai multe instrumente de depozitare a datelor pe piață. Iată câteva dintre cele mai cunoscute exemple:
1. MarkLogic:
MarkLogic este un sistem de depozitare a datelor care utilizează o serie de capabilități de afaceri pentru a face integrarea datelor mai ușoară și mai rapidă. Poate interoga multe tipuri de date, cum ar fi documente, relații și metadate.
https://www.marklogic.com/product/getting-started/
2. Oracol:
Oracle este cea mai populară bază de date din industrie. Oferă o mare varietate de soluții Data Warehouse atât pentru serviciile on-premise, cât și pentru serviciile cloud. Ajută la o experiență mai bună pentru clienți prin creșterea eficienței operaționale.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift este o soluție de depozitare de date de la Amazon. Este o soluție simplă și rentabilă pentru analiza diferitelor tipuri de date cu SQL standard și instrumente de business intelligence existente. De asemenea, permite executarea de interogări complexe pe petabytes de date structurate.
https://aws.amazon.com/redshift/?nc2=h_m1
Concluzie
Acest articol v-a oferit o înțelegere profundă a ce este ETL, precum și un tutorial pas cu pas despre cum să vă configurați ETL în Python. De asemenea, v-a oferit o listă cu cele mai bune instrumente pe care majoritatea organizațiilor le folosesc în prezent pentru a-și construi conductele de date ETL.
Majoritatea organizațiilor din zilele noastre, pe de altă parte, au o cantitate masivă de date cu o structură foarte dinamică. Crearea unei conducte ETL de la zero pentru astfel de date este o procedură grea, deoarece organizațiile vor trebui să utilizeze un număr mare de resurse pentru a crea această conductă și apoi să se asigure că poate ține pasul cu volumul mare de date și cu modificările Schemei.
Despre autor
Prashant Sharma
În prezent, îmi urmăresc licența în tehnologie (B.Tech) de la Institutul de Tehnologie Vellore. Sunt foarte entuziasmat de programare și de aplicațiile sale reale, inclusiv dezvoltarea de software, învățarea automată, învățarea profundă și știința datelor.
Sper sa va placa articolul. Dacă vrei să te conectezi cu mine, te poți conecta pe:
sau pentru orice alte îndoieli, puteți trimite un mail și mie
Legate de
- '
- "
- Cont
- activ
- TOATE
- Amazon
- Google Analytics
- aplicatii
- ZONĂ
- articol
- Automata
- Backup
- stimularea
- construi
- Clădire
- afaceri
- business intelligence
- apel
- Verificări
- cleveland
- Cloud
- servicii de tip cloud
- cod
- Comunicare
- companie
- conexiune
- continua
- Crearea
- scrisori de acreditare
- de date
- integrarea datelor
- știința datelor
- depozit de date
- depozite de date
- Baza de date
- baze de date
- învățare profundă
- dezvolta
- Dezvoltatorii
- Dezvoltare
- Dispozitive
- Dimensiune
- documente
- şofer
- eficiență
- etc
- execuție
- directori
- Experiențe
- extracţie
- extracte
- Eșec
- First
- debit
- format
- proaspăt
- Complet
- ghida
- Piese metalice
- aici
- Înalt
- istorie
- Cum
- Cum Pentru a
- HTTPS
- identifica
- Impactul
- importatoare
- Inclusiv
- informații
- integrare
- Inteligență
- probleme de
- IT
- Cheie
- mare
- AFLAȚI
- învăţare
- Linie
- Listă
- încărca
- masina de învățare
- administrare
- Hartă
- Piață
- Mass-media
- Cel mai popular
- muta
- nume
- notificare
- numere
- promoții
- de operare
- sisteme de operare
- Operațiuni
- oracol
- comandă
- organizații
- Altele
- parteneri
- Parolă
- performanță
- platformă
- Platforme
- Popular
- Produs
- producere
- Programare
- Programe
- proiect
- trăgând
- Piton
- Crud
- în timp real
- Realitate
- înregistrări
- recuperare
- Relaţii
- Rapoarte
- Resurse
- răspuns
- venituri
- Ştiinţă
- Servicii
- set
- Pantaloni scurți
- simplu
- So
- Software
- de dezvoltare de software
- soluţii
- SQL
- Etapă
- Stat
- stoca
- magazine
- sistem
- sisteme
- Ţintă
- tech
- tehnici de
- Tehnologia
- Sursa
- timp
- Transformare
- tutorial
- Actualizează
- valoare
- furnizori
- volum
- Depozit
- depozitare
- săptămânal
- Apartamente
- fabrică