Geekly articles weekly

So empfangen Sie Daten von Google Analytics mit R in Microsoft SQL Server

In diesem Artikel möchte ich detailliert zeigen, wie Sie R in Microsoft SQL Server verwenden können, um Daten von Google Analytics (und im Allgemeinen von einer beliebigen API) abzurufen.


Die Aufgabe - wir haben MS SQL Server und möchten Daten in DWH per API empfangen


Wir werden das googleAnalyticsR- Paket verwenden, um eine Verbindung zu Google Analytics (GA) herzustellen .


Dieses Paket wird aufgrund seiner Beliebtheit als Beispiel gewählt. Sie können ein anderes Paket verwenden, zum Beispiel: RGoogleAnalytic .
Die Ansätze zur Problemlösung werden gleich sein.


Installieren Sie R auf MS SQL Server


Dies erfolgt über die Standardschnittstelle zur Installation von MS SQL-Komponenten.





  1. Dies ist R, mit dem SQL Server direkt interagiert (in SQL-Abfragen aufgerufen).
  2. Sie können mit der R-Client-Kopie von R Studio arbeiten, ohne befürchten zu müssen, dass etwas auf dem Datenbankserver beschädigt wird.

Akzeptieren Sie die Lizenzvereinbarung und achten Sie darauf, dass nicht gewöhnliches R installiert wird, sondern Microsoft R Open



Kurz gesagt, was es ist:
Microsoft nimmt R Open, verbessert es mit seinen Paketen und verteilt es kostenlos.
Dementsprechend stehen Pakete dieser R-Version nicht in CRAN , sondern in MRAN zum Download zur Verfügung.


Es kommt noch mehr. Tatsächlich erhalten wir bei der Installation von MS SQL kein sauberes MRAN, sondern etwas mehr - Microsoft ML Server .


Dies bedeutet für uns, dass der Satz der R-Bibliotheken - RevoScaleR - zusätzliche Pakete enthält .


RevoScaleR wurde für die Verarbeitung von Big Data und die Erstellung von Modellen für maschinelles Lernen auf großen Datenmengen entwickelt.


Diese Informationen sollten berücksichtigt werden, da eine hohe Wahrscheinlichkeit für Fragen zu verschiedenen R-Versionen besteht.


Nach der Installation der Komponenten erhalten wir die Microsoft R-Interaktion.



Diese Konsole ist nicht sehr bequem zu bedienen, laden Sie also sofort die kostenlose Version RStudio herunter und installieren Sie sie.


Konfigurieren Sie den SQL Server für die Interaktion mit R.


Führen Sie die folgenden Skripts in SSMS aus:


Ermöglichen, dass Skripte auf SQL Server ausgeführt werden


sp_configure 'external scripts enabled', 1; RECONFIGURE WITH OVERRIDE;

Server SQL Neustart


Stellen Sie sicher, dass R-Skripte ausgeführt werden


 EXECUTE sp_execute_external_script @language =N'R', @script=N'print(version)';

Suchen Sie den Speicherort von R-Paketen, die von SQL Server verwendet werden


 declare @Rscript nvarchar(max) set @Rscript = N' InstaledLibrary <- library() InstaledLibrary <- as.data.frame(InstaledLibrary$results ) OutputDataSet <- InstaledLibrary ' EXECUTE sp_execute_external_script @language = N'R' , @script = @Rscript WITH RESULT SETS (([Package] varchar(255) NOT NULL, [LibPath] varchar(255) NOT NULL, [Title] varchar(255) NOT NULL));


In meinem Fall der Pfad zu R MS SQL-Paketen:
C: / Programme / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library


Führen Sie RStudio aus.


Es besteht eine gute Chance, dass mehrere R-Versionen auf dem Computer installiert sind. Stellen Sie daher sicher, dass wir mit der Version von SQL Server arbeiten.




Die Einstellungen werden nach dem Neustart von RStudio übernommen.


Installieren Sie das googleAnalyticsR-Paket


Zu RStudio mit dem Befehl


 library()

Finden Sie den Pfad zur Paketbibliothek der R-Client-Version heraus (mit der RStudio arbeitet).


In meinem Fall dieser Pfad:
C: / Programme / Microsoft SQL Server / 140 / R_SERVER / library


Installieren Sie das googleAnalyticsR-Paket über RStudio




Hier ist eine schattierte Nuance:
Sie können den MS SQL-Systemordnern nicht einfach alles hinzufügen, was Sie möchten. Pakete werden in einem temporären Verzeichnis als ZIP-Archiv gespeichert.



