PolyBase 连接配置 (Transact-SQL)PolyBase Connectivity Configuration (Transact-SQL)

适用于: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

显示或更改 PolyBase Hadoop 和 Azure blob 存储连接的全局配置设置。Displays or changes global configuration settings for PolyBase Hadoop and Azure blob storage connectivity.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


--List all of the configuration options  
--Configure Hadoop connectivity  
sp_configure [ @configname = ] 'hadoop connectivity',  
             [ @configvalue = ] { 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 }  


[ @configname=** ] ' option_name '[ @configname=** ] 'option_name'
配置选项的名称。Is the name of a configuration option. option_name 的数据类型为 varchar(35) ,默认值为 NULL。option_name is varchar(35), with a default of NULL. 如果未指定该参数,则返回选项的完整列表。If not specified, the complete list of options is returned.

[ @configvalue=** ] ' value '[ @configvalue=** ] 'value'
新的配置设置。Is the new configuration setting. value 的数据类型为 int,默认值为 NULL。value is int, with a default of NULL. 最大值取决于各个选项。The maximum value depends on the individual option.

“hadoop 连接”'hadoop connectivity'
为从 PolyBase 到 Hadoop 群集或 Azure blob 存储 (WASB) 的所有连接指定 Hadoop 数据源类型。Specifies the type of Hadoop data source for all connections from PolyBase to Hadoop clusters or Azure blob storage (WASB). 若要为外部表创建外部数据源,需要使用此设置。This setting is required in order to create an external data source for an external table. 有关详细信息,请参阅 CREATE EXTERNAL DATA SOURCE (Transact-SQL)For more information, see CREATE EXTERNAL DATA SOURCE (Transact-SQL),

这些是 Hadoop 连接设置和其相应支持的 Hadoop 数据源。These are the Hadoop connectivity settings and their corresponding supported Hadoop data sources. 一次只有一种设置有效。Only one setting can be in effect at a time. 选项 1、4 和 7 允许创建多种类型的外部数据源,并在服务器上的所有会话中进行使用。Options 1, 4, and 7 allow multiple types of external data sources to be created and used across all sessions on the server.

  • 选项 0:禁用 Hadoop 连接Option 0: Disable Hadoop connectivity

  • 选项 1:Windows Server 上的 Hortonworks HDP 1.3Option 1: Hortonworks HDP 1.3 on Windows Server

  • 选项 1:Azure blob 存储 (WASB[S])Option 1: Azure blob storage (WASB[S])

  • 选项 2:Linux 上的 Hortonworks HDP 1.3Option 2: Hortonworks HDP 1.3 on Linux

  • 选项 3:Linux 上的 Cloudera CDH 4.3Option 3: Cloudera CDH 4.3 on Linux

  • 选项 4:Windows Server 上的 Hortonworks HDP 2.0Option 4: Hortonworks HDP 2.0 on Windows Server

  • 选项 4:Azure blob 存储 (WASB[S])Option 4: Azure blob storage (WASB[S])

  • 选项 5:Linux 上的 Hortonworks HDP 2.0Option 5: Hortonworks HDP 2.0 on Linux

  • 选项 6:Linux 上的 Cloudera 5.1、5.2、5.3、5.4、5.5、5.9、5.10、5.11、5.12 和 5.13Option 6: Cloudera 5.1, 5.2, 5.3, 5.4, 5.5, 5.9, 5.10, 5.11, 5.12, and 5.13 on Linux

  • 选项 7:Linux 上的 Hortonworks 2.1、2.2、2.3、2.4、2.5、2.6、3.0Option 7: Hortonworks 2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 3.0 on Linux

  • 选项 7:Windows Server 上的 Hortonworks 2.1、2.2 和 2.3Option 7: Hortonworks 2.1, 2.2, and 2.3 on Windows Server

  • 选项 7:Azure blob 存储 (WASB[S])Option 7: Azure blob storage (WASB[S])

更新运行值 (run_value) 以匹配配置值 (config_value)。Updates the run value (run_value) to match the configuration value (config_value). 请参阅 结果集 以了解 run_value 和 config_value 的定义。See Result Sets for definitions of run_value and config_value. 在 RECONFIGURE 语句设置运行值之前,由 sp_configure 设置的新配置值不会生效。The new configuration value that is set by sp_configure does not become effective until the run value is set by the RECONFIGURE statement.

