你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

通过 Synapse SQL 使用外部表

项目
03/25/2023

外部表指向位于 Hadoop、Azure 存储 Blob 或 Azure Data Lake Storage 中的数据。外部表用于读取文件中的数据，或将数据写入 Azure 存储中的文件。借助 Synapse SQL，可以使用外部表通过专用 SQL 池或无服务器 SQL 池读取和写入数据。

根据外部数据源的类型，可以使用两种类型的外部表：

Hadoop 外部表，可用于读取和导出各种数据格式（例如 CSV、Parquet 和 ORC）的数据。 Hadoop 外部表可在专用 SQL 池中使用，但无法在无服务器 SQL 池中使用。
原生外部表，可用于读取和导出各种数据格式（如 CSV 和 Parquet）的数据。原生外部表可在无服务器 SQL 池中使用，在专用 Synapse SQL 池中为公共预览版。使用 CETAS 和原生外部表编写/导出数据仅在无服务器 SQL 池中适用，但不适用于专用 SQL 池。

下表列出了 Hadoop 和本机外部表之间的主要区别：

外部表类型	Hadoop	本机
专用 SQL 池	可用	仅 Parquet 表以公共预览版提供。
无服务器 SQL 池	不可用	可用
支持的格式	带分隔符/CSV、Parquet、ORC、Hive RC 和 RC	无服务器 SQL 池：带分隔符/CSV、Parquet 和 Delta Lake 专用 SQL 池：Parquet（预览版）
文件夹分区清除	否	分区消除仅适用于根据从 Apache Spark 池同步的 Parquet 或 CSV 格式创建的已分区表。可以在 Parquet 已分区文件夹中创建外部表，但分区列将不可访问且被忽略，而分区消除将不会应用。不要在 Delta Lake 文件夹中创建外部表，因为它们不受支持。如果需要查询已分区的 Delta Lake 数据，请使用 Delta 已分区视图。
文件消除（谓词下推）	否	在无服务器 SQL 池中为“是”。对于字符串下推，需要对 `VARCHAR` 列使用 `Latin1_General_100_BIN2_UTF8` 排序规则以启用下推。有关排序规则的详细信息，请参阅 Synapse SQL 支持的排序规则类型。
适用于位置的自定义格式	否	是，对 Parquet 或 CSV 格式使用通配符，例如 `/year=/month=/day=`。自定义文件夹路径在 Delta Lake 中不可用。在无服务器 SQL 池中，还可以使用递归通配符 `/logs/*` 来引用被引用文件夹下任何子文件夹中的 Parquet 或 CSV 文件。
递归文件夹扫描	是	是的。在无服务器 SQL 池中，必须在位置路径末尾指定 `/**`。在专用池中，文件夹始终以递归方式扫描。
存储身份验证	存储访问密钥 (SAK)、Azure Active Directory 直通、托管标识、自定义应用程序 Azure Active Directory 标识	共享访问签名 (SAS)、Azure Active Directory 直通、托管标识、自定义应用程序 Azure AD 标识。
列映射	序号 - 外部表定义中的列按位置映射到基础 Parquet 文件中的列。	无服务器池：按名称。外部表定义中的列按列名匹配映射到基础 Parquet 文件中的列。专用池：序号匹配。外部表定义中的列按位置映射到基础 Parquet 文件中的列。
CETAS（导出/转换）	是	以原生表为目标的 CETAS 仅适用于无服务器 SQL 池。不能使用专用 SQL 池通过原生表导出数据。

注意

原生外部表是池中推荐使用的解决方案（这些表在池中正式发布）。如果需要访问外部数据，请始终使用无服务器池中的原生表。在专用池中，你应切换到原生表，以便在 Parquet 文件正式发布后对其进行读取。仅当需要访问原生外部表中不支持的某些类型（例如 ORC、RC）时或原生版本不可用时，才使用 Hadoop 表。

专用 SQL 池和无服务器 SQL 池中的外部表

可以使用外部表来执行以下操作：

使用 Transact-SQL 语句查询 Azure Blob 存储和 Azure Data Lake Gen2。
使用 CETAS 将查询结果存储到 Azure Blob 存储或 Azure Data Lake Storage 中的文件。
从 Azure Blob 存储和 Azure Data Lake Storage 导入数据并将其存储到专用 SQL 池中（仅限专用池中的 Hadoop 表）。

注意

与 CREATE TABLE AS SELECT 语句结合使用时，从外部表中选择数据可将数据导入到专用 SQL 池中的表。

如果专用池中的 Hadoop 外部表的性能无法满足性能目标，请考虑使用 COPY 语句将外部数据加载到数据仓库表中。

