共用方式為

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

  • 發行項

無伺服器 SQL 集區可讓您查詢 Data Lake 中的數據。 它提供 Transact-SQL （T-SQL） 查詢介面區，以容納半結構化和非結構化數據查詢。 在查詢方面，可支援下列 T-SQL 層面：

如需目前不支援或不支援之專案的詳細資訊，請參閱 無伺服器 SQL 集區概觀，或下列文章：

概觀

為了支援就地查詢位於 Azure 儲存體 檔案中的數據，無伺服器 SQL 集區會使用 OPENROWSET 函式搭配更多功能：

查詢 PARQUET 檔案

若要查詢 Parquet 來源資料， 請使用 FORMAT = 'PARQUET'：

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

如需使用範例，請參閱 查詢 Parquet 檔案。

查詢 CSV 檔案

若要查詢 CSV 源資料，請使用 FORMAT = 'CSV'。 當您查詢 CSV 檔案時，您可以將 CSV 檔案的架構指定為函式的 OPENROWSET 一部分：

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

有一些額外的選項可用來將剖析規則調整為自訂 CSV 格式：

  • ESCAPE_CHAR = 'char' 指定檔案中用來逸出本身和檔案中所有分隔符值的字元。 如果逸出字元後面接著本身或任何分隔符號值以外的值，讀取值時就會捨棄逸出字元。 ESCAPE_CHAR不論 是否FIELDQUOTE啟用 或未啟用 ，都套用 參數。 它不會用來逸出引用字元。 引號字元必須以另一個引號字元來逸出。 只有在以引號字元封住值時，引號字元才能在資料行值中出現。
  • FIELDTERMINATOR ='field_terminator' 指定要使用的欄位終止符。 預設欄位終止符為逗號 （,）。
  • ROWTERMINATOR ='row_terminator' 指定要使用的數據列終止符。 預設數據列終止符元是換行符 （\r\n）。

查詢 DELTA LAKE 格式

若要查詢 Delta Lake 源數據，請使用 FORMAT = 'DELTA' 並參考包含 Delta Lake 檔案的根資料夾。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

根資料夾必須包含名為 _delta_log 的子資料夾。 如需使用範例，請參閱 查詢 Delta Lake （v1） 檔案。

檔案結構描述

Synapse SQL 中的 SQL 語言可讓您將檔案的架構定義為函式的 OPENROWSET 一部分，以及讀取所有數據行或子集，或是嘗試使用架構推斷從檔案自動判斷數據行類型。

讀取選擇的資料行子集

若要指定您想要讀取的數據行，您可以在 語句內OPENROWSET提供選擇性WITH子句。

  • 如果有 CSV 資料檔，請提供資料行名稱和其資料類型來讀取所有資料行。 如果您想要讀取一小組資料行，請使用序數從原始資料檔案中依序挑選資料行。 數據行是由序數指定所系結。
  • 如果有 Parquet 資料檔案，請提供與原始資料檔案中資料行名稱相符的資料行名稱。 數據行會依名稱系結。
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows;

針對每個資料行，您都必須在 WITH 子句中指定資料行名稱和類型。 如需範例，請參閱 讀取 CSV 檔案而不指定所有數據行。

結構描述推斷

藉由省略 語句中的 WITHOPENROWSET 子句，您可以指示服務從基礎檔案自動偵測架構（推斷）。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

請確定使用適當的推斷資料類型來達到最佳效能。

查詢多個檔案或資料夾

若要對資料夾或資料夾集合中的一組檔案執行 T-SQL 查詢，並同時將其視為單一實體或資料列集，請提供單一資料夾的路徑，或代表一組檔案或資料夾的模式 (使用萬用字元)。

適用的規則如下：

  • 模式可能會出現在目錄路徑或檔案名稱中。
  • 相同目錄步驟或檔案名稱中可出現多個模式。
  • 如果有多個通配符，則所有相符路徑內的檔案都會包含在產生的檔案集中。
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

如需使用範例，請參閱 查詢資料夾和多個檔案。

