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 = 5REJECT_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: ContainerObject 复选框才能生成 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

后续步骤

适用于:Azure SQL 托管实例

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

可以使用 CREATE EXTERNAL TABLE AS SELECT (CETAS) 来完成以下任务:

  • 在 Azure Blob 存储或 Azure Data Lake Storage (ADLS) Gen2 中创建基于 Parquet 或 CSV 文件的外部表。
  • 将 T-SQL SELECT 语句的结果并行导出到创建的外部表中。
  • 有关 Azure SQL 托管实例的更多数据虚拟化功能,请参阅 Azure SQL 托管实例的数据虚拟化

注意

此内容仅适用于 Azure SQL 托管实例。 对于其他平台,请从拖放选择器中选择相应版本的 CREATE EXTERNAL TABLE AS SELECT

Transact-SQL 语法约定

语法

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

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

参数

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

要创建的表的一到三部分名称。 对于外部表,仅存储表元数据。 不会移动或存储任何实际数据。

LOCATION = 'path_to_folder'

在外部数据源上指定写入 SELECT 语句结果的位置。 根文件夹是外部数据源中指定的数据位置。 LOCATION 必须指向一个文件夹并具有尾随的 /。 示例:aggregated_data/

CETAS 的目标文件夹必须为空。 如果路径和文件夹尚不存在,则会自动创建它们。

DATA_SOURCE = external_data_source_name

指定外部数据源对象的名称,该对象包含外部数据的存储位置。 若要创建外部数据源,请使用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)

FILE_FORMAT = external_file_format_name

指定包含外部数据文件格式的外部文件格式对象的名称。 若要创建外部文件格式,请使用 CREATE EXTERNAL FILE FORMAT (Transact-SQL)。 目前仅支持 FORMAT_TYPE=PARQUET 和 FORMAT_TYPE=DELIMITEDTEXT 的外部文件格式。 不支持 DELIMITEDTEXT 格式的 GZip 压缩。

[, PARTITION ( column name [ , ...n ] ) ]

将输出数据分区为多个 parquet 文件路径。 分区按给定列 (column_name) 进行,将 LOCATION 中的通配符 (*) 与相应的分区列匹配。 PARTITION 部分中的列数必须与 LOCATION 中的通配符数匹配。 必须至少有一列不用于分区。

WITH <common_table_expression>

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

SELECT <select_criteria>

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

注意

CETAS 的 SELECT 不支持 ORDER BY 子句。

权限

存储中的权限

需要具有列出文件夹内容和向 LOCATION 路径写入内容的权限才能使用 CETAS。

支持的身份验证方法是托管标识或共享访问签名(SAS)令牌。

  • 如果使用托管标识进行身份验证,请确保 SQL 托管实例的服务主体在目标容器上具有存储 Blob 数据参与者的角色
  • 如果使用 SAS 令牌,则需要“读取”、“写入”和“列出”权限。
  • 对于 Azure Blob 存储,必须选中 Allowed Services: Blob 复选框才能生成 SAS 令牌。
  • 对于 Azure Data Lake Gen2,必须选中 Allowed Services: ContainerObject 复选框才能生成 SAS 令牌。

不支持用户分配的托管标识。 不支持Microsoft Entra 直通身份验证。 Microsoft Entra ID 为 (以前为 Azure Active Directory)。

SQL 托管实例中的权限

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

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

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

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT

重要

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

支持的数据类型

CETAS 存储包含以下 SQL 数据类型的结果集:

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

注意

大于 1MB 的 LOB 无法与 CETAS 一起使用。

限制和局限

  • 默认情况下禁用 Azure SQL 托管实例的 CREATE EXTERNAL TABLE AS SELECT (CETAS)。 有关详细信息,请参阅下一节,默认情况下禁用
  • 有关 Azure SQL 托管实例中数据虚拟化的限制或已知问题的详细信息,请参阅限制和已知问题

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

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

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

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

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

无法在当前包含数据的位置创建外部表。 若要重用已用于存储数据的位置,则必须在 ADLS 上手动删除该位置。

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

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

存储类型

文件可以存储在 Azure Data Lake Storage Gen2 或 Azure Blob 存储中。 若要查询文件,需要以特定格式提供位置,并使用与外部源和终结点/协议的类型相对应的位置类型前缀,如以下示例所示:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

重要

提供的位置类型前缀用于选择最佳通信协议并利用特定存储类型提供的任何高级功能。 禁止使用泛型 https:// 前缀。 始终使用特定于终结点的前缀。

默认情况下禁用

CREATE EXTERNAL TABLE AS SELECT (CETAS) 允许将数据从 SQL 托管实例导出到外部存储帐户,因此这些功能可能存在数据外泄风险。 因此,对于 Azure SQL 托管实例,CETAS 默认处于禁用状态。

启用 CETAS

Azure SQL 托管实例的 CETAS 只能通过需要提升的 Azure 权限的方法启用,而不能通过 T-SQL 启用。 由于存在未经授权的数据外泄风险,因此无法通过 sp_configure T-SQL 存储过程启用 CETAS,而是要求用户在 SQL 托管实例外部执行操作。

启用 CETAS 的权限

若要通过 Azure PowerShell 启用,运行命令的用户必须具有 SQL 托管实例的参与者SQL 安全管理器 Azure RBAC 角色。

也可以为此创建自定义角色,需要 Microsoft.Sql/managedInstances/serverConfigurationOptions 操作的“读取”和“写入”操作。

启用 CETAS 的方法

