搭配 Synapse SQL 使用外部表格

Tip

Microsoft Fabric Data Warehouse 是一個企業規模的關聯式倉庫，建立在資料湖基礎上，具備未來準備架構、內建 AI 及新功能。如果你是資料倉儲新手，建議先從Fabric Data Warehouse開始。現有的專用 SQL 工作負載可升級至 Fabric，以取得資料科學、即時分析與報告等多項新功能。

外部數據表會指向 Hadoop、Azure 儲存體 Blob 或 Azure Data Lake Storage （ADLS）中的數據。

您可以使用外部數據表從檔案讀取數據，或將數據寫入 Azure 儲存體中的檔案。透過 Azure Synapse SQL，您可以使用外部資料表，使用專用 SQL 集區或無伺服器 SQL 集區來讀取外部數據。

根據外部資料來源的類型，您可以使用兩種類型的外部資料表：

您可以用來讀取和匯出各種數據格式的 Hadoop 外部數據表 ，例如 CSV、Parquet 和 ORC。 Hadoop 外部數據表可在專用 SQL 集區中使用，但無法在無伺服器 SQL 集區中使用。
原生外部數據表 ，可用來讀取和匯出各種數據格式的數據，例如 CSV 和 Parquet。原生外部數據表可在無伺服器 SQL 集區和專用 SQL 集區中使用。使用 CETAS 和原生外部數據表寫入/匯出數據僅適用於無伺服器 SQL 集區，但不適用於專用 SQL 集區。

Hadoop 與原生外部數據表之間的主要差異：

外部數據表類型	Hadoop	原生
專用 SQL 集區	可用的	僅限 Parquet
無伺服器 SQL 集區	不可用	可用的
支援的格式	定界符/CSV、Parquet、ORC、Hive RC 和 RC	無伺服器 SQL 集區：分隔/CSV、Parquet 和 Delta Lake 專用 SQL 集區：Parquet
資料夾分割區刪除	No	從 Apache Spark 集區同步的 Parquet 或 CSV 格式建立的分區資料表中，僅提供分區消除功能。您可以在 Parquet 分區資料夾上建立外部資料表，但分區欄位無法存取且會被忽略，同時不會套用分區消除。請勿在 Delta Lake 資料夾上建立外部資料表，因為它們不受支援。如果您需要查詢 Delta Lake 的分區資料，請使用 Delta 分區檢視。
檔案刪除（述詞下推）	No	是，在無伺服器 SQL 集區中。針對字串下推，您必須在數據行上使用`Latin1_General_100_BIN2_UTF8VARCHAR`定序來啟用下推。如需定序的詳細資訊，請參閱 Azure Synapse Analytics 中 Synapse SQL 的資料庫定序支援。
位置的自定義格式	No	是，可以使用像 `/year=/month=/day=` 這樣的 wildcard 針對 Parquet 或 CSV 格式。 Delta Lake 中無法使用自定義資料夾路徑。在無伺服器 SQL 集區中，您也可以使用遞歸通配符 `/logs/*` ，參考參考資料夾下任何子資料夾中的 Parquet 或 CSV 檔案。
遞歸資料夾掃描	Yes	是。在無伺服器 SQL 集區中，必須在位置路徑的結尾指定 `/**` 。在專用集區中，資料夾一律會以遞迴方式掃描。
存儲驗證	儲存體存取金鑰（SAK），Microsoft Entra 傳遞、受控識別、自定義應用程式Microsoft Entra 身分識別	共用存取簽章（SAS）、 Microsoft Entra 傳遞、受控識別、自定義應用程式Microsoft Entra 身分識別。
欄位對應	序數 - 外部數據表定義中的數據行會依位置對應至基礎 Parquet 檔案中的數據行。	無伺服器集區：依名稱。外部表定義中的欄位會透過欄位名稱匹配對應到基礎 Parquet 檔案中的欄位。專用集區：序列匹配。外部表定義中的欄位會依位置對應至基礎 Parquet 檔案中的欄位。
CETAS （匯出/轉換）	Yes	以原生數據表作為目標的 CETAS 僅適用於無伺服器 SQL 集區。您無法使用專用 SQL 集區，使用原生資料表匯出數據。

注意

原生外部數據表是集區中一般可用的建議解決方案。如果您需要存取外部資料，請一律使用無伺服器或專用集區中的原生資料表。只有在您需要存取原生外部數據表不支援的某些類型時，才使用 Hadoop 數據表（例如 - ORC、RC），或無法使用原生版本時。

專用 SQL 集區和無伺服器 SQL 集區中的外部資料表

您可以使用外部資料表來：

使用 Transact-SQL 語句查詢 Azure Blob 儲存體和 ADLS Gen2。
使用 CETAS 搭配 Synapse SQL，將查詢結果儲存至 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 集區中建立外部資料表：

建立 EXTERNAL DATA SOURCE 以參考外部 Azure 記憶體，並指定應該用來存取記憶體的認證。
建立 EXTERNAL FILE FORMAT 來描述 CSV 或 Parquet 檔案的格式。
使用相同檔格式，在數據源上放置的檔案上方建立EXTERNAL TABLE 。

資料夾分割區刪除

