Geekly articles weekly

如何在Microsoft SQL Server中使用R从Google Analytics（分析）获取数据

在本文中，我想详细展示如何在Microsoft SQL Server中使用R来实现从Google Analytics（分析）（通常是从任何API）进行数据检索。


致谢：


由于我从未从事过营销工作，因此需要专家的帮助。 测试室和对Google Analytics（分析）的访问权限由Alexei Seleznev组织，并提供了实用建议。
他是市场营销专业分析师。 在此感谢Aleksey的电报频道 ，感谢他的帮助。


任务-我们有一个MS SQL服务器，我们想通过API在DWH中接收数据


要连接到Google Analytics（分析），我们将使用googleAnalyticsR软件包。


例如，由于其受欢迎而选择了该包装。 您可以使用另一个包，例如： RGoogleAnalytic 。
解决问题的方法是相同的。


在MS SQL Server上安装R


这是通过用于安装MS SQL组件的标准界面完成的。





  1. 这是SQL Server将直接使用的R（在SQL查询中称为）。
  2. R的客户端副本可以从RStudio使用它，而不必担心会破坏数据库服务器上的某些内容。

我们同意该许可证，并注意不是将安装普通R ，而是要安装Microsoft R Open



简而言之，这是什么：
微软将R Open与其包装一起使之高贵，并免费分发。
因此，此版本的R的软件包不是在CRAN中而是在MRAN中可供下载。


但这还不是全部。 实际上，在安装MS SQL时，我们得到的不是纯MRAN，而是更多的东西-Microsoft ML Server 。


对我们来说，这意味着R库集-RevoScaleR中将包含其他软件包。


RevoScaleR旨在处理大数据并在重要的数据集上建立机器学习模型。


请记住此信息，因为与R.软件包的不同版本有关的问题的可能性很高。


安装组件后，我们将获得用于与Microsoft的R进行交互的默认界面。



这个控制台并不是您可以使用的最方便的方式，因此请立即下载并安装RStudio的免费版本。


我们配置SQL Server与R一起使用


在SSMS中，我们执行以下脚本：


我们允许在SQL Server上执行脚本


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

重新启动服务器SQL


确保脚本R脚本已执行


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

查找SQL Server使用的R包的位置


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


就我而言，R MS SQL包的路径为：
C：/程序文件/ Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES /库


我们启动RStudio。


可能会在计算机上安装多个版本的R，并且您需要确保我们使用的是SQL Server版本。




重新启动RStudio后将应用设置。


安装googleAnalyticsR软件包


在RStudio团队中


 library()

找出R的客户端版本的软件包库的路径（RStudio可以与之一起使用）



就我而言，这种方式：
C：/程序文件/ Microsoft SQL Server / 140 / R_SERVER /库


通过RStudio安装googleAnalyticsR软件包




这里有一个明显的细微差别：
您不能只写一些东西到MS SQL系统文件夹中。 软件包将作为ZIP存档保存在临时目录中。



在资源管理器中，转到临时文件夹并解压缩所有软件包。



必须将解压缩的软件包复制到R Services库目录（MS SQL Server与之配合使用）。


在我的示例中，这是一个文件夹
C：/程序文件/ Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES /库


另外，必须将解压缩的软件包复制到R的客户端版本（RStudio与之一起使用）


在我的示例中，这是一个文件夹
C：/程序文件/ Microsoft SQL Server / 140 / R_SERVER /库


（我们从以前执行的脚本中学到了这些路径）


在复制到R Services文件夹之前，最好先保存库文件夹的副本，如实践所示，存在不同的情况，并且最好能够返回到现有的程序包。


复制时，请更换所有可用的包装。


要巩固所学技能，请重复练习。
只是现在我们不安装软件包，而是更新所有可用的软件包。
（不必连接到GA，但最好拥有所有软件包的最新版本）


在RStudio中，我们检查是否有新软件包



软件包将下载到一个临时文件夹。
我们对它们执行与安装新软件包时相同的操作。


检查MS SQL Internet访问


 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

由于默认情况下SQL Server无法访问Internet，因此您的先前脚本很可能会导致以下错误。



我们通过SQL为R脚本打开Internet访问。


SQL 2017



SQL2019



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

获取Google Analytics（分析）令牌


在RStudio中，我们执行以下代码：
同时，将在浏览器中打开Goog​​le服务中的身份验证窗口，您需要登录并授予访问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)

在SSMS中，确保已接收到来自Google的令牌并将其记录在数据库中


 Select * from [GA].[token]

通过RStudio检查与GA的连接


 #    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

如果一切顺利，请将R脚本添加到SQL并执行查询。


 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

请注意，该脚本使用登录名和密码-这不是很好。
因此，我们将连接字符串更改为Windows身份验证。


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

更改身份验证方法后，有必要将对数据库的访问添加到调用方R。



（当然，最好使用用户组，作为我简化解决方案的演示的一部分）


我们把SQL查询作为一个过程


 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

检查程序的操作


 --    exec Ga.Get_session --      exec Ga.Get_session @Date_start ='2019-08-01', @Date_End ='2019-09-01'

R脚本并不复杂；可以始终将其复制到R Studio。 修改并保存在SQL过程中。
例如，我只更改了Dimensions参数，并且已经可以按日期加载LandingPage。


 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

正在检查


 exec [GA].[Get_landingPage_session]

原则上，一切就绪。


我想指出的是，通过SQL使用R，您可以从任何API获取数据
例如：获取汇率


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

或从您获得的第一个API获取数据，澳大利亚的一些农场...


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

总计：


  • 连接密码未存储在任何地方
  • 权限通过活动目录帐户集中分配
  • 没有其他配置文件
  • 没有带小提琴的python文件包含数据库密码
  • 所有代码都在过程中，并在备份数据库时保存

包含所有代码的备份数据库MS SQL 2017可在此处获得
（要播放，您需要安装软件包，授予权限，指定服务器的名称）

Source: https://habr.com/ru/post/zh-CN466339/

More articles:


All Articles