Co to jest ETL?
ETL to proces, który wyodrębnia dane z wielu systemów źródłowych, zmienia je (poprzez obliczenia, konkatenacje itd.), a następnie umieszcza je w systemie hurtowni danych. ETL oznacza wyodrębnianie, przekształcanie i ładowanie.
Łatwo uwierzyć, że zbudowanie hurtowni danych jest tak proste, jak pobieranie danych z wielu źródeł i wprowadzanie ich do bazy danych hurtowni danych. Jest to dalekie od przypadku i wymagana jest skomplikowana procedura ETL. Proces ETL, który jest technicznie złożony, wymaga aktywnego udziału różnych interesariuszy, w tym programistów, analityków, testerów i kadry kierowniczej wyższego szczebla.
Aby zachować swoją wartość jako narzędzia do podejmowania decyzji, system hurtowni danych musi rozwijać się w synchronizacji z rozwojem biznesowym. ETL to regularny (codzienny, tygodniowy, miesięczny) proces systemu hurtowni danych, który musi być zwinny, zautomatyzowany i odpowiednio udokumentowany.
Jak działa ETL?
Tutaj dowiemy się jak działa proces ETL krok po kroku:
Krok 1) Ekstrakcja
Dane są pobierane z systemu źródłowego i umieszczane w obszarze przemieszczania podczas ekstrakcji. Jeśli wymagane są jakiekolwiek przekształcenia, są one wykonywane w obszarze pomostowym, aby nie pogorszyć wydajności systemu źródłowego. Wycofanie będzie trudne, jeśli uszkodzone dane zostaną przeniesione bezpośrednio ze źródła do bazy danych hurtowni danych. Przed przeniesieniem wyodrębnionych danych do hurtowni danych można je zweryfikować w obszarze przemieszczania.
Hurtownie danych mogą łączyć systemy z różnym sprzętem, systemami zarządzania bazami danych, systemami operacyjnymi i protokołami komunikacyjnymi. Hurtownie danych muszą łączyć systemy z różnymi DBMS, sprzętem, systemami operacyjnymi i protokołami komunikacyjnymi. Źródła mogą obejmować starsze programy, takie jak komputery mainframe, dostosowane aplikacje, urządzenia kontaktowe, takie jak bankomaty i centrale telefoniczne, pliki tekstowe, arkusze kalkulacyjne, ERP, dane od dostawców i partnerów itd.
Dlatego przed wyodrębnieniem danych i fizycznym załadowaniem ich wymagana jest logiczna mapa danych. Połączenie między danymi źródłowymi i docelowymi jest pokazane na tej mapie danych.
Trzy metody ekstrakcji danych:
- Ekstrakcja częściowa – Jeśli system źródłowy ostrzega o modyfikacji rekordu, jest to najprostszy sposób na uzyskanie danych.
- Częściowa ekstrakcja (bez powiadomienia o aktualizacji) – Nie wszystkie systemy mogą dostarczyć powiadomienie o wystąpieniu aktualizacji; ale mogą wskazać rekordy, które zostały zmienione i zapewnić ekstrakcję tych rekordów.
- Pełny ekstrakt – Niektóre systemy w ogóle nie są w stanie określić, które dane zostały zmienione. W tym scenariuszu jedynym sposobem na usunięcie danych z systemu jest wykonanie pełnego wyodrębnienia. To podejście wymaga posiadania kopii zapasowej poprzedniego ekstraktu w pliku
ten sam format pod ręką, aby zidentyfikować wprowadzone zmiany.
Niezależnie od przyjętej metody ekstrakcja nie powinna mieć wpływu na wydajność czy czas odpowiedzi systemów źródłowych. Są to produkcyjne bazy danych działające w czasie rzeczywistym. Każde spowolnienie lub blokada może mieć wpływ na wyniki finansowe firmy.
Krok 2) Transformacja
Dane pobrane z serwera źródłowego są nieprzetworzone i nie nadają się do użytku w oryginalnym stanie. W rezultacie musi zostać oczyszczony, zmapowany i przekształcony. W rzeczywistości jest to kluczowy krok, w którym proces ETL dodaje wartość i przekształca dane w celu stworzenia sensownych raportów BI.
Jest to kluczowa koncepcja ETL, w której stosuje się zbiór funkcji do wyodrębnionych danych. Bezpośredni ruch or przejść przez data to typ danych, który nie wymaga żadnej transformacji.
Na etapie transformacji można wykonywać niestandardowe operacje na danych. Załóżmy na przykład, że klient chce uzyskać sumę przychodów ze sprzedaży, która nie istnieje w bazie danych. lub jeśli imiona i nazwiska w tabeli znajdują się w osobnych kolumnach. Przed załadowaniem można je połączyć.
Oto kilka przykładów problemów z integralnością danych:
- Różne pisownie tej samej osoby, takie jak Prashant, Parshant itp.
- Istnieje wiele sposobów reprezentowania nazwy firmy, na przykład Google, Google Inc.
- Używane są różne nazwy, takie jak Cleveland i Cleveland.
- Możliwe jest, że różne aplikacje dla tego samego klienta generują wiele numerów kont.
- Niektóre potrzebne pliki danych są puste.
Krok 3) Ładowanie
Ostatnim etapem procesu ETL jest załadowanie danych do docelowej bazy danych hurtowni danych. W typowej hurtowni danych ładowana jest duża ilość danych w stosunkowo krótkim czasie. W rezultacie proces ładowania powinien być zoptymalizowany pod kątem wydajności.
W przypadku wystąpienia awarii obciążenia należy wdrożyć procedury przywracania, aby operacje mogły zostać wznowione od punktu awarii bez narażania integralności danych. Administratorzy hurtowni danych muszą monitorować, kontynuować i zatrzymywać ładowanie w oparciu o wydajność serwera.
Rodzaje ładowania:
- Początkowe obciążenie — wypełnienie wszystkich
tabel hurtowni danych - Obciążenie przyrostowe — wdrażanie w toku
modyfikacje w razie potrzeby na bieżąco - Pełne odświeżenie — opróżnianie zawartości
jednej lub więcej tabel i ponowne załadowanie ich świeżymi danymi
Weryfikacja obciążenia
- Sprawdź, czy nie brakuje kluczowych danych w polu lub czy nie są one puste.
- Należy przetestować widoki modelowania oparte na tabelach docelowych.
- Zbadaj połączone wartości3 i obliczone miary.
- Kontrole danych w tabelach wymiarów i historii.
- Sprawdź raporty BI dotyczące załadowanej tabeli faktów i wymiarów.
Konfigurowanie ETL przy użyciu PythonScript
W rezultacie musisz wykonać podstawowe ładowanie wyodrębniania transformacji (ETL) z kilku baz danych do hurtowni danych w celu agregacji danych na potrzeby analizy biznesowej. Dostępnych jest kilka pakietów ETL, które Twoim zdaniem były zbyt duże dla twojego podstawowego przypadku użycia.
W tym artykule pokażę ci, jak wyodrębnić dane z MySQL, SQL-server i firebird. Używając Pythona 3.6, przekształć dane i załaduj je do serwera SQL (hurtowni danych).
Przede wszystkim musimy stworzyć katalog dla naszego projektu:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Aby skonfigurować ETL przy użyciu języka Python, musisz wygenerować następujące pliki w katalogu projektu.
- db_credentials.py: Powinien zawierać wszystkie informacje potrzebne do połączenia ze wszystkimi bazami danych. takie jak hasło do bazy danych, numer portu itp.
- sql_queries.py: Wszystkie powszechnie używane zapytania do bazy danych służące do wyodrębniania i ładowania danych w formacie String powinny być dostępne.
- etl.py: Połącz się z bazą danych i przeprowadź potrzebne zapytania, wykonując wszystkie niezbędne procedury.
- main.py: Odpowiedzialny za zarządzanie przepływem operacji i wykonywanie podstawowych operacji w określonej kolejności.
W tej sekcji sql_queries.py jest to miejsce, w którym będziemy przechowywać wszystkie nasze zapytania sql do wyodrębniania ze źródłowych baz danych i importowania do naszej docelowej bazy danych (hurtowni danych)
Skonfiguruj poświadczenia bazy danych i zmienne
W pliku variables.py utwórz zmienną do zapisu nazwy bazy.
datawarehouse_name = 'your_datawarehouse_name'
Skonfiguruj wszystkie parametry połączenia źródłowej i docelowej bazy danych oraz poświadczenia w pliku db_credentials.py, jak pokazano poniżej. Zapisz konfigurację jako listę, abyśmy mogli później powtarzać ją w razie potrzeby w wielu bazach danych.
ze zmiennych import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (docelowa baza danych, 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 (baza źródłowa) sqlserver_db_config = [ { 'Trusted_Connection ': 'tak', 'sterownik': '{SQL Server}', 'serwer': 'twój_serwer_sql', 'baza danych': 'db1', 'użytkownik': 'nazwa_użytkownika_twojej bazy danych', 'hasło': 'twoje_hasło_db', ' autocommit': True, } ] # mysql (baza źródłowa) mysql_db_config = [ { 'użytkownik': 'twój_użytkownik_1', 'hasło': 'twoje_hasło_1', 'host': 'db_connection_string_1', 'baza danych': 'db_1', } , { 'użytkownik': 'twój_użytkownik_2', 'hasło': 'twoje_hasło_2', 'host': 'db_connection_string_2', 'baza danych': 'db_2', }, ] # firebird (źródłowa baza danych) fdb_db_config = [ { 'dsn' : "/twoja/ścieżka/do/źródła.db", 'użytkownik': "nazwa_użytkownika", 'hasło': "twoje_hasło", } ]
Zapytania SQL
W tej sekcji sql_queries.py jest to miejsce, w którym będziemy przechowywać wszystkie nasze zapytania sql do wyodrębniania ze źródłowych baz danych i importowania do naszej docelowej bazy danych (hurtowni danych).
Musimy zaimplementować różne składnie dla każdej bazy danych, ponieważ pracujemy z wieloma platformami danych. Możemy to zrobić, oddzielając zapytania na podstawie typu bazy danych.
# przykładowe zapytania będą różne dla różnych platform 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 = (''' WYBIERZ fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' WSTAW DO tabeli_2 (kolumna_1, kolumna_2, kolumna_3) WARTOŚCI (?, ?, ? ) ''') sqlserver_extract = (''' SELECT sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 FROM sqlserver_table ''') sqlserver_insert = (''' WSTAW DO tabeli (kolumna_1, kolumna_2, kolumna_3) WARTOŚCI (?,?,?) ''' ) 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 (?, ?, ?) ''') # eksportowanie zapytań class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # utwórz instancje dla SqlQuery class fbd_query = SqlQuery(firebird_extract, firebird_insert) fbd_query_2 = SqlQuery(firebird_extract_2, firebird_insert_2) sqlserver_query y = SqlQuery(sqlserver_extract , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # zapisz jako listę dla iteracji fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sql_server_query] mysql_queries = [mysql_query]
Wyodrębnij obciążenie transformacji
Aby skonfigurować ETL przy użyciu Pythona dla wyżej wymienionych źródeł danych, potrzebujesz następujących modułów:
# moduły Pythona import mysql.connector import pyodbc import fdb # zmienne ze zmiennych import datawarehouse_name
Możemy w tym celu zastosować dwie techniki: etl() i etl_process().
etl_process() to procedura nawiązywania połączenia ze źródłem bazy danych i wywoływania metody etl() w oparciu o platformę bazy danych.
A w drugiej metodzie, którą jest metoda etl(), najpierw uruchamia zapytanie wyodrębniające, następnie przechowuje dane SQL w danych zmiennych i wstawia je do docelowej bazy danych, która jest naszą hurtownią danych. Transformacja danych może być dokonana poprzez zmianę zmiennej danych krotki typu.
def etl(query, source_cnx, target_cnx): # wyodrębnij dane ze źródła db source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # ładuj dane do hurtowni db if data: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('dane załadowane do hurtowni danych') target_cursor.close() else : print('dane są puste') def etl_process(queries, target_cnx, source_db_config, db_platform): # nawiąż połączenie ze źródłową bazą danych, jeśli 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 'Błąd! nierozpoznana platforma db' # pętla przez zapytania sql dla zapytania w zapytaniach: etl(query, source_cnx, target_cnx) # zamknij źródłowe połączenie db source_cnx.close()
Składanie wszystkiego razem
Teraz, w następnym kroku, możemy zapętlić wszystkie poświadczenia w main.py i wykonać etl dla wszystkich baz danych.
W tym celu musimy zaimportować wszystkie wymagane zmienne i metody:
# zmienne z db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config z sql_queries import fbd_queries, sqlserver_queries, mysql_queries ze zmiennych import * # metody z etl import etl_process
Kod w tym pliku jest odpowiedzialny za iterację poświadczeń w celu nawiązania połączenia z bazą danych i wykonania niezbędnych operacji ETL przy użyciu języka Python.
def main(): print('starting etl') # ustanowienie połączenia z docelową bazą danych (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # przejście przez poświadczenia # mysql dla konfiguracji w mysql_db_config: try: print("ładowanie db: " + config ['baza danych']) etl_process (mysql_queries, target_cnx, config, 'mysql') oprócz wyjątku jako błąd: print („etl dla {} ma błąd”.format (config ['baza danych'])) print ('komunikat o błędzie: {}'.format(error)) kontynuuj # sql-server dla konfiguracji w sqlserver_db_config: try: print("ładowanie bazy danych: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') z wyjątkiem wyjątku jako błąd: print("etl dla {} ma błąd".format(config['baza danych'])) print('komunikat o błędzie: {}'.format(error)) kontynuuj # firebird dla konfiguracji w fbd_db_config: try: print("ładowanie bazy danych: " + config['baza danych']) etl_process(fbd_queries, target_cnx, config, 'firebird') oprócz wyjątku jako błąd: print("etl dla {} ma błąd".format(config ['baza danych'])) print('komunikat o błędzie: {}'.format(error)) kontynuuj target_cnx.close() if __name__ == "__main__": main()
W swoim terminalu wpisz python main.py i właśnie utworzyłeś ETL przy użyciu czystego skryptu Pythona.
Narzędzia ETL
Na rynku dostępnych jest kilka narzędzi do hurtowni danych. Oto niektóre z najbardziej znanych przykładów:
1. Logika znaku:
MarkLogic to system hurtowni danych, który wykorzystuje szereg możliwości biznesowych, aby integracja danych była łatwiejsza i szybsza. Może wyszukiwać wiele rodzajów danych, takich jak dokumenty, relacje i metadane.
https://www.marklogic.com/product/getting-started/
2. Wyrocznia:
Oracle to najpopularniejsza baza danych w branży. Oferuje szeroką gamę rozwiązań hurtowni danych zarówno dla usług lokalnych, jak i chmurowych. Pomaga w lepszych doświadczeniach klientów poprzez zwiększenie wydajności operacyjnej.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift to rozwiązanie do przechowywania danych firmy Amazon. Jest to proste i ekonomiczne rozwiązanie do analizowania różnego rodzaju danych za pomocą standardowego języka SQL i istniejących narzędzi Business Intelligence. Umożliwia także wykonywanie złożonych zapytań na petabajtach ustrukturyzowanych danych.
https://aws.amazon.com/redshift/?nc2=h_m1
Wnioski
Ten artykuł dał ci dogłębne zrozumienie, czym jest ETL, a także samouczek krok po kroku, jak skonfigurować ETL w Pythonie. Dało ci to również listę najlepszych narzędzi używanych obecnie przez większość organizacji do tworzenia potoków danych ETL.
Z drugiej strony większość współczesnych organizacji dysponuje ogromną ilością danych o wysoce dynamicznej strukturze. Tworzenie potoku ETL od podstaw dla takich danych jest trudną procedurą, ponieważ organizacje będą musiały użyć dużej liczby zasobów, aby utworzyć ten potok, a następnie upewnić się, że będzie on w stanie nadążyć za dużą ilością danych i zmianami schematu.
O autorze
Prashant Sharma
Obecnie kontynuuję studia licencjackie z technologii (B.Tech) w Vellore Institute of Technology. Jestem bardzo entuzjastycznie nastawiony do programowania i jego rzeczywistych zastosowań, w tym tworzenia oprogramowania, uczenia maszynowego, głębokiego uczenia się i nauki o danych.
Mam nadzieję, że podoba Ci się artykuł. Jeśli chcesz się ze mną połączyć, możesz połączyć się na:
lub w przypadku jakichkolwiek innych wątpliwości możesz wyślij maila dla mnie też
Związane z
- '
- "
- Konto
- aktywny
- Wszystkie kategorie
- Amazonka
- analityka
- aplikacje
- POWIERZCHNIA
- artykuł
- zautomatyzowane
- backup
- pobudzanie
- budować
- Budowanie
- biznes
- business intelligence
- wezwanie
- Wykrywanie urządzeń szpiegujących
- cleveland
- Chmura
- usługi w chmurze
- kod
- Komunikacja
- sukcesy firma
- połączenie
- kontynuować
- Tworzenie
- Listy uwierzytelniające
- dane
- integracja danych
- nauka danych
- hurtownia danych
- magazyn danych
- Baza danych
- Bazy danych
- głęboka nauka
- rozwijać
- deweloperzy
- oprogramowania
- urządzenia
- Wymiary
- dokumenty
- kierowca
- efektywność
- itp
- egzekucja
- kierownictwo
- Doświadczenia
- ekstrakcja
- Wyciągi
- Brak
- i terminów, a
- pływ
- format
- świeży
- pełny
- poprowadzi
- sprzęt komputerowy
- tutaj
- Wysoki
- historia
- W jaki sposób
- How To
- HTTPS
- zidentyfikować
- Rezultat
- importowanie
- Włącznie z
- Informacja
- integracja
- Inteligencja
- problemy
- IT
- Klawisz
- duży
- UCZYĆ SIĘ
- nauka
- Linia
- Lista
- załadować
- uczenie maszynowe
- i konserwacjami
- mapa
- rynek
- Media
- Najbardziej popularne posty
- ruch
- Nazwy
- powiadomienie
- z naszej
- Oferty
- operacyjny
- system operacyjny
- operacje
- wyrocznia
- zamówienie
- organizacji
- Inne
- wzmacniacz
- Hasło
- jest gwarancją najlepszej jakości, które mogą dostarczyć Ci Twoje monitory,
- Platforma
- Platformy
- Popularny
- Wytworzony
- Produkcja
- Programowanie
- Programy
- projekt
- ciągnięcie
- Python
- Surowy
- w czasie rzeczywistym
- Rzeczywistość
- dokumentacja
- regeneracja
- Relacje
- Raporty
- Zasoby
- odpowiedź
- dochód
- nauka
- Usługi
- zestaw
- Short
- Prosty
- So
- Tworzenie
- rozwoju oprogramowania
- Rozwiązania
- SQL
- STAGE
- Stan
- sklep
- sklep
- system
- systemy
- cel
- tech
- Techniki
- Technologia
- Źródło
- czas
- Transformacja
- Tutorial
- Aktualizacja
- wartość
- sprzedawców
- Tom
- Magazyn
- Magazynowanie
- tygodniowy
- Praca
- działa