搭配 Synapse SQL 使用外部表格

發行項
01/14/2025

外部數據表會指向 Hadoop、Azure 儲存體 Blob 或 Azure Data Lake Storage 中的數據。您可以使用外部數據表從檔案讀取數據，或將數據寫入 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 數據，請使用差異數據分割檢視。
檔案刪除（述詞下推）	No	是，在無伺服器 SQL 集區中。針對字串下推，您必須在數據行上使用`Latin1_General_100_BIN2_UTF8VARCHAR`定序來啟用下推。如需定序的詳細資訊，請參閱 Synapse SQL 支援的定序類型。
位置的自定義格式	No	是，針對 Parquet 或 CSV 格式使用通 `/year=/month=/day=` 配符。 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 集區，使用原生資料表匯出數據。

注意

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

專用 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 集區中建立外部資料表：

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

資料夾分割區刪除

Synapse 集區中的原生外部數據表能夠忽略與查詢無關之資料夾中的檔案。如果您的檔案儲存在資料夾階層中（例如 - /year=2020/month=03/day=16），以及、和 day 的值monthyear公開為數據行，則包含篩選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 記憶體上的檔案 - 如需範例，請參閱開發記憶體檔案記憶體存取控制一文。

下列範例會在指向紐約數據集的 Azure Data Lake Gen2 專用 SQL 集區中建立 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 的外部數據源，指向可公開取得的紐約數據集：

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

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

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'
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 集區存取外部記憶體。下列範例會建立 Azure Data Lake 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 的 Data Lake 探索功能，您現在可以使用 Synapse SQL 集區來建立及查詢外部數據表，並以滑鼠右鍵按兩下檔案。只有 Parquet 檔案支援單鍵手勢，從 ADLS Gen2 儲存器帳戶建立外部數據表。

必要條件

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

從 [資料] 面板中，選取您想要從下列專案建立外部數據表的檔案：

externaltable1

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

externaltable2

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

externaltable3

執行指令碼。文稿會自動執行選取前 100 名 *..

externaltable4

現在會建立外部數據表，以供未來探索此外部數據表的內容，使用者可以直接從 [數據] 窗格查詢它：

externaltable5

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

共用方式為

搭配 Synapse SQL 使用外部表格

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

資料夾分割區刪除

檔案刪除

安全性

CREATE EXTERNAL DATA SOURCE 的範例

CREATE EXTERNAL FILE FORMAT 的範例

CREATE EXTERNAL TABLE 範例

從 Azure Data Lake 中的檔案建立和查詢外部數據表

必要條件

意見反應

其他資源

共用方式為

搭配 Synapse SQL 使用外部表格

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

資料夾分割區刪除

檔案刪除

安全性

CREATE EXTERNAL DATA SOURCE 的範例

CREATE EXTERNAL FILE FORMAT 的範例

CREATE EXTERNAL TABLE 範例

從 Azure Data Lake 中的檔案建立和查詢外部數據表

必要條件

相關內容

意見反應

其他資源