通过 Synapse SQL 使用外部表

项目
08/14/2024

外部表指向位于 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)、Microsoft Entra 直通、托管标识、自定义应用程序 Microsoft Entra 标识	共享访问签名 (SAS)、Microsoft Entra 直通、托管标识、自定义应用程序 Microsoft Entra 标识。
列映射	序号 - 外部表定义中的列按位置映射到基础 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 存储上的文件 - 有关示例，请参阅开发存储文件存储访问控制一文。

以下示例在专用 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 的示例

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

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 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