CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

适用于：SQL Server 2022（16.x）及更高版本的 Azure Synapse Analytics 平台系统（PDW）

创建一个外部表，然后并行导出 Transact-SQL SELECT 语句的结果。

• Azure Synapse Analytics 和 Analytics Platform System，支持 Hadoop 或 Azure Blob 存储。
• SQL Server 2022 (16.x) 及更高版本支持使用 CREATE EXTERNAL TABLE AS SELECT (CETAS) 来创建外部表，然后将 Transact-SQL SELECT 语句的结果并行导出到 Azure Data Lake Storage (ADLS) Gen2、Azure 存储帐户 V2 以及 S3 兼容的对象存储。

注意

适用于 Azure SQL 托管实例的 CETAS 的功能和安全性不同于 SQL Server 或 Azure Synapse Analytics。 有关详细信息，请参阅 CREATE EXTERNAL TABLE AS SELECT 的 Azure SQL 托管实例版本。

注意

Azure Synapse Analytics 中用于无服务器池的 CETAS 的功能和安全性不同于 SQL Server。 有关详细信息，请参阅将 CETAS 与 Synapse SQL 配合使用。

Transact-SQL 语法约定

语法

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

参数

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

要在数据库中创建的表的一到三个部分的名称。 对于外部表，关系数据库仅存储表元数据。

[ ( column_name [ ,...n ] ) ]

表列的名称。

LOCATION

适用于：Azure Synapse Analytics 和 Analytics Platform System

'hdfs_folder'**
在外部数据源上指定写入 SELECT 语句结果的位置。 位置是文件夹名称，可选择性地包含相对于 Hadoop 群集或 Blob 存储的根文件夹的路径。 如果尚未存在，PolyBase 将创建路径和文件夹。

外部文件写入 hdfs_folder 并命名为 QueryID_date_time_ID.format，其中 ID 是增量标识符，format 是导出的数据格式。 例如 QID776_20160130_182739_0.orc。

LOCATION 必须指向一个文件夹并具有尾随 /，例如 aggregated_data/。

适用于：SQL Server 2022 (16.x) 及更高版本

prefix://path[:port] 提供 SELECT 语句结果将写入的外部数据源的连接协议（前缀）、路径和端口（可选）。

如果目标为符合 S3 标准的对象存储，则必须先存在存储桶，但 PolyBase 可以在必要时创建子文件夹。 SQL Server 2022 (16.x) 支持 Azure Data Lake Storage Gen2、Azure 存储帐户 V2 和符合 S3 标准的对象存储。 目前不支持 ORC 文件。

DATA_SOURCE = external_data_source_name

指定包含存储或将存储外部数据位置的外部数据源对象的名称。 位置是 Hadoop 群集或 Azure Blob 存储。 若要创建外部数据源，请使用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)。

FILE_FORMAT = external_file_format_name

指定包含外部数据文件格式的外部文件格式对象的名称。 若要创建外部文件格式，请使用 CREATE EXTERNAL FILE FORMAT (Transact-SQL)。

REJECT 选项

运行 CREATE EXTERNAL TABLE AS SELECT 语句时，REJECT 选项不适用。 而是在此处指定，以便数据库稍后可从外部表导入数据时使用它们。 稍后，当 CREATE TABLE AS SELECT 语句从外部表中选择数据时，数据库将使用拒绝选项以确定在停止导入前无法导入的行的数量或百分比。

• REJECT_VALUE = reject_value

  指定在数据库暂停导入前无法导入的行的值或百分比。

• REJECT_TYPE = value | percentage

  阐明 REJECT_VALUE 选项是文本值还是百分比。

  • value

    当 REJECT_VALUE 是文本值而非百分比时使用。 当失败行数超过 reject_value 时，数据库将停止从外部数据文件导入行。

    例如，如果 REJECT_VALUE = 5 且 REJECT_TYPE = value，数据库将在导入五行失败后停止导入行。

  • percentage

    当 REJECT_VALUE 是百分比而非文本值时使用。 当失败行的百分比超过 reject_value 时，数据库将停止从外部数据文件导入行。 每隔一段时间计算失败行的百分比。 当 TYPE=HADOOP 时，仅在专用 SQL 池中有效。

