polybase配置 sql_访问外部数据：SQL Server - PolyBase - SQL Server | Microsoft Docs

配置 PolyBase 以访问 SQL Server 中的外部数据Configure PolyBase to access external data in SQL Server

10/06/2020

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

本文介绍如何使用 SQL Server 实例上的 PolyBase 来查询另一个 SQL Server 实例中的外部数据。This article explains how to use PolyBase on a SQL Server instance to query external data in another SQL Server instance.

先决条件Prerequisites

如果尚未安装 PolyBase，请参阅 PolyBase 安装。If you haven't installed PolyBase, see PolyBase installation. 这篇安装文章介绍了安装的先决条件。The installation article explains the prerequisites. Once installed, also be sure to enable PolyBase.

SQL Server 外部数据源使用 SQL 身份验证。The SQL Server external data source uses SQL Authentication.

在创建数据库范围凭据之前，必须先创建主密钥。Before creating a database scoped credential a Master Key must be created.

配置 SQL Server 外部数据源Configure a SQL Server external data source

若要查询 SQL Server 数据源中的数据，必须创建外部表以引用外部数据。To query the data from a SQL Server data source, you must create external tables to reference the external data. 本节提供用于创建这些外部表的示例代码。This section provides sample code to create these external tables.

为了获得最佳查询性能，请在外部表列上创建统计信息，尤其是用于联接、筛选和聚合的表列。For optimal query performance, create statistics on external table columns, especially for the ones used for joins, filters, and aggregates.

此部分中使用了以下 Transact-SQL 命令：The following Transact-SQL commands are used in this section:

创建数据库范围凭据以访问 SQL Server 源。Create a database scoped credential for accessing the SQL Server source. 下面的示例使用 IDENTITY = 'username' 和 SECRET = 'password' 创建外部数据源的凭据。The following example creates a credential to the external data source with IDENTITY = 'username' and SECRET = 'password'.

CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials

WITH IDENTITY = 'username', SECRET = 'password';

重要

用于 PolyBase 的 SQL ODBC 连接器仅支持基本身份验证，不支持 Kerberos 身份验证。The SQL ODBC Connector for PolyBase only supports basic authentication, not Kerberos authentication.

Create an external data source with CREATE EXTERNAL DATA SOURCE. 如下示例中：The following example:

创建名为 SQLServerInstance 的外部数据源。Creates an external data source named SQLServerInstance.

标识外部数据源 (LOCATION = '://[:]')。Identifies the external data source (LOCATION = '://[:]'). 在示例中，它指向 SQL Server 的默认实例。In the example it points to a default instance of SQL Server.

标识是否应将计算推送到源 (PUSHDOWN)。Identifies whether computation should be pushed to the source (PUSHDOWN). PUSHDOWN 默认设置为 ON。PUSHDOWN is ON by default.

最后，该示例使用先前创建的凭据。Finally, the example uses the credential created previously.

CREATE EXTERNAL DATA SOURCE SQLServerInstance

WITH ( LOCATION = 'sqlserver://SqlServer',

PUSHDOWN = ON,

CREDENTIAL = SQLServerCredentials);

也可在外部表上创建统计信息。Optionally, create statistics on an external table.

为了获得最佳查询性能，请在外部表列上创建统计信息，尤其是用于联接、筛选和聚合的表列。For optimal query performance, create statistics on external table columns, especially the ones used for joins filters and aggregates.

CREATE STATISTICS statistics_name ON customer (C_CUSTKEY)

WITH FULLSCAN;

重要

创建外部数据源后，可以使用 CREATE EXTERNAL TABLE 命令在该数据源上创建可查询的表。Once you have created an external data source, you can use the CREATE EXTERNAL TABLE command to create a queryable table over that source.

SQL Server 连接器兼容类型SQL Server connector compatible types

可以连接到可识别 SQL Server 连接的其他数据源。You can make a connection to other data sources that recognizes a SQL Server connection. 使用 SQL Server PolyBase 连接器创建 Azure Synapse Analytics 和 Azure SQL 数据库的外部表。Use the SQL Server PolyBase connector to create an external table of both Azure Synapse Analytics and Azure SQL Database. 若要完成此任务，请执行前面列出的相同步骤。To accomplish this task, follow the same steps listed previously. 确保数据库作用域凭据、服务器地址、端口和位置字符串与要连接的兼容数据源的相应内容相关联。Make sure the database scoped credential, server address, port, and location string correlate to that of the compatible data source you want to connect to.

后续步骤Next steps

若要了解有关 PolyBase 的详细信息，请参阅 SQL Server PolyBase 的概述。To learn more about PolyBase, see Overview of SQL Server PolyBase.