Zamień chaos w zysk: Zrozumienie procesu ETL

Opublikowany: 2022-03-11

Jednym z najbardziej krytycznych błędów, które uniemożliwiają firmom dostarczanie użytkownikom biznesowym istotnych informacji decyzyjnych, jest brak wiarygodnych danych z jednego lub kilku źródeł danych, zgromadzonych w jednym miejscu, uporządkowanych i przygotowanych do użycia.

Wyobraź sobie taki scenariusz — jesteś kierownikiem działu IT firmy zajmującej się etui na telefony komórkowe, której sklepy detaliczne znajdują się w całych Stanach Zjednoczonych. Twój zarząd ma trudności z podejmowaniem decyzji w oparciu o sprzedaż, ponieważ informacje są dostępne, ale znajdują się w różnych lokalizacjach i formatach. Jeden z dyrektorów prosi o wygenerowanie dashboardu z informacjami o sprzedaży ze wszystkich punktów sprzedaży do zaprezentowania na następnym posiedzeniu zarządu.

Wiesz już, że konsolidacja informacji jest prawie niemożliwa ze względu na różne formaty i struktury. Niektóre kioski detaliczne nadal korzystają z zastrzeżonego systemu w bazie danych Oracle. Większe sklepy korzystają z nowego systemu Salesforce. Najnowsze kioski, które rozpoczęły działalność podczas zmiany systemu, mają udostępnione arkusze kalkulacyjne służące do obliczania sprzedaży.

Jak zestawić wszystkie dane z różnych lokalizacji, formatów i struktur w unikalną bazę danych, do której można uzyskać dostęp w celu wygenerowania pulpitu nawigacyjnego?

Podstawy ETL

ETL to skrót od Extract, Transform i Load . ETL to grupa procesów zaprojektowanych w celu przekształcenia tego złożonego magazynu danych w zorganizowany, niezawodny i replikowalny proces, który pomoże Twojej firmie generować większą sprzedaż na podstawie danych, które już posiadasz.

W naszym przypadku otrzymamy dane z bazy danych Oracle (większość kiosków), z Salesforce (sklepy) i z arkuszy kalkulacyjnych (nowsze kioski), wyodrębnimy dane, przekształcimy je w razie potrzeby i załadujemy do jednej bazy danych hurtowni danych do dostępne za pomocą narzędzi raportowania i wykorzystywane do generowania pulpitów nawigacyjnych i kart wyników.

Przejdźmy do trzech etapów ETL, aby omówić ten proces.

Ekstrakcja

Ekstrakcja to proces otrzymywania danych z jednego lub wielu źródeł. Źródła mogą mieć różne formaty i struktury, takie jak dokumenty, arkusze kalkulacyjne, pliki CSV, pliki płaskie, relacyjne bazy danych, takie jak Oracle, MySQL, SQL Server, nierelacyjne bazy danych i tak dalej.

Istnieją dwa główne rodzaje ekstrakcji: pełne i częściowe .

  • Pełna ekstrakcja jest używana do wstępnych ekstrakcji lub gdy ilość danych, a w konsekwencji czas ekstrakcji, są akceptowalne.
  • Częściowe wyodrębnianie jest zalecane, gdy nie ma potrzeby ponownego wyodrębniania wszystkich danych lub ilość danych jest na tyle duża, że ​​pełne wyodrębnienie jest niemożliwe. W częściowej ekstrakcji zostaną wyodrębnione tylko zaktualizowane lub nowe dane.

Oprócz tych aspektów, przy wyborze między pełnym lub częściowym wydobyciem konieczne są pewne inne względy i chcę opisać jeden z nich: dostępność i integralność danych .

Oznacza to, że tylko zakończone transakcje będą brane pod uwagę do ekstrakcji, eliminując dane, które mogą spowodować błąd integralności. Na przykład test online mający na celu określenie wiedzy inżyniera za pomocą 10 pytań. Jeśli inżynier jest w trakcie testu i odpowiedział na kilka pytań, ale jeszcze go nie ukończył, proces ekstrakcji nie może odczytać pytań z niedokończonych testów. Może to spowodować błąd integralności.

