建立外部資料庫物件

6 分鐘

您可以在 SQL 查詢中使用 OPENROWSET 函式，該查詢可在內建的無伺服器 SQL 集區預設 master 資料庫中執行，以探索資料湖中的資料。不過，有時候您可能會想要建立自訂資料庫，其包含某些物件可輕鬆地處理資料湖中需要經常查詢的外部資料。

建立資料庫

在無伺服器 SQL 集區中建立資料庫，如同在 SQL Server 執行個體中建立資料庫一樣。您可以使用 Synapse Studio 中的圖形介面，或使用 CREATE DATABASE 陳述式。其中一個考量為設定資料庫的定序，以支援將檔案中文字資料轉換為適當的 Transact-SQL 資料類型。

下列範例程式碼會建立一個附有定序且名為 salesDB 的資料庫，可讓您輕鬆地將以 UTF-8 編碼的文字資料匯入 VARCHAR 資料欄中。

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

建立外部資料來源

您可以使用 OPENROWSET 函式搭配 BULK 路徑，查詢您自有資料庫中的檔案資料，其操作和在 master 資料庫中一樣；但如果您打算要經常查詢相同位置中的資料，則直接定義一個會參照該位置的外部資料來源會更有效率。例如，下列程式碼會建立一個針對假設的 資料夾，且名為 fileshttps://mydatalake.blob.core.windows.net/data/files/ 的資料來源：

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

外部資料來源有一項優點，就是可以透過使用資料來源組合以及想要查詢資料夾或檔案的相對路徑來簡化 OPENROWSET 查詢：

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

在此範例中，會使用 BULK 參數來指定 orders 資料夾中所有 .csv 檔案的相對路徑，也就是資料來源所參照 files 資料夾的子資料夾。

使用資料來源的另一個優點是您可以指派認證給存取基礎儲存空間時要使用的資料來源，讓您可以透過 SQL 提供資料存取權，但不會允許使用者直接存取儲存體帳戶中的資料。例如，下列程式碼會建立一個認證，該認證會針對裝載資料湖的基礎 Azure 儲存體帳戶，使用共用存取簽章 (SAS) 來進行驗證。

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

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

提示

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

建立外部檔案格式

當外部資料來源簡化了透過 OPENROWSET 函式存取檔案所需的程式碼時，您仍需要提供所要存取檔案的格式詳細資料；其中可能包含了多個分隔符號文字檔案的設定。您可將這些設定封裝於一個外部檔案格式中，例如：

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

針對所要處理的特定資料檔案建立檔案格式之後，您可以使用該檔案格式來建立外部資料表，如以下的討論。

建立外部資料表

當您需要使用資料湖中的檔案來執行大量分析或報表時，使用 OPENROWSET 函式可能會產生包含資料來源與檔案路徑的複雜程式碼。若要簡化資料存取，您可將檔案封裝於外部資料表中；而使用者及報表應用程式就可以使用標準的 SQL SELECT 陳述式來進行查詢，如同其他任何資料庫資料表。若要建立外部資料表，可以使用 CREATE EXTERNAL TABLE 陳述式，將資料行結構描述指定為標準資料表，並加入具有指定外部資料來源、相對路徑及資料外部檔案格式的 WITH 子句。

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

藉由建立包含本單元所討論之外部物件的資料庫，您可以透過資料湖中的檔案提供關聯式資料庫層，讓許多資料分析師及報表工具能透過標準的 SQL 查詢語意，以較輕鬆的方式存取資料。

建立外部資料庫物件

建立資料庫

建立外部資料來源

建立外部檔案格式

建立外部資料表

意見反應