Gehen Sie zum temporären Ordner und entpacken Sie alle Pakete im Explorer.



Entpackte Pakete müssen in das R Services-Bibliotheksverzeichnis kopiert werden (mit dem MS SQL Server arbeitet).


In meinem Beispiel ist dies der Ordner
C: / Programme / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library

Entpackte Pakete müssen auch in die R-Client-Version kopiert werden (mit der RStudio arbeitet).


In meinem Beispiel ist dies der Ordner
C: / Programme / Microsoft SQL Server / 140 / R_SERVER / library

(Wir haben diese Pfade aus zuvor ausgeführten Skripten gelernt)


Vor dem Kopieren in den R Services-Ordner ist es besser, eine Kopie des Bibliotheksordners zu speichern. Die Erfahrung hat gezeigt, dass es unterschiedliche Situationen gibt und es besser ist, zu vorhandenen Paketen zurückzukehren.


Ersetzen Sie beim Kopieren alle vorhandenen Pakete.


Wiederholen Sie die Übung, um die Fertigkeit zu festigen.
Erst jetzt installieren wir keine Pakete, sondern aktualisieren alle vorhandenen.
(Dies ist für die Verbindung zu GA nicht erforderlich, es ist jedoch besser, die neuesten Versionen aller Pakete zu haben.)


Suchen Sie in RStudio nach neuen Paketen



Pakete werden in einen temporären Ordner heruntergeladen.
Führen Sie die gleichen Aktionen aus wie bei der Installation neuer Pakete.


Überprüfen des MS SQL-Internetzugangs


 declare @Rscript nvarchar(max) set @Rscript = N' library(httr) HEAD("https://www.yandex.ru", verbose()) ' EXECUTE sp_execute_external_script @language = N'R' , @script = @Rscript

Da SQL Server standardmäßig keinen Internetzugang hat, verursacht das vorherige Skript wahrscheinlich den folgenden Fehler.



Bereitstellung des Internetzugangs für R-Skripte aus SQL.


SQL 2017



SQL 2019



In ssms


 --      create database Demo go use Demo go --  ,       Google Analytics create schema GA go --        GA drop table if exists [GA].[token] create table [GA].[token]( [id] varchar(200) not null, [value] varbinary(max) constraint unique_id unique (id))

Holen Sie sich das Google Analytics-Token


Führen Sie den folgenden Code in RStudio aus:
Dadurch wird das Authentifizierungsfenster für Google-Dienste in Ihrem Browser geöffnet. Sie müssen sich anmelden und die Berechtigung zum Zugriff auf Google Analytics erteilen.


 #       Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #   ga_auth() PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- readBin(PathTokenFile, "raw", file.info(PathTokenFile)$size) #     conStr <- 'Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Uid=Sa;Pwd=SaSql123' ds <- RxOdbcData(table="ga.token", connectionString=conStr) #     rxWriteObject(ds, "ga_TokenFile", TokenFile)

Stellen Sie in SSMS sicher, dass das Token von Google empfangen und in der Datenbank aufgezeichnet wird


 Select * from [GA].[token]

Überprüfen Sie die Verbindung zu GA über RStudio


 #    RStudio Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- 'Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Uid=Sa;Pwd=SaSql123' #      ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="2019-01-01", end="2019-08-01", metrics = "sessions", dimensions = "date") OutputDataSet

Wenn alles gut gegangen ist, fügen Sie SQL ein R-Skript hinzu und führen Sie die Abfrage aus.


 drop table if exists #GA_session create table #GA_session ( [date] date, [sessions] int ) declare @Rscript nvarchar(max) set @Rscript = N' Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Uid=Sa;Pwd=SaSql123'' #      ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="2019-01-01", end="2019-08-01", metrics = "sessions", dimensions = "date") OutputDataSet$date <- as.character(OutputDataSet$date) ' -- print @Rscript insert into #GA_session ([date],[sessions]) EXECUTE sp_execute_external_script @language = N'R', @script = @Rscript Select * from #GA_session order by [date] asc

Achten Sie darauf, dass das Skript einen Benutzernamen und ein Passwort verwendet, was eine gute Sache ist.
Daher ändern wir die Verbindungszeichenfolge in Windows-Authentifizierung.


 conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Trusted_Connection=true'' #   <<<===     –

Nach dem Ändern der Authentifizierungsmethode müssen Sie dem Dienst, der R aufruft, Datenbankzugriffsrechte hinzufügen.



(Natürlich ist es besser, mit Benutzergruppen zu arbeiten. Ich habe die Lösung im Rahmen der Demonstration vereinfacht.)


