使用無伺服器 SQL 集區查詢檔案

10 分鐘

您可以使用無伺服器 SQL 集區來查詢各種常見檔案格式的資料檔案，包括：

分隔符號文字，例如逗號分隔值 (CSV) 檔案。
JavaScript 物件標記法 (JSON) 檔案。
Parquet 檔案。

查詢的基本語法與上述所有檔案類型相同，並是以 OPENROWSET SQL 函數所建置；此函數會從一或多個檔案中的資料產生表格式資料列集。例如，下列查詢可用來擷取 CSV 檔案中的資料。

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

OPENROWSET 函數包含更多可決定因數的參數，例如：

結果資料列集的結構描述
分隔符號文字檔的其他格式設定選項。

提示

您可以在 Azure Synapse分析文件中找到 OPENROWSET 函數的完整語法。

OPENROWSET 的輸出是必須指派別名的資料列集。在上一個範例中，別名列是用來命名產生的資料列集。

BULK 參數包含資料湖中位置的完整 URL，該資料湖會包含資料檔案。這可以是個別檔案，或是含有萬用字元運算式的資料夾，以篩選應包含的檔案類型。 FORMAT 參數會指定要查詢的資料類型。上述範例會讀取檔案資料夾中所有 .csv 檔案的分隔符號文字。

注意

此範例假設使用者可以存取基礎存放區中的檔案，如果檔案受到 SAS 金鑰或自訂身分識別保護，您必須建立伺服器範圍的認證。

如上例所示，您可以在 BULK 參數中使用萬用字元，在查詢中包含或排除檔案。下列清單顯示幾個範例，說明如何使用萬用字元：

https://mydatalake.blob.core.windows.net/data/files/file1.csv：只能在檔案資料夾中包含 file1.csv。
https://mydatalake.blob.core.windows.net/data/files/file*.csv：檔案資料夾中所有名稱開頭為 "file" 的 .csv 檔案。
https://mydatalake.blob.core.windows.net/data/files/*：檔案資料夾中的所有檔案。
https://mydatalake.blob.core.windows.net/data/files/**：檔案資料夾中的所有檔案，並遞迴其子資料夾。

您也可以在 BULK 參數中指定多個檔案路徑，以逗號分隔每個路徑。

查詢分隔符號文字檔

分隔符號文字檔是許多企業中常見的檔案格式。分隔符號檔案中使用的特定格式設定可能會有所不同，例如：

包含或不包含標題資料列。
逗點或定位字元分隔值。
Windows 或 Unix 樣式的行尾符號。
不加引號或加引號的值，及逸出字元。

無論您使用何種類型的分隔符號檔案，您都可以使用 OPENROWSET 函數搭配 csv 格式參數來讀取檔案的資料，以及需要使用其他參數來處理資料的特定格式設定詳細資料。例如：

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

此 PARSER_VERSION 是用來判斷查詢如何解譯檔案中所使用的文字編碼。預設為版本 1.0，支援各種檔案編碼，而版本 2.0 支援較少的編碼，但提供更好的效能。 FIRSTROW 參數是用來略過文字檔中的列、消除任何非結構化的前序文字，或忽略包含資料行標題的資料列。

使用分隔符號文字檔時，您可能需要的其他參數包括：

