CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)

项目
02/01/2023

适用于： SQL Server 2022 (16.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例 Analytics Platform System (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 兼容的对象存储。

语法

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。

适用于：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 时，数据库将停止从外部数据文件导入行。每隔一段时间计算失败行的百分比。
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)。

列选项

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
在 Azure Synapse Analytics 和 Analytics Platform System 中，写入权限能读写 Hadoop 集群上或 Blob 存储中的外部文件夹。
在 SQL Server 2022 (16.x) 中，还需要对外部位置设置适当的权限。写入权限可将数据输出到该位置，而读取访问权限可访问该位置。
对于 Azure Blob 存储和 Azure Data Lake Gen2，必须授予 SHARED ACCESS SIGNATURE 令牌对容器的以下特权：读取、写入、创建。

重要

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 无服务器 SQL 池的外部表。若要重用已用于存储数据的位置，则必须在 ADLS 上手动删除该位置。

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

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

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

字符错误

数据中存在的以下字符可能会导致错误，包括拒绝使用 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。

锁定

采用 SCHEMARESOLUTION 对象上的共享锁。

示例

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 新外部表，该表使用 AdventureWorks2019 数据库的表 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 AdventureWorks2019.[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

CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)

语法

参数

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

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

LOCATION

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

REJECT 选项

WITH common_table_expression

SELECT <select_criteria>

权限

错误处理。

注解

限制和局限

字符错误

锁定

示例

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

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

C. 使用 CETAS 更改列属性

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

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

后续步骤

其他资源

其他资源