PolyBase Transact-SQL 参考

适用于: SQL Server(所有受支持的版本)

本文回顾了使用 PolyBase 查询各种外部数据源的就地外部数据(称为数据虚拟化)的选项。

若要使用 PolyBase,必须创建外部表来引用外部数据。 请参阅:

注意

若要使用 PolyBase,必须对数据库具有 sysadmin 或 CONTROL SERVER 级别的权限。

有关创建各种外部数据源的详细信息和教程,请参阅:

先决条件

若要开始,请在 WindowsLinux 中的 SQL Server上安装 PolyBase,然后查看 PolyBase 配置

为 Hadoop 创建外部表

适用于:SQL Server 2016 (13.x) 及更高版本,Analytics Platform System (PDW)

1. 创建数据库范围的凭据

仅当为 Kerberos 保护的 Hadoop 群集时,才必须执行这一步操作。

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

-- Create a database master key on the database if one does not already exist.  
-- Required to encrypt the credential secret.  
  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';  

然后,为 Kerberos 保护的 Hadoop 群集创建数据库范围的凭据。 IDENTITY 是 Kerberos 用户名,是 SECRET Kerberos 密码。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

-- Create a database scoped credential for Kerberos-secured Hadoop clusters.  
-- IDENTITY: the Kerberos user name.  
-- SECRET: the Kerberos password  
  
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1   
WITH IDENTITY = '<hadoop_user_name>', Secret = '<hadoop_password>';  

2. 创建外部数据源

创建外部数据源。

  • LOCATION 是必需的,并且是 Hadoop 名称节点 IP 地址和端口。 请注意,前缀LOCATION的选项因Azure SQL的不同版本的SQL Server和平台而异,请始终引用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)
  • RESOURCE MANAGER LOCATION 是可选项。 这是用于启用下推计算的 Hadoop 资源管理器 位置。
  • CREDENTIAL 是在上一步中创建的数据库作用域凭据。
-- Create an external data source.  
-- LOCATION (Required) : Hadoop Name Node IP address and port.  
-- RESOURCE MANAGER LOCATION (Optional): Hadoop Resource Manager location to enable pushdown computation.  
-- CREDENTIAL (Optional):  the database scoped credential, created in the previous step.  
  
CREATE EXTERNAL DATA SOURCE MyHadoopCluster WITH (  
        TYPE = HADOOP,   
        LOCATION ='hdfs://10.xxx.xx.xxx:xxxx',   
        RESOURCE_MANAGER_LOCATION = '10.xxx.xx.xxx:xxxx',   
        CREDENTIAL = HadoopUser1      
);  

3.创建外部文件格式

创建外部文件格式,其中 FORMAT_TYPE 在 Hadoop(如 DELIMITEDTEXTRCFILEORCPARQUET)中创建格式。

-- Create an external file format.  
-- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).  
  
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
        FORMAT_TYPE = DELIMITEDTEXT,   
        FORMAT_OPTIONS (FIELD_TERMINATOR ='|',   
                USE_TYPE_DEFAULT = TRUE)  
  

4. 创建外部表

创建指向 Hadoop 中存储的数据的外部表,其中 LOCATION 文件或目录的路径包含相对于 HDFS 根目录的数据。

-- Create an external table pointing to data stored in Hadoop.  
-- LOCATION: path to file or directory that contains the data (relative to HDFS root).  
  
CREATE EXTERNAL TABLE [dbo].[CarSensor_Data] (  
        [SensorKey] int NOT NULL,   
        [CustomerKey] int NOT NULL,   
        [GeographyKey] int NULL,   
        [Speed] float NOT NULL,   
        [YearMeasured] int NOT NULL  
)  
WITH (LOCATION='/Demo/',   
        DATA_SOURCE = MyHadoopCluster,  
        FILE_FORMAT = TextFileFormat  
);  

5. 创建统计信息

最后,在新的外部表上手动创建统计信息对象。

-- Create statistics on an external table.   
CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  

为 Azure blob 存储创建外部表

适用于: SQL Server 2016 (13.x) 和更高版本。

有关详细信息,请参阅配置 PolyBase 以访问Azure Blob 存储中的外部数据

1. 创建数据库范围的凭据

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