若要在计算机上调用 PowerShell 命令,必须在本地安装 Az 包版本 9.7.0 或更高版本。 或者,考虑使用 Azure Cloud Shellshell.azure.com 运行 Azure PowerShell。

首先,登录到 Azure 并为订阅设置适当的上下文:

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

若要管理服务器配置选项“allowPolybaseExport”,请将以下 PowerShell 脚本调整为订阅和 SQL 托管实例名称,然后运行命令。 有关详细信息,请参阅 Set-AzSqlServerConfigurationOptionGet-AzSqlServerConfigurationOption

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

禁用服务器配置选项“allowPolybaseExport”:

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

获取服务器配置选项“allowPolybaseExport”的当前值:

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

验证 CETAS 的状态

可以随时检查 CETAS 配置选项的当前状态。

连接到 SQL 托管实例。 运行以下 T-SQL 并观察响应的 value 列。 完成服务器配置更改后,此查询的结果应与所需的设置匹配。

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

疑难解答

有关 Azure SQL 托管实例中的数据虚拟化故障排除的更多步骤,请参阅故障排除。 Azure SQL 托管实例中 CETAS 的错误处理和常见错误消息如下所示。

错误处理。

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

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

常见错误消息

这些常见错误消息提供了 Azure SQL 托管实例的 CETAS 的快速说明。

  1. 指定存储中已存在的位置。

    解决方案:清除存储位置(包括快照),或更改查询中的位置参数。

    错误消息示例:Msg 15842: Cannot create external table. External table location already exists.

  2. 列值使用 JSON 对象进行了格式设置。

    解决方案:将值列转换为单个 VARCHAR 或 NVARCHAR 列,或者具有显式定义类型的一组列。

    错误消息示例:Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.

  3. 位置参数无效(例如多个 //)。

    解决方案:修复位置参数。

    错误消息示例:Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.

  4. 缺少所需选项之一(DATA_SOURCE、FILE_FORMAT、LOCATION)。

    解决方案:将缺少的参数添加到 CETAS 查询。

    错误消息示例:Msg 46505: Missing required external DDL option 'FILE_FORMAT'

  5. 访问出现问题(凭据无效、凭据过期或凭据权限不足)。 另一种可能的原因是路径无效,其中 SQL 托管实例从存储收到错误 404。

    解决方案:验证凭据的有效性和权限。 或者,验证路径是否有效且存储是否存在。 使用 URL 路径 adls://<container>@<storage_account>.blob.core.windows.net/<path>/

    错误消息示例:Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'

  6. DATA_SOURCE 的位置部分包含通配符。

    解决方案:从位置删除通配符。

    错误消息示例:Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.

  7. LOCATION 参数中的通配符数和分区列数不匹配。

    解决方案:确保 LOCATION 中的通配符数与分区列相同。

    错误消息示例:Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.

  8. PARTITION 子句中的列名与列表中的任何列都不匹配。

    解决方案:确保 PARTITION 中的列有效。

    错误消息示例:Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list

  9. PARTITION 列表中多次指定列。

    解决方案:确保 PARTITION 子句中的列是唯一的。

    错误消息示例:Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.

  10. 列在 PARTITION 列表中多次指定,或者与 SELECT 列表中的列都不匹配。

    解决方案:确保分区列表中没有重复项,并且 SELECT 部分中存在分区列。

    错误消息示例:Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter.Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.

  11. 正在 PARTITION 列表中使用所有列。

    解决方案:SELECT 部分中至少有一个列不得位于查询的 PARTITION 部分中。

    错误消息示例:Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.

  12. 功能已禁用。

    解决方案:使用本文中的默认情况下禁用部分启用该功能。

    错误消息示例:Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

锁定

采用 SCHEMARESOLUTION 对象上的共享锁。

示例

A. 使用带视图的 CETAS 创建外部表并使用托管标识进行身份验证

此示例提供使用系统托管标识身份验证将 CETAS 作为源编写 CETAS 的代码。

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 [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

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

B. 使用带视图的 CETAS 创建外部表并使用 SAS 令牌进行身份验证

下面是将带视图的 CETAS 编写为源并使用 SAS 令牌进行身份验证的代码示例。

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

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

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

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

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

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

C. 将一个外部表创建为存储上的单个 parquet 文件

接下来的两个示例演示如何将本地表的某些数据卸载到作为 parquet 文件存储在 Azure Blob 存储容器上的外部表中。 它们旨在与 AdventureWorks2022 数据库配合使用。 此示例演示如何将外部表创建为单个 parquet 文件,下一个示例演示如何创建外部表并将其分区到包含 parquet 文件的多个文件夹中。

以下示例使用托管标识进行身份验证。 因此,请确保 Azure SQL 托管实例服务主体对 Azure Blob 存储容器具有“存储 Blob 数据参与者”角色。 或者,可以修改示例并使用共享访问机密 (SAS) 令牌进行身份验证。

以下示例将外部表创建为 Azure Blob 存储中的单个 parquet 文件,从 SalesOrderHeader 表中选择 2014 年 1 月 1 日前的订单:

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

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

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

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

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D. 将一个分区的外部表创建为存储在文件夹树中的多个 parquet 文件

此示例以上一示例为基础,演示如何创建外部表并将其分区为包含 parquet 文件的多个文件夹。 如果数据集很大,可以使用分区表来获得性能优势。

使用示例 B 中的步骤基于 SalesOrderHeader 数据创建外部表,但按 OrderDate 年份和月份对外部表进行分区。 查询分区的外部表时,可以通过消除分区来提高性能。

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

后续步骤