CREATE EXTERNAL TABLE (Transact-SQL)

创建外部表。

本文提供所选任何 SQL 产品的语法、参数、注解、权限和示例。

有关语法约定的详细信息，请参阅 Transact-SQL 语法约定。

选择一个产品

在下面的行中，选择你感兴趣的产品名称，系统将只显示该产品的信息。

* SQL Server *  

SQL 数据库

Azure SQL 托管实例

Azure Synapse
分析

Analytics Platform
System (PDW)

 

概述：SQL Server

此命令为 PolyBase 创建一个外部表，以访问存储在 Hadoop 群集或 Azure Blob 存储中的数据（引用存储在 Hadoop 群集或 Azure Blob 存储中的数据的 PolyBase 外部表）。

适用于：SQL Server 2016 或更高版本

使用带有外部数据源的外部表进行 PolyBase 查询。 外部数据源用于建立连接以及支持以下这些用例：

• 使用 PolyBase 执行数据虚拟化和数据加载
• 使用 BULK INSERT 或 OPENROWSET 通过 SQL Server 或 SQL 数据库进行批量加载操作

另请参阅 CREATE EXTERNAL DATA SOURCE 和 DROP EXTERNAL TABLE。

语法

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

参数

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

要创建的表的一到三部分名称。 对于外部表，SQL 仅存储表元数据以及有关 Hadoop 或 Azure Blob 存储中引用的文件或文件夹的基本统计信息。 在 SQL Server 中不移动或存储任何实际数据。

重要

为了获得最佳性能，如果外部数据源驱动程序支持由三部分组成的名称，则强烈建议提供由三部分组成的名称。

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE 支持配置列名、数据类型、为 Null 性和排序规则的功能。 不能对外部表使用 DEFAULT CONSTRAINT。

列定义（包括数据类型和列数）必须与外部文件中的数据匹配。 如果存在不匹配，则在查询实际数据时会拒绝文件行。

LOCATION = 'folder_or_filepath'

为 Hadoop 或 Azure Blob 存储中的实际数据指定文件夹或文件路径和文件名。 此外，从 SQL Server 2022 (16.x) 开始，支持 S3 兼容对象存储。 位置从根文件夹开始。 根文件夹是外部数据源中指定的数据位置。

在 SQL Server 中，如果路径和文件夹不存在，则 CREATE EXTERNAL TABLE 语句会进行创建。 然后，可使用 INSERT INTO 将数据从本地 SQL Server 表导出到外部数据源。 有关详细信息，请参阅 PolyBase 查询。

如果将 LOCATION 指定为一个文件夹，则从外部表中进行选择的 PolyBase 查询会从该文件夹及其所有子文件夹中检索文件。 正如 Hadoop 一样，PolyBase 不返回隐藏文件夹。 它也不返回文件名以下划线 (_) 或句点 (.) 开头的文件。

在下图示例中，如果 LOCATION='/webdata/'，则 PolyBase 查询会从 mydata.txt 和 mydata2.txt 返回行。 它不会返回 mydata3.txt，因为它是隐藏子文件夹中的文件。 它不返回 _hidden.txt，因为这是隐藏文件。

若要更改默认值并且只从根文件夹进行读取，请在 core-site.xml 配置文件中将属性 <polybase.recursive.traversal> 设置为“false”。 此文件位于 SQL Server 的 bin 根的 <SqlBinRoot>\PolyBase\Hadoop\Conf 下。 例如，C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn。

DATA_SOURCE = external_data_source_name

指定包含外部数据位置的外部数据源的名称。 此位置是 Hadoop 文件系统 (HDFS)、Azure Blob 存储容器或 Azure Data Lake Store。 要创建外部数据源，请使用 CREATE EXTERNAL DATA SOURCE。

FILE_FORMAT = external_file_format_name

指定用于存储外部数据的文件类型和压缩方法的外部文件格式对象的名称。 若要创建外部文件格式，请使用 CREATE EXTERNAL FILE FORMAT。

外部文件格式可由多个类似的外部文件重复使用。

拒绝选项

此选项只能用于类型为 HADOOP 的外部数据源。

