RevoScaleR functions for working with SQL Server data

APPLIES TO: yesSQL Server (Windows only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This topic provides an overview of the main functions provided in RevoScaleR for working with SQL Server data.

For a complete list of ScaleR functions and how to use them, see the Microsoft R Server reference.

Create SQL Server data sources

The following functions let you define a SQL Server data source. A data source object is a container that specifies a connection string together with the set of data that you want, defined either as a table, view, or query. Stored procedure calls are not supported.

Perform DDL statements

You can execute DDL statements from R, if you have the necessary permissions on the instance and database. The following functions use ODBC calls to execute DDL statements or retrieve the database schema.

  • rxSqlServerTableExists and rxSqlServerDropTable - Drop a SQL Server table, or check for the existence of a database table or object

  • rxExecuteSQLDDL - Execute a Data Definition Language (DDL) command that defines or manipulates database objects. This function cannot return data, and is used only to retrieve or modify the object schema or metadata.

Define or manage compute contexts

The following functions let you define a new compute context, switch compute contexts, or identify the current compute context.

  • RxComputeContext - Create a compute context.

  • rxInSqlServer - Generate a SQL Server compute context that lets ScaleR functions run in SQL Server R Services. This compute context is currently supported only for SQL Server instances on Windows.

  • rxGetComputeContext and rxSetComputeContext - Get or set the active compute context.

Move data and transform data

After you have created a data source object, you can use the object to load data into it, transform data, or write new data to the specified destination. Depending on the size of the data in the source, you can also define the batch size as part of the data source and move data in chunks.

  • rxOpen-methods - Check whether a data source is available, open or close a data source, read data from a source, write data to the target, and close a data source

  • rxImport - Move data from a data source into file storage or into a data frame.

  • rxDataStep - Transform data while moving it between data sources.

The following functions can be used to create a local data store in the XDF format. This file can be useful when working with more data than can be transferred from the database in one batch, or more data than can fit in memory.

For example, if you regularly move large amounts of data from a database to a local workstation, rather than query the database repeatedly for each R operation, you can use the XDF file as a kind of cache to save the data locally and then work with it in your R workspace.

  • RxXdfData - Create an XDF data object

  • rxReadXdf - Reads data from an XDF file into a data frame

For more information about working with these functions, including using data sources other than SQL Server, see Howto guides for data analysis in Microsoft R.