Compartir a través de

通过 Synapse SQL 使用外部表

  • Artículo

外部表指向位于 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 池中创建外部表：

  1. CREATE EXTERNAL DATA SOURCE 以引用外部 Azure 存储并指定应当用来访问该存储的凭据。
  2. CREATE EXTERNAL FILE FORMAT 以描述 CSV 或 Parquet 文件的格式。
  3. 基于以相同文件格式放置在数据源上的文件来 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 的示例

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

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

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

externaltable2

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

externaltable3

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

externaltable4

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

externaltable5

后续步骤

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