Power Query Source w Integration Services

W ostatnich dniach pojawiła się świetna wiadomość! Chodzi mianowicie o fakt integracji języka M aka Power Query z Integration Services! Jest to o tyle istotna informacja, że postanowiłem przetestować samą funkcjonalność i przedstawić ją na blogu, dlatego też z tego miejsca zapraszam Was do lektury niniejszego artykułu.

Na wstępie kilka faktów na temat samej funkcjonalności. Skrypty języka M w Integration Services mogą być wykonywane z poziomu Power Query Source, jest to nowy adapter dostępny w najnowszej aktualizacji Data Tools. Na ten moment adapter ten jak i cała funkcjonalność znajduje się w fazie preview tak więc możemy być pewni, że z ostateczna wersja będzie wyglądała nieco inaczej. Skrypty M możemy możemy wykonywać z dwóch poziomów tj:

• IDE czyli Data Tools,
• Azure Data Factory Integratation Runtime.

Obecnie interfejs jest nieco ubogi i pozwala nam jedynie na wklejenie skryptu M lub wybranie zmiennej, która takowy skrypt zawiera. Po co w takim razie nam taka funkcjonalność w SSIS? Z perspektywy architektury rozwiązań opartych o platformę danych bardzo istotna jest integracja różnych narzędzi wchodzących w jej skład, dlatego też z mojego punktu widzenia takie zabiegi jak możliwość hostowania raportów Reporting Services na PBI Report Server, Data Factory IR dla SSIS czy też rzeczony Power Query + SSIS to kroki w bardzo dobrym kierunku.

Z deweloperskiego punktu widzenia należy powiedzieć, że łatwiej przeprowadzać niektóre transformacje (np. znana z Power Query operacja Unpivot other columns) lub łączyć się do źródeł, które wcześniej albo nie były obsługiwane albo wymagały niestandardowego pisania kodu np. w C# (m.in. Dane ze stron internetowych, Webservice itp).

Mamy kilka wprowadzających informacji przejdźmy zatem do przetestowania opisywanej funkcjonalności. W momencie gdy już mamy zainstalowany najnowszą aktualizację Data Tools możemy stworzyć standardowo nowy projekt Integration Services czyli wybieramy
File -> New -> Project, a następnie Integration Services->Integration Services Project:

Adapter Power Query jest dostępny oczywiście jako zadanie przepływu Data Flow i z poziomu edytora tego zadania możemy nim operować:

Standardowo będąc wewnątrz Data Flow Task powinniśmy dostrzec naszego dzisiejszego bohatera tj. Power Query Data Source dostępnego w sekcji Common (w finalnej wersji zapewne znajdzie się w sekcji Sources) :

Po przeciągnięciu Power Query Source na pole przepływu komponent jest gotowy do konfiguracji:

Pierwszym krokiem w jego konfiguracji (tak jak w przypadku innych komponentów tego typu) jest stworzenie odpowiedniego menedżera połączeń (Connection Manager). Zrobimy to klikając prawym przyciskiem myszy na pole menedżerów połączeń znajdujące się w dolnej części ekranu i wybierając z menu kontekstowego New Connection…:

W dalszym kroku powinniśmy wybrać z listy dostępnych opcji konektor Power Query:

Okno konfiguracyjne konektora dostosowuje się do wybranego rodzaju połączenia jednakże zaraz po otwarciu powinno wyglądać w następujący sposób:

W tym właśnie oknie powinniśmy wybrać typ źródła danych oraz sposób uwierzytelnienia. Jeśli chodzi o dostępne źródła to lista jest całkiem pokaźna i składa się z następujących typów źródeł:

• Active Directory
• Analysis Services
• Azure Blobs
• AzureDataLakeStorage
• AzureTables
• DB2
• Exchange
• File
• Folder
• Hdfs
• HDInsight
• Informix
• MySQL
• OData
• Odbc
• Oracle
• PostgreSQL
• SharePoint
• SQL
• Sybase
• Teradata
• Web

