建立外部資料庫物件

6 分鐘

您可以在 SQL 查詢中使用 OPENROWSET 函式，在內建無伺服器 SQL 集區的預設主要資料庫執行，以探索 Data Lake 中的數據。不過，有時候您可能想要建立自定義資料庫，其中包含一些物件，讓您更輕鬆地在需要經常查詢的 Data Lake 中使用外部數據。

建立資料庫

您可以在無伺服器 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 資料庫中一樣;但是，如果您打算經常查詢相同位置中的數據，則定義參考該位置的外部數據源更有效率。例如，下列程式代碼會為假設 https://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 參數來指定順序資料夾中所有 .csv 檔案的相對路徑，這是數據源所參考檔案資料夾的子資料夾。

使用數據來源的另一個優點是，您可以在存取基礎記憶體時指派數據源使用的認證，讓您能夠透過 SQL 提供資料的存取權，而不允許使用者直接在記憶體帳戶中存取數據。例如，下列程式代碼會建立一個認證，該認證會使用共用存取簽章（SAS）來針對裝載 Data Lake 的基礎 Azure 記憶體帳戶進行驗證。

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

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

建立外部數據表

當您需要從 Data Lake 中的檔案執行大量分析或報告時，使用 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 查詢語意來存取數據。

建立外部資料庫物件

建立資料庫

建立外部數據源

建立外部檔案格式

建立外部數據表

意見反應