Wyświetlanie i podsumowywanie danych programu SQL Server przy użyciu języka R (przewodnik)

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje

W tej lekcji przedstawiono funkcje w pakiecie RevoScaleR i wykonasz czynności opisane w następujących zadaniach:

• Nawiązywanie połączenia z programem SQL Server
• Definiowanie zapytania zawierającego potrzebne dane lub określanie tabeli lub widoku
• Definiowanie co najmniej jednego kontekstu obliczeniowego do użycia podczas uruchamiania kodu języka R
• Opcjonalnie zdefiniuj przekształcenia, które są stosowane do źródła danych podczas jego odczytywania ze źródła

Definiowanie kontekstu obliczeniowego programu SQL Server

Uruchom następujące instrukcje języka R w środowisku języka R na stacji roboczej klienckiej. W tej sekcji założono, że stacja robocza do nauki o danych z klientem Microsoft R Zawiera wszystkie pakiety RevoScaleR, a także podstawowy, lekki zestaw narzędzi języka R. Na przykład możesz użyć Rgui.exe do uruchomienia skryptu języka R w tej sekcji.

1. Jeśli pakiet RevoScaleR nie został jeszcze załadowany, uruchom ten wiersz kodu języka R:

   library("RevoScaleR")
   

   Znaki cudzysłowu są opcjonalne, jednak w tym przypadku zalecane.

   Jeśli wystąpi błąd, upewnij się, że środowisko programistyczne języka R korzysta z biblioteki zawierającej pakiet RevoScaleR. Użyj polecenia, takiego jak .libPaths() , aby wyświetlić bieżącą ścieżkę biblioteki.

2. Utwórz parametry połączenia dla programu SQL Server i zapisz je w zmiennej języka R connStr.

   Należy zmienić zmienną zastępczą "your_server_name" na poprawną nazwę instancji serwera SQL. W przypadku nazwy serwera może wystarczyć użycie tylko nazwy wystąpienia albo konieczne może być pełne określenie nazwy, w zależności od konfiguracji sieci.

   W przypadku uwierzytelniania programu SQL Server składnia połączenia jest następująca:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Uid=your-sql-login;Pwd=your-login-password"
   

   W przypadku uwierzytelniania systemu Windows składnia jest nieco inna:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Trusted_Connection=True"
   

   Ogólnie rzecz biorąc, zalecamy używanie uwierzytelniania systemu Windows tam, gdzie to możliwe, aby uniknąć zapisywania haseł w kodzie języka R.

3. Zdefiniuj zmienne do użycia podczas tworzenia nowego kontekstu obliczeniowego. Po utworzeniu obiektu kontekstu obliczeniowego można go użyć do uruchomienia kodu języka R w wystąpieniu programu SQL Server.

   sqlShareDir <- paste("C:\\AllShare\\",Sys.getenv("USERNAME"),sep="")
   sqlWait <- TRUE
   sqlConsoleOutput <- FALSE
   

   • Język R używa katalogu tymczasowego podczas serializacji obiektów języka R między stacją roboczą a komputerem z programem SQL Server. Możesz określić katalog lokalny, który jest używany jako sqlShareDir, lub zaakceptować wartość domyślną.

   • Użyj polecenia sqlWait , aby wskazać, czy język R ma czekać na wyniki z serwera. Aby zapoznać się z omówieniem zadań oczekujących i nieoczekujących, zobacz Rozproszone i równoległe przetwarzanie za pomocą RevoScaleR w Microsoft R.

   • Użyj argumentu sqlConsoleOutput , aby wskazać, że nie chcesz wyświetlać danych wyjściowych z konsoli języka R.

4. Wywołasz konstruktor RxInSqlServer , aby utworzyć obiekt kontekstu obliczeniowego ze zdefiniowanymi już zmiennymi i parametrami połączenia, a następnie zapisać nowy obiekt w zmiennej R sqlcc.

   sqlcc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir, wait = sqlWait, consoleOutput = sqlConsoleOutput)
   