My na ten moment wybierzemy Web ponieważ w standardowym podejściu w SSIS tego typu źródło zazwyczaj sprawiało trochę problemów. Załóżmy, że chcę pobrać dane ze strony https://www.fifa.com/fifa-world-ranking/ranking-table/men/ Dane te to nic innego jak ranking reprezentacji narodowych w piłce nożnej FIFA. Dane te mają prostą formę tabelaryczną jednakże do tej pory w momencie gdy chcielibyśmy podłączyć się do tego typu struktury z użyciem SSIS to musieliśmy pisać niestandardowy kod języka C# (lub w zależności od preferencji Visual Basic) w dostosowanym zadaniu typu Script Task/Script Component. Okno konfiguracyjne połączenia SSIS dla tego źródła danych wygląda następująco:

Po dodaniu definicji połączenia zgodnie z dobrymi praktykami zmieniłem jego nazwę w standardowy sposób na PQCM_FifaRanking abym nie miał wątpliwości czym jest stworzone przeze mnie połączenie. Dalej możemy przejść do samego adaptera (Power Query Source) gdzie w pierwszej kolejności musimy podać źródło kodu języka M. Do dyspozycji mamy dwie opcje tj. bezpośrednie wklejenie bądź zmienna:

W tym konkretnym przykładzie wygenerujemy sobie zapytanie przy pomocy Power BI Desktop, które wkleimy bezpośrednio do adaptera. Całość sprowadza się do podania odpowiedniego adresu URL do konektora Web i wybrania wykrytej automatycznie tabeli:

Gdy mamy już pobrane dane wystarczy wejść do edytora zapytań i tam wyciągnąć cały wygenerowany kod języka M:

Po klejeniu kodu należy przejść na zakładkę Connection Managers i tam kliknąć Detect Data Source  tak aby wykryć automatycznie użyte w skrypcie M połączenia:

Po prawidłowym wykryciu połączenia w M możemy je zmapować do połączenia stworzonego przez nas wcześniej w SSIS:

Na zakładce Columns jedyne co możemy zrobić to zmienić aliasy dostępnych kolumn na bardziej przystępne:

Warto zwrócić uwagę na fakt, iż w powyższym oknie nie możemy dokonać projekcji kolumn tj. nie możemy dokonać wyboru kolumn, które nas interesują. Jeżeli nie chcemy wszystkich kolumn to wyboru musimy dokonać w samym skrypcie M lub w dalszej części przepływu Data Flow.

Aby podejrzeć działanie omawianego komponentu dodałem transformację Derived Column – po co? Ponieważ chciałbym podejrzeć wygenerowane przez konektora dane za pomocą Data Viewera:

Po uruchomieniu możemy zauważyć, że dane zostały pobrane w prawidłowy sposób:

Widoczne na powyższym zrzucie ekranowym oznaczenia <Long Text> wskazują na fakt, iż dane w tych kolumnach pochodzące ze strony internetowej zostały zinterpretowane jako NTEXT co jest raczej oczekiwanym rezultatem. Oczywiście typy możemy tak jak niemal wszystko zmienić i dostosować (najlepiej dokonując zmiany typu w samym M) .

Jak widać na załączonym obrazku funkcjonalność działa i jest świetną zapowiedzią mariażu SSIS oraz Power Query. Funkcjonalność ta  z całą pewnością tchnie w SSIS drugie życie i poszerzy zakres zastosowań tego zasłużonego narzędzia. Istnieje kilka ograniczeń na ten moment jak brak możliwości użycia sterownika ADO.NET do Oracle czy ograniczenia używanego w niniejszym artykule WebConnectora ale na ten moment nie ma sensu się na nich skupiać ze względu na naprawdę pierwszą wersję (i do tego Preview) Power Query w SSIS.

Teraz nie pozostaje nam nic innego jak trzymać kciuki aby cała integracja posiadała jak najmniej limitów i dawała jak największe pole manewru. Dodatkowo można zauważyć, że Power Query powoli podbija świat danych spod znaku Microsoftu występując nie tylko w usłudze Power BI i Excelu, ale również w Analysis Services i Integration Services. Niezmiernie mnie to cieszy, ze względu na fakt, iż jestem wielkim entuzjastą tejże technologii – tak trzymać!