运行 RECONFIGURE 之后,必须停止并重启 SQL Server 服务。After running RECONFIGURE, you must stop and restart the SQL Server service. 请注意,停止 SQL Server 服务时,另外两个 PolyBase 引擎和数据移动服务将自动停止。Note that when stopping the SQL Server service, the two additional PolyBase Engine and Data Movement Service will automatically stop. 重启 SQL Server 引擎服务之后,再次重启这两项服务(它们不会自动启动)。After restarting the SQL Server engine service, re-start these two services again (they won't start automatically).

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

在不使用参数执行时, sp_configure 会返回五列的结果集。When executed with no parameters, sp_configure returns a result set with five columns.

列名称Column name 数据类型Data type 说明Description
name name nvarchar(35)nvarchar(35) 配置选项的名称。Name of the configuration option.
最小值minimum intint 配置选项的最小值。Minimum value of the configuration option.
最大值maximum intint 配置选项的最大值。Maximum value of the configuration option.
config_valueconfig_value intint 使用 sp_configure设置的值。Value that was set using sp_configure.
run_valuerun_value intint PolyBase 正在使用当前值。Current value in use by PolyBase. 此值通过运行 RECONFIGURE 进行设置。This value is set by running RECONFIGURE.

config_valuerun_value 通常是相同的,除非该值正在进行更改。The config_value and run_value are usually the same unless the value is in the process of being changed.

如果正在进行重新配置,则可能需要重启计算机,运行值才会准确。A restart might be required before this run value is accurate, if the reconfiguration is in progress.

一般备注General Remarks

SQL ServerSQL Server中,运行 RECONFIGURE 之后,为使“hadoop 连接”的运行值生效,需要重启 SQL ServerSQL ServerIn SQL ServerSQL Server, after running RECONFIGURE, for the run value of the 'hadoop connectivity' to take effect, you need to restart SQL ServerSQL Server.
并行数据仓库Parallel Data Warehouse中,运行 RECONFIGURE 之后,为使“hadoop 连接”的运行值生效,需要重启 并行数据仓库Parallel Data Warehouse 区域。In 并行数据仓库Parallel Data Warehouse, after running RECONFIGURE, for the run value of the 'hadoop connectivity' to take effect, you need to restart the 并行数据仓库Parallel Data Warehouse region.

限制和局限Limitations and Restrictions

不允许在显式或隐式事务中使用 RECONFIGURE。RECONFIGURE is not allowed in an explicit or implicit transaction.


所有的用户都可以不使用参数或者使用 参数执行 sp_configure @configname 。All users can execute sp_configure with no parameters or the @configname parameter.

需要 ALTER SETTINGS 服务器级别权限或 sysadmin 中固定服务器角色的成员资格,才能更改配置值或运行 RECONFIGURE。Requires ALTER SETTINGS server-level permission or membership in the sysadmin fixed server role to change a configuration value or to run RECONFIGURE.


A.A. 列出所有可用的配置设置List all available configuration settings

以下示例显示如何列出所有的配置选项。The following example shows how to list all configuration options.

EXEC sp_configure;  

结果返回选项名称,后跟该选项的最小值和最大值。The result returns the option name followed by the minimum and maximum values for the option. config_value 是重新配置完成后 SQL 或者 PolyBase 将使用的值。The config_value is the value that SQL, or PolyBase, will use when reconfiguration is complete. run_value 是当前正在使用的值。The run_value is the value that is currently being used. config_valuerun_value 通常是相同的,除非该值正在进行更改。The config_value and run_value are usually the same unless the value is in the process of being changed.

B.B. 列出一个配置名称的配置设置List the configuration settings for one configuration name

EXEC sp_configure @configname='hadoop connectivity';  

C.C. 设置 hadoop 连接Set Hadoop connectivity

此示例将 PolyBase 设置为选项 7。This example sets PolyBase to option 7. 此选项允许 PolyBase 在 Linux 和 Windows Server 以及 Azure blob 存储中,创建和使用 Hortonworks 2.1、2.2 和 2.3 上的外部表。This option allows PolyBase to create and use external tables on Hortonworks 2.1, 2.2, and 2.3 on Linux and Windows Server, and Azure blob storage. 例如,SQL 可能拥有 30 个外部表,其中 7 个引用 Linux 上 Hortonworks 2.1 的数据,4 个引用 Linux 上 Hortonworks 2.2 的数据,7 个引用 Linux 上 Hortonworks 2.3 的数据,其余 12 个引用 Azure blog 存储。For example, SQL could have 30 external tables with 7 of them referencing data on Hortonworks 2.1 on Linux, 4 on Hortonworks 2.2 on Linux, 7 on Hortonworks 2.3 on Linux, and the other 12 referencing Azure blob storage.

--Configure external tables to reference data on Hortonworks 2.1, 2.2, and 2.3 on Linux, and Azure blob storage  
sp_configure @configname = 'hadoop connectivity', @configvalue = 7;  

另请参阅See Also

sp_configure (Transact-SQL) sp_configure (Transact-SQL)