在 Azure Synapse Analytics 中使用 SQL 池创建和使用本机外部表

项目
08/14/2024

本部分介绍如何在 Synapse SQL 池中创建和使用本机外部表。与在外部数据源定义中包含 TYPE=HADOOP 的外部表相比，本机外部表的性能更好。这是因为，本机外部表使用本机代码来访问外部数据。

当你想要控制对 Synapse SQL 池中外部数据的访问时，外部表非常有用。如果你想要将 Power BI 等工具与 Synapse SQL 池结合使用，外部表也很有用。外部表可以访问两种类型的存储：

公用存储，用户可访问其中的公用存储文件。
受保护存储，其中用户使用 SAS 凭据、Microsoft Entra 标识或 Synapse 工作区的托管标识来访问存储文件。

注意

在专用 SQL 池中，只能使用 Parquet 文件类型的本机外部表，此功能以公共预览版提供。如果你想要在专用 SQL 池中使用正式版 Parquet 读取器功能，或者需要访问 CSV 或 ORC 文件，请使用 Hadoop 外部表。无服务器 SQL 池中的本机外部表已推出正式版。请在通过 Synapse SQL 使用外部表中详细了解本机外部表与 Hadoop 外部表的差别。

下表列出了支持的数据格式：

数据格式（本机外部表）	无服务器 SQL 池	专用 SQL 池
Parquet	是 (GA)	是（公共预览版）
CSV	是	否（或者，使用 Hadoop 外部表）
delta	是	否
Spark	是	否
Dataverse	是	否
Azure Cosmos DB 数据格式（JSON、BSON 等）	否（或者，创建视图）	否

先决条件

第一步是创建将在其中创建表的数据库。创建数据库范围凭据之前，数据库必须具有主密钥用于保护凭据。有关详细信息，请参阅 CREATE MASTER KEY (Transact-SQL)。然后创建要在此示例中使用的以下对象：

DATABASE SCOPED CREDENTIAL sqlondemand，可以访问受 SAS 保护的 https://sqlondemandstorage.blob.core.windows.net Azure 存储帐户。

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'

EXTERNAL DATA SOURCE sqlondemanddemo，引用了使用 SAS 密钥保护的演示存储帐户；以及 EXTERNAL DATA SOURCE nyctlc，引用了位置 https://azureopendatastorage.blob.core.windows.net/nyctlc/ 上公开可用的 Azure 存储帐户。

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

文件格式 QuotedCSVWithHeaderFormat 和 ParquetFormat，描述 CSV 和 parquet 文件类型。

CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

本文中的查询将在示例数据库上执行，并使用这些对象。

基于某个文件的外部表

可以创建用于访问 Azure 存储帐户中的数据的外部表，该帐户允许具有某些 Microsoft Entra 标识或 SAS 密钥的用户进行访问。创建外部表的方式可以与创建常规 SQL Server 外部表的方式相同。

下面的查询将创建一个外部表，该表读取 SynapseSQL 演示 Azure 存储帐户中的 population.csv 文件，该帐户使用 sqlondemanddemo 数据源来进行引用，并受名为 sqlondemand 的数据库范围的凭据保护。

数据源和数据库范围的凭据在安装脚本中创建。

注意

更改查询中的第一行（即 [mydbname]），以便使用你创建的数据库。

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

本机 CSV 表目前仅在无服务器 SQL 池中可用。

基于文件集的外部表

可以创建从 Azure 存储中的一组文件读取数据的外部表：

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

可以指定文件必须符合哪种模式才能被外部表引用。只需为 Parquet 和 CSV 表指定模式。如果使用 Delta Lake 格式，则只需指定一个根文件夹，外部表将自动查找模式。

注意

表是基于分区文件夹结构创建的，但你无法利用某种形式的分区消除。如果你想要通过跳过不满足某些条件（例如，本例中的特定年份或月份）的文件来获得更好的性能，请使用基于外部数据的视图。

可追加文件的外部表

查询运行时不应更改外部表引用的文件。在长时间运行的查询中，SQL 池可以重试读取、读取部分文件，甚至多次读取文件。如果对文件内容进行更改，会导致错误的结果。因此，如果在查询执行过程中检测到任何文件的修改时间发生了更改，SQL 池将导致查询失败。在某些情况下，需要在不断追加的文件上创建一个表。为避免由于不断追加文件而导致查询失败，可以使用 TABLE_OPTIONS 设置指定外部表应忽略潜在的不一致读取操作。

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

ALLOW_INCONSISTENT_READS 读取选项将在查询生命周期期间禁用文件修改时间检查，并读取外部表引用的文件中的任何可用内容。在可追加文件中，不更新现有内容，仅添加新行。因此，与可更新文件相比，这样做生成错误结果的可能性最低。使用此选项，可以在不处理错误的情况下读取经常追加的文件。

此选项仅在以 CSV 文件格式创建的外部表中可用。

注意

顾名思义，该选项表示表的创建者接受结果可能不一致的风险。在可追加文件中，如果通过自联接表强制多次读取基础文件，则可能会得到不正确的结果。在大多数“经典”查询中，外部表仅忽略在查询运行时追加的某些行。

Delta Lake 外部表

可以在 Delta Lake 文件夹的顶层创建外部表。基于单个文件或文件集创建的外部表，与基于 Delta Lake 格式创建的外部表的唯一差别在于，在 Delta Lake 外部表中，需要引用包含 Delta Lake 结构的文件夹。

ECDC COVID-19 Delta Lake folder

下面是基于 Delta Lake 文件夹创建的表定义示例：

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

无法基于分区文件夹创建外部表。查看 Synapse 无服务器 SQL 池自助页上的其他已知问题。

已分区文件夹中的 Delta 表

无服务器 SQL 池中的外部表不支持 Delta Lake 格式的分区。如果已将 Delta Lake 数据集分区，请使用 Delta 分区视图，而不要使用表。

重要

不要在已分区的 Delta Lake 文件夹中创建外部表，即使你发现这些表在某些情况下可正常工作。在已分区的 Delta 文件夹中使用不受支持的功能（例如外部表）可能会导致无服务器池出现问题或不稳定。如果无服务器池在已分区的文件夹中使用表，Azure 支持将无法解决任何问题。在继续解决问题之前，你需要转换为 Delta 已分区视图并重写代码，以仅使用受支持的功能。

使用外部表

可以在查询中使用外部表，其方式与在 SQL Server 查询中使用外部表的方式相同。

以下查询演示了如何使用已在上一部分中创建的 population 外部表。它按降序返回国家/地区名称及其 2019 年的人口。

注意