Che cos'è ETL?
ETL è un processo che estrae i dati da più sistemi di origine, li modifica (tramite calcoli, concatenazioni e così via) e quindi li inserisce nel sistema Data Warehouse. ETL sta per Estrai, Trasforma e Carica.
È facile credere che costruire un data warehouse sia semplice come estrarre dati da numerose fonti e inserirli in un database di data warehouse. Questo è tutt'altro che vero ed è necessaria una complicata procedura ETL. Il processo ETL, che è tecnicamente complesso, prevede la partecipazione attiva di una varietà di parti interessate, inclusi sviluppatori, analisti, tester e dirigenti senior.
Per preservare il suo valore come strumento decisionale, il sistema di data warehouse deve svilupparsi in sincronia con gli sviluppi aziendali. ETL è un processo regolare (giornaliero, settimanale, mensile) di un sistema di data warehouse che deve essere agile, automatizzato e adeguatamente documentato.
Come funziona l'ETL?
Qui impareremo come funziona il processo ETL passo dopo passo:
Passaggio 1) Estrazione
I dati vengono estratti dal sistema di origine e inseriti nell'area di staging durante l'estrazione. Se sono necessarie trasformazioni, vengono eseguite nell'area di staging in modo che le prestazioni del sistema di origine non vengano danneggiate. Il rollback sarà difficile se i dati danneggiati vengono trasferiti direttamente dall'origine nel database del data warehouse. Prima di spostare i dati estratti nel data warehouse, possono essere convalidati nell'area di staging.
I data warehouse possono combinare sistemi con hardware, sistemi di gestione dei database, sistemi operativi e protocolli di comunicazione diversi. I data warehouse devono combinare sistemi con DBMS, hardware, sistemi operativi e protocolli di comunicazione diversi. Le fonti possono includere programmi legacy come mainframe, applicazioni personalizzate, dispositivi point-of-contact come bancomat e commutatori di chiamata, file di testo, fogli di calcolo, ERP, dati di fornitori e partner e così via.
Pertanto, prima di estrarre i dati e caricarli fisicamente, è necessaria una mappa logica dei dati. La connessione tra le fonti e i dati di destinazione è mostrata in questa mappa dei dati.
Tre metodi di estrazione dei dati:
- Estrazione Parziale – Se il sistema di origine ti avvisa quando un record viene modificato, questo è il modo più semplice per ottenere i dati.
- Estrazione parziale (senza notifica di aggiornamento) – Non tutti i sistemi possono inviare una notifica quando si verifica un aggiornamento; tuttavia, possono indicare ai record che sono stati modificati e fornire l'estrazione di tali record.
- Estratto completo – Alcuni sistemi non sono in grado di determinare quali dati sono stati modificati. In questo scenario, l'unico modo per estrarre i dati dal sistema è eseguire un'estrazione completa. Questo approccio richiede la presenza di un backup dell'estrazione precedente nel file
stesso formato a portata di mano per identificare le modifiche apportate.
Indipendentemente dal metodo adottato, l'estrazione non dovrebbe avere un impatto sulle prestazioni o sui tempi di risposta dei sistemi sorgente. Questi sono database di produzione in tempo reale. Qualsiasi rallentamento o blocco potrebbe avere un impatto sui profitti dell'azienda.
Passaggio 2) Trasformazione
I dati recuperati dal server di origine sono grezzi e inutilizzabili nel loro stato originale. Di conseguenza, deve essere pulito, mappato e trasformato. In realtà, questo è il passaggio chiave in cui il processo ETL aggiunge valore e trasforma i dati per produrre report BI significativi.
È un concetto ETL chiave in cui si applica una raccolta di funzioni ai dati estratti. Movimento diretto or passare attraverso data è il tipo di dato che non richiede alcuna trasformazione.
È possibile eseguire operazioni personalizzate sui dati durante la fase di trasformazione. Si supponga, ad esempio, che il cliente desideri una somma delle entrate delle vendite che non esiste nel database. o se il nome e il cognome in una tabella sono in colonne separate. Prima del caricamento, possono essere concatenati.
Di seguito sono riportati alcuni esempi di problemi di integrità dei dati:
- Diverse ortografie dello stesso individuo, come Prashant, Parshant e così via.
- Esistono molti modi per rappresentare il nome di una società, ad esempio Google, Google Inc.
- Vengono utilizzati vari nomi, come Cleaveland e Cleveland.
- È possibile che più numeri di conto vengano prodotti da diverse applicazioni per lo stesso cliente.
- Alcuni file di dati necessari vengono lasciati vuoti.
Passaggio 3) Caricamento
La fase finale del processo ETL consiste nel caricare i dati nel database del data warehouse di destinazione. Un grande volume di dati viene caricato in un periodo di tempo relativamente breve in un tipico data warehouse. Di conseguenza, il processo di caricamento dovrebbe essere ottimizzato per le prestazioni.
In caso di errore di caricamento, è necessario mettere in atto procedure di ripristino in modo che le operazioni possano essere riavviate dal punto in cui si è verificato l'errore senza compromettere l'integrità dei dati. Gli amministratori del data warehouse devono monitorare, continuare e arrestare i carichi in base alle prestazioni del server.
Tipi di caricamento:
- Carico iniziale - riempiendo tutto
le tabelle del Data Warehouse - Carico incrementale — attuazione in corso
modifiche necessarie su base regolare - Aggiornamento completo — cancellare il contenuto
di una o più tabelle e ricaricandole con nuovi dati
Verifica del carico
- Verificare che i dati del campo chiave non siano mancanti o nulli.
- È necessario testare le viste di modellazione basate sulle tabelle di destinazione.
- Esaminare i valori combinati3 e le misure calcolate.
- Controlli dei dati nelle tabelle delle dimensioni e della cronologia.
- Esaminare i report BI sulla tabella dei fatti e delle dimensioni caricata.
Configurare ETL usando PythonScript
Di conseguenza, è necessario eseguire l'ETL (Extract Transform Load) di base da diversi database a un data warehouse per eseguire l'aggregazione dei dati per la business intelligence. Sono disponibili diversi pacchetti ETL che ritenevi eccessivi per il tuo caso d'uso di base.
Ti mostrerò come estrarre i dati da MySQL, SQL-server e Firebird in questo articolo. Usando Python 3.6, trasforma i dati e caricali in SQL-server (data warehouse).
Prima di tutto, dobbiamo creare una directory per il nostro progetto:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Per configurare ETL utilizzando Python, dovrai generare i seguenti file nella directory del tuo progetto.
- db_credentials.py: Dovrebbe avere tutte le informazioni necessarie per connettersi a tutti i database. come password del database, numero di porta, ecc.
- sql_queries.py: Dovrebbero essere disponibili tutte le query di database comunemente utilizzate per l'estrazione e il caricamento dei dati in formato stringa.
- etl.py: Connettiti al database e conduci le query necessarie eseguendo tutte le procedure necessarie.
- principale.py: Responsabile della gestione del flusso delle operazioni e dell'esecuzione delle operazioni essenziali in un ordine specificato.
In questa sezione di sql_queries.py, questo è il luogo in cui memorizzeremo tutte le nostre query sql per l'estrazione dai database di origine e l'importazione nel nostro database di destinazione (data warehouse)
Imposta le credenziali e le variabili del database
In variable.py, crea una variabile per registrare il nome della base.
datawarehouse_name = 'tuo_datawarehouse_name'
Configura tutte le stringhe di connessione e le credenziali del database di origine e di destinazione in db_credentials.py come mostrato di seguito. Salva la configurazione come elenco in modo da poterla iterare ogni volta che sarà necessario attraverso molti database in seguito.
dalle variabili 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', ' database': '{}'.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': "you r_password", } ]
Query SQL
In questa sezione di sql_queries.py, questo è il luogo in cui memorizzeremo tutte le nostre query sql per l'estrazione dai database di origine e l'importazione nel nostro database di destinazione (data warehouse).
Dobbiamo implementare varie sintassi per ogni database perché stiamo lavorando con più piattaforme di dati. Possiamo farlo separando le query in base al tipo di database.
# query di esempio, saranno diverse su diverse piattaforme db 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_3) VALUES (?, ?, ?) ''') firebird_extract_2 = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (column_1, column_2, column_3) VALUES (?, ?, ? ) ''') sqlserver_extract = (''' SELECT sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''' ) mysql_extract = (''' SELECT mysql_column_1, mysql_column_2, mysql_column_3 FROM mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') # query di esportazione class 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_2, firebird_insert_2) 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]
Estrai Trasforma Carica
Per configurare ETL utilizzando Python per le origini dati sopra menzionate, avrai bisogno dei seguenti moduli:
# moduli Python import mysql.connector import pyodbc import fdb # variabili dalle variabili import datawarehouse_name
Possiamo usare due tecniche in questo: etl() e etl_process().
etl_process() è la procedura per stabilire una connessione all'origine del database e chiamare il metodo etl() basato sulla piattaforma del database.
E nel secondo metodo che è il metodo etl(), esegue prima la query di estrazione, quindi memorizza i dati SQL nei dati variabili e li inserisce nel database di destinazione, che è il nostro data warehouse. La trasformazione dei dati può essere eseguita alterando la variabile di dati della tupla di tipo.
def etl(query, source_cnx, target_cnx): # estrae i dati dal database sorgente source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # carica i dati nel warehouse db if data: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('dati caricati nel warehouse db') target_cursor.close() else : print('i dati sono vuoti') def etl_process(queries, target_cnx, source_db_config, db_platform): # stabilisce la connessione db sorgente 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 'Errore! piattaforma db non riconosciuta' # passa attraverso le query sql per la query nelle query: etl(query, source_cnx, target_cnx) # chiude la connessione db di origine source_cnx.close()
Mettere tutto insieme
Ora, nel passaggio successivo, possiamo eseguire il loop su tutte le credenziali in main.py ed eseguire l'etl per tutti i database.
Per questo dobbiamo importare tutte le variabili e i metodi richiesti:
# variabili da db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config da sql_queries import fbd_queries, sqlserver_queries, mysql_queries da variabili import * # metodi da etl import etl_process
Il codice in questo file è responsabile dell'iterazione delle credenziali per connettersi al database ed eseguire le necessarie operazioni ETL Using Python.
def main(): print('starting etl') # stabilisce la connessione per il database di destinazione (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # passa attraverso le credenziali # mysql per la configurazione in mysql_db_config: try: print("loading db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql')except Exception as error: print("etl for {} has error".format(config['database'])) print ('messaggio di errore: {}'.format(errore)) continue # sql-server for config in sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') tranne Exception as error: print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) continue # firebird for config in fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird')except Exception as error: print("etl for {} has error".format(config ['database'])) print('messaggio di errore: {}'.fo rmat(errore)) continue target_cnx.close() if __name__ == "__main__": main()
Nel tuo terminale, digita python main.py e hai appena creato un ETL usando uno script Python puro.
Strumenti ETL
Esistono diversi strumenti di Data Warehousing sul mercato. Ecco alcuni degli esempi più famosi:
1. SegnaLogica:
MarkLogic è un sistema di data warehousing che utilizza una serie di funzionalità aziendali per rendere l'integrazione dei dati più facile e veloce. Può interrogare molti tipi di dati, come documenti, relazioni e metadati.
https://www.marklogic.com/product/getting-started/
2.Oracoli:
Oracle è il database più popolare del settore. Offre una vasta gamma di soluzioni di data warehouse sia per servizi on-premise che cloud. Aiuta a migliorare le esperienze dei clienti aumentando l'efficienza operativa.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift è una soluzione di data warehousing di Amazon. È una soluzione semplice ed economica per l'analisi di vari tipi di dati con SQL standard e strumenti di business intelligence esistenti. Consente inoltre l'esecuzione di query complesse su petabyte di dati strutturati.
https://aws.amazon.com/redshift/?nc2=h_m1
Conclusione
Questo articolo ti ha fornito una profonda comprensione di cosa sia ETL, oltre a un tutorial passo-passo su come impostare il tuo ETL in Python. Ti ha anche fornito un elenco dei migliori strumenti che la maggior parte delle organizzazioni oggi utilizza per creare le proprie pipeline di dati ETL.
La maggior parte delle organizzazioni al giorno d'oggi, d'altra parte, dispone di un'enorme quantità di dati con una struttura altamente dinamica. La creazione di una pipeline ETL da zero per tali dati è una procedura difficile poiché le organizzazioni dovranno utilizzare un numero elevato di risorse per creare questa pipeline e quindi garantire che possa tenere il passo con l'elevato volume di dati e le modifiche allo schema.
Circa l'autore
Prashant Sharma
Attualmente, sto perseguendo i miei Bachelor of Technology (B.Tech) presso il Vellore Institute of Technology. Sono molto entusiasta della programmazione e delle sue applicazioni reali, tra cui lo sviluppo di software, l'apprendimento automatico, il deep learning e la scienza dei dati.
Spero che l'articolo ti piaccia. Se vuoi connetterti con me, puoi connetterti su:
o per qualsiasi altro dubbio, puoi manda una mail anche a me
Leggi Anche
- '
- "
- Il mio account
- attivo
- Tutti
- Amazon
- analitica
- applicazioni
- RISERVATA
- articolo
- Automatizzata
- di riserva
- potenziamento
- costruire
- Costruzione
- affari
- business intelligence
- chiamata
- Controlli
- cleveland
- Cloud
- servizi cloud
- codice
- Comunicazione
- azienda
- veloce
- continua
- Creazione
- Credenziali
- dati
- integrazione dei dati
- scienza dei dati
- data warehouse
- data warehouse
- Banca Dati
- banche dati
- apprendimento profondo
- sviluppare
- sviluppatori
- Mercato
- dispositivi
- Dimensioni
- documenti
- autista
- efficienza
- eccetera
- esecuzione
- dirigenti
- Esperienze
- estrazione
- estratti
- Fallimento
- Nome
- flusso
- formato
- fresco
- pieno
- guida
- Hardware
- qui
- Alta
- storia
- Come
- Tutorial
- HTTPS
- identificare
- Impact
- importazione
- Compreso
- informazioni
- integrazione
- Intelligence
- sicurezza
- IT
- Le
- grandi
- IMPARARE
- apprendimento
- linea
- Lista
- caricare
- machine learning
- gestione
- carta geografica
- Rappresentanza
- Media
- Più popolare
- cambiano
- nomi
- notifica
- numeri
- Offerte
- operativo
- sistemi operativi
- Operazioni
- oracolo
- minimo
- organizzazioni
- Altro
- partner
- Password
- performance
- piattaforma
- Piattaforme
- Popolare
- Prodotto
- Produzione
- Programmazione
- Programmi
- progetto
- traino
- Python
- Crudo
- tempo reale
- Realtà
- record
- recupero
- Relazioni
- Report
- Risorse
- risposta
- Le vendite
- Scienze
- Servizi
- set
- Corti
- Un'espansione
- So
- Software
- lo sviluppo del software
- Soluzioni
- SQL
- Stage
- Regione / Stato
- Tornare al suo account
- negozi
- sistema
- SISTEMI DI TRATTAMENTO
- Target
- Tech
- tecniche
- Tecnologia
- L’ORIGINE
- tempo
- Trasformazione
- lezione
- Aggiornanento
- APPREZZIAMO
- fornitori
- volume
- Magazzino
- Magazzinaggio
- settimanale
- Lavora
- lavori