Geekly articles weekly

Comment dans Microsoft SQL Server pour obtenir des données de Google Analytics en utilisant R

Dans cet article, je veux montrer en détail comment vous pouvez utiliser R dans Microsoft SQL Server pour implémenter la récupération de données à partir de Google Analytics (et généralement à partir de n'importe quelle API).


Remerciements:


Comme je n'ai jamais été marketeur, j'avais besoin de l'aide d'un spécialiste. La salle de test et l'accès à Google Analytics (GA) ont été organisés par Alexei Seleznev et ont également donné des conseils pratiques.
Il est analyste professionnel en marketing. Et en guise de gratitude pour l'aide, la chaîne de télégramme d'Aleksey est mentionnée ici, où il mène son activité.


La tâche - nous avons un serveur MS SQL et nous voulons recevoir des données en DWH par API


Pour nous connecter à Google Analytics (GA), nous utiliserons le package googleAnalyticsR .


Ce package est sélectionné, par exemple, en raison de sa popularité. Vous pouvez utiliser un autre package, par exemple: RGoogleAnalytic .
Les approches pour résoudre le problème 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 du R avec lequel SQL Server travaillera directement (appelé dans les requêtes SQL).
  2. Une copie client de R peut fonctionner avec RStudio sans craindre de casser quelque chose sur le serveur de base de données.

Nous sommes d'accord avec la licence et faisons attention à ce que R ordinaire ne soit pas installé, mais Microsoft R Open



En un mot, qu'est-ce que c'est:
Microsoft prend R Open ennoblit avec ses packages et le distribue également gratuitement.
Par conséquent, les packages de cette version de R peuvent être téléchargés non pas dans CRAN mais dans MRAN .


Mais ce n'est pas tout. En fait, lors de l'installation de MS SQL, nous n'obtenons pas un pur MRAN, mais quelque chose de plus - Microsoft ML Server .


Pour nous, cela signifie 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 des ensembles de données importants.


Ces informations doivent être gardées à l'esprit car il existe une forte probabilité de questions liées aux différentes versions des packages R.


Après avoir installé les composants, nous obtenons l'interface par défaut pour interagir avec R de Microsoft.



Cette console n'est pas la plus pratique que vous puissiez utiliser, alors téléchargez et installez immédiatement la version gratuite de RStudio .


Nous configurons SQL Server pour fonctionner avec R


Dans SSMS, nous exécutons les scripts suivants:


Nous permettons d'exécuter des scripts sur le serveur SQL


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

Redémarrez le serveur SQL


Assurez-vous que les scripts R scripts 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 le serveur SQL


 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


Nous démarrons RStudio.


Il est possible que plusieurs versions de R soient installées sur l'ordinateur et vous devez vous assurer que nous travaillons avec une version de SQL Server.




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


Installez le package googleAnalyticsR


Dans l'équipe RStudio


 library()

connaître le chemin d'accès à la bibliothèque de packages de la version client de R (avec laquelle RStudio fonctionne)



Dans mon cas, de cette façon:
C: / Program Files / Microsoft SQL Server / 140 / R_SERVER / bibliothèque


Installez le package googleAnalyticsR via RStudio




Ici, il y a une nuance non évidente:
Vous ne pouvez pas simplement écrire quelque chose dans les dossiers système MS SQL. Les packages seront enregistrés dans un répertoire temporaire sous forme d'archives ZIP.



Dans l'Explorateur, accédez au dossier temporaire et décompressez tous les packages.



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 un dossier
C: / Program Files / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library


De plus, les packages décompressés doivent être copiés dans la version client de R (avec laquelle RStudio fonctionne)


Dans mon exemple, c'est un 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, comme le montre la pratique, il existe différents cas et il est préférable de pouvoir revenir aux packages existants.


Lors de la copie, remplacez tous les packages disponibles.


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


Dans RStudio, nous recherchons de nouveaux packages



Les packages seront téléchargés dans un dossier temporaire.
Nous effectuons avec eux 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, votre script précédent provoquera probablement l'erreur suivante.



Nous ouvrons l'accès à Internet pour les scripts R à partir de SQL.


SQL 2017



SQL2019



Dans 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 le jeton Google Analytics


Dans RStudio, nous exécutons le code suivant:
Dans le même temps, la fenêtre d'authentification des services Google s'ouvrira dans le navigateur, vous devrez vous connecter et autoriser l'accès à 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 se passe bien, 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' #    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

Veuillez noter que le script utilise un identifiant et un mot de passe - ce n'est pas très bon.
Par conséquent, nous changeons la chaîne de connexion en authentification Windows.


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

Après avoir changé la méthode d'authentification, il sera nécessaire d'ajouter l'accès à la base de données au R. appelant.



(Bien sûr, il est préférable d'utiliser des groupes d'utilisateurs, dans le cadre de la démo, j'ai simplifié la solution)


On fait 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érifier le fonctionnement de la procédure


 --    exec Ga.Get_session --      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 déjà charger la landingPage 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érifient


 exec [GA].[Get_landingPage_session]

En principe, tout est prêt.


Je voudrais noter qu'en utilisant R via SQL, vous pouvez obtenir des données de n'importe quelle API
Par exemple: obtenir le 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 que vous obtenez, 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

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
  • il n'y a pas de fichiers python avec des violons contenant des mots de passe pour la base de données
  • tout le code est dans les procédures et est enregistré lorsque la base de données est sauvegardée

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

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

More articles:


All Articles