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

發行項
12/13/2024

在本節中，您將瞭解如何在 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 SOURCEnyctlc。

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 資料夾

在 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 記憶體放在相同的區域中。

後續步驟

將查詢結果儲存至記憶體

共用方式為