Geekly articles weekly

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

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


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


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


由于其受欢迎程度，选择了此软件包作为示例。 您可以使用另一个包，例如： RGoogleAnalytic 。
解决问题的方法是相同的。


在MS SQL Server上安装R


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





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

接受许可协议，并注意不是将安装普通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



SQL 2019



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

检查程序操作


 -- 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脚本并不复杂，可以始终将其复制到R Studio。 修改并保存在SQL过程中。
例如，我仅更改了维度参数，现在可以按日期加载“着陆页”。


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

More articles:


All Articles