FIELDTERMINATOR - 用來分隔每一列欄位值的字元。例如，定位字元分隔的檔案會使用 TAB (\t) 字元來分隔欄位。預設的欄位結束字元為逗號 (,)。
ROWTERMINATOR - 用來表示資料列結尾的字元。例如，標準 Windows 文字檔使用由程式碼 \n 指示的歸位字元 (CR) 和換行字元 (LF) 組合；而 UNIX 樣式文字檔則使用單一換行字元，以便可使用程式碼 0x0a 來表示。
FIELDQUOTE - 用來括住引用字串值的字元。例如，若要確保位址欄位值 126 Main St, apt 2 中的逗號不會解譯為欄位分隔符號，您可以使用引號括住整個欄位值，例如 "126 Main St, apt 2"。雙引號 (") 是預設的欄位引號字元。

提示

如需處理分隔符號文字檔時其他參數的詳細資料，請參閱 Azure Synapse Analytics 文件。

指定資料列集的結構描述

分隔符號文字檔通常會在第一資料列中包含資料行名稱。 OPENROWSET 函數可以使用此名稱來定義產生的資料列集結構描述，並根據所包含的值自動推斷資料行的資料類型。例如，請參考下列分隔符號文字：

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

資料包括下列三個資料行：

product_id (整數)
product_name (字串)
list_price (十進位數)

您可以使用下列查詢來擷取正確資料行名稱的資料，並適當推斷 SQL Server 資料類型 (在此案例中為 INT、NVARCHAR 和 DECIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

HEADER_ROW 參數 (僅在使用剖析器版本 2.0 時才能使用) 指示查詢引擎使用每個檔案中第一資料列的資料作為資料行名稱，如下所示：

product_id	product_name	list_price
123	Widget	12.9900
124	Gadget	3.9900

請參考下列資料：

123,Widget,12.99
124,Gadget,3.99

這一次，檔案在標題列中不包含標題資料列；因此，雖然資料類型仍可推斷，但資料行名稱會設定為 C1、C2、C3，依此類推。

C1	C2	C3
123	Widget	12.9900
124	Gadget	3.9900

若要指定明確的資料行名稱和資料類型，您可以在 WITH 子句中提供結構描述定義，藉此覆寫預設資料行名稱和推斷的資料類型，如下所示：

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

此查詢會產生預期的結果：

product_id	product_name	list_price
123	Widget	12.99
124	Gadget	3.99

提示

處理文字檔時，您可能會遇到一些與 UTF-8 編碼資料的不相容，以及無伺服器 SQL 集區在主資料庫中使用的定序問題。若要克服此問題，您可以為結構描述中的個別 VARCHAR 資料行指定相容的定序。如需詳細資訊，請參閱疑難排解指導方針。

查詢 JSON 檔案

JSON 是 Web 應用程式的熱門格式，可透過 REST 介面或使用 NoSQL 資料存放區 (例如 Azure Cosmos DB) 來交換資料。因此，通常會在資料湖的檔案中將資料保存為 JSON 文件以供分析。

例如，定義個別產品的 JSON 檔案呈現如下所示：

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

若要以這種格式從包含多個 JSON 檔案的資料夾傳回產品資料，您可以使用下列 SQL 查詢：

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET 沒有 JSON 檔案的特定格式，因此您必須使用 csv 格式搭配設為 0x0b的 FIELDTERMINATOR、 FIELDQUOTE和 ROWTERMINATOR，以及包含單一 NVARCHAR (MAX) 資料行的結構描述。此查詢的結果是包含 JSON 文件單一資料行的資料列集，如下所示：

doc
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

若要從 JSON 擷取個別值，您可以在 SELECT 陳述式中使用 JSON_VALUE 函數，如下所示：

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

此查詢會傳回類似下列結果的資料列集：

product	price
Widget	12.99
Gadget	3.99

查詢 Parquet 檔案

Parquet 是一種常用的格式，用於在分散式檔案儲存空間上進行大量資料處理。這是有效率的資料格式，且其以針對壓縮和分析查詢最佳化。

在大部分的情況下，資料結構描述會內嵌在 Parquet 檔案中，因此您只需要指定包含所要讀取檔案路徑的 BULK 參數，以及 parquet 的 FORMAT 參數，如下所示：

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

查詢分割區的資料

在資料湖中，在反映分割準則的子資料夾中分割多個檔案，以分割資料是很常見的作法。這可讓分散式處理系統平行處理資料的多個分割，或是根據篩選準則輕鬆刪除特定資料夾中的資料讀取。例如，假設您需要有效率處理銷售訂單資料，而且通常需要根據下單的年份和月份進行篩選。您可以使用資料夾來分割資料，如下所示：

/orders
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

若要建立查詢來篩選結果以僅包含 2020 年 1 月和 2 月的訂單，則您可以使用下列程式碼：

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');