• REJECT_SAMPLE_VALUE = reject_sample_value

  当 REJECT_TYPE = percentage 时，需要此选项。 指定在数据库重新计算失败行的百分比之前尝试导入的行数。

  例如，如果 REJECT_SAMPLE_VALUE = 1000，数据库将在尝试从外部数据文件导入 1000 行后计算失败行的百分比。 如果失败行的百分比小于 reject_value，数据库将尝试加载另外 1000 行。 数据库在尝试导入每个其他 1000 行后继续重新计算失败行的百分比。

  注意

  由于数据库按间隔计算失败行的百分比，因此失败行的实际百分比可能会超过 reject_value。

  示例：

  此示例演示三个 REJECT 选项相互之间如何交互。 例如，如果 REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100，则可能会出现以下情况：

  • 数据库尝试加载前 100 行，其中 25 行失败，75 行成功。
  • 失败行的百分比计算结果为 25%，小于 30% 的拒绝值。 因此，无需暂停加载。
  • 数据库尝试加载下一个 100 行。 这次 25 行成功，75 行失败。
  • 重新计算的失败行的百分比为 50%。 失败行的百分比已超过 30% 的拒绝值。
  • 尝试加载 200 行后，加载失败的行数为 50%，大于指定的 30% 限制。

WITH common_table_expression

指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。 有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)

SELECT <select_criteria>

使用 SELECT 语句的结果填充新表。 select_criteria 是 SELECT 语句的主体，用于确定将哪些数据复制到新表中。 有关 SELECT 语句的信息，请参阅 SELECT (Transact-SQL)。

注意

SELECT 中的 ORDER BY 子句对 CETAS 没有影响。

列选项

• column_name [ ,...n ]

  列名不允许 CREATE TABLE 中提及的列选项。 相反，可以为新表提供包含一个或多个列名的可选列表。 新表中的列使用指定的名称。 指定列名时，列表中的列数必须与所选结果中的列数相匹配。 如果未指定任何列名，新的目标表将使用 select 语句结果中的列名。

  不能指定任何其他列选项，例如数据类型、排序规则或为 Null 性。 每个属性都派生自 SELECT 语句的结果。 但可以使用 SELECT 语句来更改属性。 有关示例，请参阅使用 CETAS 更改列属性。

权限

若要运行此命令，数据库用户需要所有这些权限或成员身份：

• 本地架构上的 ALTER SCHEMA 权限，将包含 db_ddladmin 固定数据库角色中的新表或成员身份 。
• CREATE TABLE 权限或 db_ddladmin 固定数据库角色的成员身份 。
• select_criteria 中引用的任何对象的 SELECT 权限。

登录名需要所有这些权限：

• ADMINISTER BULK OPERATIONS
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT
• 通常需要具有列出文件夹内容和向 LOCATION 文件夹写入内容的权限才能使用 CETAS。
• 在 Azure Synapse Analytics 和 Analytics Platform System 中，写入权限能读写 Hadoop 集群上或 Azure Blob 存储中的外部文件夹。
• 在 SQL Server 2022 (16.x) 中，还需要对外部位置设置适当的权限。 写入权限可将数据输出到该位置，而读取访问权限可访问该位置。
• 对于 Azure Blob 存储和 Azure Data Lake Gen2，必须授予 SHARED ACCESS SIGNATURE 令牌对容器的以下特权：读取、写入、列出、创建。
• 对于 Azure Blob 存储，必须选中 Allowed Services: Blob 复选框才能生成 SAS 令牌。
• 对于 Azure Data Lake Gen2，必须选中 Allowed Services: Container 和 Object 复选框才能生成 SAS 令牌。

重要

ALTER ANY EXTERNAL DATA SOURCE 权限授予任何主体创建和修改任何外部数据源对象的能力，因此，它还授予访问数据库上所有数据库作用域凭据的能力。 必须将此权限视为高度特权，并且必须仅授予系统中受信任的主体。

错误处理。

当 CREATE EXTERNAL TABLE AS SELECT 将数据导出到文本分隔文件时，没有无法导出的行的拒绝文件。

创建外部表时，数据库将尝试连接到外部位置。 如果连接失败，该命令将失败且不会创建外部表。 由于数据库重新尝试连接至少三次，因此需要一分钟或更多时间命令才会失败。

如果 CREATE EXTERNAL TABLE AS SELECT 取消或失败，数据库将一次性尝试删除已在外部数据源上创建的任何新文件和文件夹。

在 Azure Synapse Analytics 和 Analytics Platform System 中，数据库将报告数据导出期间在外部数据源上发生的任何 Java 错误。

注解

