通过 Synapse SQL 使用外部表

外部表指向位于 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)中,并且 yearmonthday 的值作为列公开,则包含筛选器(例如 year=2020)的查询将只读取放置在 year=2020 文件夹的子文件夹中的文件。 在此查询中,将忽略放置在其他文件夹(year=2021year=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 外部表