В этом материале я хочу подробно показать, как можно при помощи R в Microsoft SQL Server реализовать получение данных из Google Analytics (и вообще из любого API).
Благодарности:
Поскольку я ни разу не маркетолог мне требовалась помощь специалиста. Тестовый кабинет и доступ Google Analytics (GA) организовал Алексей Селезнёв , а также давал дельные консультации.
Он профессионально занимается аналитикой в маркетинге. И в качестве благодарности за помощь упоминается здесь телеграмм канал Алексея, где он ведет свою активность.
Задача — у нас есть сервер MS SQL и мы хотим получать данные в DWH по API
Для подключения к Google Analytics (GA) будем использовать пакет googleAnalyticsR.
Данный пакет выбран, для примера в силу своей популярности. Вы можете использовать другой пакет, например: RGoogleAnalytic.
Подходы к решению задачи будут одинаковыми.
Устанавливаем R на сервере MS SQL
делается это через стандартный интерфейс установки компонентов MS SQL.
- Это R с которым будет работать непосредственно SQL Server (вызываться в SQL запросах).
- Клиентская копия R с ним можно будет работать из RStudio не боясь сломать что-то на сервере базы данных.
Соглашаемся с лицензией и обращаем внимание что будет установлен не обыкновенный R, а Microsoft R Open
В двух словах что это такое:
Microsoft берет R Open его облагораживает своими пакетами и так же бесплатно распространяет.
Соответственно пакеты этой версии R доступны для скачивания не в CRAN а в MRAN.
Но и это еще не всё. На самом деле при установке MS SQL мы получаем не чистый MRAN, а нечто большее — Microsoft ML Server.
Для нас это означает, что в комплекте библиотек R будут еще дополнительные пакеты – RevoScaleR.
RevoScaleR предназначен, для обработки больших данных и построение моделей машинного обучения на значительных датасетах.
Эту информацию надо иметь в виду потому, что велика вероятность вопросов связанных с разными версиями пакетов R.
После установки компонентов мы получаем дефолтный интерфейс взаимодействия с R от Microsoft.
Эта консоль не самое удобное что можно использовать, поэтому сразу скачиваем и устанавливаем бесплатную версию RStudio.
Настраиваем SQL server на работу с R
В SSMS выполняем следующие скрипты:
Разрешаем на SQL сервере выполнять скрипты
sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE; Рестартуем Server SQL
Убеждаемся, что скрипты R скрипты выполняются
EXECUTE sp_execute_external_script
@language =N'R',
@script=N'print(version)';Находим расположение R пакетов, которые используются 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));
В моем случае путь до R пакетов MS SQL:
C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library
Запускаем RStudio.
Не исключено, что на компьютере будет установлено несколько версий R и надо убедиться, что мы работаем с версией SQL сервера.
Настройки применятся после рестарта RStudio.
Устанавливаем пакет googleAnalyticsR
В RStudio командой
library()узнаем путь до библиотеки пакетов клиентской версии R (с которой работает RStudio)
В моем случае этот путь:
C:/Program Files/Microsoft SQL Server/140/R_SERVER/library
Через RStudio устанавливаем пакет googleAnalyticsR
Вот тут есть неочевидный нюанс:
Нельзя взять и просто так что-то записать в системные папки MS SQL. Пакеты будут сохранены во временной директории в виде ZIP архивов.
В проводнике заходим во временную папку и разархивируем все пакеты.
Разархивированные пакеты надо скопировать в директорию библиотек R Services (с которыми работает сервер MS SQL).
В моем примере это папка
C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library
Так же разархивированные пакеты надо скопировать в клиентскую версию R (c которой работает RStudio)
В моем примере это папка
C:/Program Files/Microsoft SQL Server/140/R_SERVER/library
(эти пути мы узнали из ранее выполненных скриптов)
Перед копированием в папку R Services лучше сохранить копию папки library, как показывает практика, случаи бывают разные и лучше иметь возможность вернуться к имеющимся пакетам.
При копировании заменяем все имеющиеся пакеты.
Что бы закрепить полученный навык повторяем упражнение.
Только теперь не устанавливаем пакеты, а обновляем все имеющиеся.
(для подключения к GA это не обязательно, но лучше иметь свежие версии всех пакетов)
В RStudio проверяемся на наличие новых пакетов
Пакеты будут загружены во временную папку.
Проделываем с ними такие же действия, как и при установке новых пакетов.
Проверяем доступ 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Поскольку SQL Server по умолчанию не имеет доступа в интернет, скорее всего у Вас предыдущий скрипт вызовет следующую ошибку.
Открываем доступ в интернет для R скриптов из SQL.
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 выполняем следующий код:
При этом в браузере откроется окно аутентификации в Google сервисах, надо будет выполнить вход и дать разрешение на доступ к 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]Проверяем подключение к GA через 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Если всё прошло удачно добавляем 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Обращаем внимание что в скрипте используется Логин и Пароль – это не очень хорошо
поэтому изменяем строку подключения на виндовс аутентификацию.
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]В принципе всё готово.
Хотелось бы отметить, что про помощи R через SQL можно получать данные из любого 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 Итого:
- пароли подключения нигде не хранятся
- права раздаются централизовано через учетные записи active directory
- дополнительных файлов настройки нету
- нету никаких питоновских файликов со скрипками содержащими пароли к базе данных
- весь код находится в процедурах и сохраняется при бэкапировании базы данных
Бэкап базы MS SQL 2017 со всем кодом доступен тут
(для воспроизведения необходимо установить пакеты, раздать права, указать название своего сервера)
Автор: FoxisII
