Сопоставления типов данных между 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")