Geekly articles weekly

Comment recevoir des données de Google Analytics à l'aide de R dans Microsoft SQL Server

Dans cet article, je veux montrer en détail comment vous pouvez utiliser R dans Microsoft SQL Server pour obtenir des données de Google Analytics (et généralement de n'importe quelle API).


La tùche - nous avons MS SQL Server et nous voulons recevoir des données en DWH par API


Nous utiliserons le package googleAnalyticsR pour nous connecter Ă  Google Analytics (GA).


Ce package est choisi à titre d'exemple en raison de sa popularité. Vous pouvez utiliser un autre package, par exemple: RGoogleAnalytic .
Les approches de rĂ©solution des problĂšmes seront les mĂȘmes.


Installer R sur MS SQL Server


cela se fait via l'interface standard pour l'installation des composants MS SQL.





  1. Il s'agit de R avec lequel SQL Server interagira directement (appelĂ© dans les requĂȘtes SQL).
  2. Vous pouvez travailler avec la copie du client R de R Studio sans craindre de casser quelque chose sur le serveur de base de données.

Acceptez le contrat de licence et faites attention à ce que R ordinaire ne soit pas installé mais Microsoft R Open



En bref, ce que c'est:
Microsoft prend R Open, l'améliore avec ses packages et distribue gratuitement.
Par conséquent, les packages de cette version R sont disponibles au téléchargement non pas dans CRAN , mais dans MRAN .


Il y a plus Ă  venir. En fait, lors de l'installation de MS SQL, nous n'obtenons pas un MRAN propre, mais quelque chose de plus - Microsoft ML Server .


Cela signifie pour nous qu'il y aura des packages supplémentaires dans l'ensemble des bibliothÚques R - RevoScaleR .


RevoScaleR est conçu pour traiter les mégadonnées et créer des modÚles d'apprentissage automatique sur de grands ensembles de données.


Ces informations doivent ĂȘtre gardĂ©es Ă  l'esprit car il existe une forte probabilitĂ© de questions liĂ©es aux diffĂ©rentes versions R.


AprÚs avoir installé les composants, nous obtenons l'interaction Microsoft R.



Cette console n'est pas trÚs pratique à utiliser, alors téléchargez et installez immédiatement la version gratuite RStudio .


Configurer le serveur SQL pour interagir avec R


Exécutez les scripts suivants dans SSMS:


Autoriser l'exécution des scripts sur SQL Server


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

Redémarrage SQL du serveur


Assurez-vous que les scripts R sont exécutés


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

Rechercher l'emplacement des packages R utilisés par SQL Server


 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));


Dans mon cas, le chemin d'accĂšs aux packages R MS SQL:
C: / Program Files / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library


Exécutez RStudio.


Il y a de fortes chances que plusieurs versions R soient installées sur l'ordinateur, vous devez donc vous assurer que nous travaillons avec la version de SQL Server.




Les paramÚtres seront appliqués aprÚs le redémarrage de RStudio.


Installez le package googleAnalyticsR


Pour RStudio Ă  l'aide de la commande


 library()

DĂ©couvrez le chemin d'accĂšs Ă  la bibliothĂšque de packages de la version du client R (avec laquelle RStudio fonctionne)


Dans mon cas, ce chemin:
C: / Program Files / Microsoft SQL Server / 140 / R_SERVER / bibliothĂšque


Installez le package googleAnalyticsR via RStudio




Voici quelques nuances ombrées:
Vous ne pouvez pas simplement ajouter tout ce que vous voulez aux dossiers systÚme MS SQL. Les packages seront enregistrés dans un répertoire temporaire sous forme d'archives ZIP.



Accédez au dossier temporaire et décompressez tous les packages dans l'Explorateur.



Les packages dĂ©compressĂ©s doivent ĂȘtre copiĂ©s dans le rĂ©pertoire de la bibliothĂšque R Services (avec lequel le serveur MS SQL fonctionne).


Dans mon exemple, c'est le dossier
C: / Program Files / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library

