Geekly articles weekly

Como receber dados do Google Analytics usando R no Microsoft SQL Server

Neste artigo, quero mostrar em detalhes como você pode usar o R ​​no Microsoft SQL Server para obter dados do Google Analytics (e geralmente de qualquer API).


A tarefa - temos o servidor MS SQL e queremos receber dados em DWH pela API


Usaremos o pacote googleAnalyticsR para conectar-se ao Google Analytics (GA).


Este pacote é escolhido como exemplo devido à sua popularidade. Você pode usar outro pacote, por exemplo: RGoogleAnalytic .
As abordagens para a resolução de problemas serão as mesmas.


Instale o R no MS SQL Server


isso é feito através da interface padrão para instalar componentes do MS SQL.





  1. É com esse R que o SQL Server irá interagir diretamente (chamado nas consultas SQL).
  2. Você pode trabalhar com a cópia do cliente R do R Studio sem medo de quebrar algo no servidor de banco de dados.

Aceite o contrato de licença e preste atenção para que o R não comum seja instalado, mas o Microsoft R Open



Resumidamente, o que é:
A Microsoft pega o R Open, aprimora-o com seus pacotes e distribui gratuitamente.
Consequentemente, os pacotes desta versão R estão disponíveis para download não no CRAN , mas no MRAN .


Há mais por vir. De fato, ao instalar o MS SQL, não temos um MRAN limpo, mas algo mais - o Microsoft ML Server .


Isso significa para nós que haverá pacotes adicionais no conjunto de bibliotecas R - RevoScaleR .


O RevoScaleR foi desenvolvido para processar big data e criar modelos de aprendizado de máquina em grandes conjuntos de dados.


Essas informações devem ser lembradas, pois há uma alta probabilidade de perguntas relacionadas a diferentes versões R.


Depois de instalar os componentes, obtemos a interação do Microsoft R.



Esse console não é muito conveniente de usar, então baixe e instale imediatamente a versão gratuita RStudio .


Configure o SQL server para interagir com o R


Execute os seguintes scripts no SSMS:


Permitir que scripts sejam executados no servidor SQL


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

Reinicialização sql do servidor


Verifique se os scripts R são executados


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

Encontre o local dos pacotes R usados ​​pelo servidor 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));


No meu caso, o caminho para os pacotes R MS SQL:
C: / Arquivos de Programas / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library


Execute o RStudio.


Há uma boa chance de que existam várias versões R instaladas no computador, portanto, você precisa se certificar de que estamos trabalhando com a versão do SQL Server.




As configurações serão aplicadas após a reinicialização do RStudio.


Instale o pacote googleAnalyticsR


Para o RStudio usando o comando


 library()

Descubra o caminho para a biblioteca de pacotes da versão do cliente R (com a qual o RStudio funciona)


No meu caso, este caminho:
C: / Arquivos de Programas / Microsoft SQL Server / 140 / R_SERVER / library


Instale o pacote googleAnalyticsR via RStudio




Aqui estão algumas nuances sombreadas:
Você não pode simplesmente adicionar o que quiser nas pastas do sistema MS SQL. Os pacotes serão salvos em um diretório temporário como arquivos ZIP.



Vá para a pasta temporária e descompacte todos os pacotes no Explorer.



Pacotes descompactados devem ser copiados para o diretório de biblioteca dos Serviços R (com o qual o MS SQL Server trabalha).


No meu exemplo, esta é a pasta
C: / Arquivos de Programas / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library

Pacotes descompactados também devem ser copiados para a versão do cliente R (com a qual o RStudio trabalha)


No meu exemplo, esta é a pasta
C: / Arquivos de Programas / Microsoft SQL Server / 140 / R_SERVER / library

(aprendemos esses caminhos com scripts executados anteriormente)


Antes de copiar para a pasta R Services, é melhor salvar uma cópia da pasta da biblioteca. A experiência provou que existem situações diferentes e é melhor poder retornar aos pacotes existentes.


Ao copiar, substitua todos os pacotes existentes.


Para consolidar a habilidade, repita o exercício.
Somente agora não instalamos pacotes, mas atualizamos todos os existentes.
(isso não é necessário para se conectar ao GA, mas é melhor ter as versões mais recentes de todos os pacotes)


Verifique novos pacotes no RStudio



Os pacotes serão baixados para uma pasta temporária.
Execute as mesmas ações que ao instalar novos pacotes.


Verificando o acesso à 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

Como o SQL Server não tem acesso à Internet por padrão, é provável que o script anterior cause o seguinte erro.



Forneça acesso à Internet aos scripts R do SQL.


SQL 2017



SQL 2019



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

Obtenha o token do Google Analytics


Execute o seguinte código no RStudio:
Isso abrirá a janela de autenticação de serviços do Google no seu navegador. Você precisará fazer login e dar permissão para acessar o 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)

No SSMS, verifique se o token do Google é recebido e registrado no banco de dados


 Select * from [GA].[token]

Verifique a conexão com o 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

Se tudo der certo, adicione o script R ao SQL e execute a consulta.


 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

Preste atenção que o script usa um nome de usuário e senha, o que é uma coisa boa.
Portanto, alteramos a cadeia de conexão para autenticação do Windows.


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

Após alterar o método de autenticação, você precisará adicionar direitos de acesso ao banco de dados no serviço que chama R.



(Obviamente, é melhor trabalhar com grupos de usuários. Simplifiquei a solução como parte da demonstração)


Executamos a consulta SQL como um procedimento.


 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

Verifique a operação do procedimento


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

O script R não é complicado, sempre pode ser copiado para o R Studio. Modifique e salve no procedimento SQL.
Por exemplo, alterei apenas o parâmetro de dimensões e agora posso carregar a página de destino por datas.


 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

verificação


 exec [GA].[Get_landingPage_session]

Basicamente, é isso.


Gostaria de observar que, usando R via SQL, você pode obter dados de qualquer API.
Por exemplo: recebendo taxas de câmbio.


 -- 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 obtendo dados da primeira API disponível, alguns farms na Austrália ...


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

No total:


● senhas de conexão não são armazenadas em nenhum lugar
● direitos são distribuídos centralmente através de contas do Active Directory
● nenhum arquivo de configuração adicional
● não há violações em Python que contenham senhas no banco de dados
● todo o código é armazenado nos procedimentos e salvo quando o backup do banco de dados é feito


O backup do banco de dados MS SQL 2017 com código completo está disponível aqui
(para reprodução, você precisa instalar pacotes, distribuir os direitos, especificar o nome do seu servidor)

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

More articles:


All Articles