Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
Приветствую
Для импорта данных в SQL Server 2016 пытаюсь использовать R-скрипт

OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/128qCX1YtvsHr4hERruFx6Ykn72qRkpRGH5brFULl7SY/pub?gid=0&single=true&output=csv", 
header = TRUE, sep = ",", encoding = "UTF-8", stringsAsFactors = FALSE)
OutputDataSet


В среде VS эти строки выполняются: http://prntscr.com/dxdz7t

В SSMS выполняю
Exec sp_configure  'external scripts enabled', 1  
Reconfigure  with override  


Тестовое выполнение работает: http://prntscr.com/dxdzte

Пробую выполнять с реальной ссылкой:
DECLARE @GoogleSheetURL nvarchar(500) = 
'OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/128qCX1YtvsHr4hERruFx6Ykn72qRkpRGH5brFULl7SY/pub?gid=0&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 ));    
go  


Ошибка соединения:
автор
Сообщение 39004, уровень 16, состояние 20, строка 27
Во время выполнения sp_execute_external_script произошла ошибка в скрипте "R" с HRESULT 0x80004004.
Сообщение 39019, уровень 16, состояние 1, строка 27
Возникла ошибка во внешнем скрипте:
Îøèáêà â file(file, "rt") :íå ìîãó îòêðûòü ñîåäèíåíèå
Âûçîâû: source ... withVisible -> eval -> eval -> read.csv -> read.table -> file
Âäîáàâîê: Ïðåäóïðåæäåíèå:
 file(file, "rt") :
'InternetOpenUrl' íå óäàëîñü: 'Íå óäàåòñÿ óñòàíîâèòü ñâÿçü ñ ñåðâåðîì'

Error in ScaleR. Check the output for more information.
Îøèáêà â eval(expr, envir, enclos) :
Error in ScaleR. Check the output for more information.
Âûçîâû: source -> withVisible -> eval -> eval -> .Call
Âûïîëíåíèå îñòàíîâëåíî
Сообщение 11536, уровень 16, состояние 1, строка 31
Не удалось выполнить инструкцию EXECUTE, поскольку в ее предложении WITH RESULT SETS указано 1 результирующих наборов, но во время выполнения инструкция отправила только 0 результирующих наборов.



Может кто работал с R на SQL. В чем проблема того что с T-SQL не удается подключиться к Google Sheet?
19 янв 17, 01:10    [20120951]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
Текст ошибки копируется с неверной кодировкой
Вот скрин

К сообщению приложен файл. Размер - 60Kb
19 янв 17, 01:21    [20120960]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
Ну начните с простого, типа пинга docs.google.com из xp_cmdshell
19 янв 17, 01:52    [20121004]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
Гавриленко Сергей Алексеевич
Ну начните с простого, типа пинга docs.google.com из xp_cmdshell


Пингуется

К сообщению приложен файл. Размер - 86Kb
19 янв 17, 10:06    [20121490]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
У вас в TSQL-коде нет завершающей кавычки в адресе:
DECLARE @GoogleSheetURL nvarchar(500) = 
'OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/128qCX1YtvsHr4hERruFx6Ykn72qRkpRGH5brFULl7SY/pub?gid=0&single=true&output=csv", 
header = TRUE, sep = ",", encoding = "UTF-8", stringsAsFactors = FALSE)'


Сообщение было отредактировано: 19 янв 17, 12:54
19 янв 17, 12:54    [20122364]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4535
hulk77, а вообще прикольно... использовать R для задачи импорта в базу... сову на глобус :) хотя - почему бы и нет...
19 янв 17, 13:46    [20122630]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
Гавриленко Сергей Алексеевич,

при копировании на форум потерялась.. в рабочей версии кавычки есть

К сообщению приложен файл. Размер - 135Kb
19 янв 17, 15:38    [20123356]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
buser
hulk77, а вообще прикольно... использовать R для задачи импорта в базу... сову на глобус :) хотя - почему бы и нет...


Чтобы загрузить с гугл доков данные, это или CLR или SSIS+C#
Оба варианта менее гибкие и более сложные в администрировании
Вот бы на R получилось - импорт в одну строку кода))
19 янв 17, 15:39    [20123367]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
Второй потенциальный косяк - неправильно заданная юникодная строка:

DECLARE @GoogleSheetURL nvarchar(500) = 
N'OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/128qCX1YtvsHr4hERruFx6Ykn72qRkpRGH5brFULl7SY/pub?gid=0&single=true&output=csv, 
header = TRUE, sep = ",", encoding = "UTF-8", stringsAsFactors = FALSE)'
19 янв 17, 16:05    [20123505]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
Гавриленко Сергей Алексеевич
Второй потенциальный косяк - неправильно заданная юникодная строка:

DECLARE @GoogleSheetURL nvarchar(500) = 
N'OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/128qCX1YtvsHr4hERruFx6Ykn72qRkpRGH5brFULl7SY/pub?gid=0&single=true&output=csv, 
header = TRUE, sep = ",", encoding = "UTF-8", stringsAsFactors = FALSE)'