在 CREATE EXTERNAL TABLE AS SELECT 语句结束后，可在外部表上运行 Transact-SQL 查询。 除非使用 CREATE TABLE AS SELECT 语句进行导入，否则这些操作将在查询期间将数据导入数据库。

外部表名和定义存储在数据库元数据中。 数据存储在外部数据源中。

即使已对源表进行分区，CREATE EXTERNAL TABLE AS SELECT 语句也会始终创建一个非分区表。

对于 SQL Server 2022 (16.x)，必须使用 sp_configure 启用选项 allow polybase export。 有关详细信息，请参阅设置 allow polybase export 配置选项。

对于 Azure Synapse Analytics 和 Analytics Platform System 中使用 EXPLAIN 创建的的查询计划，数据库对外部表使用以下查询计划操作：外部随机移动、外部广播移动、外部分区移动。

在 Analytics Platform System 中，作为创建外部表的先决条件，设备管理员需要配置 hadoop 连接。 有关详细信息，请参阅 Analytics Platform System 文档中的“配置与外部数据的连接 (Analytics Platform System)”，可从 Microsoft 下载中心下载该文档。

限制和局限

由于外部表数据驻留在数据库之外，所以备份和还原操作仅对存储在数据库中的数据进行。 因此，仅备份和还原元数据。

还原包含外部表的数据库备份时，数据库不会验证与外部数据源的连接。 如果原始源不可访问，外部表的元数据仍会成功还原，但外部表上的 SELECT 操作将失败。

数据库不保证数据库与外部数据之间的数据一致性。 客户全权负责维护外部数据和数据库之间的一致性。

外部表上不支持数据操作语言 (DML) 操作。 例如，不能使用 Transact-SQL 更新、插入或删除 Transact-SQL 语句来修改外部数据。

CREATE TABLE、DROP TABLE、CREATE STATISTICS、DROP STATISTICS、CREATE VIEW 和 DROP VIEW 是外部表中允许的唯一数据定义语言 (DDL) 操作。

Azure Synapse Analytics 的限制与局限

• 在 Azure Synapse Analytics 专用 SQL 池和 Analytics Platform System 中，PolyBase 在运行 32 个并发 PolyBase 查询时，可以在每个文件夹中最多使用 33,000 个文件。 此最大数量包括每个 HDFS 文件夹中的文件和子文件夹。 如果并发度小于 32，用户可以针对 HDFS 中包含超过 33,000 个文件的文件夹运行 PolyBase 查询。 我们建议 Hadoop 和 PolyBase 的用户保持文件路径简短，并且每个 HDFS 文件夹不超过 30,000 个文件。 当引用太多文件时，会发生 JVM 内存不足异常。

• 在无服务器 SQL 池中，无法在当前包含数据的位置创建外部表。 若要重用已用于存储数据的位置，则必须在 ADLS 上手动删除该位置。 有关限制和最佳做法的更多信息，请参阅筛选器优化最佳做法。

在 Azure Synapse Analytics 专用 SQL 池和 Analytics Platform System 中，当 CREATE EXTERNAL TABLE AS SELECT 从 RCFile 中进行选择时，RCFile 中的列值不得包含管道 (|) 字符。

SET ROWCOUNT (Transact-SQL) 对 CREATE EXTERNAL TABLE AS SELECT 没有影响。 要实现类似的行为，请使用 TOP (Transact-SQL)。

有关文件名限制，请查看命名和引用容器、Blob 和元数据。

字符错误

数据中存在的以下字符可能会导致错误，包括拒绝使用 CREATE EXTERNAL TABLE AS SELECT 记录到 Parquet 文件。

在 Azure Synapse Analytics 和 Analytics Platform System 中，这也适用于 ORC 文件。

• |
• "（引号字符）
• \r\n
• \r
• \n

若要使用包含这些字符的 CREATE EXTERNAL TABLE AS SELECT，必须先运行 CREATE EXTERNAL TABLE AS SELECT 语句，将数据导出到带分隔符的文本文件，然后可使用外部工具将其转换为 Parquet 或 ORC。

使用 parquet

使用 parquet 文件时，CREATE EXTERNAL TABLE AS SELECT 将为每个可用的 CPU 生成一个 parquet 文件，直到配置的最大并行度 (MAXDOP)。 每个文件可以增长到 190 GB，之后 SQL Server 会根据需要生成更多的 Parquet 文件。