5. Domyślnie kontekst obliczeniowy jest lokalny, dlatego należy jawnie ustawić aktywny kontekst obliczeniowy.

   rxSetComputeContext(sqlcc)
   

   • rxSetComputeContext zwraca wcześniej aktywny kontekst obliczeniowy w sposób niewidoczny, aby można było z niego korzystać
   • funkcja rxGetComputeContext zwraca aktywny kontekst obliczeniowy

   Należy pamiętać, że ustawienie kontekstu obliczeniowego wpływa tylko na operacje korzystające z funkcji w pakiecie RevoScaleR ; kontekst obliczeniowy nie ma wpływu na sposób wykonywania operacji języka R typu open source.

Tworzenie źródła danych przy użyciu serwera RxSqlServer

W przypadku korzystania z bibliotek języka Microsoft R, takich jak RevoScaleR i MicrosoftML, źródło danych jest obiektem tworzonym przy użyciu funkcji RevoScaleR. Obiekt źródła danych określa zestaw danych, które mają być używane dla zadania, takie jak trenowanie modelu lub wyodrębnianie funkcji. Możesz pobrać dane z różnych źródeł, w tym z programu SQL Server. Aby uzyskać listę aktualnie obsługiwanych źródeł, zobacz RxDataSource.

Wcześniej zdefiniowaliśmy parametry połączenia i zapisano te informacje w zmiennej języka R. Możesz ponownie użyć tych informacji o połączeniu, aby określić dane, które chcesz uzyskać.

1. Zapisz zapytanie SQL jako zmienną ciągu. Zapytanie definiuje dane do trenowania modelu.

   sampleDataQuery <- "SELECT TOP 1000 tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"
   

   W tym miejscu użyliśmy klauzuli TOP, aby przyspieszyć działanie, ale rzeczywiste wiersze zwracane przez zapytanie mogą się różnić w zależności od kolejności sortowania. W związku z tym wyniki podsumowania mogą być również inne niż te wymienione poniżej. Możesz usunąć klauzulę TOP.

2. Przekaż definicję zapytania jako argument do funkcji RxSqlServerData .

   inDataSource <- RxSqlServerData(
     sqlQuery = sampleDataQuery,
     connectionString = connStr,
     colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
     dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
     rowsPerRead=500
     )
   

   • Argument colClasses określa typy kolumn do użycia podczas przenoszenia danych między programem SQL Server i językiem R. Jest to ważne, ponieważ program SQL Server używa różnych typów danych niż R i więcej typów danych. Aby uzyskać więcej informacji, zobacz Biblioteki języka R i typy danych.

   • Argument rowsPerRead jest ważny dla zarządzania użyciem pamięci i wydajnymi obliczeniami. Większość rozszerzonych funkcji analitycznych w usługach języka R (In-Database) przetwarza dane we fragmentach i gromadzi wyniki pośrednie, zwracając końcowe obliczenia po odczytaniu wszystkich danych. Dodając parametr rowsPerRead , można kontrolować, ile wierszy danych jest odczytywanych do każdego fragmentu do przetwarzania. Jeśli wartość tego parametru jest zbyt duża, dostęp do danych może być powolny, ponieważ nie masz wystarczającej ilości pamięci, aby efektywnie przetwarzać tak duży fragment danych. W niektórych systemach ustawienie rowsPerRead na zbyt małą wartość może również skutkować niższą wydajnością.

3. Na tym etapie utworzono obiekt inDataSource , ale nie zawiera żadnych danych. Dane nie są pobierane z zapytania SQL do środowiska lokalnego, dopóki nie uruchomisz funkcji, takiej jak rxImport lub rxSummary.

   Jednak teraz, po zdefiniowaniu obiektów danych, możesz użyć go jako argumentu do innych funkcji.

Używanie danych programu SQL Server w podsumowaniach języka R

W tej sekcji wypróbujesz kilka funkcji dostępnych w usługach języka R (In-Database), które obsługują zdalne konteksty obliczeniowe. Stosując funkcje języka R do źródła danych, można eksplorować, podsumowywać i wykresować dane programu SQL Server.

