Pentaho - praca z datami

Jak odczytywać daty, konwertować do innego formatu i inne operacje na datach w Pentaho Kettle

Odczyt dat i ich konwersja sprawia wiele problemów niedoświadczonym użytkownikom. Bez zbędnej gadaniny - przejdźmy prosto do tematu.

Odczyt daty

Przy czytaniu plików (Tekstowe, pliki Excela) daty w kolumnie mogą mieć jeden z przykładowych formatów (tylko mała próbka jak mogą być one zapisane):

2023-10-26
26/10/2023
10/26/23 14:15
26/Oct/23 5:43 AM

Często krok odczytujący plik, np. "Text file input" potrafi automatycznie, poprawnie zinterpretować format daty i zaproponować właściwy w opcjach "Format". Czasami jednak musimy ręcznie wstawić tam właściwy. Np. jeśli nasze daty zapisane są w formacie "2023-10-26", w polu "Format" wpisujemy maskę "yyyy-MM-dd". Upewnij się że odczytywana data ma typ danych "Date" - jeśli będzie to "String", nie będziesz mógł użyć takiej informacji jako daty w następnych krokach.

Amerykański zapis daty jest często "dziwaczny" i nie dość że miesiąc występuje tam na początku, często zapisany jest słownie skrótem lub pełną nazwą miesiąca. Dodatkowo nie rzadko godzina występuje w formacie dwunastogodzinnym z następującą po nim określeniu czy chodzi o rano czy popołudnie; AM, PM. I tak gdybyśmy chcieli odczytać datę "26/Oct/23 05:29 AM" musimy użyć maski "dd/MMM/yy h:mm a".

Uwaga, powyższa maska może nie zadziałać jeśli masz system operacyjny w polskiej wersji. Popularne formaty dat zostały wymienione w tym dokumencie Pentaho: https://help.hitachivantara.com/Documentation/Pentaho/9.3/Products/Common_Formats

Konwersja daty do innego formatu

Jeśli w pliku masz inny format daty niż Twoja potrzeba, musisz przekonwertować datę z jednego formatu do drugiego. W takim przypadku w pierwszym kroku, np. "Microsoft Excel input" określ kolumnę z datą właściwym typem danych (Date), ustaw format w jakim zapisane są daty (np. dla dat "10/26/23 14:15" będzie to "MM-dd-yy HH:mm"). OK, mamy odczytaną datę w formacie źródłowym. Teraz konwertujemy ją do formatu docelowego. Wstaw następny krok w transformacji - "Select values". W zakładce "Meta-data" wybierz formatowe pole, ustaw typ danych jako "Date" i wstaw docelowy "Format" daty.

Ten następny krok zrobi konwersję daty do pożądanego formatu. Pamiętaj że to jak wyświetlana jest data nie oznacza że data ulega rzeczywistej konwersji. Samo pole daty jest typu binarnego i jest przenoszone przez projekt przepływu bez konwersji. Jeśli chcesz naprawdę dokonać konwersji, zamień datę na string a następnie ze string uzyskaj datę.

Daty w tej samej kolumnie mają różną postać - co robić?

Dość trywialne jest ustawienie maski odczytu dat jeśli wszystkie wiersze są sformatowane w ten sam sposób. Co jednak zrobić jeśli część wierszy ma np. date w formacie "2023-10-26" a następne "26/10/2023" (a być może i są jeszcze inne)?

Przede wszystkim jeśli formatujemy daty, wszystkie pola muszą być datą. Jeśli mamy ewidentnie niepoprawne informacje, należy takie wiersze usunąć lub wstawić dla nich jakąś datę (choćby "1970-01-01" co oznaczać będzie że tutaj nie mamy poprawnej daty). Możemy to zrobić np. krokiem "Replace in string".

Odczyt różnych formatów dat możemy zrobić budując transformację z kaskadą kroków "Select values". Każdy krok tego typu odpowiada za jeden format daty. Błędy danego kroku, przy łączeniu kroków w transformacji wybierz połączenie typu "Error handling of step", przesyłane są do następnego kroku "Select values".

Jak widzisz, transformacja powyżej obsługuje trzy różne formaty dat odczytane z pliku. Pierwszy krok odczytuje datę jako tekst a w krokach "Select values" jest ona formatowana do typu danych "Date". Ostatni krok, "Text file output" ma możliwość zapisu daty w pożądanym formacie. Zauważ że wszystkie pozostałe błędy możesz skierować do innego pliku; lub części transformacji i tam je obsłużyć.

Operacje na datach

Często w analizach danych potrzebujemy obliczyć różnice czasowe (ile czasu upłynęło, kiedy wygasa termin ważności, obliczyć SLA... wymień inne). Pentaho Kettle oferuje co najmniej dwa kroki które do tego służą. Pierwszym, dla nieco bardziej zaawansowanych użytkowników jest "Modified JavaScript value". W kroku tym masz dostępne funkcje JavaScript (Pentaho używa silnika Rhino) których możesz użyć do manipulacji datami. Przykłady:

//Numer kwartału roku:
var obecny_kwartal = quarter(obecna_data);

//Tydzien roku:
var nr_tygodnia = week(jakas_data);

//Ile miesięcy upłynęło od wymienionej daty:
var ile_miesiecy = dateDiff(data1,data2,"m");

//Jaki będzie następny dzień pracujący:
var nast_dzien_prac = getNextWorkingDay(date1);

Drugi sposób to użycie kroku "Calculator" w którym mamy gotowe funkcje do różnych obliczeń bazujących na datach. Czasami te obliczenia wymagają tylko dwóch dat a czasami więcej. Dlatego też w parametrach mamy pola "Field A", "Field B", "Field C" - tam wybieramy pola które maja daty na których bazujemy. Przykłady co możemy zrobić z pomocą kalkulatora (pole "Calculation"):

Rok, miesiąc lub dzień z daty
Usunięcie czasu z daty
Odjęcie jednej daty od drugiej
Dodanie godzin do daty
Dodanie minut do daty
Obliczenie ile upłynęło dni roboczych od danej daty
Wyliczenie sekund z minut daty
Dodanie miesięcy do daty

Możemy także manipulować datami używając do tego języka SQL. Oto przykład wykorzystania kroku "Table input" by pobrać dane w interesującym nas przedziale czasowym (używamy funkcji MySQL do obliczenia różnicy w czasie):

SELECT customer_number, order_date, DATE_FORMAT(?, '%Y-%m-%d') AS c_date FROM ret_sales 
WHERE customer_number = ? 
AND order_date 
BETWEEN 
CONCAT(YEAR(DATE_SUB(?, INTERVAL 6 MONTH)),"-",MONTH(DATE_SUB(?, INTERVAL 6 MONTH)),"-01") 
AND 
LAST_DAY(CONCAT(YEAR(DATE_SUB(?, INTERVAL 1 MONTH)),"-",MONTH(DATE_SUB(?, INTERVAL 1 MONTH)),"-01"))
GROUP BY year(order_date), month(order_date), order_number

Ale możemy użyć też SQL by po prostu dokonać kalkulacji na datach bez zwracania jakichkolwiek danych z tabeli bazy danych:

Select 
CONCAT(YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)),"-",MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)),"-01") AS m_1,
CONCAT(YEAR(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)),"-",MONTH(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)),"-01") AS m_2,
CONCAT(YEAR(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)),"-",MONTH(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)),"-01") AS m_3

To już wszystko na temat jak radzić sobie z datami w ETL Pentaho.