Własna kontrolka SSIS – część 9 – metadane pobierania plików

Pobieranie plików z serwera SFTP za pomocą biblioteki WinSCPNet to nic nadzwyczaj trudnego. Jest dobrze opisujący to przykład wykorzystujący funkcję GetFiles() – nic tylko kopiować i używać. W naszym przypadku będzie z tym nieco więcej roboty bo najpierw musimy określić, które pliki nas interesują, a dopiero potem je pobrać. Żeby określić, które nas interesują musimy najpierw mieć informację o plikach już pobranych – żeby nie pobierać tych zbędnych. Żeby mieć informację o plikach już pobranych musimy najpierw ją gdzieś zapisać. Żeby ją zapisać musimy mieć do tego jakieś struktury. Żeby mieć struktury – po prostu je zbudujmy.

Co będzie potrzebne:

  • informacja o plikach już pobranych (można też dodać – przetworzonych)
  • informacja o plikach na serwerze
  • informacja o plikach do pobrania jako wynik porównania dwóch wcześniejszych informacji

To zestaw podstawowy. Docelowo uczynimy nasz model nieco bardziej złożony. Załóżmy, że nie chcemy po raz kolejny pobrać część plików (np. wrzucono „lepsze wersje”), ale nie chcemy grzebać w liście plików już pobranych. Albo odwrotnie – wiemy, że jakieś pliki są błędne, mimo to pobieramy je za każdym razem – spróbujmy wykluczyć część plików z pobierania. Potrzebujemy zatem jeszcze dwóch elementów:

  • informacja o plikach do ponownego pobrania
  • informacja o plikach wykluczonych z pobierania

Tabele plików

Na początku projektu założyłem, że miejscem przechowywania metadanych będzie SQL Server. Projekt jednak powinien pozwalać bezboleśnie dodawać inne bazy danych. To jednak w ewentualnych dalszych etapach, na razie skoncentrujmy się na tej jednej. Wszystkie nazwy tabel opisanych poniżej mają być docelowo parametryzowane – ale od czegoś trzeba zacząć.

Informacje o plikach już pobranych będzie przechowywać tabela dbo.DownloadedFiles, która dodatkowo będzie się odwoływać do słownika statusów dbo.FileStatus:

CREATE TABLE dbo.DownloadedFiles (
    Id                   INT  IDENTITY(1, 1)        NOT NULL,
    RemoteFilePath       VARCHAR(255)               NOT NULL,
    RemoteDirectoryName  VARCHAR(255)               NOT NULL,
    LocalFileName        VARCHAR(255)               NOT NULL,

    LoadDTTM             DATETIME2(0)
        CONSTRAINT DF_DownloadedFiles_LoadDTTM
            DEFAULT SYSUTCDATETIME()                NOT NULL,

    FileStatusId         TINYINT                    NOT NULL
        CONSTRAINT FK_DownloadedFiles_FileStatus
            FOREIGN KEY
            REFERENCES dbo.FileStatus(Id),

    AuditKey             INT                        NOT NULL
);

CREATE TABLE dbo.FileStatus (
    Id            TINYINT        NOT NULL,
    StatusName    VARCHAR(10)    NOT NULL,

    CONSTRAINT PK_FileStatusId
        PRIMARY KEY CLUSTERED (Id)
);

dbo.FileStatus zawiera możliwe statusy pliku (nie skupiamy się na niuansach – wystarczy informacja o przetworzeniu poprawnym lub nie, braku przetworzenia i domyślne „nie mam pojęcia”):

INSERT INTO dbo.FileStatus (Id, StatusName)
VALUES
    (0, 'Unknown'),
    (1, 'New'),
    (2, 'Processed'),
    (3, 'Error')
;

Tabela plików z kolei przechowuje informacje o pełnej ścieżce pliku na serwerze (skąd pobrano), nazwie katalogu, w którym plik się znajdował (fragment ścieżki – może potem zrezygnujemy, ale na razie niech zostanie) i nazwie pliku, pod jaką zapisano go lokalnie po ściągnięciu. W tym ostatnim przypadku nazywam plik jego pełną nazwą razem ze ścieżką, przy czym zamieniam znaki slash / na podkreślenia _.

Obrazu dopełnia informacja o dacie pobrania pliku przez proces ETL (LoadDTTM) i identyfikatorze procesu zasilającego do celów audytowych – AuditKey. Nie jestem do końca przekonany do nazwy LoadDTTM, ale na razie niech zostanie.