可以指定用于确定 PolyBase 如何处理它从外部数据源检索的脏记录的拒绝参数。 如果实际数据类型或列数与外部表的列定义不匹配，则数据记录被视为“脏”记录。

未指定或更改拒绝值时，PolyBase 会使用默认值。 使用 CREATE EXTERNAL TABLE 语句创建外部表时，有关拒绝参数的此信息会存储为附加元数据。 在将来的 SELECT 语句或 SELECT INTO SELECT 语句从外部表中选择数据时，PolyBase 会使用拒绝选项确定在实际查询失败之前可以拒绝的行数或行百分比。 查询会返回（部分）结果，直到超出拒绝阈值。 查询随后失败，并出现相应的错误消息。

REJECT_TYPE = value | percentage

说明 REJECT_VALUE 选项是指定为文本值还是百分比。

value

REJECT_VALUE 是文本值，而非百分比。 当拒绝的行数超过 reject_value 时，此查询会失败。

例如，如果 REJECT_VALUE = 5 且 REJECT_TYPE = value，则 SELECT 查询将在拒绝五行后失败。

percentage

REJECT_VALUE 是百分比，而非文本值。 当失败行的百分比超过 reject_value 时，查询会失败。 每隔一段时间计算失败行的百分比。

REJECT_VALUE = reject_value

指定在查询失败之前可以拒绝的行数的值或百分比。

对于 REJECT_TYPE = value，reject_value 必须是介于 0 与 2,147,483,647 之间的整数。

对于 REJECT_TYPE = percentage，reject_value 必须是介于 0 与 100 之间的浮点数。

REJECT_SAMPLE_VALUE = reject_sample_value

当指定 REJECT_TYPE = percentage 时，此属性是必需的。 它确定在 PolyBase 重新计算拒绝的行的百分比之前要尝试检索的行数。

Reject_sample_value 参数必须是介于 0 与 2,147,483,647 之间的整数。

例如，如果 REJECT_SAMPLE_VALUE = 1000，则 PolyBase 会在尝试从外部数据文件导入 1000 行后计算失败行的百分比。 如果失败行的百分比小于 reject_value，则 PolyBase 会尝试检索另外 1000 行。 它在尝试导入每个另外 1000 行后会继续重新计算失败行的百分比。

注意

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

示例：

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

• PolyBase 尝试检索前 100 行；25 行失败，75 行成功。
• 失败行的百分比计算结果为 25%，小于 30% 的拒绝值。 因此，PolyBase 会继续从外部数据源检索数据。
• PolyBase 尝试加载下一个 100 行；这次 25 行成功，75 行失败。
• 重新计算的失败行的百分比为 50%。 失败行的百分比已超过 30% 的拒绝值。
• 在尝试返回前 200 行之后，PolyBase 查询失败，拒绝的行为 50%。 请注意，匹配行在 PolyBase 查询检测到超过拒绝阈值之前已返回。

REJECTED_ROW_LOCATION = 目录位置

适用于：SQL Server 2019 CU6 及更高版本、Azure Synapse Analytics。

指定应该写入拒绝行和对应错误文件的外部数据源中的目录。

如果指定的路径不存在，PolyBase 将代你创建一个。 创建名称为“_rejectedrows”的子目录。 除非在位置参数中明确命名，否则，“_”字符将确保对该目录转义以进行其他数据处理。 在此目录中，有一个根据负载提交时间创建的文件夹，其格式为 YearMonthDay -HourMinuteSecond（例如 20230330-173205）。 在此文件夹中，将写入两种文件类型，_原因文件和数据文件。 此选项只能用于 TYPE = HADOOP 的外部数据源，以及使用 DELIMITEDTEXT FORMAT_TYPE 的外部表。 有关详细信息，请参阅 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

原因文件和数据文件均包含与 CTAS 语句关联的 queryID。 因为数据和原因位于单独的文件中，相应的文件具有匹配的后缀。

权限

需要以下用户权限：

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT（仅适用于 Hadoop 和 Azure 存储外部数据源）
• CONTROL DATABASE（仅适用于 Hadoop 和 Azure 存储外部数据源）

