Asignaciones de tipos de datos entre R y SQL Server
Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Managed Instance
En este artículo se enumeran los tipos de datos admitidos y las conversiones de tipos de datos que se realizan cuando se usa la característica de integración de R en SQL Server Machine Learning Services.
Versión de R base
SQL Server 2016 R Services y SQL Server Machine Learning Services con R están en consonancia con versiones específicas de Microsoft R Open. Por ejemplo, la versión más reciente, SQL Server 2019 Machine Learning Services, se basa en Microsoft R Open 3.5.2.
Para ver la versión de R asociada a una instancia determinada de SQL Server, abra RGui en la instancia de SQL. Por ejemplo, la ruta de acceso de la instancia predeterminada en SQL Server 2019 sería: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\R_SERVICES\bin\x64\Rgui.exe
.
La herramienta carga Base R y otras bibliotecas. La información de versión del paquete se proporciona en una notificación para cada paquete que se carga en el inicio de la sesión.
Tipos de datos de SQL y R
Aunque SQL Server admite varias decenas de tipos de datos, R tiene un número limitado de tipos de datos escalares (numérico, entero, complejo, lógico, carácter, fecha/hora y sin formato). Por consiguiente, cada vez que se usan datos de SQL Server en scripts de R, es posible que los datos se conviertan implícitamente en un tipo de datos compatible. Si bien, a menudo no se puede realizar una conversión exacta automáticamente y se devuelve un error, como "Unhandled SQL data type" (Tipo de datos SQL no controlado).
En esta sección se indican las conversiones implícitas que se ofrecen y los tipos de datos no admitidos. Se ofrecen algunas instrucciones para asignar tipos de datos entre R y SQL Server.
Conversiones implícitas de tipos de datos
En la tabla siguiente se muestran los cambios que se producen en los tipos de datos y los valores cuando los datos de SQL Server se usan en un script de R y después se devuelven a SQL Server.
Tipo SQL | Clase de R | Tipo de conjunto de resultados | Comentarios |
---|---|---|---|
bigint | numeric |
float | Ejecutar un script de R con sp_execute_external_script permite el tipo de datos bigint como datos de entrada. Sin embargo, dado que se convierten al tipo numérico de R, sufren una pérdida de precisión con valores muy altos o con valores de separador decimal. R solo admite enteros de 53 bits y, posteriormente, empezará a tener una pérdida de precisión. |
binary(n) n <= 8000 |
raw |
varbinary(max) | Solo se permite como parámetro de entrada y salida. |
bit | logical |
bit | |
char(n) n <= 8000 |
character |
ntext | El marco de datos de entrada (input_data_1) se crea sin establecer explícitamente el parámetro de stringsAsFactors, por lo que el tipo de columna dependerá de default.stringsAsFactors() en R. |
datetime | POSIXct |
datetime | Se representa como GMT. |
date | POSIXct |
datetime | Se representa como GMT. |
decimal(p,s) | numeric |
float | Ejecutar un script de R con sp_execute_external_script permite el tipo de datos decimal como datos de entrada. No obstante, dado que se convierten al tipo numérico de R, sufren una pérdida de precisión con valores muy altos o con valores de separador decimal. sp_execute_external_script con un script de R no admite el rango completo del tipo de datos y modificaría los últimos dígitos decimales, especialmente aquellos con fracción. |
float | numeric |
float | |
int | integer |
int | |
money | numeric |
float | Ejecutar un script de R con sp_execute_external_script permite el tipo de datos money como datos de entrada. No obstante, dado que se convierten al tipo numérico de R, sufren una pérdida de precisión con valores muy altos o con valores de separador decimal. A veces, los valores de céntimos podrían ser imprecisos y, en este caso, se emitiría una advertencia: Advertencia: No se pueden representar con precisión los valores de céntimos. |
numeric(p,s) | numeric |
float | Ejecutar un script de R con sp_execute_external_script permite el tipo de datos numeric como datos de entrada. No obstante, dado que se convierten al tipo numérico de R, sufren una pérdida de precisión con valores muy altos o con valores de separador decimal. sp_execute_external_script con un script de R no admite el rango completo del tipo de datos y modificaría los últimos dígitos decimales, especialmente aquellos con fracción. |
real | numeric |
float | |
smalldatetime | POSIXct |
datetime | Se representa como GMT. |
smallint | integer |
int | |
smallmoney | numeric |
float | |
tinyint | integer |
int | |
uniqueidentifier | character |
ntext | |
varbinary(n) n <= 8000 |
raw |
varbinary(max) | Solo se permite como parámetro de entrada y salida. |
varbinary(max) | raw |
varbinary(max) | Solo se permite como parámetro de entrada y salida. |
varchar(n) n <= 8000 |
character |
ntext | El marco de datos de entrada (input_data_1) se crea sin establecer explícitamente el parámetro de stringsAsFactors, por lo que el tipo de columna dependerá de default.stringsAsFactors() en R. |
Tipos de datos no compatibles con R
De las categorías de tipos de datos compatibles con el sistema de tipos de SQL Server, es probable que los siguientes tipos planteen problemas al pasarlos a código de R:
- Tipos de datos mostrados en la sección Otros tipos de datos del artículo sobre el sistema de tipos de SQL: cursor, timestamp, hierarchyid, uniqueidentifier, sql_variant, xml, table
- Todos los tipos espaciales
- image
Tipos de datos cuya conversión puede ser deficiente
- La mayoría de los tipos de fecha y hora deberían funcionar, salvo datetimeoffset.
- Se admite la mayoría de los tipos de datos numéricos, pero es posible que las conversiones de money y smallmoney no se realicen.
- Se admite varchar, pero dado que SQL Server usa Unicode por norma, se recomienda usar nvarchar y otros tipos de datos de texto Unicode siempre que sea posible.
- Las funciones de la biblioteca RevoScaleR que usan el prefijo rx pueden tratar los tipos de datos binarios de SQL (binary y varbinary), pero en la mayoría de los escenarios se necesitará un tratamiento especial de estos tipos. La mayor parte del código de R no puede trabajar con columnas binarias.
Para obtener información sobre los tipos de datos de SQL Server, vea Tipos de datos (Transact-SQL).
Cambios en los tipos de datos entre versiones de SQL Server
Microsoft SQL Server 2016 y versiones posteriores incluyen mejoras en las conversiones de tipos de datos y en otras operaciones. La mayoría de estas mejoras ofrecen mayor precisión en lo que respecta al uso de tipos de datos de punto flotante, así como cambios menores en operaciones en tipos de datos datetime clásicos.
Estas mejoras están todas disponibles de manera predeterminada cuando se usa un nivel de compatibilidad de la base de datos de 130 o posterior. Pero si usa otro nivel de compatibilidad de la base de datos o se conecta a la base de datos con una versión anterior, puede que observe diferencias en la precisión de números u otros resultados.
Para obtener más información, vea Mejoras de SQL Server 2016 en el control de algunos tipos de datos y operaciones infrecuentes.
Comprobar esquemas de datos de SQL y R de antemano
En general, siempre que tenga alguna duda sobre cómo se usa en R un tipo o una estructura de datos en concreto, use la función str()
para obtener la estructura interna y el tipo del objeto de R. El resultado de la función se imprime en la consola de R y también está disponible en los resultados de la consulta, en la pestaña Mensajes de Management Studio.
Al recuperar datos de una base de datos para su uso en código de R, debe eliminar siempre las columnas que no se puedan usar en R, así como las columnas que no sean útiles para el análisis, como GUID (uniqueidentifier), marcas de tiempo y otras columnas que se usan en auditoría, o información de linaje creada con procesos de ETL.
Tenga en cuenta que la inclusión de columnas innecesarias puede reducir considerablemente el rendimiento del código de R, especialmente si se usan columnas de alta cardinalidad como factores. Por consiguiente, se recomienda usar vistas de información y procedimientos almacenados del sistema de SQL Server para obtener de antemano los tipos de datos de una tabla determinada y eliminar o convertir las columnas incompatibles. Para obtener más información, vea Vistas de esquema de información del sistema (Transact-SQL)
Si R no admite un tipo de datos de SQL Server en concreto, pero hay que usar las columnas de datos en el script de R, se recomienda que use las funciones CAST y CONVERT (Transact-SQL) para garantizar que las conversiones de los tipos de datos se realicen como se espera antes de usar los datos en el script de R.
Advertencia
Si usa el rxDataStep para quitar columnas incompatibles al mover los datos, tenga en cuenta que los argumentos varsToKeep y varsToDrop no son compatibles con el tipo de origen de datos RxSqlServerData.
Ejemplos
Ejemplo 1: Conversión implícita
En el ejemplo siguiente se muestra cómo se transforman los datos al hacer el recorrido de ida y vuelta entre SQL Server y R.
En la consulta se obtiene una serie de valores de una tabla de SQL Server y se usa el procedimiento almacenado sp_execute_external_script para generar los valores con el entorno de ejecución de 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));
Resultados
Fila núm. | C1 | C2 | C3 | C4 |
---|---|---|---|---|
1 | 1 | Hola | 6e225611-4b58-4995-a0a5-554d19012ef1 | 4 |
2 | -11 | world | 6732ea46-2d5d-430b-8ao1-86e7f3351c3e | 2 |
Observe el uso de la función str
en R para obtener el esquema de los datos de salida. Esta función devuelve la siguiente información:
'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
Aquí puede ver que las siguientes conversiones de tipos de datos se han realizado implícitamente como parte de esta consulta:
Columna C1. La columna se representa como int en SQL Server,
integer
en R y int en el conjunto de resultados de salida.No se ha realizado ninguna conversión de tipo.
Columna C2. La columna se representa como varchar(10) en SQL Server,
factor
en R y varchar(max) en la salida.Observe los cambios que se han producido en la salida: todas las cadenas de R (tanto si son un factor como una cadena normal) se representarán como varchar(max) , independientemente de la longitud de las cadenas.
Columna C3. La columna se representa como uniqueidentifier en SQL Server,
character
en R y varchar(max) en la salida.Observe la conversión de tipo de datos que se ha producido. SQL Server admite uniqueidentifier , pero R no. Por eso, los identificadores se representan como cadenas.
Columna C4. La columna contiene valores generados por el script de R que no están presentes en los datos originales.
Ejemplo 2: Selección de columnas dinámicas con R
En el ejemplo siguiente se muestra cómo puede usar código de R para buscar tipos de columnas no válidos. Luego se obtiene el esquema de una tabla especificada mediante vistas del sistema de SQL Server y se quitan las columnas que tengan un tipo no válido especificado.
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")