Transformacja

Po wyodrębnieniu danych możemy rozpocząć proces transformacji: czyścić, manipulować i przekształcać dane zgodnie z regułami biznesowymi i kryteriami technicznymi w celu utrzymania akceptowalnego poziomu jakości danych.

W zależności od wielu czynników może być konieczne wykorzystanie obszaru postojowego. Obszar pomostowy to pośrednia przestrzeń pamięci używana do tymczasowego przechowywania danych wyodrębnionych ze źródeł danych w celu ich przekształcenia.

W niektórych projektach, zwykle tych z niewielką ilością danych, nie ma potrzeby korzystania z obszarów pomostowych, ale większość projektów z nich korzysta.

W fazie transformacji wykonuje się szereg zadań:

  • Wybór: kryteria wyboru wyodrębnionych danych. Selekcji można dokonać w fazie ekstrakcji, w fazie transformacji lub w obu fazach.
  • Integracja: połączenie danych z fazy ekstrakcji do obszaru pomostowego. Ta kombinacja oznacza dodawanie, usuwanie i aktualizowanie danych w obszarze przemieszczania na podstawie wyodrębnionych danych.
  • Złączenia: używane do łączenia wyodrębnionych danych, podobnie jak złączenia SQL (sprzężenie wewnętrzne, lewe, prawe, pełne itp.)
  • Oczyszczanie lub czyszczenie: usuwa niespójne lub nieprawidłowe dane lub dane z błędami w celu poprawy jakości danych. Praca z wieloma źródłami danych zwiększa prawdopodobieństwo wystąpienia problemów z danymi, które wymagają czyszczenia, takich jak:
    • Integralność referencyjna (klient z nieistniejącą kategorią)
    • Brakujące wartości (klient bez identyfikatora)
    • Wyjątkowość (więcej niż jedna osoba z tym samym numerem SSN)
    • Błędna pisownia (Sun Diego, Cannada, L.Angeles)
    • Sprzeczne wartości (Alex ur. 27.04.1974, Alex ur. 14.04.2000)
    • i wiele innych
  • Podsumowania: podsumuj zestawy danych do późniejszego wykorzystania
  • Agregacje: dane zebrane i podsumowane w grupach
  • Konsolidacje: dane z wielu źródeł lub struktur skonsolidowane w jednym zestawie danych

Oto kilka typowych typów transformacji:

  • Usuń zduplikowane dane
  • Dzielenie i łączenie
  • Konwersje (data, godzina, maski liczbowe, pomiary)
  • Kodowanie (męski na M)
  • Obliczenia (wartość_elementu = cena_jednostkowa * ilość)
  • Generowanie kluczy

Ładowanie

Ostatnim, ale nie mniej ważnym procesem w ETL jest załadowanie danych do miejsca docelowego. Ładowanie to czynność wstawiania przekształconych danych (z obszaru pomostowego lub nie) do repozytorium, zwykle bazy danych hurtowni danych.

Istnieją trzy główne typy ładowania danych: pełne lub początkowe, przyrostowe i odświeżające.

  • Pełny lub początkowy oznacza pełny ładunek wyodrębnionych i przekształconych danych. Wszystkie dane w obszarze pomostowym zostaną załadowane do ostatecznego miejsca docelowego, aby były gotowe dla użytkowników biznesowych.
  • Ładowanie przyrostowe to proces porównywania przekształconych danych z danymi w ostatecznym miejscu docelowym i ładowania tylko nowych danych. Obciążenie przyrostowe może być używane w połączeniu z ładowaniem odświeżającym, co wyjaśniono poniżej.
  • Ładowanie odświeżające to proces aktualizowania danych w ostatecznym miejscu docelowym w celu odzwierciedlenia zmian wprowadzonych w oryginalnym źródle. Odświeżanie może być pełne lub przyrostowe.