-- Create a database master key on the database if one does not already exist.  
-- Required to encrypt the credential secret.  
  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';  

然后,创建数据库范围的凭据。 IDENTITY 在这种情况下是任何字符串,因为此字符串不用于对 Azure 存储进行身份验证。 SECRET 是 Azure 存储帐户密钥。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

-- Create a database scoped credential  for Azure blob storage.  
-- IDENTITY: any string (this is not used for authentication to Azure storage).  
-- SECRET: your Azure storage account key.  
  
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential   
WITH IDENTITY = 'user', Secret = '<azure_storage_account_key>';  

2. 创建外部数据源

  • LOCATION 是必需的,并且是 Azure 帐户存储帐户名称和 Blob 容器名称。 请注意,Azure SQLTYPE中不同版本的SQL Server和平台中的选项和LOCATION前缀各不相同,请始终参考 CREATE EXTERNAL DATA SOURCE (Transact-SQL)
  • CREDENTIAL 是在上一步中创建的数据库作用域凭据。

若要在 SQL Server 2022 (16.x) 预览版之前使用 PolyBase 连接到 ADLS Gen2,请使用abfsswasbs

-- LOCATION:  Azure account storage account name and blob container name.  
-- CREDENTIAL: The database scoped credential created in the previous step.  
  
CREATE EXTERNAL DATA SOURCE AzureStorage with (  
        TYPE = BLOB_STORAGE,   
        LOCATION ='wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',  
        CREDENTIAL = AzureStorageCredential  
);  
  

从 SQL Server 2022 (16.x) 预览版中的 PolyBase 开始,请使用 ADLS Gen2 的新前缀adls

-- For ADLS Gen 2 in SQL Server 2022 and later - Create an external data source
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH
  ( LOCATION = 'abfss://daily@logs.dfs.core.windows.net/' ,
    CREDENTIAL = AzureStorageCredential ,
    TYPE = HADOOP
  );

3.创建外部文件格式

-- Create an external file format.  
-- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).  
  
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
        FORMAT_TYPE = DELIMITEDTEXT,   
        FORMAT_OPTIONS (FIELD_TERMINATOR ='|',   
                USE_TYPE_DEFAULT = TRUE)  
  

4. 创建外部表

-- Create an external table pointing to data stored in Azure storage.  
-- LOCATION: path to a file or directory that contains the data (relative to the blob container).  
-- To point to all files under the blob container, use LOCATION='/'   
  
CREATE EXTERNAL TABLE [dbo].[CarSensor_Data] (  
        [SensorKey] int NOT NULL,   
        [CustomerKey] int NOT NULL,   
        [GeographyKey] int NULL,   
        [Speed] float NOT NULL,   
        [YearMeasured] int NOT NULL  
)  
WITH (LOCATION='/Demo/',   
        DATA_SOURCE = AzureStorage,  
        FILE_FORMAT = TextFileFormat  
);  

5. 创建统计信息

最后,在新的外部表上手动创建统计信息对象。

-- Create statistics on an external table.   
CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed);

为 Azure Data Lake Store 创建外部表

适用于:Azure Synapse Analytics、Analytics Platform System (PDW)

有关详细信息,请参阅 使用 Azure Data Lake Store 加载

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

1. 创建数据库范围的凭据

-- Create a Database Master Key.
-- Only necessary if one does not already exist.
-- Required to encrypt the credential secret in the next step.

CREATE MASTER KEY;

然后,创建数据库范围的凭据。 IDENTITY 是 Azure Active Directory 应用程序中的客户端 ID 和 OAuth 2.0 令牌终结点令牌,用 a @分隔。 SECRET 是 AAD 应用程序服务主体密钥。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

-- Create a database scoped credential
-- IDENTITY: Pass the client id and OAuth 2.0 Token Endpoint token from your Azure Active Directory Application
-- SECRET: Provide your AAD Application Service Principal key.

CREATE DATABASE SCOPED CREDENTIAL ADL_User
WITH
    IDENTITY = '<client_id>@<OAuth_2.0_Token_EndPoint>'
    ,SECRET = '<key>'
;