查询提示 OPTION (MAXDOP n) 只会影响 CREATE EXTERNAL TABLE AS SELECT 的 SELECT 部分，不会影响 parquet 文件的数量。 仅考虑数据库级 MAXDOP 和实例级 MAXDOP。

锁定

采用 SCHEMARESOLUTION 对象上的共享锁。

支持的数据类型

CETAS 可用于存储包含以下 SQL 数据类型的结果集：

• binary
• varbinary
• char
• varchar
• nchar
• nvarchar
• smalldate
• date
• datetime
• datetime2
• datetimeoffset
• time
• Decimal
• numeric
• FLOAT
• real
• bigint
• tinyint
• smallint
• int
• bigint
• bit
• money
• smallmoney

示例

A. 使用 CREATE EXTERNAL TABLE AS SELECT 创建 Hadoop 表

适用于：Azure Synapse Analytics 和 Analytics Platform System

以下示例使用源表 dimCustomer 中的列定义和数据创建一个名为 hdfsCustomer 的新外部表。

表定义存储在数据库中，并且 SELECT 语句的结果将导出到 Hadoop 外部数据源 customer_ds 上的 /pdwdata/customer.tbl 文件。 该文件根据外部文件格式 customer_ff 设置格式。

文件名由数据库生成，并包含查询 ID，便于保持文件与生成该文件的查询的一致性。

“客户”目录前的路径 hdfs://xxx.xxx.xxx.xxx:5000/files/ 必须已经存在。 如果 Customer 目录不存在，数据库将创建该目录。

注意

此示例指定为 5000。 如果未指定端口，数据库将使用 8020 作为默认端口。

生成的 Hadoop 位置和文件名将为 hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt.。

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. 通过 CREATE EXTERNAL TABLE AS SELECT 使用查询提示

适用于：Azure Synapse Analytics 和 Analytics Platform System

此查询显示在 CREATE EXTERNAL TABLE AS SELECT 语句中使用查询联接提示的基本语法。 提交查询后，数据库使用哈希联接策略生成查询计划。 有关联接提示以及如何使用 OPTION 子句的详细信息，请参阅 OPTION 子句 (Transact-SQL)。

注意

此示例指定为 5000。 如果未指定端口，数据库将使用 8020 作为默认端口。

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. 使用 CETAS 更改列属性

适用于：Azure Synapse Analytics 和 Analytics Platform System

本示例使用 CETAS 更改 FactInternetSales 表中多个列的数据类型、为 Null 性和排序规则。

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. 使用 CREATE EXTERNAL TABLE AS SELECT 将数据导出为 parquet

适用于：SQL Server 2022 (16.x)

以下示例创建了一个名为 ext_sales 新外部表，该表使用 AdventureWorks2022 的表 SalesOrderDetail 中的数据。 必须启用允许 PolyBase 导出配置选项。

SELECT 语句的结果将保存在以前配置并命名为 s3_eds 的符合 S3 标准的对象存储上，并将正确的凭据创建为 s3_dsc。 parquet 文件位置将为 <ip>:<port>/cetas/sales.parquet，其中 cetas 是以前创建的存储桶。

注意

增量格式目前仅支持作为只读格式。

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. 从增量表到 parquet 使用 CREATE EXTERNAL TABLE AS SELECT

适用于：SQL Server 2022 (16.x)

以下示例创建了一个新外部表 Delta_to_Parquet，该表使用符合 S3 标准的对象存储 s3_delta 中的增量表类型的数据，并将结果写入另一个数据源 s3_parquet 中作为 parquet 文件。 为此，此示例使用 OPENROWSET 命令。 必须启用允许 PolyBase 导出配置选项。

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. 使用 CREATE EXTERNAL TABLE AS SELECT，将视图作为源

适用于：Azure Synapse Analytics 无服务器 SQL 池和专用 SQL 池。

在此示例中，我们可以看到一个模板代码示例，该模板代码使用用户定义的视图作为源编写 CETAS，使用托管标识作为身份验证，以及 wasbs:。

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. 使用 CREATE EXTERNAL TABLE AS SELECT，将视图作为源

适用于：Azure Synapse Analytics 无服务器 SQL 池和专用 SQL 池。

在此示例中，我们可以看到一个模板代码示例，该模板代码使用用户定义的视图作为源编写 CETAS，使用托管标识作为身份验证，以及 https:。

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

后续步骤

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics, Parallel Data Warehouse)
• CREATE EXTERNAL TABLE AS SELECT (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)