Podsumowując, każda firma, niezależnie od jej wielkości, może wykorzystywać procesy ETL do integrowania informacji, które już istnieją i generowania jeszcze większego bogactwa informacji do podejmowania decyzji, zamieniając dane, które wcześniej nie mogły być wykorzystane w nowe źródło przychodów.

Testowanie

Testowanie jest jedną z najważniejszych faz ETL, a jednocześnie jedną z najczęściej pomijanych.

Przekształcanie danych z różnych źródeł i struktur oraz ładowanie ich do hurtowni danych jest bardzo złożone i może generować błędy. Najczęstsze błędy zostały opisane w powyższej fazie transformacji.

Dokładność danych jest kluczem do sukcesu, a niedokładność jest receptą na katastrofę. Dlatego misją specjalistów ETL jest zagwarantowanie integralności danych podczas całego procesu. Po każdej fazie należy przeprowadzić test. Niezależnie od tego, czy dane są pobierane z jednego źródła, czy z wielu źródeł, dane należy sprawdzić, aby ustalić, czy nie ma błędów.

To samo należy zrobić po każdej transformacji. Na przykład podczas podsumowywania danych w fazie transformacji dane muszą być sprawdzone, aby zagwarantować, że żadne dane nie zostały utracone, a sumy są poprawne.

Po załadowaniu przetworzonych danych do hurtowni danych należy ponownie przeprowadzić proces testowania. Załadowane dane należy porównać z danymi przekształconymi, a następnie z danymi wyodrębnionymi.

W naszym przykładzie firmy zajmującej się etui na telefony komórkowe pracujemy z trzema różnymi źródłami (zastrzeżoną bazą danych Oracle, Salesforce i arkuszami kalkulacyjnymi) i różnymi formatami. W fazie testowania można wykorzystać przykładowe dane z oryginalnych źródeł i porównać je z danymi znajdującymi się w obszarze pomostowym, aby zagwarantować, że wyodrębnienie przebiegło bez błędów.

Przykładowe dane — w tym przypadku mogą to być informacje o sprzedaży z trzech różnych lokalizacji (sklepów, starych kiosków, nowych kiosków) — należy porównać z oryginalnym źródłem. Ewentualne różnice należy przeanalizować, aby sprawdzić, czy są akceptowalne, czy też są błędami.

Jeśli zostaną znalezione błędy, należy je naprawić, a jeśli trzeba je naprawić, należy podjąć kilka decyzji: Czy należy zmodyfikować oryginalne dane? Czy to możliwe? Jeśli błędów nie można naprawić w oryginalnym źródle, czy można je naprawić za pomocą jakiejś transformacji?

W niektórych przypadkach dane z błędami należy wyeliminować i uruchomić alert, aby poinformować osoby odpowiedzialne.

Kilka przykładów testów:

  • Dane wymagają walidacji
  • Jakość danych
  • Występ
  • Zasady dotyczące danych
  • Modelowanie danych

Logowanie

Rejestrowanie procesów ETL to kluczowa gwarancja, że ​​posiadasz systemy łatwe w utrzymaniu i łatwe do naprawy.

ETL z prawidłowym procesem rejestrowania jest ważny dla utrzymania całej operacji ETL w stanie ciągłej poprawy, pomagając zespołowi zarządzać błędami i problemami ze źródłami danych, formatami danych, transformacjami, miejscami docelowymi itp.

Niezawodny proces rejestrowania pomaga zespołom zaoszczędzić czas, pozwalając im szybciej i łatwiej identyfikować problemy, a główni inżynierowie potrzebują mniej czasu na bezpośrednie zlokalizowanie problemu. Czasami w trakcie wyodrębniania ton danych pojawiają się błędy, a bez dziennika identyfikacja problemu jest trudna, a czasami prawie niemożliwa. Bez logów cały proces musi zostać ponownie uruchomiony. Korzystając z dzienników, zespół może szybko zidentyfikować plik i wiersz, które spowodowały problem, i naprawić tylko te dane.

Jedyny przypadek, jaki mogę sobie wyobrazić, w którym logi nie są tak ważne, to bardzo małe niezautomatyzowane systemy, w których proces przebiega ręcznie i istnieje niewielka ilość danych, które można monitorować ręcznie.