Никак не повлияла. Да я и пробовал уже варианты с N
19 янв 17, 23:39    [20125086]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
Вот ответ
К моему стыду..

Спасибо, Сергей Алексеевич, что пытались помочь

Скрипт в первом посте - рабочий

К сообщению приложен файл. Размер - 137Kb
20 янв 17, 00:01    [20125137]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
hulk77,

Ну, букву N все равно к юникодным литералам дописывайте.

В целом, ваша информация полезная, спасибо, что поделились.

Сообщение было отредактировано: 20 янв 17, 02:06
20 янв 17, 01:29    [20125238]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
hulk77
Member

Откуда:
Сообщений: 123
Для того чтобы записывать в таблицу полученные через R данные, пришлось пойти таким путем:

TRUNCATE TABLE  [dbo].[Планы];

DECLARE @RQUERY nvarchar(1000)
SET @RQUERY = 
'DECLARE @GoogleSheetURL nvarchar(500) = 
 N''OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/17TyTYCKQ8/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)



Для подключения внешних библиотек R в SQL Server:
1. Открыть доступ для учетки SQL Server к папке C:\Program Files\Microsoft SQL Server\MSSQL13\R_SERVICES\library
2. Выполнить
DECLARE @GoogleSheetURL nvarchar(500) = 
N'install.packages("rjson") '

EXEC sp_execute_external_script  
  @language =N'R',    
  @script = @GoogleSheetURL,      
  @input_data_1 = N'   ;'    
 go  


Для такой структуры JSON
{
"value": [
{
"Контрагент": "Физические лица",
"КонтрагентID": "8cab790a-bddd-11e5-9f43-d8cb8a136158",
"Родитель1": "Яблоки"
},
{
"Контрагент": "Юридические лица",
"КонтрагентID": "8cab790b-bddd-11e5-9f43-d8cb8a136158",
"Родитель1": "Груши"
}
]
}


Можно использовать такой R + T-SQL скрипт

DECLARE @GoogleSheetURL nvarchar(500) = 
N'library(rjson)
my.JSON <- fromJSON(file = ''C:/k.json'')
df <- lapply(my.JSON, function(value)
    {
        data.frame(matrix(unlist(value), ncol = 3, byrow = T))
    })
df <- do.call(rbind, df)
colnames(df) <- names(my.JSON[[1]][[1]])
rownames(df) <- NULL
OutputDataSet <- df
'
EXEC sp_execute_external_script  
  @language =N'R',    
  @script = @GoogleSheetURL,      
  @input_data_1 = N'   ;'    
  WITH RESULT SETS (([Контрагент] nvarchar(100),[КонтрагентID] nvarchar(100),[Родитель1] nvarchar(100)));    
go  


Может кому пригодится
23 янв 17, 22:17    [20137615]     Ответить | Цитировать Сообщить модератору
 Re: R-скрипт под T-SQL  [new]
iMac
Member

Откуда: Владивосток
Сообщений: 11
Такая же ошибка, но дело в не Брандмауэре.

exec sp_execute_external_script    
      @language = N'R'    
    , @script = N'
LoadPolygons <- function(x){
  # Получить данные о координатах полигона
  # Аргументы: 
  # x: название географического объекта
  #
  # Возвращаемые значения: 
  # Таблица координат полиuгона
  ############################
  # Обратимся к необходимым пакетам и обновим их
 
library("rjson",lib.loc = "C:/RService")
library("bitops",lib.loc = "C:/RService")	
library("RCurl",lib.loc = "C:/RService")
library("magrittr",lib.loc = "C:/RService")



  # Получим данные
  query <- x
  format <- "json" 
  limit <- 1
  polygon <- 1
  paramsQuery <- list (q=query, polygon = polygon, format = format, limit = limit)
  paramsQuery <- sprintf ("?%s", paste (names (paramsQuery), "=", unlist (paramsQuery), collapse = "&", sep = ""))
  urlPolygon <- sprintf("http://nominatim.openstreetmap.org/search%s", paramsQuery)
  listPolygon <- fromJSON (file = urlPolygon)
  if (length(listPolygon) == 0){
    return (NULL)
  } 
  else{
    polygon <- listPolygon[[1]]$polygonpoints %>% as.data.frame(.) %>% t (.) 
    row.names (polygon) <- NULL
    colnames (polygon) <- c ("lon", "lat") 
    return (polygon)
  }
}

p1 <- LoadPolygons ("Фокино, приморский край")
OutputDataSet <- as.data.frame(p1)
'
 ,@output_data_1_name = N'OutputDataSet'  
with result sets (("Lon" numeric,"Lat" numeric))

GO


В консоли R скрипт отрабатывает и данные возвращает. А в MSSMS результат вывести не могу
30 май 17, 09:36    [20521997]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить