Как в Microsoft SQL Server получать данные из Google Analytics при помощи R

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


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


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


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


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


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


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


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





  1. Это R с которым будет работать непосредственно SQL Server (вызываться в SQL запросах).
  2. Клиентская копия 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 со всем кодом доступен тут
(для воспроизведения необходимо установить пакеты, раздать права, указать название своего сервера)

Комментарии 17

    +1
    Пару лет назад в какой-то версии SQL Server пытался установить компонент R. Сначала потребовалась одна библиотека, потом другая, потом третья. А третья сказала, что на вашей версии windows я ставиться не буду.
    Еще однажды был забавный проект с Google Analytics. Нужно было из базы MS SQL выгружать данные в GA, а именно чеки оплаты по кассе с помощью Measurement Protocol. Недолго думая просто использовал какой-то вариант вида
    sp_OACreate 'MSXML2.ServerXMLHTTP'
    В GA упорно не отображались транзакции после отправки POST или GET запроса. Если открыть ту же самую URL c с теми же самыми параметрами через браузер, то все появлялось.
    Общались тогда через Skype, когда отправлял через чат сгенеренные скриптом строки url, то они тоже появлялись в аналитике. (кто бы сомневался)
    В какой-то момент уже начал запускать wget на сервере через EXEC xp_cmdshell, все равно не отображались. Потом случайно выяснилось, что в урл нужно добавлять параметры dh и uip. Осталось непонятным, почему через браузер транзакции успешно отправлялись без этих параметров.
    Потом маркетологи недоумевали, а почему же в аналитике так много клиентов без идентификаторов гугла, откуда же они берутся…
      +1
      Надеюсь моя статья поможет справляться с подобными проблемами :)
      0
      А что если ответ от API будет в JSON размеров в 3Gb, то R спокойно его переварит, разберёт и сложит в таблицу?
        +1
        Конечно, желательно не запрашивать такой объем данных.
        Можно выбирать данные по порциям, например по дням

        Но в общем случае ответ:
        Да, а почему нет?
        Роль R здесь сводится к получению ответа от API и преобразование JSON.
        Непосредственно в таблицу будет складывать MS SQL — а ему таблички в 3 GB это мелочи :)
          0
          Такой вот хитрый API, отдающий только весь набор данных за всю историю без возможности фильтра по датам (и без уникальных ключей, так что запросить по одному полю, а затем склейить по столбцам не вариант). :(
          Таблички в 3Gb, то да, а вот NVARCHAR(MAX) имеет ограничение в 2GB. И такой JSON он (MS SQL Server) уже не переваривает. :(

          Вот я и хотел понять: таки R разбирает содержимое JSON в свой объект dataset, а затем из него уже вставляет в таблицу?
            +1
            R разбирает содержимое JSON в свой объект dataset, а затем из него уже вставляет в таблицу?


            Да, сначала JSON разбирается в DataSet
            И в случае такого хитрого API это Ваш вариант

            честно говоря строку в 3GB я не пробовал — но по логике если оперативки хватит, то проглотит :)
            попробуйте сначала в RStudio
              0
              Ок. Спасибо.
        +1
        Вот тут есть неочевидный нюанс:
        Нельзя взять и просто так что-то записать в системные папки MS SQL. Пакеты будут сохранены во временной директории в виде ZIP архивов.

        так все ж проще делается — из консоли R, только той которая в глубинах папок установленного SQL Server
        вот тут написанно www.mssqltips.com/sqlservertip/4982/installing-external-r-packages-to-use-with-sql-server-2017
          0
          epee
          из консоли R, только той которая в глубинах папок установленного SQL Server


          В теории — Да, всё должно быть просто и устанавливаться одной командой
          install.packages(“”)

          Но и тут есть Нюанс: - в Клиентской части не обновятся пакеты (отлаживать запросы в RStudio не получится )

          Можно поступить как в приведенной Вами ссылке.
          Но сам я копирую всё вручную и всем остальным советую. Так я уверен: всё будет четко - везде одинаковые версии :)
          +1
          Здравствуйте! Пробую выполнить то, что здесь описано. На этапе проверки R скриптов получил ошибку, что они не могут быть выполнены. Служба LaunchPad не запускается. Ошибка 1053: Error 1053: The service did not respond the start or control request in a timely fashion. Попытки увеличить таймаут в регистре, изменить права доступа ни к чему не привели. Были ли подобные ошибки и как с ними справлялись?
            0
            Добрый день,
            Это у Вас не запускается служба R Service (R сиквел сервера)
            У меня такое было когда пытался работать из R studio в папке сервера (а не в клиентской версии)
            в моем примере эта папка сервера вот такая:
            «C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library»
            Так же разархивированные пакеты надо скопировать в клиентскую версию R (c которой работает RStudio)

            В моем примере это папка
            C:/Program Files/Microsoft SQL Server/140/R_SERVER/library


            Почему происходит ошибка:
            — RStudio настраивает окружение R под себя (создает свои файлы, сохраняет состояние сеанса и тд)
            — При рестарте SQL пытается запустить R и возникает проблема с окружением — и служба не стартуется

            я лечил переустановкой R службы в SQL Server
            Возможно есть способ лучше… но я поступаю радикально, так как это решение всех проблем :)

            и да, повторюсь — из R Studio работаем только с клиентской версией
            Клиентская копия R с ним можно будет работать из RStudio не боясь сломать что-то на сервере базы данных.

              0
              Здравствуйте! НЕ совсем понятно. Я поступал, как описано в статье: «Устанавливаем R на сервере MS SQL

              делается это через стандартный интерфейс установки компонентов MS SQL.»…
              Вот я и запускаю этот интерфейс. Ставлю R. Далее идет моя проблема. Ни до каких пакетов я не дошел.
              Если вы говорите о клиентской версии, то вопрос:
              1. Также нужно запустить интерфейс установки? Но он не предлагает каких-то путей, все по умолчанию. Если я переустановлю, то не получится то же самое?
              2. Если речь идет о клиентсткой части, то ОТКУДА и КАК надо запустить установку ( или какие ей параметры нужно передать)? Причем в моем случае я нашел н асервере всего один установщик.
              Буду благодарен за помощь.
                0
                Из Вашего описания — получается что у Вас вообще R не установился… не сталкивался с таким
                я бы копал в сторону прав — посмотрите под каким логином запускается служба LaunchPad

                Если речь идет о клиентсткой части, то ОТКУДА и КАК надо запустить установку

                Все установки через стандартный интерфейс, надо проставить две галочки — как на скриншоте.

                Причем в моем случае я нашел н асервере всего один установщик.

                Да, всё правильно инсталлятор SQL Server — один.

                В порядке интереса — какая у Вас версия SQL?


                  0
                  SQL Server 2017, версия 14.0.3162.1. Да, именно две галочки и проставил. В меню появился R, запускается. Логин, от имени которого стартует служба добавил в политики безопасносни ( вернее добавил группу, в которую он входит). Но далее — 1053 и все.
                    0
                    В таком случае точного рецепта у меня нет — только бубны

                    — поменяйте логин на системный, под которым стартует служба (если у Вас не NT Service)

                    image

                    — попробуйте службу запустить вручную
                    — посмотрите журнал логов — может быть что то будет более понятно

                    и всегда есть ультимативный выход — переустановить R
                    через стандартный интерфейс установки

            +1

            Спасибо, что показали настройку (у самих Майкрософтовцев таких подробных инструкций нет))), но зачем нужна привязка к SQL-серверу? Почему нельзя тот же код использовать в R-сервере?

              0
              зачем нужна привязка к SQL-серверу? Почему нельзя тот же код использовать в R-сервере?

              Можно использовать код где угодно :)
              Однако, в этом варианте у Вас:
              — процедура которая бекапируется вместе с базой
              — при необходимости можно процедуру открыть и поправить
              — задание выполняется по расписанию и мониторится стандартными средствами SQL
              Этот подход дает удобство обслуживания загрузки данных в хранилище — всё находится в одном месте и в случае чего известно куда смотреть.

              Мне кажется такой подход более инфраструктурный :)

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

            Самое читаемое