Proces ETL przy pomocy Visual Studio

Po tygodniu przerwy wracam do pisania (niestety projekt na studia zajął mój cały wolny czas).

Chciałbym bliżej przyjrzeć się importowi danych do bazy danych MSSQL za pomocą narzędzi dostępnych w Visual Studio 2015. Dzięki temu momentalnie stworzymy proces ETL. Ale co tak tak właściwie jest?

ETL (Extract, Transform, Load)

Według definicji z Wikipedii:

Narzędzia wspomagające proces pozyskania danych dla baz danych, szczególnie dla hurtowni danych.

Zadaniem narzędzi ETL jest:

  • pozyskanie danych ze źródeł zewnętrznych,
  • przekształcenie danych,
  • załadowanie danych do bazy danych (zazwyczaj będącej hurtownią danych).

Cel tutoriala

W dalszej części wpisu, pokażę jak w łatwy i szybki sposób zaimportować dane z pliku tekstowego (.txt) do bazy danych. Przejdziemy krok po kroku, tworząc wszystkie potrzebne etapy, aby dane wejściowe znalazły się w odpowiedniej tabeli.

Przygotowania

Aby rozpocząć przygodę z importem danych do bazy danych będziemy potrzebowali:

Gdy mamy już wszystko zainstalowane czas na stworzenie tabeli, do której będziemy kopiowali dane. Na potrzeby tego tutoriala stworzyłem bazę IntegrationSerivesTutorial wraz z tabelą Pracownik:

CREATE TABLE [dbo].[Pracownik](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Imie] [varchar](30) NOT NULL,
[Nazwisko] [varchar](50) NOT NULL,
[DataUrodzenia] [date] NOT NULL,
[Plec] [bit] NOT NULL,
[DataZatrudnienia] [date] NOT NULL,
[DataZwolnienia] [date] NULL,
CONSTRAINT [PK_Pracownik] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

oraz prosty plik, który zaimportujemy do bazy:

Nazwisko;Imie;Plec;DataUrodzenia;DataZatrudnienia;DataZwolnienia
Kowalski;Jan;mężczyzna;1982-01-02;2017-02-03;
Wiśniewska;Natalia;kobieta;1988-12-24;2016-06-09;
Kwiatek;Anna;kobieta;1979-07-15;2016-09-01;
Nowak;Tomasz;mężczyzna;1976-10-30;2015-12-01;2017-01-22
;Anonim;1992-01-24;2016-05-02

Celowo wprowadziłem ostatni wiersz błędny, aby później go pominąć podczas dodawania do bazy danych.

Integration Services Project

Otwieramy Visual Studio (jako administrator) i tworzymy Integration Services Project z Templates/Business Intelligence/Integration Services.

wybór_projekt

Z Solution Explorer otwieramy SSIS Packeges/Package.dtsx. W tym pliku będziemy definiować kroki naszego procesu. Podstawowym z nich będzie Data Flow Task. Aby go dodać, z okna SSIS Toolbox przeciągamy Data Flow Task do nowo otwartego okna Package.dtsx i dwukrotnie klikamy nowy element w oknie.

Źródło

Przechodzimy do nowego ekranu, do którego przeciągamy Flat File Source. Klikamy dwukrotnie na ten element i otwiera się nam nowe okno. Zaznaczamy opcję Retain null values from the source as null values in the data flow. Wybieramy New i otwiera
się nam kolejne okno. W Connection manager name wpiszmy: Źródło Pracowników
i wybierzmy Browse, aby wybrać plik, który jest źródłem danych.

Zostawiamy wszystkie wartości domyślne i przechodzimy do zakładki Columns,
w której mamy podgląd naszych danych. Powinno to wyglądać tak:

wybór_źródło1

Przechodzimy do zakładki Advanced, w której możemy zmienić nazwy kolumny
oraz typ danych. Dla Imie ustawiamy OutputColumnWidth na 30.

Klikamy OK oraz ponownie OK.

Filtrowanie

Teraz będziemy chcieli odfiltrować błędne rekordy (te którym brakuje danych).

Musimy dodać element Conditional Split, a następnie zaznaczyć poprzedni element Flat File Source i przeciągnąć z niego niebieską strzałkę do nowego elementu. Po tym klikamy dwukrotnie na niego.

W nowym oknie, na dole w Default output name zmieniamy na Błędne dane. Dalej uzupełniamy jak na obrazku:

filtrowanie_1

Dzięki temu na wyjściu z danego klocka będą poprawne dane. Zamykamy okno klikając Ok.

Konwersja

W bazie danych, pole Plec jest reprezentowane za pomocą typu bit, a w danych źródłowych mamy jako string. Czas na zamianę tego. Aby tego dokonać, dodajemy nowy element o nazwie Derived Column i przeciągamy strzałkę z Conditional Split
do nowego elementu. Pojawia się nowe okno, w którym wybieramy:filtrowanie_2

To zapewnia nam, że przefiltrowane dane trafią do dalszej obróbki. Zatwierdzamy klikając Ok. Otwieramy Derived Column i wypełniamy jak na obrazku:

konwersja_1

Dodaliśmy nowe kolumny. Jedna jest konwersją kolumny Plec na boola, pozostałe konwertują dane na typ date.

Zapis do bazy

Pozostała najważniejsza część procesu, czyli zapis do bazy.

Wybieramy SQL Server Destination i łączymy go z Derived Column. Klikamy dwukrotnie na element i wybieramy dwa razy New. Podajemy adres do bazy
i wybieramy bazę, w której jest tabela Pracownik. Niestety, możemy połączyć się tylko
z bazą lokalną- jak chcemy ze zdalnym serwerem, to trzeba użyć elementu OLE DB Destionation).  Zatwierdzamy dwukrotnie klikając Ok.

W Use a table or view wybieramy tabelę Pracownik i przechodzimy do zakładki Mappings. Wypełniamy ją według poniższego obrazka:

mapowanie_1

Klikamy Ok.

Cały proces powinien prezentować się następująco:

proces

Uruchomienie procesu

Przechodzimy do Solution Explorer i klikamy prawym na Package.dtsx i wybieramy Execute Package. Jeśli postępowaliśmy dokładnie ze wskazówkami, dane znajdą się
w tabeli:

dane_w_bazie

Ewentualne błędy możemy zobaczyć w zakładce Execution Results w widoku Package.dtsx.

Podsumowanie

Pokazany sposób, to tyko wierzchołek góry lodowej możliwości tego narzędzia. Mamy do wyboru inne źródła danych, jak również bardziej rozbudowane narzędzia do filtrowania czy modyfikowania danych.

Reklamy

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj /  Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj /  Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj /  Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj /  Zmień )

w

Connecting to %s