В этом материале я хочу подробно показать, как можно при помощи 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 со всем кодом доступен тут
(для воспроизведения необходимо установить пакеты, раздать права, указать название своего сервера)
ссылка на оригинал статьи https://habr.com/ru/post/466339/
Добавить комментарий