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

AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 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
              Этот подход дает удобство обслуживания загрузки данных в хранилище — всё находится в одном месте и в случае чего известно куда смотреть.

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

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

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