[Из песочницы] Как в Microsoft SQL Server получать данные из Google Analytics при помощи R05.09.2019 16:49

В этом материале я хочу подробно показать, как можно при помощи R в Microsoft SQL Server реализовать получение данных из Google Analytics (и вообще из любого API).

Благодарности:

Поскольку я ни разу не маркетолог мне требовалась помощь специалиста. Тестовый кабинет и доступ Google Analytics (GA) организовал Алексей Селезнёв , а также давал дельные консультации.
Он профессионально занимается аналитикой в маркетинге. И в качестве благодарности за помощь упоминается здесь телеграмм канал Алексея, где он ведет свою активность.


Задача — у нас есть сервер MS SQL и мы хотим получать данные в DWH по API

Для подключения к Google Analytics (GA) будем использовать пакет googleAnalyticsR.

Данный пакет выбран, для примера в силу своей популярности. Вы можете использовать другой пакет, например: RGoogleAnalytic.
Подходы к решению задачи будут одинаковыми.


Устанавливаем R на сервере MS SQL

делается это через стандартный интерфейс установки компонентов MS SQL.

h1b6ls8d_lgdpu902rrfe3v4qtq.png

seldn_yx4cdfdqkus8-w54pk0k8.png

gf-gzwnjlnlgtuksv5-wmjxbb30.png


  1. Это R с которым будет работать непосредственно SQL Server (вызываться в SQL запросах).
  2. Клиентская копия R с ним можно будет работать из RStudio не боясь сломать что-то на сервере базы данных.

Соглашаемся с лицензией и обращаем внимание что будет установлен не обыкновенный R, а Microsoft R Open

ns0ogdh5m3bfm8mjefxtdailvue.png

В двух словах что это такое:
Microsoft берет R Open его облагораживает своими пакетами и так же бесплатно распространяет.
Соответственно пакеты этой версии R доступны для скачивания не в CRAN, а в MRAN.

Но и это еще не всё. На самом деле при установке MS SQL мы получаем не чистый MRAN, а нечто большее — Microsoft ML Server.

Для нас это означает, что в комплекте библиотек R будут еще дополнительные пакеты — RevoScaleR.

RevoScaleR предназначен, для обработки больших данных и построение моделей машинного обучения на значительных датасетах.

Эту информацию надо иметь в виду потому, что велика вероятность вопросов связанных с разными версиями пакетов R.

После установки компонентов мы получаем дефолтный интерфейс взаимодействия с R от Microsoft.

smylanlw8kctrkylh_inrj_wrns.png

Эта консоль не самое удобное что можно использовать, поэтому сразу скачиваем и устанавливаем бесплатную версию RStudio.


Настраиваем SQL server на работу с R

В SSMS выполняем следующие скрипты:

Разрешаем на SQL сервере выполнять скрипты

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

Рестартуем Server SQL
yuqftnhqd9w3mlxtpazqtnmla2o.png

Убеждаемся, что скрипты 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));

ol_yojqjdgdmv-nksxrfacxoxtm.png

В моем случае путь до R пакетов MS SQL:
C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library

Запускаем RStudio.

Не исключено, что на компьютере будет установлено несколько версий R и надо убедиться, что мы работаем с версией SQL сервера.

zcqyb_ovi7u699vrcuxcgasczqe.png

l2i9ohuqn5zkywadbiphg2e1doi.png

Настройки применятся после рестарта RStudio.


Устанавливаем пакет googleAnalyticsR

В RStudio командой

library()

узнаем путь до библиотеки пакетов клиентской версии R (с которой работает RStudio)

yqpldxxzpmjf1flqqd9-oztsrn4.png

В моем случае этот путь:
C:/Program Files/Microsoft SQL Server/140/R_SERVER/library

Через RStudio устанавливаем пакет googleAnalyticsR

0ztiwqcivhytf2u2rlyd8c1l-dq.png

lxcxsnitj9cw_zn37apqajbugbm.png

Вот тут есть неочевидный нюанс:
Нельзя взять и просто так что-то записать в системные папки MS SQL. Пакеты будут сохранены во временной директории в виде ZIP архивов.

4qt5ueo_hzv0bv4qiwuu96g-0gs.png

В проводнике заходим во временную папку и разархивируем все пакеты.

kfge9jhiz7ccurynbi3yr7amh2k.png

Разархивированные пакеты надо скопировать в директорию библиотек 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 проверяемся на наличие новых пакетов

viw4yqtkwn3f92tozbpgsm-lifm.png

Пакеты будут загружены во временную папку.
Проделываем с ними такие же действия, как и при установке новых пакетов.


Проверяем доступ 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 по умолчанию не имеет доступа в интернет, скорее всего у Вас предыдущий скрипт вызовет следующую ошибку.

ctpzos-kjpqtligr_cje3kg3xou.png

Открываем доступ в интернет для R скриптов из SQL.

SQL 2017

bjlkhlu9qy4twf-arpu8dr2jgua.gif

SQL2019

bnpicoaaii15qaqx-hu-xj2zq6u.gif

В 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 права на доступ к базе.

kld4kcpha_8hjpzvwjiuxeidpog.gif

(Конечно, лучше использовать группы пользователей, в рамках демонстрации я упростил решение)

Оформляем 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 со всем кодом доступен тут
(для воспроизведения необходимо установить пакеты, раздать права, указать название своего сервера)

© Habrahabr.ru