有关加载操作的教程，请参阅使用 PolyBase 从 Azure Blob 存储加载数据。

可通过以下步骤在 Synapse SQL 池中创建外部表：

CREATE EXTERNAL DATA SOURCE 以引用外部 Azure 存储并指定应当用来访问该存储的凭据。
CREATE EXTERNAL FILE FORMAT 以描述 CSV 或 Parquet 文件的格式。
基于以相同文件格式放置在数据源上的文件来 CREATE EXTERNAL TABLE。

文件夹分区清除

Synapse 池中的原生外部表可以忽略放置在与查询无关的文件夹中的文件。如果文件存储在文件夹层次结构（例如 - /year=2020/month=03/day=16）中，并且 year、month 和 day 的值作为列公开，则包含筛选器（例如 year=2020）的查询将只读取放置在 year=2020 文件夹的子文件夹中的文件。在此查询中，将忽略放置在其他文件夹（year=2021 或 year=2022）中的文件和文件夹。此消除称为“分区消除”。

在从 Synapse Spark 池同步的原生外部表中提供了文件夹分区消除。如果已对数据集进行分区，但想要将分区消除用于所创建的外部表，请使用分区视图而不是外部表。

文件消除

某些数据格式（例如 Parquet 和 Delta）包含每列的文件统计信息（例如，每列的最小/最大值）。筛选数据的查询不会读取所需列值不存在于其中的文件。查询将首先浏览查询谓词中使用的列的最小/最大值，以查找不包含所需数据的文件。将忽略这些文件并将其从查询计划中消除。此方法也称为筛选器谓词下推，它可以提高查询的性能。 Parquet 和 Delta 格式的无服务器 SQL 池中提供筛选器下推。若要对字符串类型使用筛选器下推，请将 VARCHAR 类型与 Latin1_General_100_BIN2_UTF8 排序规则结合使用。有关排序规则的详细信息，请参阅 Synapse SQL 支持的排序规则类型。

安全性

用户必须具有对外部表的 SELECT 权限才能读取数据。外部表使用数据库范围的凭据访问基础 Azure 存储，这些凭据使用以下规则在数据源中定义：

没有凭据的数据源使外部表可访问 Azure 存储上公开可用的文件。
数据源可能有一个凭据，外部表可以使用该凭据通过 SAS 令牌或工作区托管标识仅访问 Azure 存储上的文件 - 有关示例，请参阅开发存储文件存储访问控制一文。

CREATE EXTERNAL DATA SOURCE

外部数据源用于连接到存储帐户。有关详细信息，请参阅 CREATE EXTERNAL DATA SOURCE。

使用 TYPE=HADOOP 的外部数据源仅在专用 SQL 池中可用。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

没有 TYPE=HADOOP 限制的外部数据源通常可在无服务器 SQL 池中使用，在专用池中为公共预览版。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
)
[;]

CREATE EXTERNAL DATA SOURCE 的参数

data_source_name

指定数据源的用户定义名称。该名称在数据库中必须唯一。

位置

LOCATION = '<prefix>://<path>' - 提供连接协议和外部数据源的路径。以下模式可用于位置：

外部数据源	位置前缀	位置路径
Azure Blob 存储	`wasb[s]`	`<container>@<storage_account>.blob.core.windows.net`
Azure Blob 存储	`http[s]`	`<storage_account>.blob.core.windows.net/<container>/subfolders`
Azure Data Lake Store Gen 1	`http[s]`	`<storage_account>.azuredatalakestore.net/webhdfs/v1`
Azure Data Lake Store Gen 2	`http[s]`	`<storage_account>.dfs.core.windows.net/<container>/subfolders`

https: 前缀允许使用路径中的子文件夹。

凭据

CREDENTIAL = <database scoped credential> 是可选凭据，用于在 Azure 存储上进行身份验证。无凭据的外部数据源可以访问公共存储帐户，也可以使用调用方的 Azure AD 标识访问存储中的文件。

在专用 SQL 池中，数据库范围的凭据可以指定自定义应用程序标识、工作区托管标识或 SAK 密钥。
在无服务器 SQL 池中，数据库范围的凭据可以指定工作区托管标识或 SAK 密钥。

TYPE

选项 TYPE = HADOOP 用于指定应使用基于 Java 的技术来访问基础文件。此参数不能用于使用内置原生读取器的无服务器 SQL 池。

CREATE EXTERNAL DATA SOURCE 的示例

Hadoop
本机

以下示例在专用 SQL 池中为 Azure Data Lake Gen2 创建一个指向 New York 数据集的 Hadoop 外部数据源：

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