檔案中繼資料函式

Filename 函式

此函式會傳回資料列來源的檔案名稱。

若要查詢特定檔案，請閱讀查詢特定檔案一文中的＜Filename＞一節。

傳回資料類型為 nvarchar(1024)。 為了達到最佳效能，請一律將 filename 函式的結果轉換成適當的資料類型。 如果您使用字元資料類型，請務必確認長度適當。

Filepath 函式

此函式會傳回完整路徑或部分路徑的：

  • 若在沒有參數的情況下進行呼叫，會傳回資料列來源的完整檔案路徑。
  • 若以參數進行呼叫，則會傳回路徑的一部分，該部分會與參數中所指定位置上的萬用字元相符。 例如，參數值 1 會傳回符合第一個萬用字元的路徑部分。

如需詳細資訊，請參閱查詢特定檔案一文的 Filepath 區段。

傳回資料類型為 nvarchar(1024)。 為了獲得最佳效能，請一律將 filepath 函式的結果轉換成適當的數據類型。 如果您使用字元資料類型，請務必確認長度適當。

使用複雜類型和巢狀或重複的資料結構

若要讓儲存在巢狀或重複數據類型中的數據順暢體驗，例如在 Parquet 檔案中，無伺服器 SQL 集區已新增下列延伸模組。

投射巢狀或重復資料

若要投影數據，請在包含巢狀數據類型數據行的 Parquet 檔案上執行 SELECT 語句。 在輸出中，巢狀值會串行化為 JSON，並以 varchar（8000） SQL 數據類型傳回。

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

如需詳細資訊，請參閱查詢 Parquet 巢狀類型一文的 Project 巢狀或重複數據一節。

從巢狀資料行存取元素

若要從巢狀數據行存取巢狀專案，例如結構，請使用 點表示法 將域名串連至路徑。 提供 路徑，如 column_name 函式的 WITHOPENROWSET 子句所示。

語法片段範例如下：

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ('column_name' 'column_type')
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

根據預設，函 OPENROWSET 式會比對來源功能變數名稱和路徑，以及 子句中 WITH 提供的數據行名稱。 您可以使用 WITH 子句來存取位於相同來源 Parquet 檔案內不同巢狀層級的專案。

傳回值

  • 函式會針對不在巢狀類型群組中的所有 Parquet 類型傳回純量值，例如 int、decimal、 和 varchar，以及指定路徑上的純量值。
  • 如果路徑指向巢狀類型的專案，此函式會從指定路徑上的頂端元素開始傳回 JSON 片段。 JSON 片段的類型為 varchar（8000）。
  • 如果在指定的 column_name找不到 屬性，則函式會傳回錯誤。
  • 如果在指定的 column_path中找不到 屬性，則視Path模式而定，函式會在 strict 模式中傳回錯誤，或在lax模式中為 null 時傳回錯誤。

如需查詢範例，請參閱查詢 Parquet 巢狀類型一文中的<從巢狀對象數據行讀取屬性>一節。

從重複的資料行存取元素

若要從重複的數據行存取元素，例如數位或對應的元素，請使用 JSON_VALUE 函式，針對您需要投影的每個純量元素，並提供：

  • 使用巢狀或重復資料行作為第一個參數
  • 第二個參數則使用 JSON 路徑，該路徑會指定要存取的元素或屬性

若要從重複的數據行存取非calar 元素，請使用 JSON_QUERY 函式，針對您需要投影並提供的每個 Nonscalar 元素：

  • 使用巢狀或重復資料行作為第一個參數
  • 第二個參數則使用 JSON 路徑，該路徑會指定要存取的元素或屬性

請參閱下列語法片段：

    SELECT
       JSON_VALUE (column_name, path_to_sub_element),
       JSON_QUERY (column_name [ , path_to_sub_element ])
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

您可以在查詢 Parquet 巢狀型別一文中，找到用來從重復資料行中存取元素的查詢範例。

相關內容

如需如何查詢不同檔案類型以及建立和使用檢視的詳細資訊，請參閱下列文章：