1. Wywołaj funkcję rxGetVarInfo , aby uzyskać listę zmiennych w źródle danych i ich typach danych.

   rxGetVarInfo jest przydatną funkcją; Można je wywołać na dowolnej ramce danych lub w zestawie danych w obiekcie danych zdalnych, aby uzyskać informacje, takie jak maksymalne i minimalne wartości, typ danych i liczba poziomów w kolumnach współczynnika.

   Rozważ uruchomienie tej funkcji po każdym rodzaju danych wejściowych, transformacji funkcji lub inżynierii cech. Dzięki temu można upewnić się, że wszystkie funkcje, które mają być używane w modelu, są oczekiwanego typu danych i uniknąć błędów.

   rxGetVarInfo(data = inDataSource)
   

   Wyniki

   Var 1: tipped, Type: integer
   Var 2: fare_amount, Type: numeric
   Var 3: passenger_count, Type: integer
   Var 4: trip_time_in_secs, Type: numeric, Storage: int64
   Var 5: trip_distance, Type: numeric
   Var 6: pickup_datetime, Type: character
   Var 7: dropoff_datetime, Type: character
   Var 8: pickup_longitude, Type: numeric
   Var 9: pickup_latitude, Type: numeric
   Var 10: dropoff_longitude, Type: numeric
   

2. Teraz wywołaj funkcję RevoScaleR rxSummary , aby uzyskać bardziej szczegółowe statystyki dotyczące poszczególnych zmiennych.

   Funkcja rxSummary jest oparta na funkcji języka R summary , ale ma pewne dodatkowe funkcje i zalety. Funkcja rxSummary działa w wielu kontekstach obliczeniowych i obsługuje fragmentowanie. Można również użyć rxSummary do przekształcania wartości lub podsumowywania na podstawie poziomów czynników.

   W tym przykładzie podsumujesz kwotę taryfy na podstawie liczby pasażerów.

   start.time <- proc.time()
   rxSummary(~fare_amount:F(passenger_count,1,6), data = inDataSource)
   used.time <- proc.time() - start.time
   print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds,
     Elapsed Time=", round(used.time[3],2),
     " seconds to summarize the inDataSource.", sep=""))
   

   • Pierwszy argument rxSummary określa formułę lub termin do podsumowania. W tym miejscu funkcja służy do konwertowania F() wartości w passenger_count na czynniki przed podsumowaniem. Należy również określić minimalną wartość (1) i wartość maksymalną (6) dla zmiennej passenger_count współczynnika.
   • Jeśli nie określisz statystyk do danych wyjściowych, domyślnie rxSummary wyprowadza dane średnie, StDev, Min, Max i liczbę prawidłowych i brakujących obserwacji.
   • Ten przykład zawiera również kod służący do śledzenia czasu uruchomienia i zakończenia funkcji, dzięki czemu można porównać wydajność.

   Wyniki

   Jeśli funkcja rxSummary zostanie pomyślnie uruchomiona, powinny zostać wyświetlone wyniki podobne do tych, a następnie lista statystyk według kategorii.

   rxSummary(formula = ~fare_amount:F(passenger_count, 1,6), data = inDataSource)
   Data: inDataSource (RxSqlServerData Data Source)
   Number of valid observations: 1000
   

Dodatkowe ćwiczenie dotyczące dużych zbiorów danych

Spróbuj zdefiniować nowy ciąg zapytania ze wszystkimi wierszami. Zalecamy skonfigurowanie nowego obiektu źródła danych dla tego eksperymentu. Możesz również spróbować zmienić parametr rowsToRead , aby zobaczyć, jak wpływa na przepływność.

bigDataQuery  <- "SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"

bigDataSource <- RxSqlServerData(
      sqlQuery = bigDataQuery,
      connectionString = connStr,
      colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
      dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
      rowsPerRead=500
      )

start.time <- proc.time()
rxSummary(~fare_amount:F(passenger_count,1,6), data = bigDataSource)
used.time <- proc.time() - start.time
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds,
  Elapsed Time=", round(used.time[3],2),
  " seconds to summarize the inDataSource.", sep=""))

Wskazówka

Gdy jest to uruchomione, możesz użyć narzędzia, takiego jak Eksplorator procesów lub Sql Profiler, aby zobaczyć, jak nawiązano połączenie, a kod języka R jest uruchamiany przy użyciu usług programu SQL Server.

Dalsze kroki

Tworzenie grafów i kreślenia przy użyciu języka R