Tworzenie obiegów pracy SSIS i SSRS za pomocą R na SQL Server

• 08/28/2020
• 5 protokół do czytania
• • d
  • M
  • a
  • D
  • c
  • +2

dotyczy: SQL Server 2016 (13.x) i nowsze

w tym artykule wyjaśniono, jak korzystać z wbudowanych skryptów R i Python przy użyciu możliwości nauki języka i danych SQL Server Machine Learning Services z dwoma ważnymi funkcjami SQL Server: SQL Server Integration Services (SSIS) i SQL Server Reporting Services SSRS. Biblioteki R i Python w SQL Server zapewniają funkcje statystyczne i predykcyjne. SSIS i SSR zapewniają odpowiednio skoordynowaną transformację ETL i wizualizacje. W tym artykule wyjaśniono, jak połączyć wszystkie te funkcje w tym schemacie przepływu pracy:

przykłady w tym artykule dotyczą głównie R i SSIS, ale pojęcia i kroki odnoszą się w równym stopniu do Pythona. Druga sekcja zawiera wskazówki i linki do wizualizacji SSRS.

użyj SSIS do automatyzacji

przepływy pracy z danymi są wysoce iteracyjne i obejmują wiele transformacji danych, w tym skalowanie, agregacje, obliczanie prawdopodobieństw oraz zmianę nazwy i łączenie atrybutów. Analitycy danych są przyzwyczajeni do wykonywania wielu z tych zadań w języku R, Python lub innym języku; jednak wykonywanie takich przepływów pracy na danych przedsiębiorstwa wymaga płynnej integracji z narzędziami i procesami ETL.

ponieważ usługi R (In-Database) umożliwiają wykonywanie złożonych operacji w R za pomocą procedur Transact-SQL i składowanych, można zintegrować zadania z nauką danych z istniejącymi procesami ETL. Zamiast wykonywać łańcuch zadań wymagających dużej ilości pamięci, przygotowanie danych można zoptymalizować przy użyciu najbardziej wydajnych narzędzi, w tym usług integracyjnych i Transact-SQL.

oto kilka pomysłów na to, jak zautomatyzować przetwarzanie danych i modelowanie potoków za pomocą usług integracji:

• Wyodrębnij dane ze źródeł lokalnych lub chmurowych, aby zbudować dane szkoleniowe
• Twórz i uruchamiaj modele R lub Python jako część przepływu pracy integracji danych
• Przekwalifikuj modele regularnie (zgodnie z harmonogramem)
• załaduj wyniki ze skryptów R lub Python do innych miejsc docelowych, takich jak Excel, Power BI, Oracle i Teradata, aby wymienić tylko kilka 3440> użyj zadań SSIS do tworzenia funkcji danych w bazie danych SQL
• użyj warunkowego rozgałęzienia, aby zmienić kontekst obliczeniowy dla zadań r i Python

przykład SSIS

ten przykład pokazuje, jak to zrobić. automatyzuj zadania za pomocą SSIS. Procedury składowane można tworzyć za pomocą wbudowanego programu R za pomocą programu SQL Server Management Studio, a następnie wykonywać je za pomocą zadań wykonuj zadania T-SQL w pakiecie SSIS.

aby przejść przez ten przykład, powinieneś znać Management Studio, SSIS, SSIS Designer, package design i T-SQL. Pakiet SSIS wykorzystuje trzy zadania Execute T-SQL, które wstawiają dane treningowe do tabeli, modelują Dane i oceniają dane, aby uzyskać wynik przewidywania.

załaduj dane treningowe

uruchom następujący skrypt w SQL Server Management Studio, aby utworzyć tabelę do przechowywania danych. Należy utworzyć i używać bazy danych testowych do tego ćwiczenia.

Utwórz procedurę składowaną, która wczytuje dane treningowe do ramki danych. Ten przykład wykorzystuje wbudowany zestaw danych tęczówki.

w SSIS Designer Utwórz zadanie Execute SQL, które wykona właśnie zdefiniowaną procedurę składowaną. Skrypt dla SQLStatement usuwa istniejące dane, określa, które dane należy wstawić, a następnie wywołuje procedurę składowaną w celu dostarczenia danych.

Wygeneruj model

uruchom następujący skrypt w SQL Server Management Studio, aby utworzyć tabelę przechowującą model.

Utwórz procedurę składowaną, która generuje model liniowy za pomocą rxLinMod. Biblioteki RevoScaleR i revoscalepy są automatycznie dostępne w sesjach R i Python na serwerze SQL, więc nie ma potrzeby importowania biblioteki.

w SSIS Designer Utwórz zadanie Execute SQL, aby wykonać procedurę składowaną generate_iris_rx_model. Model jest serializowany i zapisywany w tabeli ssis_iris_models. Skrypt dla SQLStatement jest następujący:

jako punkt kontrolny, po zakończeniu tego zadania, możesz wysłać zapytanie do ssis_iris_models, aby sprawdzić, czy zawiera on jeden model binarny.

przewiduj wyniki za pomocą modelu „wyszkolonego”

teraz, gdy masz kod, który ładuje dane treningowe i generuje model, jedynym krokiem pozostaje wykorzystanie modelu do generowania prognoz.

aby to zrobić, umieść skrypt R w zapytaniu SQL, aby uruchomić wbudowaną funkcję rxpredict na ssis_iris_model. Procedura składowana o nazwie predict_species_length realizuje to zadanie.

w SSIS Designer Utwórz zadanie Execute SQL, które wykonuje procedurę składowaną predict_species_length, aby wygenerować przewidywaną długość płatka.

exec predict_species_length 'rxLinMod';

Uruchom rozwiązanie

w SSIS Designer, naciśnij klawisz F5, aby uruchomić pakiet. Powinieneś zobaczyć wynik podobny do poniższego zrzutu ekranu.

użyj SSR do wizualizacji

chociaż R może tworzyć wykresy i ciekawe wizualizacje, nie jest dobrze zintegrowany z zewnętrznymi źródłami danych, co oznacza, że każdy wykres lub wykres musi być indywidualnie produkowany. Dzielenie się również może być trudne.

korzystając z usług R (In-Database), możesz wykonywać złożone operacje w R za pomocą procedur składowanych Transact-SQL, które mogą być łatwo wykorzystywane przez różne narzędzia raportowania korporacyjnego, w tym usługi raportowania i Power BI.

przykłady SSIS i SSRS w tym artykule ilustrują dwa przypadki wykonywania procedur składowanych, które zawierają wbudowany skrypt R lub Python. Kluczem do sukcesu jest to, że możesz udostępnić skrypt R lub Python dowolnej aplikacji lub narzędziu, które mogą wysłać żądanie wykonania procedury składowanej. Dodatkową korzyścią dla SSIS jest możliwość tworzenia pakietów, które automatyzują i planują szeroki zakres operacji, takich jak Akwizycja danych, czyszczenie, manipulacja itp., Z FUNKCJAMI R lub Python data science włączonymi do łańcucha operacji. Aby uzyskać więcej informacji i pomysłów, zobacz operacjonalizacja kodu R przy użyciu procedur składowanych w usługach uczenia maszynowego SQL Server.