Synapse 集區中的原生外部數據表能夠忽略與查詢無關之資料夾中的檔案。如果您的檔案儲存在資料夾階層中（例如 - /year=2020/month=03/day=16），而year、month和day的值被公開為數據欄，則包含如year=2020的篩選條件的查詢只會從位於year=2020資料夾內的子資料夾中讀取這些檔案。在這個查詢中，放置於其他資料夾的year=2021year=2022檔案和資料夾將會忽略。此刪除稱為 分割區消除。

從 Synapse Spark 集區同步自的原生外部資料表中，可以使用分割區排除。如果您已分割數據集，而且想要將分割區消除與您所建立的外部數據表搭配使用，請使用資料分割檢視，而不是外部數據表。

檔案刪除

Parquet 和 Delta 等某些數據格式包含每個資料行的檔案統計數據（例如，每個數據行的最小值/最大值）。篩選數據的查詢不會讀取不存在必要數據行值的檔案。查詢會先探索查詢述詞中使用的數據行最小值/最大值，以尋找不包含必要數據的檔案。這些檔案會被忽略，並從查詢計劃中排除。這項技術也稱為篩選述詞下推，並可改善查詢的效能。在 Parquet 和 Delta 格式的無伺服器 SQL 集區中，可以使用篩選下推。若要將篩選下推套用到字串類型，請使用具備 Latin1_General_100_BIN2_UTF8 定序的 VARCHAR 類型。如需定序的詳細資訊，請參閱 Azure Synapse Analytics 中 Synapse SQL 的資料庫定序支援。

安全性

用戶必須具有 SELECT 外部數據表的許可權，才能讀取數據。外部資料表會使用資料來源中定義的資料庫範圍認證，使用下列規則來存取基礎 Azure 記憶體：

沒有認證的數據源可讓外部數據表存取 Azure 記憶體上的公開可用檔案。
數據源可以有一個認證，可讓外部數據表只使用 SAS 令牌或工作區受控識別存取 Azure 記憶體上的檔案 - 如需範例，請參閱開發記憶體檔案記憶體存取控制一文。

備註

為了確保可靠的查詢執行，外部資料表所參考的來源檔案和資料夾必須在整個作業期間保持不變。

在查詢執行時修改、刪除或取代任何參考的檔案或資料夾可能會導致失敗或導致不一致的結果。
在查詢專用 SQL 集區中的外部資料表之前，請確認所有來源資料都穩定，且在執行期間不會變更。

下列範例會在指向公用紐約數據集的 ADLS Gen2 專用 SQL 集區中建立 Hadoop 外部數據源：

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2022-11-02&ss=b&srt=co&sp=rl&se=2042-11-26T17:40:55Z&st=2024-11-24T09:40:55Z&spr=https&sig=DKZDuSeZhuCWP9IytWLQwu9shcI5pTJ%2Fw5Crw6fD%2BC8%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
  ) ;

下列範例會建立指向紐約公開可用資料集之 ADLS Gen2 的外部資料來源：

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

下列範例會針對可使用 SAS 認證存取的 ADLS Gen2，在無伺服器或專用 SQL 集區中建立外部數據來源：

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2022-11-02&ss=b&srt=co&sp=rl&se=2042-11-26T17:40:55Z&st=2024-11-24T09:40:55Z&spr=https&sig=DKZDuSeZhuCWP9IytWLQwu9shcI5pTJ%2Fw5Crw6fD%2BC8%3D'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

注意

SQL 用戶必須具備資料庫範圍認證的適當許可權，才能存取 Azure Synapse Analytics 無伺服器 SQL 集區中的數據源。在 Azure Synapse Analytics 中使用無伺服器 SQL 集區存取外部記憶體。

下列範例會建立指向紐約公開可用數據集之 ADLS Gen2 的外部數據來源：

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

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 的資料湖探索功能，您現在可以使用 Synapse SQL 集區來建立及查詢外部數據表，並以滑鼠右鍵按兩下檔案。支援從 ADLS Gen2 儲存帳戶以一鍵操作建立外部表的僅限於 Parquet 檔案。

必要條件

您必須至少具有Storage Blob Data Contributor存取角色，以存取 ADLS Gen2 帳戶或存取控制清單（ACL），從而能夠在工作區中查詢檔案。
您必須擁有至少許可權，才能在 Synapse SQL 集區上建立外部數據表並查詢外部數據表（專用或無伺服器）。

從 [資料] 面板中，選取您要用來建立外部表格的檔案：

對話框視窗隨即開啟。選取專用 SQL 集區或無伺服器 SQL 集區，為資料表指定名稱，然後選取開啟的腳稿：

Azure 入口網站中 Azure Synapse Analytics 的 [建立外部數據表] 對話框的螢幕快照。

SQL 腳本會自動從檔案推斷架構：

執行指令碼。腳本會自動執行 SELECT TOP 100 *：

現在已建立外部數據表。您現在可以直接從 [資料] 窗格查詢外部資料表。

請參閱 CETAS 一文，瞭解如何將查詢結果儲存至 Azure 儲存體中的外部數據表。或者，您可以開始使用 Apache Spark 查詢 Azure Synapse 的外部表。

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-05-03