Les packages dĂ©compressĂ©s doivent Ă©galement ĂȘtre copiĂ©s dans la version du client R (avec laquelle RStudio fonctionne)


Dans mon exemple, c'est le dossier
C: / Program Files / Microsoft SQL Server / 140 / R_SERVER / bibliothĂšque

(nous avons appris ces chemins à partir de scripts exécutés précédemment)


Avant de copier dans le dossier R Services, il est préférable d'enregistrer une copie du dossier de la bibliothÚque. L'expérience a prouvé qu'il existe des situations différentes et il vaut mieux pouvoir revenir aux packages existants.


Lors de la copie, remplacez tous les packages existants.


Pour consolider la compétence, répétez l'exercice.
Seulement maintenant, nous n'installons pas de packages, mais mettons Ă  jour tous les packages existants.
(ce n'est pas nécessaire pour se connecter à GA, mais il est préférable d'avoir les derniÚres versions de tous les packages)


Rechercher de nouveaux packages dans RStudio



Les packages seront téléchargés dans un dossier temporaire.
Effectuez les mĂȘmes actions que lors de l'installation de nouveaux packages.


VĂ©rification de l'accĂšs Internet MS SQL


 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

Étant donnĂ© que SQL Server n'a pas accĂšs Ă  Internet par dĂ©faut, il est probable que le script prĂ©cĂ©dent provoquera l'erreur suivante.



Fournissez un accĂšs Internet aux scripts R Ă  partir de SQL.


SQL 2017



SQL 2019



En 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))

Obtenez un jeton Google Analytics


Exécutez le code suivant dans RStudio:
Cela ouvrira la fenĂȘtre d'authentification des services Google dans votre navigateur. Vous devrez vous connecter et donner l'autorisation d'accĂ©der Ă  Google Analytics.


 #       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)

Dans SSMS, assurez-vous que le jeton de Google est reçu et enregistré dans la base de données


 Select * from [GA].[token]

VĂ©rifier la connexion Ă  GA via 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

Si tout s'est bien passĂ©, ajoutez le script R Ă  SQL et exĂ©cutez la requĂȘte.


 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

Faites attention que le script utilise un nom d'utilisateur et un mot de passe, ce qui est une bonne chose.
Par conséquent, nous modifions la chaßne de connexion en authentification Windows.


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

AprÚs avoir modifié la méthode d'authentification, vous devrez ajouter des droits d'accÚs à la base de données au service appelant R.



(Bien sûr, il est préférable de travailler avec des groupes d'utilisateurs. J'ai simplifié la solution dans le cadre de la démonstration)


Nous exĂ©cutons la requĂȘte SQL comme une procĂ©dure.


 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

Vérifiez le fonctionnement de la procédure


 -- 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'

Le script R n'est pas compliquĂ©, il peut toujours ĂȘtre copiĂ© dans R Studio. Modifiez et enregistrez dans la procĂ©dure SQL.
Par exemple, je n'ai modifié que le paramÚtre des dimensions et je peux maintenant charger la page de destination par date.


 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

vérification


 exec [GA].[Get_landingPage_session]

Fondamentalement, c'est tout.


Je voudrais noter qu'en utilisant R via SQL, vous pouvez obtenir des données à partir de n'importe quelle API.
Par exemple: recevoir des taux de change.


 -- 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

ou obtenir des données de la premiÚre API disponible, certaines fermes en Australie ...


 -- 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

Au total:


● les mots de passe de connexion ne sont stockĂ©s nulle part
● les droits sont distribuĂ©s de maniĂšre centralisĂ©e via les comptes Active Directory
● aucun fichier de configuration supplĂ©mentaire
● aucun violon Python contenant des mots de passe pour la base de donnĂ©es
● tout le code est stockĂ© dans les procĂ©dures et enregistrĂ© lors de la sauvegarde de la base de donnĂ©es


La sauvegarde de la base de données MS SQL 2017 avec le code complet est disponible ici
(pour la lecture, vous devez installer des packages, distribuer les droits, spécifier le nom de votre serveur)

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

More articles:


All Articles