使用 T-SQL (CREATE EXTERNAL LIBRARY) 将 R 包安装在 SQL Server 上Use T-SQL (CREATE EXTERNAL LIBRARY) to install R packages on SQL Server

仅限 适用于:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x)适用于:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x) only

本文介绍如何在启用了机器学习的 SQL Server 实例上安装新的 R 包。This article explains how to install new R packages on an instance of SQL Server where machine learning is enabled. 有多种方法可供选择。There are multiple approaches to choose from. 使用 T-SQL 最适用于不熟悉 R 的服务器管理员。Using T-SQL works best for server administrators who are unfamiliar with R.

使用 CREATE EXTERNAL LIBRARY 语句,可以将包或包集添加到实例或特定数据库,而无需直接运行 R 或 Python 代码。The CREATE EXTERNAL LIBRARY statement makes it possible to add a package or set of packages to an instance or a specific database without running R or Python code directly. 但是,此方法需要准备好包以及额外的数据库权限。However, this method requires package preparation and additional database permissions.

  • 所有包都必须作为本地压缩文件提供,而不是根据需要从 Internet 下载。All packages must be available as a local zipped file, rather than downloaded on demand from the internet.

  • 必须按名称和版本标识所有依赖项,并将其包含在 zip 文件中。All dependencies must be identified by name and version, and included in the zip file. 如果所需的包不可用(包括下游包依赖项),则语句将失败。The statement fails if required packages are not available, including downstream package dependencies.

  • 你必须是 db_owner 或在数据库角色中享有 CREATE EXTERNAL LIBRARY 权限。You must be db_owner or have CREATE EXTERNAL LIBRARY permission in a database role. 有关详细信息,请参阅 CREATE EXTERNAL LIBRARYFor details, see CREATE EXTERNAL LIBRARY.

下载存档格式的包Download packages in archive format

如果要安装单个包,请下载压缩格式的包。If you are installing a single package, download the package in zipped format.

由于包依赖项,更常见的方法是安装多个包。It's more common to install multiple packages due to package dependencies. 当包需要其他包时,必须在安装过程中验证是否所有包都可供访问。When a package requires other packages, you must verify that all of them are accessible to each other during installation. 建议使用 miniCRAN 创建本地存储库来组装一个完整的包集合,以及使用 igraph 来分析包依赖项。We recommend creating a local repository using miniCRAN to assemble a full collection of packages, as well as igraph for analyzing packages dependencies. 安装包的错误版本或省略包依赖项可能导致 CREATE EXTERNAL LIBRARY 语句失败。Installing the wrong version of a package or omitting a package dependency can cause a CREATE EXTERNAL LIBRARY statement to fail.

将文件复制到本地文件夹Copy the file to a local folder

将包含所有包的压缩文件复制到服务器上的本地文件夹。Copy the zipped file containing all packages to a local folder on the server. 如果无法访问服务器上的文件系统,还可以使用二进制格式将整个包作为变量传递。If you do not have access to the file system on the server, you can also pass a complete package as a variable, using a binary format. 有关详细信息,请参阅 CREATE EXTERNAL LIBRARYFor more information, see CREATE EXTERNAL LIBRARY.

运行语句上传包Run the statement to upload packages

使用具有管理权限的帐户打开“查询”窗口。Open a Query window, using an account with administrative privileges.

运行 T-SQL 语句 CREATE EXTERNAL LIBRARY 将压缩包集合上传到数据库。Run the T-SQL statement CREATE EXTERNAL LIBRARY to upload the zipped package collection to the database.

例如,下面的语句将 miniCRAN 存储库命名为包源,其中包含 randomForest 包及其依赖项。For example, the following statement names as the package source a miniCRAN repository containing the randomForest package, together with its dependencies.

CREATE EXTERNAL LIBRARY [randomForest]
FROM (CONTENT = 'C:\Temp\Rpackages\randomForest_4.6-12.zip')
WITH (LANGUAGE = 'R');

不能使用任意名称;外部库名称必须与加载或调用包时预期使用的名称相同。You cannot use an arbitrary name; the external library name must have the same name that you expect to use when loading or calling the package.

验证包安装Verify package installation

如果成功创建库,则可以在 SQL Server 中运行包,方法是在存储过程中调用包。If the library is successfully created, you can run the package in SQL Server, by calling it inside a stored procedure.

EXEC sp_execute_external_script
@language =N'R',
@script=N'library(randomForest)'

另请参阅See also