从R脚本插入SQL Server VARBINARY列 [英] Inserting into SQL Server VARBINARY column from R script

问题描述

我有一个plots表，其列包括plot，该表存储图像文件的二进制数据.我正在运行一个T-SQL查询，该查询调用R脚本并获取要插入的数据的数据框.数据框如下所示:

I have a plots table, whose columns include plot, which stores the binary data of an image file. I'm running a T-SQL query which calls an R script and gets back a data frame of the data to insert. The data frame looks like this:

    plot     name  date_from    date_to
1 ABCDEF  plot1   2016-08-25   2016-08-31
2 AAAAAA  plot2   2016-08-25   2016-08-31

如您所见，plot列已经包含原始数据.

As you can see, the plot column contains raw data already.

为澄清起见，我想做的是在数据库中插入两行数据，其中数据在数据框中(数据框的列名称与数据库列匹配).

To clarify, what I want to do is insert two rows into the database with the data in the data frame (the data frame column names match the database columns).

我遇到的问题

INSERT INTO dbo.plots
EXECUTE sp_execute_external_script
    @language = N'R'
    ,@script = N'source("path/to/r/script.R")'
    ,@output_data_1_name = N'output_dataset'

是不允许从数据类型nvarchar(max)到varbinary(max)的隐式转换.使用CONVERT函数运行此查询".

is "Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query".

但是，我不确定如何纠正此错误.我将CONVERT函数放在哪里?还是有其他方法?

However I'm not sure how I can rectify this error. Where would I put the CONVERT function? Or is there some other way?

推荐答案

对于SQL Server R服务，字符类型映射到VARCHAR，原始类型映射到VARBINARY(请参见

For SQL Server R services, character type maps to VARCHAR and raw type maps to VARBINARY (see Working with R Data Types). To store data as VARBINARY, the hex string has to be converted to raw bytes, which can be done either in R or SQL. Here is an example with conversion done in SQL using a temporary table (inspired by scsimon's comment)

CREATE TABLE #test
(
    [data] VARBINARY(MAX),
)


CREATE TABLE #temp
(
    [data] VARCHAR(MAX),
)

INSERT INTO #temp
EXEC sp_execute_external_script
@language=N'R',
@script=N'OutputDataSet <- as.data.frame("ABCDEF")' 

INSERT INTO #test SELECT CONVERT(VARBINARY(MAX), data, 2) FROM #temp;

这篇关于从R脚本插入SQL Server VARBINARY列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！