Geekly articles weekly

Wie in Microsoft SQL Server Daten von Google Analytics mit R abrufen

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.


Danksagung:


Da ich noch nie Vermarkter war, brauchte ich die Hilfe eines Spezialisten. Der Testraum und der Zugriff auf Google Analytics (GA) wurden von Alexei Seleznev organisiert und gaben auch praktische Ratschläge.
Er ist ein professioneller Analyst im Marketing. Und als Dank für die Hilfe wird hier Alekseys Telegrammkanal erwähnt, auf dem er seine Aktivitäten durchführt.


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


Um eine Verbindung zu Google Analytics (GA) herzustellen, verwenden wir das Paket googleAnalyticsR .


Dieses Paket wird zum Beispiel aufgrund seiner Beliebtheit ausgewählt. Sie können ein anderes Paket verwenden, zum Beispiel: RGoogleAnalytic .
Die Lösungsansätze für das Problem sind dieselben.


Installieren Sie R auf MS SQL Server


Dies erfolgt über die Standardschnittstelle zum Installieren von MS SQL-Komponenten.





  1. Dies ist das R, mit dem SQL Server direkt arbeitet (in SQL-Abfragen aufgerufen).
  2. Eine Client-Kopie von R kann von RStudio aus damit arbeiten, ohne befürchten zu müssen, dass etwas auf dem Datenbankserver beschädigt wird.

Wir stimmen der Lizenz zu und achten darauf, dass nicht gewöhnliches R installiert wird, sondern Microsoft R Open



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


Das ist aber noch nicht alles. Tatsächlich erhalten wir bei der Installation von MS SQL kein reines MRAN, sondern etwas mehr - Microsoft ML Server .


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


RevoScaleR wurde entwickelt, um Big Data zu verarbeiten und Modelle für maschinelles Lernen auf wichtigen Datensätzen zu erstellen.


Diese Informationen sollten berücksichtigt werden, da mit hoher Wahrscheinlichkeit Fragen zu verschiedenen Versionen von R.-Paketen gestellt werden.


Nach der Installation der Komponenten erhalten wir die Standardschnittstelle für die Interaktion mit R von Microsoft.



Diese Konsole ist nicht die bequemste, die Sie verwenden können. Laden Sie daher sofort die kostenlose Version von RStudio herunter und installieren Sie sie.


Wir konfigurieren SQL Server für die Arbeit mit R.


In SSMS führen wir die folgenden Skripte aus:


Wir erlauben die Ausführung von Skripten auf dem SQL Server


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

Starten Sie Server SQL neu


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


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

Suchen Sie den Speicherort der R-Pakete, die vom 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


Wir starten RStudio.


Es ist möglich, dass mehrere Versionen von R auf dem Computer installiert werden und Sie müssen sicherstellen, dass wir mit einer Version von SQL Server arbeiten.




Die Einstellungen werden nach dem Neustart von RStudio übernommen.


Installieren Sie das googleAnalyticsR-Paket


Im RStudio-Team


 library()

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



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


Installieren Sie das googleAnalyticsR-Paket über RStudio




Hier gibt es eine nicht offensichtliche Nuance:
Sie können nicht einfach etwas in die MS SQL-Systemordner schreiben. Pakete werden in einem temporären Verzeichnis als ZIP-Archiv gespeichert.



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



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


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


Außerdem müssen entpackte Pakete in die Client-Version von R kopiert werden (mit der RStudio arbeitet).


In meinem Beispiel ist dies ein 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. Wie die Praxis zeigt, gibt es verschiedene Fälle, und es ist besser, zu vorhandenen Paketen zurückkehren zu können.


Ersetzen Sie beim Kopieren alle verfügbaren Pakete.


Wiederholen Sie die Übung, um die erworbene Fähigkeit zu festigen.
Erst jetzt installieren wir keine Pakete, sondern aktualisieren alle verfügbaren.
(Um eine Verbindung zu GA herzustellen, ist dies nicht erforderlich, es ist jedoch besser, die neuesten Versionen aller Pakete zu haben.)


In RStudio suchen wir nach neuen Paketen



Pakete werden in einen temporären Ordner heruntergeladen.
Wir führen mit ihnen 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 Ihr vorheriges Skript höchstwahrscheinlich den folgenden Fehler.



Wir öffnen den Internetzugang für R-Skripte aus SQL.


SQL 2017



SQL2019



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


In RStudio führen wir den folgenden Code aus:
Gleichzeitig wird das Authentifizierungsfenster in den Google-Diensten im 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 geht, fügen Sie das R-Skript zu SQL 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' #    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$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

Bitte beachten Sie, dass das Skript Login und Passwort verwendet - dies ist nicht sehr gut.
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 muss dem aufrufenden R der Zugriff auf die Datenbank hinzugefügt werden.



(Natürlich ist es besser, Benutzergruppen zu verwenden, da ich im Rahmen der Demo die Lösung vereinfacht habe.)


Wir erkennen die SQL-Abfrage als Prozedur


 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 die Funktionsweise des Verfahrens


 --    exec Ga.Get_session --      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 die LandingPage bereits 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

prüfen


 exec [GA].[Get_landingPage_session]

Im Prinzip ist alles fertig.


Ich möchte darauf hinweisen, dass Sie mit R über SQL Daten von jeder API abrufen können
Zum Beispiel: den Wechselkurs 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 API erhalten, die Sie 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

Gesamt:


  • Verbindungskennwörter werden nirgendwo gespeichert
  • Rechte werden zentral über Active Directory-Konten verteilt
  • Keine zusätzlichen Konfigurationsdateien
  • Es gibt keine Python-Dateien mit Violinen, die Kennwörter für die Datenbank enthalten
  • Der gesamte Code befindet sich in Prozeduren und wird beim Sichern der Datenbank gespeichert

Die Sicherungsdatenbank MS SQL 2017 mit dem gesamten Code finden Sie hier
(Für die Wiedergabe müssen Sie Pakete installieren, Rechte vergeben und den Namen Ihres Servers angeben.)

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

More articles:


All Articles