2.创建外部数据源以引用 Azure Data Lake Store (ADLS) Gen 1 或 2

  • LOCATION 是必需的,并且是 Azure 帐户存储帐户名称和 Blob 容器名称。 请注意,Azure SQLTYPE中不同版本的SQL Server和平台中的选项和LOCATION前缀各不相同,请始终参考 CREATE EXTERNAL DATA SOURCE (Transact-SQL)
  • CREDENTIAL 是在上一步中创建的数据库作用域凭据。

连接到 ADLS Gen1:

-- For ADLS Gen1 - Create an external data source
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH 
  ( LOCATION = 'adl://<AzureDataLake account_name>.azuredatalakestore.net',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
  );

连接到 ADLS Gen2:

-- For ADLS Gen 2 - Create an external data source
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://<file-system-name>@<storage-account-name>.dfs.core.windows.net/' , 
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  );

3.创建外部文件格式

-- FIELD_TERMINATOR: Marks the end of each field (column) in a delimited text file
-- STRING_DELIMITER: Specifies the field terminator for data of type string in the text-delimited file.
-- DATE_FORMAT: Specifies a custom format for all date and time data that might appear in a delimited text file.
-- Use_Type_Default: Store all Missing values as NULL

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(   FORMAT_TYPE = DELIMITEDTEXT
,    FORMAT_OPTIONS    (   FIELD_TERMINATOR = '|'
                    ,    STRING_DELIMITER = ''
                    ,    DATE_FORMAT         = 'yyyy-MM-dd HH:mm:ss.fff'
                    ,    USE_TYPE_DEFAULT = FALSE
                    )
);

4. 创建外部表

-- LOCATION: Folder under the ADLS root folder.
-- DATA_SOURCE: Specifies which Data Source Object to use.
-- FILE_FORMAT: Specifies which File Format Object to use
-- REJECT_TYPE: Specifies how you want to deal with rejected rows. Either Value or percentage of the total
-- REJECT_VALUE: Sets the Reject value based on the reject type.