注意，在 CREATE EXTERNAL TABLE 命令中使用的 DATABASE SCOPED CREDENTIAL 中指定的远程登录必须对 LOCATION 参数中指定的外部数据源的路径/表/集合具有读取权限。 如果计划使用此 EXTERNAL TABLE 将数据导出到 Hadoop 或 Azure 存储外部数据源，则指定的登录名必须对 LOCATION 中指定的路径具有写入权限。 请注意，SQL Server 2022 (16.x) 当前不支持 Hadoop。

对于 Azure Blob 存储，在 Azure 门户、Azure Blob 存储或 ADLS Gen2 存储帐户中配置访问密钥和共享访问签名 (SAS) 时，将“允许的权限”配置为至少授予读取和写入权限。 跨文件夹搜索时可能还需要列表权限。 还必须同时选择“容器”和“对象”作为允许的资源类型。

重要

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

错误处理。

执行 CREATE EXTERNAL TABLE 语句时，PolyBase 会尝试连接到外部数据源。 如果连接尝试失败，则该语句会失败且不会创建外部表。 由于 PolyBase 在使查询最终失败之前会重新尝试连接，因此命令需要一分钟或更多时间才会失败。

注解

在即席查询方案（例如 SELECT FROM EXTERNAL TABLE）中，PolyBase 会将从外部数据源检索的行存储在临时表中。 查询完成之后，PolyBase 会移除并删除临时表。 任何永久数据都不会存储在 SQL 表中。

相反，在导入方案（例如 SELECT INTO FROM EXTERNAL TABLE）中，PolyBase 会将从外部数据源检索的行作为永久数据存储在 SQL 表中。 当 PolyBase 检索外部数据时，会在查询执行期间创建新表。

PolyBase 可以将某些查询计算推送到 Hadoop 以提高查询性能。 此操作称为谓词下推。 若要启用它，请在 CREATE EXTERNAL DATA SOURCE 中指定 Hadoop 资源管理器位置选项。

可以创建许多引用相同或不同外部数据源的外部表。

限制和局限

外部表的数据不受 SQL Server 直接控制，因此可随时通过外部进程更改或删除这些数据。 因此，针对外部表的查询结果不保证具有确定性。 相同查询可能会在每次针对外部表运行时返回不同结果。 同样，如果外部数据已移动或删除，则查询可能会失败。

可以创建各自引用不同外部数据源的多个外部表。 如果同时针对不同 Hadoop 数据源运行查询，则每个 Hadoop 源都必须使用相同的“hadoop 连接”服务器配置设置。 例如，不能同时针对 Cloudera Hadoop 群集和 Hortonworks Hadoop 群集运行查询，因为这些群集使用不同的配置设置。 有关配置设置和受支持的组合，请参阅 PolyBase 连接配置。

当外部表使用DELIMITEDTEXT、CSV、PARQUET 或 DELTA 作为数据类型时，外部表仅支持每个 CREATE STATISTICS 命令一列的统计信息。

外部表上仅允许使用以下这些数据定义语言 (DDL) 语句：

• CREATE TABLE 和 DROP TABLE
• CREATE STATISTICS 和 DROP STATISTICS
• CREATE VIEW 和 DROP VIEW

不支持构造和操作：

• 外部表列上的 DEFAULT 约束
• 删除、插入和更新的数据操作语言 (DML) 操作

查询限制

运行 32 个并发 PolyBase 查询时，每个文件夹中 PolyBase 最多可使用 33000 个文件。 此最大数量包括每个 HDFS 文件夹中的文件和子文件夹。 如果并发度小于 32，用户可以针对 HDFS 中包含超过 33000 个文件的文件夹运行 PolyBase 查询。 建议保持外部文件路径简短，并且每个 HDFS 文件夹不超过 30000 个文件。 当引用太多文件时，可能会发生 Java 虚拟机 (JVM) 内存不足异常。

表宽度限制

基于表定义中单个有效行的最大大小，SQL Server 2016 中的 PolyBase 具有 32 KB 的行宽限制。 如果列架构的总和大于 32 KB，则 PolyBase 无法查询数据。

数据类型限制