Pliki na serwerze mogą się znajdować w kilku katalogach, dodatkowo możemy mieć dostęp tylko do kilku katalogów. Dodamy (w dedykowanym schemacie) tabelę konfiguracyjną, w której przechowamy nazwy katalogów do przeszukiwania:

CREATE TABLE config.ServerCatalog (
    Name        VARCHAR(255)                    NOT NULL,
    IsActive    BIT
        CONSTRAINT DF_ServerCatalog_IsActive
                DEFAULT 1                       NOT NULL
);

Nic wyszukanego – nazwa katalogu i flaga, czy w danym momencie chcemy ten katalog przeszukiwać, czy też nie. Została ostatnia rzecz – pliki, które chcemy pobrać ponownie i pliki, które chcemy wykluczyć z pobierania. Tworzymy dwie dodatkowe tabele w schemacie config:

CREATE TABLE config.ReimportFiles (
    RemoteFilePath       VARCHAR(255)               NOT NULL,
    RemoteDirectoryName  VARCHAR(255)               NOT NULL,
    IsActive    BIT
        CONSTRAINT DF_ReimportFiles_IsActive
                DEFAULT 1                           NOT NULL
);
GO

CREATE TABLE config.IgnoreFiles (
    RemoteFilePath        VARCHAR(255)            NOT NULL,
    RemoteDirectoryName   VARCHAR(255)            NOT NULL,
    Reason                VARCHAR(150)            NOT NULL,
    InsertedByLogin       VARCHAR(128)            NOT NULL,
    AuditKey              INT
);
GO

Tabela config.ReimportFiles to lista plików do ponownego pobrania z serwera. Oprócz pełnej nazwy pliku (ze ścieżką) i katalogu na serwerze (pole nadmiarowe, do zastanowienia się czy zostawić) jest flaga czy plik jest aktywny. Założenie jest takie, że do tabeli zawsze dodajemy pliki do ponownego pobrania, a te już pobrane zachowujemy sobie jako ślad, że chcieliśmy je w ogóle pobrać ponownie. Po pobraniu plików ustawiamy flagę 0, że już pobraliśmy i wpis jest nieaktywny. Do zastanowienia się, czy rozbudować tabelę o dodatkowy audyt – np. kiedy pobrano dany plik – ale na razie zostawmy w spokoju.

Tabela config.IgnoreFiles to pliki do pominięcia. Tak jak w plikach pobieranych ponownie mamy pełną nazwę pliku i katalogu na serwerze, ale są też trzy dodatkowe kolumny audytowe. Reason to powód znalezienia się pliku na liście. Założenie na przyszłość jest takie, że automat zasilający będzie badał historię importów i w przypadku jeśli plik pojawi się jako błędny X razy, to automatycznie trafi na listę, żeby go nie pobierać ponownie. Możemy też samodzielnie wskazać plik jako błędny – wówczas zasygnalizujemy to innym opisem.

Oprócz tego podajemy login użytkownika, który dodał rekord do wykluczeń. Domyślnie będzie to konto procesu ETL, ale w ramach zespołu można przyjąć zasadę, że jeśli ktoś doda rekord ręcznie podaje swój login. Do tego jeszcze AuditKey jako identyfikator procesu zasilającego i podstawowy zestaw tabel wydaje się być gotowy.

A co w przypadku kilku źródeł danych?

Do wyboru są dwie drogi:

  • rozbuduj tabele plików o kolumnę związaną z systemem źródłowym / interfejsem danych
  • utwórz odrębne tabele konfiguracyjne dla każdego systemu źródłowego / interfejsu

Przez interfejs lub system źródłowy rozumiem tu np. serwer FTP, zasób sieciowy, pliki Sharepoint itd. W naszym przypadku będzie można zastosować oba podejścia – zakładamy od początku, że kontrolka sama zatroszczy się o obsługę różnych konfiguracji. Na chwilę obecną przyjmijmy, że jest jeden zestaw tabel, bo mamy jedno źródło danych, a przy rozbudowie kontrolki, która będzie dobrze działać dla jednego zestawu tabel obsłużymy oba modele wymienione wyżej. Z tyłu głowy zostanie kwestia czy dodać kolejną kolumnę do obsługi (np. SourceSystemId / InterfaceId) lub parametryzację nazw tabel (np. prefiks nazwy tabeli?).

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 z Twittera

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

Zdjęcie na Facebooku

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

Zdjęcie na Google+

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

Connecting to %s