以下示例为 Azure Data Lake Gen2 创建一个指向公开可用的 New York 数据集的外部数据源：

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

以下示例在无服务器或专用 SQL 池中为 Azure Data Lake Gen2 创建一个允许使用 SAS 凭据进行访问的外部数据源：

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

注意

SQL 用户需要对数据库范围内的凭据具有适当的权限，才能访问 Azure Synapse Analytics 无服务器 SQL 池中的数据源。在 Azure Synapse Analytics 中使用无服务器 SQL 池访问外部存储。

以下示例为 Azure Data Lake Gen2 创建一个指向公开可用的 New York 数据集的外部数据源：

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

CREATE EXTERNAL FILE FORMAT

创建一个外部文件格式对象，用于定义存储在 Azure Blob 存储或 Azure Data Lake Storage 中的外部数据。创建外部文件格式是创建外部表的先决条件。此处提供了完整文档。

创建外部文件格式会指定外部表引用的数据的实际布局。

CREATE EXTERNAL FILE FORMAT 的语法

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
    [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  

--Create an external file format for DELIMITED TEXT files
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    );  

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter
    | FIRST_ROW = integer
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | ENCODING = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}
}

CREATE EXTERNAL FILE FORMAT 的参数

file_format_name - 指定外部文件格式的名称。

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT] - 指定外部数据的格式。

PARQUET - 指定 Parquet 格式。
DELIMITEDTEXT - 指定具有列分隔符（也称为字段终止符）的文本格式。

FIELD_TERMINATOR = field_terminator - 仅适用于带分隔符的文本文件。字段终止符指定一个或多个字符，用于在带分隔符的文本文件中标记每个字段（列）的末尾。默认为竖线字符（“|”）。

示例：

FIELD_TERMINATOR = '|'
FIELD_TERMINATOR = ' '
FIELD_TERMINATOR = ꞌ\tꞌ

STRING_DELIMITER = string_delimiter - 为带分隔符的文本文件中的字符串类型数据指定字段终止符。字符串分隔符的长度是一个或多个字符，并且用单引号括起来。默认为空字符串 ("")。

示例：

STRING_DELIMITER = '"'
STRING_DELIMITER = '*'
STRING_DELIMITER = ꞌ,ꞌ

FIRST_ROW = First_row_int - 指定首先要读取的行号，此值将应用于所有文件。如果将值设置为 2，则在加载数据时，会跳过每个文件中的第一行（标题行）。会基于行终止符（/r/n、/r、/n）的存在跳过行。

USE_TYPE_DEFAULT = { TRUE | FALSE } - 指定在从文本文件中检索数据时，如何处理带分隔符的文本文件中缺失的值。

注意

请注意，FORMAT_TYPE = DELIMITEDTEXT、PARSER_VERSION = '2.0' 不支持 USE_TYPE_DEFAULT=true。

TRUE - 如果从文本文件中检索数据，则使用外部表定义中对应列的默认值数据类型来存储每个缺失值。例如，将缺失值替换为：

如果列定义为数字列，则替换为 0。不支持小数列，它们会导致错误。
如果列是字符串列，则替换为空字符串 ("")。
如果列是日期列，则为“1900-01-01”。

FALSE - 将所有缺失值作为 NULL 存储。在带分隔符的文本文件中使用 NULL 一词存储的任何 NULL 值都会作为字符串“NULL”导入。

Encoding = {'UTF8' | 'UTF16'} - 无服务器 SQL 池可以读取 UTF8 和 UTF16 编码的带分隔符的文本文件。

DATA_COMPRESSION = data_compression_method - 此参数为外部数据指定数据压缩方法。

PARQUET 文件格式类型支持以下压缩方法：

DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

从 PARQUET 外部表进行读取时，会忽略此参数，但在使用 CETAS 写入外部表时会使用此参数。

DELIMITEDTEXT 文件格式类型支持以下压缩方法：

DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

PARSER_VERSION = 'parser_version' 指定读取 CSV 文件时要使用的分析器版本。可用的分析器版本为 1.0 和 2.0。此选项仅适用于无服务器 SQL 池。

CREATE EXTERNAL FILE FORMAT 的示例

以下示例为人口普查文件创建外部文件格式：

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

CREATE EXTERNAL TABLE

CREATE EXTERNAL TABLE 命令为 Synapse SQL 创建一个外部表，用于访问 Azure Blob 存储或 Azure Data Lake Storage 中存储的数据。

CREATE 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
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]
        [, <reject_options> [ ,...n ] ] 
    )
[;] 

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

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

CREATE EXTERNAL TABLE 的参数

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

要创建的表的一到三部分名称。对于外部表，Synapse SQL 池仅存储表元数据。在 Synapse SQL 数据库中不移动或存储任何实际数据。

