Поделиться через


Сопоставления типов данных между R и SQL Server

Область применения: SQL Server 2016 (13.x) и более поздних версий Управляемый экземпляр SQL Azure

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

Базовая версия R

Службы SQL Server 2016 R и службы машинного обучения SQL Server с R согласуются с конкретными выпусками Microsoft R Open. Например, последний выпуск служб машинного обучения SQL Server 2019 построен на основе версии Microsoft R Open 3.5.2.

Чтобы просмотреть версию R, связанную с конкретным экземпляром SQL Server, откройте RGui в экземпляре SQL. Например, путь для экземпляра по умолчанию в SQL Server 2019 должен быть таким: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\R_SERVICES\bin\x64\Rgui.exe.

Это средство загружает базовую версию R и другие библиотеки. Сведения о версии каждого пакета приводятся в уведомлении, которое загружается при запуске сеанса.

Типы данных R и SQL

Хотя SQL Server поддерживает несколько десятков типов данных, R имеет ограниченное количество скалярных типов данных (числовые, целые числа, сложные, логические, символы, дата и время и необработанные). Поэтому при каждом использовании данных из SQL Server в скриптах R они могут быть неявно преобразованы в совместимый тип. Однако зачастую точное преобразование невозможно выполнить автоматически, и в результате возвращается ошибка, например "Необработанный тип данных SQL".

В этом разделе описываются предусмотренные явные преобразования, а также перечислены неподдерживаемые типы данных. Кроме того, приводятся некоторые рекомендации по сопоставлению типов данных между R и SQL Server.

Неявные преобразования типов данных

В следующей таблице показаны изменения типов данных и значений, когда данные из SQL Server используются в скрипте R, а затем возвращаются в SQL Server.

Тип SQL Класс R Тип результирующего набора Комментарии
bigint numeric float Выполнение сценария R с sp_execute_external_script допускает использование типа данных bigint в качестве входных данных. Однако, поскольку они преобразуются в числовой тип R, это приводит к ухудшению точности в случае очень высоких значений или значений с десятичным разделителем. R поддерживает только максимум 53-разрядные целые числа, после чего начнется потеря точности.
binary(n)
n <= 8000
raw varbinary(max) Может использоваться только для входных параметров и выходных данных
bit logical bit
char(n)
n <= 8000
character varchar(max) Входной кадр данных (input_data_1) создается без явного задания параметра stringsAsFactors, поэтому тип столбца будет зависеть от значения default.stringsAsFactors() в R
datetime POSIXct datetime Указывается в формате GMT
date POSIXct datetime Указывается в формате GMT
decimal(p,s) numeric float Выполнение сценария R с sp_execute_external_script допускает использование типа данных decimal в качестве входных данных. Однако, поскольку они преобразуются в числовой тип R, это приводит к ухудшению точности в случае очень высоких значений или значений с десятичным разделителем. sp_execute_external_script со сценарием R не поддерживает полный диапазон типа данных и изменяет несколько последних десятичных знаков, особенно у чисел с дробной частью.
float numeric float
int integer int
money numeric float Выполнение сценария R с sp_execute_external_script допускает использование типа данных money в качестве входных данных. Однако, поскольку они преобразуются в числовой тип R, это приводит к ухудшению точности в случае очень высоких значений или значений с десятичным разделителем. Иногда значения центов будут неточными, и предупреждение будет выдано: предупреждение: не удается точно представлять значения центов.
numeric(p,s) numeric float Выполнение сценария R с sp_execute_external_script допускает использование типа данных numeric в качестве входных данных. Однако, поскольку они преобразуются в числовой тип R, это приводит к ухудшению точности в случае очень высоких значений или значений с десятичным разделителем. sp_execute_external_script со сценарием R не поддерживает полный диапазон типа данных и изменяет несколько последних десятичных знаков, особенно у чисел с дробной частью.
real numeric float
smalldatetime POSIXct datetime Указывается в формате GMT
smallint integer int
smallmoney numeric float
tinyint integer int
uniqueidentifier character varchar(max)
varbinary(n)
n <= 8000
raw varbinary(max) Может использоваться только для входных параметров и выходных данных
varbinary(max) raw varbinary(max) Может использоваться только для входных параметров и выходных данных
varchar(n)
n <= 8000
character varchar(max) Входной кадр данных (input_data_1) создается без явного задания параметра stringsAsFactors, поэтому тип столбца будет зависеть от значения default.stringsAsFactors() в R

Типы данных, не поддерживаемые языком R

Следующие типы данных, поддерживаемые системой типов SQL Server, могут создавать проблемы при передаче в код R:

  • типы данных, перечисленные в разделе Другое статьи, посвященной системным типам SQL: cursor, timestamp, hierarchyid, uniqueidentifier, sql_variant, xml, table
  • все пространственные типы;
  • Изображение

Типы данных, которые могут быть преобразованы с ошибками

  • Большинство типов datetime поддерживаются, за исключением типа datetimeoffset.
  • Большинство числовых типов данных поддерживаются, но преобразования могут завершаться ошибкой для типов money и smallmoney.
  • Поддерживается тип varchar, но так как SQL Server, как правило, использует формат Юникод, во всех возможных случаях рекомендуется применять nvarchar и другие типы текстовых данных в Юникоде.
  • Функции библиотеки RevoScaleR, которые начинаются с префикса rx, могут обрабатывать двоичные типы данных SQL (например, binary и varbinary), но в большинстве случаев для этих типов требуется особая обработка. Большая часть кода R не поддерживает работу с двоичными столбцами.

Дополнительные сведения о типах данных SQL Server см. в статье Типы данных (Transact-SQL).

Изменения типов данных между версиями SQL Server

