在 Azure Synapse Analytics 中使用 SQL 集區建立和使用原生外部資料表

發行項
10/18/2023

在本節中，您將瞭解如何在 Synapse SQL 集區中建立和使用原生外部資料表。相較于外部資料表在其外部資料源定義中的外部資料表，原生外部資料表 TYPE=HADOOP 具有較佳的效能。這是因為原生外部資料表會使用原生程式碼來存取外部資料。

當您想要控制 Synapse SQL 集區中外部資料的存取權時，外部資料表很有用。如果您想要搭配 Synapse SQL 集區使用 Power BI 之類的工具，外部資料表也很有用。外部資料表可以存取兩種類型的儲存體：

使用者存取公用儲存體檔案的公用儲存體。
受保護的儲存體，讓使用者使用 SAS 認證、Microsoft Entra 身分識別或 Synapse 工作區的受控識別來存取儲存體檔案。

注意

在專用 SQL 集區中，您只能使用原生外部資料表搭配 Parquet 檔案類型，此功能處於 公開預覽狀態 。如果您想要在專用 SQL 集區中使用一般可用的 Parquet 讀取器功能，或需要存取 CSV 或 ORC 檔案，請使用 Hadoop 外部資料表。原生外部資料表在無伺服器 SQL 集區中正式推出。深入瞭解搭配 Synapse SQL 使用外部資料表中的原生和 Hadoop 外部資料表之間的差異。

下表列出支援的資料格式：

資料格式（原生外部資料表）	無伺服器 SQL 集區	專用 SQL 集區
Parquet	是（GA）	是 (公開預覽)
CSV	Yes	否（或者，使用 Hadoop 外部資料表）
delta	是	No
Spark	是	No
Dataverse	是	No
Azure Cosmos DB 資料格式（JSON、BSON 等）	否（或者，建立檢視）	No

必要條件

您的第一個步驟是建立將建立資料表的資料庫。在建立資料庫範圍認證之前，資料庫必須具有保護認證的主要金鑰。如需詳細資訊，請參閱 CREATE MASTER KEY （Transact-SQL）。然後，建立此範例中使用的下列物件：

資料庫 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'

外部資料源 sqlondemanddemo ，參考受 SAS 金鑰保護的示範儲存體帳戶，以及參考位置 https://azureopendatastorage.blob.core.windows.net/nyctlc/ 上公開可用 Azure 儲存體帳戶的 EXTERNAL DATA SOURCE nyctlc 。

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 集區自助頁面上的其他已知問題。

分割資料夾上的差異資料表

無伺服器 SQL 集區中的外部資料表不支援 Delta Lake 格式的資料分割。如果您已分割 Delta Lake 資料集，請使用差異資料分割檢視，而不是資料表。

重要

即使您看到這些資料表在某些情況下可能運作，也不會在分割的 Delta Lake 資料夾上建立外部資料表。在資料分割差異資料夾上使用外部資料表等不支援的功能，可能會導致無伺服器集區的問題或不穩定。 Azure 支援在使用分割資料夾上的資料表時，將無法解決任何問題。系統會要求您轉換至 Delta 資料分割檢視，並重寫程式碼，以在繼續解決問題之前，只使用支援的功能。

使用外部資料表

您可以在查詢中使用外部資料表，就像在 SQL Server 查詢中使用外部資料表一樣。

下列查詢會使用我們在上一節中建立的母體外部資料表來示範這一點。它會以遞減順序傳回 2019 年其人口的國家/地區名稱。

注意

變更查詢中的第一行，也就是 [mydbname]，因此您使用您所建立的資料庫。

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

此查詢的效能可能會因區域而異。您的工作區可能不會放在與這些範例中使用的 Azure 儲存體帳戶相同的區域中。針對生產工作負載，請將 Synapse 工作區和 Azure 儲存體放在相同的區域中。

下一步

如需如何將查詢結果儲存至儲存體的資訊，請參閱將查詢結果儲存至儲存體一文。

共用方式為