<column_definition>, ...< ]

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

重要

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

从 Parquet 文件读取数据时，可以仅指定所要读取的列，并跳过其余的列。

LOCATION = 'folder_or_filepath'

为 Azure Blob 存储中的实际数据指定文件夹或文件路径和文件名。位置从根文件夹开始。根文件夹是外部数据源中指定的数据位置。

外部表的递归数据

与 Hadoop 外部表不同的是，除非在路径末尾指定 /**，否则本机外部表不返回子文件夹。在此示例中，如果 LOCATION='/webdata/'，则无服务器 SQL 池查询将返回 mydata.txt 中的行。它不返回 mydata2.txt 和 mydata3.txt，因为这些文件位于子文件夹中。 Hadoop 表将返回任何子文件夹中的所有文件。

Hadoop 和本机外部表均会跳过名称以下划线 (_) 或句点 (.) 开头的文件。

DATA_SOURCE = external_data_source_name

指定包含外部数据位置的外部数据源的名称。要创建外部数据源，请使用 CREATE EXTERNAL DATA SOURCE。

FILE_FORMAT = external_file_format_name

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

拒绝选项

注意

已拒绝行功能目前以公共预览版提供。请注意，已拒绝行功能适用于带分隔符的文本文件和 PARSER_VERSION 1.0。

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

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

REJECT_TYPE = value

这是目前唯一支持的值。阐明将 REJECT_VALUE 选项指定为文本值。

value

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

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

REJECT_VALUE = reject_value

指定在查询失败之前可以拒绝的行数。

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

REJECTED_ROW_LOCATION = 目录位置

指定应该写入拒绝行和对应错误文件的外部数据源中的目录。如果指定的路径不存在，服务将代表你创建一个。创建名称为“rejectedrows”的子目录。除非在位置参数中明确命名，否则，“ ”字符将确保对该目录转义以进行其他数据处理。在此目录中，存在根据负载提交时间创建的文件夹，格式为 YearMonthDay_HourMinuteSecond_StatementID（例如 20180330-173205-559EE7D2-196D-400A-806D-3BF5D007F891）。可以使用语句 ID 将文件夹与生成它的查询相关联。在此文件夹中，将写入两个文件：error.json 文件和数据文件。

error.json 文件包含遇到拒绝的行相关错误的 json 数组。表示错误的每个元素都包含以下属性：

Attribute	说明
错误	拒绝行的原因。
行	文件中拒绝的行序号。
列	拒绝的列序号。
值	拒绝的列值。如果值大于 100 个字符，则只显示前 100 个字符。
文件	指向包含行的文件路径。

TABLE_OPTIONS

TABLE_OPTIONS = json 选项 - 指定描述如何读取基础文件的选项集。目前，唯一可用的选项是 "READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]，它指示外部表忽略对基础文件进行的更新，即使这可能导致一些不一致的读取操作。仅在经常追加文件的特殊情况下使用此选项。此选项在 CSV 格式的无服务器 SQL 池中可用。

CREATE EXTERNAL TABLE 的权限

若要从某个外部表中进行选择，需要提供拥有列出和读取权限的适当凭据。

CREATE EXTERNAL TABLE 示例

以下示例创建一个外部表。它返回第一行：

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

从 Azure Data Lake 中的文件创建和查询外部表

现在，可以使用 Synapse Studio 的 Data Lake 浏览功能，通过简单的右键单击文件操作，使用 Synapse SQL 池创建和查询外部表。仅 Parquet 文件支持通过单击手势从 ADLS Gen2 存储帐户创建外部表。

先决条件

你必须有权访问工作区，并且至少具有 Storage Blob Data Contributor 访问角色（拥有对 ADLS Gen2 帐户或访问控制列表 (ACL) 的访问权限），然后才能查询这些文件。
必须至少拥有在 Synapse SQL 池（专用或无服务器）中创建和查询外部表的权限。

在“数据”面板中，选择要从其创建外部表的文件：

externaltable1

此时会打开一个对话框窗口。选择“专用 SQL 池”或“无服务器 SQL 池”，为表命名，然后选择“打开脚本”：

externaltable2

系统会从该文件推理架构并自动生成 SQL 脚本：

externaltable3

运行该脚本。该脚本将自动运行 Select Top 100 *.：

externaltable4

现已创建外部表，将来若要浏览此外部表的内容，用户可以直接从“数据”窗格查询：

externaltable5

后续步骤

查看 CETAS 一文，了解如何将查询结果保存到 Azure 存储中的外部表。或者可以开始查询 Apache Spark for Azure Synapse 外部表。