Microsoft SQL Server 2016 и более поздних версий содержат улучшения преобразований типов данных и других операций. Большинство этих улучшений обеспечивают повышенную точность при работе с типами с плавающей запятой, а также незначительные изменения операций в классических типах datetime.

Эти улучшения доступны по умолчанию при использовании уровня совместимости базы данных 130 и выше. Однако при использовании другого уровня совместимости или подключении к базе данных с помощью более старой версии, точность чисел или других результатов может отличаться.

Дополнительные сведения см. в статье SQL Server 2016 improvements in handling some data types and uncommon operations (Улучшения SQL Server 2016 для обработки некоторых типов данных и нестандартных операций).

Предварительная проверка схем данных R и SQL

Если у вас есть сомнения по поводу использования определенного типа или структуры данных в R, обычно вы можете применить функцию str(), чтобы получить внутреннюю структуру и тип объекта R. Результат функции выводится в консоль R и также доступен в результатах запроса на вкладке "Сообщения " в Management Studio.

При получении данных из базы данных для использования в коде R всегда следует удалять столбцы, которые нельзя использовать в R, а также столбцы, бесполезные при анализе, например идентификаторы GUID (uniqueidentifier), метки времени и другие столбцы, используемые для аудита или данных журнала преобразований, созданных в рамках процессов извлечения, преобразования и загрузки.

Обратите внимание, что включение ненужных столбцов может значительно снизить производительность кода R, особенно если столбцы большого количества элементов используются как коэффициенты. Поэтому мы советуем использовать системные хранимые процедуры SQL Server и представления сведений для получения типов данных для данной таблицы заранее и устранения или преобразования несовместимых столбцов. Дополнительные сведения см. в статье о представлении информационной схемы в Transact-SQL.

Если некоторый тип данных SQL Server не поддерживается языком R, но вам необходим доступ к столбцам такого типа из скрипта R, мы советуем использовать функции CAST и CONVERT (Transact-SQL). С их помощью вы сможете правильно преобразовать тип данных в скрипте R.

Предупреждение

При использовании rxDataStep для удаления несовместимых столбцов во время перемещения данных учтите, что аргументы varsToKeep и varsToDrop не поддерживаются для типа источника данных RxSqlServerData.

Примеры

Пример 1. Неявное преобразование

В следующем примере показано преобразование данных при выполнении цикла приема-передачи между SQL Server и R.

Этот запрос получает ряд значений из таблицы SQL Server, а затем использует хранимую процедуру sp_execute_external_script для вывода значений при помощи среды выполнения R.

CREATE TABLE MyTable (    
 c1 int,    
 c2 varchar(10),    
 c3 uniqueidentifier    
);    
go    
INSERT MyTable VALUES(1, 'Hello', newid());    
INSERT MyTable VALUES(-11, 'world', newid());    
SELECT * FROM MyTable;    
  
EXECUTE sp_execute_external_script    
 @language = N'R'    
 , @script = N'    
inputDataSet["cR"] <- c(4, 2)    
str(inputDataSet)    
outputDataSet <- inputDataSet'    
 , @input_data_1 = N'SELECT c1, c2, c3 FROM MyTable'    
 , @input_data_1_name = N'inputDataSet'    
 , @output_data_1_name = N'outputDataSet'    
 WITH RESULT SETS((C1 int, C2 varchar(max), C3 varchar(max), C4 float));  

Результаты

Строка # C1 C2 C3 C4
1 1 Привет 6e225611-4b58-4995-a0a5-554d19012ef1 4
2 -11 миру 6732ea46-2d5d-430b-8ao1-86e7f3351c3e 2

Обратите внимание на использование функции str в R для получения схемы выходных данных. Эта функция возвращает следующую информацию:

'data.frame':2 obs. of  4 variables:
 $ c1: int  1 -11
 $ c2: Factor w/ 2 levels "Hello","world": 1 2
 $ c3: Factor w/ 2 levels "6732EA46-2D5D-430B-8A01-86E7F3351C3E",..: 2 1
 $ cR: num  4 2

На приведенном примере можно увидеть, что при выполнении этого запроса были выполнены следующие типы неявных преобразований.

  • Столбец C1. Столбец представлен как int в SQL Server, integer в R и int в выходном наборе результатов.

    Преобразование типа не выполнялось.

  • Столбец C2. Столбец представлен как varchar(10) в SQL Server, factor в R и varchar(max) в выходных данных.

    Обратите внимание, что тип выходных данных изменился. Любая строка из R (фактор или обычная строка) будет представлена как varchar(max)вне зависимости от длины строки.

  • Столбец C3. Столбец представлен как уникальный идентификатор в SQL Server, character в R и varchar(max) в выходных данных.

    Обратите внимание на преобразование типа данных. SQL Server поддерживает уникальный идентификатор , но R не имеет, поэтому идентификаторы представляются в виде строк.

  • Столбец C4. Этого столбца нет в исходных данных. Он содержит значения, созданные сценарием R.

Пример 2. Динамический выбор столбцов с помощью R

В следующем примере показано, как использовать код R для проверки на наличие недопустимых типов столбцов. Приведенный ниже код получает схему указанной таблицы с помощью системных представлений SQL Server и удаляет все столбцы с заданным недопустимым типом.

connStr <- "Server=.;Database=TestDB;Trusted_Connection=Yes"
data <- RxSqlServerData(connectionString = connStr, sqlQuery = "SELECT COLUMN_NAME FROM TestDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'testdata' AND DATA_TYPE <> 'image';")
columns <- rxImport(data)
columnList <- do.call(paste, c(as.list(columns$COLUMN_NAME), sep = ","))
sqlQuery <- paste("SELECT", columnList, "FROM testdata")

См. также