使用 CREATE EXTERNAL TABLE AS SELECT 陳述式轉換資料檔案

5 分鐘

SQL 語言包含許多特色與功能，可讓您處理資料。例如，您可以使用 SQL 來：

篩選資料集中的資料列和資料行。
重新命名資料欄位，並在資料類型間轉換。
計算衍生的資料欄位。
處理字串值。
分組與彙總資料。

Azure Synapse 無伺服器 SQL 集區可用來執行 SQL 陳述式，以轉換資料並將結果保存為資料湖中的檔案，供進一步處理或查詢。如果熟悉 Transact-SQL 語法，您就可以製作 SELECT 陳述式，套用您感興趣的特定轉換，以選取的檔案格式將 SELECT 陳述式的結果儲存在可用 SQL 查詢的中繼資料表結構描述中。

您可以在專用 SQL 集區或無伺服器 SQL 集區中使用 CREATE EXTERNAL TABLE AS SELECT (CETAS) 陳述式，以外部資料表保存查詢結果，將資料儲存在資料湖的檔案中。

CETAS 陳述式包含 SELECT 陳述式，可查詢及操作來自任何有效資料來源的資料 (可能是資料庫中現有的資料表或檢視，或從資料湖讀取檔案資料的 OPENROWSET 函式)。接著使用外部資料表保存 SELECT 陳述式的結果，這是資料庫中的中繼資料物件，可提供檔案儲存資料的關聯式摘要。下圖以視覺效果說明此概念：

顯示使用 CREATE EXTERNAL TABLE AS SELECT 陳述式將查詢結果儲存為檔案的圖表。

運用這項技術，您可以使用 SQL 擷取與轉換檔案或資料表中的資料，並儲存轉換的結果供下游處理或分析。已轉換資料的後續作業可以在 SQL 集區資料庫的關聯式資料表中執行，或直接在基礎資料檔案中執行。

建立外部資料庫物件以支援 CETAS

若要使用 CETAS 運算式，您必須在資料庫中為無伺服器或專用 SQL 集區建立下列類型的物件。使用無伺服器 SQL 集區時，請在自訂的資料庫中建立這些物件 (使用 CREATE DATABASE 陳述式建立)，不是在內建的資料庫中建立。

外部資料來源

外部資料來源會將檔案系統連線位置封裝在資料湖中。然後，您可以使用此連線指定儲存外部資料表資料檔案的相對路徑，此外部資料表是以 CETAS 陳述式所建立。

如果 CETAS 陳述式來源資料的檔案路徑與資料湖相同，您可以在查詢資料來源的 OPENROWSET 函式中使用相同的外部資料來源。或者，您也可以為來源檔案另行建立一個外部資料來源，或在 OPENROWSET 函式中使用完整的檔案路徑。

若要建立外部資料來源，請使用 CREATE EXTERNAL DATA SOURCE 陳述式，如以下範例所示：

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

上一個範例假設，使用外部資料來源執行查詢的使用者有足夠的權限可存取檔案。替代方法是將認證封裝在外部資料來源中，使其可用以存取檔案資料，但不授與所有使用者直接的讀取權限：

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

提示

除了 SAS 驗證以外，您還可以定義使用「受控識別」(Azure Synapse 工作區所使用的 Microsoft Entra 身分識別) 的認證，這是特定的 Microsoft Entra 主體，或以執行查詢的使用者身分識別為基礎的傳遞驗證 (此為預設的驗證類型)。如需深入了解如何在無伺服器 SQL 集區中使用認證，請參閱 Azure Synapse Analytics 說明文件中在 Azure Synapse Analytics 中控制無伺服器 SQL 集區儲存體帳戶存取一文的內容。

外部檔案格式

CETAS 陳述式會使用儲存在檔案中的資料，建立資料表。您必須將要建立的檔案格式指定為外部檔案格式。

若要建立外部檔案格式，請使用 CREATE EXTERNAL FILE FORMAT 陳述式，如以下範例所示：

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

提示

在此範例中，檔案會以 Parquet 格式儲存。您也可以建立其他類型檔案的外部檔案格式。如需詳細資訊，請參閱 CREATE EXTERNAL FILE FORMAT (Transact-SQL)。

使用 CETAS 陳述式

建立外部資料來源和外部檔案格式之後，您就可以使用 CETAS 陳述式轉換資料，並將結果儲存在外部資料表中。

例如，假設您想要轉換的來源資料為以逗號分隔的銷售訂單文字檔，且這些檔案儲存在資料湖的資料夾中。您想要篩選資料，只包含標示為「特殊訂單」的訂單，並將轉換的資料儲存為 Parquet 檔案，放在相同資料湖的不同資料夾中。來源和目的地資料夾可使用相同的外部資料來源，如以下範例所示：

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

上例中的 LOCATION 和 BULK 參數分別是結果和來源檔案的相對路徑。這些是相對於檔案外部資料來源參考之檔案系統位置的路徑。

重點是，您必須使用外部資料來源指定要儲存外部資料表轉換資料的位置。使用相同的資料夾階層儲存檔案來源資料時，您可以使用相同的外部資料來源。不然，您可以使用第二個資料來源定義來源資料的連線，或使用完整路徑，如以下範例所示：

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

卸除外部資料表

如果不再需要包含轉換資料的外部資料表，您可以使用 DROP EXTERNAL TABLE 陳述式從資料庫中卸除此外部資料表，如下所示：

DROP EXTERNAL TABLE SpecialOrders;