Wir führen die SQL-Abfrage als Prozedur aus.


 Create procedure Ga.Get_session @Date_start date ='2019-01-01', @Date_End date ='2019-08-01' as drop table if exists #GA_session create table #GA_session ( [date] date, [sessions] int ) declare @Rscript nvarchar(max) set @Rscript =CONCAT( N' #    RStudio Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Trusted_Connection=true'' #   <<<===     -    ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="' , @Date_start ,N'", end="' , @Date_End ,N'", metrics = "sessions", dimensions = "date") OutputDataSet$date <- as.character(OutputDataSet$date) ' ) -- print @Rscript insert into #GA_session ([date],[sessions]) EXECUTE sp_execute_external_script @language = N'R', @script = @Rscript Select * from #GA_session order by [date] asc

Überprüfen Sie den Vorgang


 -- Default options exec Ga.Get_session -- Get sessions for a given period exec Ga.Get_session @Date_start ='2019-08-01', @Date_End ='2019-09-01'

R-Skript ist nicht kompliziert, es kann immer nach R Studio kopiert werden. Ändern und Speichern in SQL-Prozedur.
Zum Beispiel habe ich nur den Dimensionsparameter geändert und kann jetzt die Landing Page nach Datum laden.


 Create procedure [GA].[Get_landingPage_session] @Date_start date ='2019-01-01', @Date_End date ='2019-08-01' as drop table if exists #GA_session create table #GA_session ( [date] date, landingPagePath nvarchar(max), [sessions] int ) declare @Rscript nvarchar(max) set @Rscript =CONCAT( N' #    RStudio Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Trusted_Connection=true'' #   <<<===     -    ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="' , @Date_start ,N'", end="' , @Date_End ,N'", metrics = "sessions", dimensions = c("date" ,"landingPagePath")) OutputDataSet$date <- as.character(OutputDataSet$date) ' ) -- print @Rscript insert into #GA_session ([date],landingPagePath,[sessions]) EXECUTE sp_execute_external_script @language = N'R', @script = @Rscript Select * from #GA_session order by [date] asc

Überprüfung


 exec [GA].[Get_landingPage_session]

Im Grunde ist es das.


Ich möchte darauf hinweisen, dass Sie mit R über SQL Daten von jeder API abrufen können.
Zum Beispiel: Wechselkurse erhalten.


 -- https://www.cbr-xml-daily.ru Declare @script nvarchar(max) set @script = N' encoding = "utf-8" Sys.setlocale("LC_CTYPE", "russian") Sys.setenv(TZ="Europe/Berlin") library(httr) url <- "https://www.cbr-xml-daily.ru/daily_json.js" resp <- GET(url) library(jsonlite) Response <- fromJSON(content(resp, as = "text")) OutputDataSet <- data.frame(matrix(unlist(Response$Valute$USD), nrow=1, byrow=T),stringsAsFactors=FALSE) OutputDataSet <- rbind(OutputDataSet,data.frame(matrix(unlist(Response$Valute$EUR), nrow=1, byrow=T),stringsAsFactors=FALSE)) ' EXEC sp_execute_external_script @language = N'R' , @script = @script with result SETS UNDEFINED

oder Daten von der ersten verfügbaren API erhalten, einige Farmen in Australien ...


 -- https://dev.socrata.com/ Declare @script nvarchar(max) set @script = N' library(httr) url <- "https://data.ct.gov/resource/y6p2-px98.json?category=Fruit&item=Peaches" resp <- GET(url) library(jsonlite) Response <- fromJSON(content(resp, as = "text")) OutputDataSet <- as.data.frame(Response) OutputDataSet <- OutputDataSet [, c("category" , "item" , "farmer_id" , "zipcode" , "business" , "l" , "location_1_location", "location_1_city" , "location_1_state" , "farm_name", "phone1" , "website", "suite")] ' EXEC sp_execute_external_script @language = N'R' , @script = @script with result SETS UNDEFINED

Insgesamt:


● Verbindungskennwörter werden nirgendwo gespeichert
● Rechte werden zentral über Active Directory-Konten verteilt
● keine zusätzlichen Konfigurationsdateien
● Keine Python-Geigen mit Passwörtern für die Datenbank
● Der gesamte Code wird in den Prozeduren gespeichert und beim Sichern der Datenbank gespeichert


Eine MS SQL 2017-Datenbanksicherung mit vollständigem Code finden Sie hier
(Für die Wiedergabe müssen Sie Pakete installieren, die Rechte verteilen und den Namen Ihres Servers angeben.)

Source: https://habr.com/ru/post/de466589/

More articles:


All Articles