-- DimProduct
CREATE EXTERNAL TABLE [dbo].[DimProduct_external] (
    [ProductKey] [int] NOT NULL,
    [ProductLabel] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL
)
WITH
(
    LOCATION='/DimProduct/'
,   DATA_SOURCE = AzureDataLakeStore
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

5. 创建统计信息

最后,在新的外部表上手动创建统计信息对象。

CREATE STATISTICS StatsForProduct on DimProduct_external(ProductKey)  

为 SQL Server 创建外部表

有关详细信息和示例,请参阅配置 PolyBase 以访问SQL Server中的外部数据

1. 创建数据库范围的凭据

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

     -- Create a database master key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';  

然后,创建数据库范围的凭据。 IDENTITY是要向SQL Server实例进行身份验证的用户名,并且SECRET是密码。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

     --  IDENTITY: user name for external source.  
     --  SECRET: password for external source.

     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials   
     WITH IDENTITY = 'username', Secret = 'password';

2. 创建外部数据源

将外部数据源创建到其他SQL Server。

  • 本例sqlserver://servername中,位置应为<vendor>://<server>[:<port>]或。sqlserver://servername\instancesqlserver://servername:port 对于Azure SQL数据库,请使用 (FQDN) 等sqlserver://servername.database.windows.net完全限定的域名。 请注意,前缀LOCATION的选项因Azure SQL的不同版本的SQL Server和平台而异,请始终引用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)
  • 默认情况下,在 SQL Server 2019 (15.x) 及更高版本中,PolyBase 的 PUSHDOWN 为 ON。 指定是否应将计算向下推送到源。
  • CREDENTIAL 是在上一步中创建的数据库作用域凭据名称。
    -- LOCATION: Location string should be of format `<vendor>://<server>[:<port>]`.
    -- PUSHDOWN: specify whether computation should be pushed down to the source. ON by default. Applies to: SQL Server 2019 (15.x) and later.
    -- CREDENTIAL: the database scoped credential, created previously.
  
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH ( 
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );

3. 创建架构

若要区分数据库中的其他表,请为此外部数据源创建外部表的架构。

     CREATE SCHEMA sqlserver;
     GO

4. 创建外部表

     -- LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     -- DATA_SOURCE: the external data source, created in the previous step.
     
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

5. 创建统计信息

最后,在新的外部表上手动创建统计信息对象。

CREATE STATISTICS CustomerCustKeyStatistics ON sqlserver.customer (C_CUSTKEY) WITH FULLSCAN; 

为 Oracle 创建外部表

有关详细信息和示例,请参阅 配置 PolyBase 以访问 Oracle 中的外部数据

1. 创建数据库范围的凭据

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

 -- Create a database master key
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  

然后,创建数据库范围的凭据。 IDENTITY 是要向 Oracle 进行身份验证的用户名,是 SECRET 密码。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

   -- IDENTITY: user name for external source.  
   -- SECRET: password for external source.

   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

2. 创建外部数据源

创建到 Oracle 数据源的外部数据源。

  • 本例sqlserver://servername中,位置应为<vendor>://<server>[:<port>]或。sqlserver://servernamesqlserver://servername:port 请注意,前缀LOCATION的选项因Azure SQL的不同版本的SQL Server和平台而异,请始终引用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)
  • 默认情况下,在 SQL Server 2019 (15.x) 及更高版本中,PolyBase 的 PUSHDOWN 为 ON。 指定是否应将计算向下推送到源。
  • 应根据需要为 2019 SQL Server 2019 (15.x) 及更高版本指定CONNECTION_OPTIONS。 指定通过 ODBC 连接到外部数据源时的其他选项。 若要使用多个连接选项,请用分号分隔它们。
  • CREDENTIAL 是在上一步中创建的数据库作用域凭据名称。
   -- LOCATION: Location string should be of format `<vendor>://<server>[:<port>]`.
   -- PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   -- CONNECTION_OPTIONS: Specify driver location for PolyBase in SQL Server 2019 (15.x) and later.
   -- CREDENTIAL: the database scoped credential, created in the previous step.
     
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH ( 
   LOCATION = 'oracle://<server address>[:<port>]',
   -- PUSHDOWN = ON | OFF,
   CREDENTIAL = credential_name)

3. 创建外部表

创建外部表以使用 T-SQL 命令查询外部数据。

  • 位置:“<database_name>”中的 Oracle 表/视图。<>schema_name。<>object_name'格式。 请注意,这在 Oracle 数据库中可能区分大小写。
  • DATA_SOURCE:在上一步中创建的外部数据源。
   -- LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   -- DATA_SOURCE: the external data source, created in the previous step.

   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
   LOCATION='database_name.schema_name.customer',
   DATA_SOURCE=  external_data_source_name
   );

4. 创建统计信息

最后,在新的外部表上手动创建统计信息对象。

   CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 

为 Teradata 创建外部表

有关详细信息和示例,请参阅 配置 PolyBase 以访问 Teradata 中的外部数据

1. 创建数据库范围的凭据

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

  -- Create a database master key
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  

然后,创建数据库范围的凭据。 IDENTITY 是要向 Teradata 进行身份验证的用户名,是 SECRET 密码。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

   -- IDENTITY: user name for external source.  
   -- SECRET: password for external source.

   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

2. 创建外部数据源

请注意,前缀LOCATION的选项因Azure SQL的不同版本的SQL Server和平台而异,请始终引用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)

  • LOCATION 的格式应为 <vendor>://<server>[:<port>]
  • 默认情况下,在 SQL Server 2019 (15.x) 及更高版本中,PolyBase 的 PUSHDOWN 为 ON。 指定是否应将计算向下推送到源。
  • 应根据需要为 2019 SQL Server 2019 (15.x) 及更高版本指定CONNECTION_OPTIONS。 指定通过 ODBC 连接到外部数据源时的其他选项。 若要使用多个连接选项,请用分号分隔它们。
  • CREDENTIAL 是在上一步中创建的数据库作用域凭据名称。
    -- LOCATION: Location string should be of format `<vendor>://<server>[:<port>]`.
    -- PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    -- CONNECTION_OPTIONS: Specify driver location
    -- CREDENTIAL: the database scoped credential, created in the previous step.
  
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH ( 
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );

3. 创建外部表

  • 位置:“<database_name>”中的 Teradata 表/视图。<>schema_name。<>object_name'格式。
  • DATA_SOURCE:在上一步中创建的外部数据源。
     -- LOCATION: Teradata table/view in '<database_name>.<object_name>' format
     -- DATA_SOURCE: the external data source, created in the previous step.

     CREATE EXTERNAL TABLE customer(
     L_ORDERKEY INT NOT NULL,
     L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

4. 创建统计信息

最后,在新的外部表上手动创建统计信息对象。

      CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 

为 MongoDB 创建外部表

有关 MongoDB 的其他示例,请参阅 配置 PolyBase 以访问 MongoDB 中的外部数据

1. 创建数据库范围的凭据

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

  -- Create a database master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  

然后,创建数据库范围的凭据。 IDENTITY 是要向 MongoDB 进行身份验证的用户名,是 SECRET 密码。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

   -- IDENTITY: user name for external source.  
   -- SECRET: password for external source.

   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', SECRET = 'password';

2. 创建外部数据源

  • LOCATION 应为 mongodb://<server>[:<port>]. 请注意,前缀LOCATION的选项因Azure SQL的不同版本的SQL Server和平台而异,请始终引用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)
  • PUSHDOWN 指定是否应将计算向下推送到源。 默认情况下为 ON。
  • 应根据需要为 2019 SQL Server 2019 (15.x) 及更高版本指定CONNECTION_OPTIONS。 指定通过 ODBC 连接到外部数据源时的其他选项。 若要使用多个连接选项,请用分号分隔它们。
  • CREDENTIAL 是在上一步中创建的数据库作用域凭据。
   -- LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
   -- PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   -- CONNECTION_OPTIONS: Specify driver location for PolyBase in SQL Server 2019 (15.x) and later.
   -- CREDENTIAL: the database scoped credential, created in the previous step.
     
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
   LOCATION = mongodb://<server>[:<port>],
   -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name
   );

3. 创建外部表

如果 对 Azure Data Studio 使用 Data Virtualization 扩展,则可以跳过此步骤,因为会为你生成 CREATE EXTERNAL TABLE 语句,并且可以从那里进一步自定义。 若要手动提供架构,请考虑以下示例脚本来创建外部表。 有关详细信息,请参阅 配置 PolyBase 以访问 MongoDB 中的外部数据

  • LOCATION:'<database_name>中的 MongoDB 对象。<>table_name'格式。 请注意,不允许使用三部分名称。
  • DATA_SOURCE:在上一步中创建的外部数据源。
     -- LOCATION: MongoDB table/view in '<database_name>.<table_name>' format.
     -- DATA_SOURCE: the external data source, created in the previous step.

     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='MyDb.customer',
     DATA_SOURCE= external_data_source_name
     );

4. 创建统计信息

最后,在新的外部表上手动创建统计信息对象。

      CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 

为 CSV 创建外部表

适用于:SQL Server 2022 (16.x) 预览版及更高版本。

有关详细信息和示例,请参阅 使用 PolyBase 虚拟化 CSV 文件

1.创建主密钥和数据库范围的凭据

用户数据库中的数据库主密钥是加密数据库范围的凭据机密所必需的。 blob_storage

USE [CSV_Demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
CREATE DATABASE SCOPED CREDENTIAL blob_storage   
WITH IDENTITY = '<user_name>', Secret = '<password>';  

2. 创建外部数据源

数据库范围的凭据将用于外部数据源。 在此示例中,CSV 文件驻留在Azure Blob 存储中,因此请使用前缀absSHARED ACCESS SIGNATURE标识方法。 从 SQL Server 2022 (16.x) 预览版开始,为 Azure 存储帐户 v2 使用新前缀 abs。 有关连接器和前缀的详细信息,包括 SQL Server 2022 (16.x) 预览版的新设置,请参阅 CREATE EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage 
);

3.创建外部文件格式

必须定义列并强类型。 若要定义文件的格式,需要外部文件格式。 此外,还建议使用外部文件格式,因为可重用性。 在以下示例中,数据从第二行开始。

CREATE EXTERNAL FILE FORMAT csv_ff
WITH
(   FORMAT_TYPE = DELIMITEDTEXT
,   FORMAT_OPTIONS  (    FIELD_TERMINATOR = ','
                    ,    STRING_DELIMITER = '"'
                    ,    FIRST_ROW = 2 )
);

