R - импорт с Google Docs в SQL Server

Перед нами стояла задача загружать планы с Google Docs в таблицу SQL Server для дальнейшего сопоставления с фактическими показателями.

Мы рассматривали несколько вариантов: SSIS+API Google Docs, CLR на C# и интеграция библиотеки в базу хранилища, и реализовать на новом для нас языке R. Забегая наперед скажу, что задача была выполнена именно с использованием R.

R — Язык программирования для статистической обработки данных и работы с графикой. Он поддерживает широкий спектр статистических и численных методов и обладает хорошей расширяемостью с помощью пакетов. Ещё одной особенностью R являются графические возможности, заключающиеся в возможности создания качественной графики. (Wiki)

Толчком для распространения этого языка стало внедрение компанией Microsoft поддержки R в своем продукте Бизнес-аналитики  Power BI.

C R на SQL Server все намного интересней. Поддержка R появилась только в версии SQL Server 2016. R устанавливается в момент установки SQL Server. Для активации  процедуры, которая работаем со скриптами R, выполните процедуру:

exec sp_configure  'external scripts enabled', 1
reconfigure  with override

После этого перезагрузите сервер.

Также хочу обратить внимание, что после настройки  процедуры external scripts enabled могут быть созданы правила в брандмауэре. Они созданы для блокировки соединения SQL Server с Веб-ресурсами через скрипты R. Эти правила нужно отключить:

 

Для проверки то, что все выполнено верно, запустите этот скрипт:

exec sp_execute_external_script  
@language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 8 as hello' 
with result sets  (([hello] int not null));

В результате вы получите таблицу в одну колонку [hello] со значением 8.

Далее перейдем в саму Google Таблицу.

В меню "Файл" выберите пункт "Опубликовать в Интернете":

 

Далее выбираем нужный лист и формат CSV:

Чтобы проверить, что скрипт выполняется верно, подставьте свою ссылку, укажите ваши поля и их типы в операторе WITH RESULT SETS и выполните следующий код:

declare @GoogleSheetURL nvarchar(500) =
N'OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/17TyT4bU12oPFVApeUw1zYCKQ8/pub?gid=810382612&single=true&output=csv", header = true, sep = ",", encoding = "UTF-8", stringsAsFactors = false)'

exec sp_execute_external_script
@language =N'R',
@script = @GoogleSheetURL,
@input_data_1 = N'   ;'
with result sets (([Дата] date,[Отдел] nvarchar(20),[Продукт] nvarchar(20),[Продавец] nvarchar(20),[Выставлено_шт] int,[Оплачено_шт] int,[Выставлено] int,[Оплачено] int ));

Для того чтобы записать все значения с листа Google Sheets в таблицу SQL Server, преобразуем наш R + T-SQL скрипт в строковую переменную и выполним через процедуру EXEC:

declare @rquery nvarchar(1000)
set @rquery =
'declare @GoogleSheetURL nvarchar(500) =
''OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/17TyT4bU12oPFVApeUw1zYCKQ8/pub?gid=810382612&single=true&output=csv", header = true, sep = ",", encoding = "UTF-8", stringsAsFactors = false)''

exec sp_execute_external_script  
@language =N''R'',    
@script = @GoogleSheetURL    
with result sets (([Дата] date,[Отдел] nvarchar(100),[Куратор] nvarchar(100),[Товар] nvarchar(100),[Заказы] nvarchar(20),[Продажи] nvarchar(20))); '

insert into [dbo].[Планы]  ([Дата],[Отдел],[Продавец],[Товар],[Заказы],[Продажи])
exec(@rquery )

Все значения таблицы Google Docs записаны в таблицу SQL Server  Планы:

Вариант открытия доступа по ссылке к Google Docs подходит для не конфиденциальных данных, т.к. отсутствует авторизация Gmail.

В общем итоге, возможность использования R на платформе SQL Server 2016 значительно расширяет варианты получения и обработки данных. В том числе импорт данных с веб-реcурсов (используя API) напрямую в таблицы SQL Server.