Logi usprawniają automatyzację. Procesy ETL z dużą ilością danych, które działają automatycznie, wymagają systemów dzienników. Jeśli są dobrze zaplanowane i wykonane, cały wysiłek włożony w zbudowanie systemu rejestrowania przyniesie korzyści w postaci szybszej identyfikacji błędów, bardziej wiarygodnych danych i punktów poprawy znalezionych w plikach dziennika.

Tworzenie systemu dzienników składa się z trzech głównych etapów: generowanie, archiwizacja i analiza .

  • Generowanie to proces dokumentowania tego, co się dzieje podczas wykonywania potoków ETL: kiedy proces się rozpoczął, który plik lub tabela jest wyodrębniany, dane zapisywane w obszarze przemieszczania, komunikaty o błędach i inne. Wszystkie ważne informacje, które mogą pomóc inżynierom, muszą być rejestrowane. Uwaga : zwróć uwagę, aby nie generować tylu informacji, które zajmą tylko czas i przestrzeń i nie będą pomocne.
  • Dane dziennika archiwizacji oznaczają prowadzenie rejestru przeszłych wykonań w celu wyszukiwania wcześniejszych scenariuszy w celu zidentyfikowania błędów lub porównania z bieżącym scenariuszem w poszukiwaniu ulepszeń. Ważne jest, aby sprawdzić trafność konkretnego punktu w historii do zapisania – dane z dawnych czasów, w których struktura wielokrotnie się zmieniała, nie są warte zachowania.
  • Analizuj . Analiza logów ma kluczowe znaczenie. Przechowywanie ton danych, które nie są analizowane, nie ma sensu. Po prostu generowanie i przechowywanie danych kosztuje czas i pieniądze. Analiza logów jest ważna nie tylko po to, aby pomóc w wyszukiwaniu błędów, ale także w identyfikacji punktów poprawy i podniesieniu ogólnej jakości danych.

Występ

Procesy ETL mogą pracować z tonami danych i mogą dużo kosztować — zarówno pod względem czasu poświęconego na ich skonfigurowanie, jak i zasobów obliczeniowych potrzebnych do przetworzenia danych. Planując integrację inżynierowie muszą pamiętać o konieczności wykorzystania wszystkich danych. Najlepszą rzeczą do zrobienia jest praca z minimalną ilością danych, aby osiągnąć cele, zamiast tracić czas i pieniądze na samą migrację bezużytecznych danych. Pamiętaj też, że ilość danych ma tendencję do zwiększania się z czasem, więc postaraj się rozważyć swoje przyszłe potrzeby.

Ile wysiłku należy włożyć w zaprojektowanie systemu rejestrowania?

Zależy to od wielu różnych czynników, takich jak ilość i częstotliwość przetwarzanych danych. Małymi systemami z niewielką ilością danych można zarządzać ręcznie, bez konieczności inwestowania w zaawansowane systemy logowania.

Firmy z dużą ilością danych, wieloma różnymi procesami, różnymi źródłami i formatami danych oraz złożonymi potokami muszą inwestować w tworzenie systemów rejestrowania. Jak już wspomnieliśmy, dobry system logów może zaoszczędzić sporo czasu i pieniędzy.

Dla bardziej oczywistego podejścia, niezależnie od wielkości firmy, ilości danych i częstotliwości integracji, proces ETL musi być opłacalny . Inwestycja w czas, pieniądze i zasoby techniczne musi generować zwroty – jak powiedział ekonomista Milton Friedman: „Nie ma darmowego obiadu”.

Podsumowując, proces ETL może pomóc firmom zwiększyć zyski dzięki danym, które już posiadają, ale które nie są wykorzystywane we właściwy sposób. Potoki ETL mogą integrować różne systemy, te arkusze kalkulacyjne z ważnymi informacjami i inne części danych, które są rozmieszczone w różnych działach i oddziałach, umożliwiając organizacjom maksymalne wykorzystanie ich danych.