4. 创建外部表

  • LOCATION 是文件相对于外部数据源中位置的路径的文件路径 call_center.csv 。 In this case, the file lies in a subfolder called 2022.
  • DATA_SOURCE是外部数据源。
  • FILE_FORMAT是SQL Server中外部文件格式的路径csv_ff
CREATE EXTERNAL TABLE extCall_Center_csv
(
            cc_call_center_sk         integer             NOT NULL  ,
            cc_call_center_id         char(16)            NOT NULL  ,
            cc_rec_start_date         date                          ,
            cc_rec_end_date           date                          ,
            cc_closed_date_sk         integer                       ,
            cc_open_date_sk           integer                       ,
            cc_name                   varchar(50)                   ,
            cc_class                  varchar(50)                   ,
            cc_employees              integer                       ,
            cc_sq_ft                  integer                       ,
            cc_hours                  char(20)                      ,
            cc_manager                varchar(40)                   ,
            cc_mkt_id                 integer                       ,
            cc_mkt_class              char(50)                      ,
            cc_mkt_desc               varchar(100)                  ,
            cc_market_manager         varchar(MAX)                  ,
            cc_division               varchar(50)                   ,
            cc_division_name          varchar(50)                   ,
            cc_company                varchar(60)                   ,
            cc_company_name           char(50)                      ,
            cc_street_number          char(10)                      ,
            cc_street_name            varchar(60)                   ,
            cc_street_type            char(15)                      ,
            cc_suite_number           char(10)                      ,
            cc_city                   varchar(60)                   ,
            cc_county                 varchar(30)                   ,
            cc_state                  char(20)                      ,
            cc_zip                    char(20)                      ,
            cc_country                varchar(MAX)                  ,
            cc_gmt_offset             decimal(5,2)                  ,
            cc_tax_percentage         decimal(5,2) 
)
WITH
(
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV
    ,FILE_FORMAT = csv_ff
)
GO

为增量表创建外部表

适用于:SQL Server 2022 (16.x) 预览版及更高版本。

有关详细信息和示例,请参阅 使用 PolyBase 虚拟化增量表

1.创建主密钥和数据库范围的凭据

如果数据库尚不存在,请在数据库上创建数据库主密钥。 这是加密凭据密钥所必需的。

  -- Create a database master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  

然后,创建数据库范围的凭据。 对于此示例,增量表位于Azure Data Lake Storage Gen2上。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

   -- IDENTITY: user name for external source.  
   -- SECRET: password for external source.

   CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc   
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE', 
   SECRET = '<SAS Token>';  

2.创建外部数据源

数据库范围凭据将用于外部数据源。 在此示例中,Delta 表驻留在 Azure Data Lake Storage Gen2 中,因此请使用前缀 adlsSHARED ACCESS SIGNATURE 标识方法。 有关连接器和前缀的详细信息,包括 SQL Server 2022 (16.x) 预览版的新设置,请参阅 CREATE EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH
(
 LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net'
,CREDENTIAL = delta_storage_dsc 
);

例如,如果存储帐户命名为 delta_lake_sample 且容器命名为 sink,则代码会为:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH
(
 LOCATION = 'abs://sink@delta_lake_sample.dfs.core.windows.net'
,CREDENTIAL = delta_storage_dsc
)

3.创建外部文件格式

若要定义文件的格式设置,需要外部文件格式。 此外,还建议使用外部文件格式,因为可重用性。 有关详细信息,请参阅 CREATE EXTERNAL FILE FORMAT

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

4.创建外部表

增量表文件位于 /delta/Delta_yob/ 此示例的外部数据源中,是以前在数据源 s3_eds下配置的 S3 兼容对象存储。 PolyBase 可以使用 LOCATION 作为 delta 表文件夹或绝对文件本身,该文件位于 delta/Delta_yob/_delta_log/00000000000000000000.json

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta 
(      id int, 
       name VARCHAR(200),
       dob date
)WITH 
(     LOCATION = '/delta/Delta_yob/'
    , FILE_FORMAT = DeltaTableFormat
    , DATA_SOURCE = s3_eds
)
GO

后续步骤

有关查询示例,请参阅 PolyBase Queries

另请参阅