以下数据类型不能在 PolyBase 外部表中使用：

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• 任何用户定义类型

数据源特定限制

Oracle

不支持将 Oracle 同义词用于 PolyBase。

包含数组的 MongoDB 集合的外部表

若要创建包含数组的 MongoDB 集合的外部表，应使用 Azure Data Studio 的数据虚拟化扩展，基于 PolyBase ODBC Driver for MongoDB 检测到的架构生成 CREATE EXTERNAL TABLE 语句。 驱动程序会自动执行平展操作。 或者，可使用 sp_data_source_objects (Transact-SQL) 来检测集合架构（列）并手动创建外部表。 sp_data_source_table_columns 存储过程还通过 PolyBase ODBC Driver for MongoDB 驱动程序自动执行平展。 Azure Data Studio 的数据虚拟化扩展和 sp_data_source_table_columns 使用相同的内部存储过程来查询外部架构。

锁定

SCHEMARESOLUTION 对象上的共享锁。

安全性

外部表的数据文件存储在 Hadoop 或 Azure Blob 存储中。 这些数据文件由你自己的进程进行创建和管理。 由你负责管理外部数据的安全。

示例

A. 创建外部表，其中包含采用带分隔符的文本格式的数据

此示例演示创建包含采用带分隔符的文本文件设置格式的数据的外部表所需的所有步骤。 它定义外部数据源 mydatasource 和外部文件格式 myfileformat。 这些数据库级别对象随后会在 CREATE EXTERNAL TABLE 语句中进行引用。 有关详细信息，请参阅 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. 创建外部表，其中包含采用 RCFile 格式的数据

此示例演示创建包含格式为 RCFile 的数据的外部表所需的所有步骤。 它定义外部数据源 mydatasource_rc 和外部文件格式 myfileformat_rc。 这些数据库级别对象随后会在 CREATE EXTERNAL TABLE 语句中进行引用。 有关详细信息，请参阅 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. 创建外部表，其中包含采用 ORC 格式的数据

此示例演示创建包含格式为 ORC 的数据的外部表所需的所有步骤。 它定义外部数据源 mydatasource_orc 和外部文件格式 myfileformat_orc 。 这些数据库级别对象随后会在 CREATE EXTERNAL TABLE 语句中进行引用。 有关详细信息，请参阅 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. 查询 Hadoop 数据

ClickStream 是连接到 Hadoop 群集上带分隔符的文本文件 employee.tbl 的外部表。 下面的查询看上去如同针对标准表的查询。 但是，此查询从 Hadoop 检索数据，然后计算结果。

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

E. 将 Hadoop 数据与 SQL 数据联接

此查询看上去如同两个 SQL 表上的标准 JOIN。 区别在于，PolyBase 从 Hadoop 检索点击流数据，然后将数据联接到 UrlDescription 表。 一个表是外部表，另一个表是标准 SQL 表。

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. 将数据从 Hadoop 导入 SQL 表中

此示例创建新 SQL 表 ms_user，此表将永久存储在标准 SQL 表 user 和外部表 ClickStream 之间进行联接的结果。

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. 为 SQL Server 创建外部表

创建数据库范围凭据之前，用户数据库必须具有用于保护凭据的主密钥。 有关详细信息，请参阅 CREATE MASTER KEY 和 CREATE DATABASE SCOPED CREDENTIAL。

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

创建名为 SQLServerInstance 的新外部数据源，以及名为 sqlserver.customer 的外部表：

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     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
     );

I. 为 Oracle 创建外部表

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * 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 above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * 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 above.
   */
   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='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

J. 为 Teradata 创建外部表

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* 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 above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     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
     );

K. 为 MongoDB 创建外部表

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* 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
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     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='customer',
     DATA_SOURCE= external_data_source_name
     );

L. 通过外部表查询 S3 兼容对象存储

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

以下示例演示如何使用 T-SQL 通过查询外部表查询存储在 S3 兼容的对象存储中的 parquet 文件。 示例使用外部数据源中的相对路径。

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

后续步骤

通过以下文章详细了解相关概念：

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• sp_data_source_objects (Transact-SQL)
• Azure Data Studio 的数据虚拟化扩展