共用方式為

Azure Synapse Analytics 中無伺服器 SQL 集區的最佳做法

  • 發行項

在本文中，您將找到使用無伺服器 SQL 集區的最佳作法集合。 無伺服器 SQL 集區是 Azure Synapse Analytics 中的資源。 如果您使用專用 SQL 集區，請參閱 專用 SQL 集 區的最佳做法以取得特定指引。

無伺服器 SQL 集區可讓您查詢 Azure 儲存體帳戶中的檔案。 它沒有本機存放區或擷取功能。 查詢目標的所有檔案都位於無伺服器 SQL 集區的外部。 與從儲存體讀取檔案相關的所有內容都可能會影響查詢效能。

部分通用指導方針如下：

  • 確保用戶端應用程式與無伺服器 SQL 集區共置。
    • 如果使用 Azure 外部的用戶端應用程式，請確保您在靠近用戶端電腦的區域中使用無伺服器 SQL 集區。 用戶端應用程式範例包括 Power BI Desktop、SQL Server Management Studio 及 Azure Data Studio。
  • 確保儲存體和無伺服器 SQL 集區位於相同的區域中。 儲存體範例包括 Azure Data Lake Storage 和 Azure Cosmos DB。
  • 嘗試使用分割來優化記憶體配置，並將檔案保持在 100 MB 到 10 GB 之間的範圍內。
  • 如果要傳回大量結果，請確保您使用的是 SQL Server Management Studio 或 Azure Data Studio，而不是 Azure Synapse Studio。 Azure Synapse Studio 是一種 Web 工具，不是針對大型結果集而設計。
  • 如果您要依字串資料行篩選結果，請嘗試使用 BIN2_UTF8 定序。 如需變更定序的詳細資訊，請參閱 Synapse SQL 支援的定序類型。
  • 考慮使用 Power BI 匯入模式或 Azure Analysis Services 在用戶端快取結果，並定期重新整理。 如果您使用複雜查詢或處理大量資料，則無伺服器 SQL 集區無法在 Power BI Direct Query 模式下提供互動式體驗。
  • 並行上限不受限制，取決於所掃描數據的查詢複雜度和數量。 一個無伺服器 SQL 集區可以同時處理執行輕量型查詢的 1,000 個作用中工作階段。 如果查詢更複雜或掃描大量資料，則數字會下降，因此在這種情況下，請考慮減少並行，並在可能的情況下在更長的時間內執行查詢。

用戶端應用程式和網路連線

請確定您的用戶端應用程式已連線到最接近的 Azure Synapse 工作區，並具有最佳連線。

  • 使用 Azure Synapse 工作區來共置用戶端應用程式。 如果您使用Power BI或 Azure Analysis Service 之類的應用程式，請確定它們位於您放置 Azure Synapse 工作區的相同區域中。 如有需要，請建立與用戶端應用程式配對的個別工作區。 將用戶端應用程式和 Azure Synapse 工作區放在不同區域中可能會導致更大的延遲和較慢的結果串流。
  • 如果您要從內部部署應用程式讀取數據，請確定 Azure Synapse 工作區位於靠近您位置的區域。
  • 在讀取大量數據時，請確定您沒有網路頻寬問題。
  • 請勿使用 Azure Synapse Studio 傳回大量數據。 Azure Synapse Studio 是使用 HTTPS 通訊協定來傳輸數據的 Web 工具。 使用 Azure Data Studio 或 SQL Server Management Studio 讀取大量數據。

記憶體和內容版面配置

以下是無伺服器 SQL 集區中記憶體和內容配置的最佳作法。

共置記憶體和無伺服器 SQL 集區

若要將延遲降至最低，請共置 Azure 儲存體 帳戶或 Azure Cosmos DB 分析記憶體和無伺服器 SQL 集區端點。 在工作區建立期間布建的記憶體帳戶和端點位於相同的區域中。

為了獲得最佳效能，如果您使用無伺服器 SQL 集區存取其他儲存器帳戶，請確定它們位於相同的區域中。 如果它們不在相同區域中，遠端區域與端點區域之間的數據網路傳輸將會增加延遲。

Azure 儲存體 節流

多個應用程式和服務可能會存取您的記憶體帳戶。 當應用程式、服務和無伺服器 SQL 集區工作負載產生的合併 IOPS 或輸送量超過記憶體帳戶的限制時，就會發生記憶體節流。 因此，您將對查詢效能產生重大負面影響。

偵測到節流時，無伺服器 SQL 集區具有內建處理來解決。 無伺服器 SQL 集區會以較慢的速度對記憶體提出要求，直到節流解決為止。

提示

為了獲得最佳查詢執行，請勿在查詢執行期間與其他工作負載強調記憶體帳戶。

準備檔案以進行查詢

可能的話，您可以準備檔案以提升效能：

  • 將大型 CSV 和 JSON 檔案轉換成 Parquet。 Parquet 是單欄格式。 因為壓縮了，所以其檔案大小會小於 CSV 或 JSON 檔案，其中包含相同的數據。 如果您要讀取 Parquet 檔案，無伺服器 SQL 集區會略過查詢中不需要的數據行和數據列。 無伺服器 SQL 集區需要較少的時間和較少的記憶體要求才能讀取它。
  • 如果查詢以單一大型檔案為目標，您將受益於將它分割成多個較小的檔案。
  • 嘗試將您的 CSV 檔案大小保持在 100 MB 到 10 GB 之間。
  • 最好為單一 OPENROWSET 路徑或外部數據表 LOCATION 具有相同大小的檔案。
  • 將資料分割儲存到不同的資料夾或檔名，以分割您的資料。 請參閱 使用檔名和檔案路徑函式來以特定分割區為目標。

共置 Azure Cosmos DB 分析記憶體和無伺服器 SQL 集區

請確定您的 Azure Cosmos DB 分析記憶體位於與 Azure Synapse 工作區相同的區域中。 跨區域查詢可能會導致巨大的延遲。 使用 連接字串 中的 region 屬性來明確指定分析存放區所在的區域（請參閱使用無伺服器 SQL 集區查詢 Azure Cosmos DB）：account=<database account name>;database=<database name>;region=<region name>'

CSV 優化

以下是在無伺服器 SQL 集區中使用 CSV 檔案的最佳做法。

使用 PARSER_VERSION 2.0 查詢 CSV 檔案

當您查詢 CSV 檔案時，可以使用效能優化的剖析器。 如需詳細資訊，請參閱 PARSER_VERSION。

以手動方式為 CSV 檔案建立統計資料

無伺服器 SQL 集區依賴統計數據來產生最佳的查詢執行計劃。 系統會使用取樣來自動為數據行建立統計數據，而且在大部分情況下取樣百分比會小於 100%。 每個檔案格式的流程都相同。 請記住，當讀取具有剖析器 1.0 版取樣的 CSV 時，不支援自動建立統計數據，取樣百分比小於 100%。 對於具有估計低基數（數據列數目）的小型數據表，將會觸發自動統計數據建立，取樣百分比為100%。 這表示會觸發 fullscan，即使使用剖析器 1.0 版的 CSV，也會建立自動統計數據。 如果未自動建立統計數據，請針對您在查詢中使用的數據行手動建立統計數據，特別是用於 DISTINCT、JOIN、WHERE、ORDER BY 和 GROUP BY 的數據行。 如需詳細資訊，請查看 無伺服器 SQL 集 區中的統計數據。

資料類型

以下是在無伺服器 SQL 集區中使用資料類型的最佳做法。

使用適當的數據類型

您在查詢中使用的數據類型會影響效能和並行。 如果您遵循下列指導方針，可以獲得最佳效能：

  • 使用可容納最大可能值的最小數據大小。
    • 如果最大字元值長度為 30 個字元，請使用長度為 30 的字元資料類型。
    • 如果所有字元數據行值都是固定大小，請使用 char 或 nchar。 否則，請使用 varchar 或 nvarchar。
    • 如果最大整數數據行值為 500，請使用 smallint ，因為它是可容納此值的最小數據類型。 如需詳細資訊，請參閱 整數數據類型範圍。
  • 可能的話，請使用 varchar 和 char，而不是 nvarchar 和 nchar。
    • 如果您要從 Parquet、Azure Cosmos DB、Delta Lake 或 CSV 搭配 UTF-8 編碼來讀取數據，請使用具有某些 UTF8 定序的 varchar 類型。
    • 如果您要從 CSV 非 Unicode 檔案讀取數據，請使用不含 UTF8 定序的 varchar 類型（例如 ASCII）。
    • 如果您要從 CSV UTF-16 檔案讀取數據，請使用 nvarchar 類型。
  • 如果可能，請使用以整數為基礎的資料類型。 SORT、JOIN 和 GROUP BY 作業在整數上的完成速度比字元資料更快。
  • 如果您使用架構推斷，請檢查推斷的數據類型，並盡可能以較小的類型明確覆寫它們。

檢查推斷的數據類型

架構推斷 可協助您快速撰寫查詢並探索數據，而不需要知道檔案架構。 這種便利性的成本是推斷的數據類型可能大於實際數據類型。 當來源檔案中沒有足夠的資訊以確保使用適當的數據類型時，就會發生這種差異。 例如，Parquet 檔案不包含有關最大字元數據行長度的元數據。 因此，無伺服器 SQL 集區會將它推斷為 varchar（8000）。

請記住，在 SQL 引擎中公開為外部數據表的可共享受控和外部 Spark 數據表時，情況可能會有所不同。 Spark 資料表提供的資料類型與 Synapse SQL 引擎不同。 您可以在這裡找到 Spark 資料表資料類型與 SQL 類型之間的對應。

您可以使用系統預存程式 sp_describe_first_results_set 來檢查查詢的結果資料類型。

下列範例示範如何優化推斷的數據類型。 此程式用來顯示推斷的資料類型：

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

結果集如下：

is_hidden column_ordinal NAME system_type_name max_length
0 1 vendor_id varchar（8000） 8000
0 2 pickup_datetime datetime2(7) 8
0 3 passenger_count int 4

知道查詢的推斷數據類型之後，您可以指定適當的數據類型：

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

篩選最佳化

以下是在無伺服器 SQL 集區中使用查詢的最佳做法。

將通配符推送至路徑中的較低層級

您可以在路徑中使用通配符來 查詢多個檔案和資料夾。 無伺服器 SQL 集區會使用記憶體 API，從第一個星號開始列出記憶體帳戶中的檔案。 其會排除不符合指定路徑的檔案。 如果最多符合指定路徑中第一個萬用字元的檔案有很多個，則減少檔案的初始清單可改善效能。

使用檔名和 filepath 函式以特定分割區為目標

數據通常會組織在分割區中。 您可以指示無伺服器 SQL 集區查詢特定資料夾和檔案。 這樣做可減少檔案數目，以及查詢讀取和處理所需的數據量。 額外的獎金是您將達到更好的效能。

如需詳細資訊，請參閱檔名和 filepath 函式的相關信息，並查看查詢特定檔案的範例。

提示

一律將filepath和filename函式的結果轉換成適當的數據類型。 如果您使用字元數據類型，請務必使用適當的長度。

除了針對 Azure Synapse Analytics 的 Apache Spark 中建立的每個數據表自動建立的函式以外，目前不支援用於分割區消除、檔案路徑和檔名的函式。

如果您的預存數據未分割，請考慮將其分割。 如此一來，您就可以使用這些函式來優化以那些檔案為目標的查詢。 當您 從無伺服器 SQL 集區查詢 Azure Synapse 數據表 的已分割 Apache Spark 時，查詢會自動以必要的檔案為目標。

使用適當的定序來利用字元數據行的述詞下推

Parquet 檔案中的資料會組織成資料列群組。 無伺服器 SQL 集區會根據 WHERE 子句中指定的述詞來略過資料列群組，以減少 IO。 結果是查詢效能會提高。

只有Latin1_General_100_BIN2_UTF8定序才支援 Parquet 檔案中字元數據行的述詞下推。 您可以使用 WITH 子句來指定特定資料行的定序。 如果您未使用 WITH 子句來指定此定序，則系統會使用資料庫定序。

將重複查詢最佳化

以下是在無伺服器 SQL 集區中使用 CETAS 的最佳做法。

使用 CETAS 來增強查詢效能和聯結

CETAS 是無伺服器 SQL 集區中最重要的功能之一。 CETAS 是一種平行作業，可建立外部數據表元數據，並將SELECT查詢結果匯出至記憶體帳戶中的一組檔案。

您可以使用 CETAS 將經常使用的查詢部分具體化，例如聯結的參考數據表，到一組新的檔案。 然後，您可以聯結至這個單一外部數據表，而不是在多個查詢中重複一般聯結。

當 CETAS 產生 Parquet 檔案時，當第一個查詢以這個外部數據表為目標時，會自動建立統計數據。 結果已改善以 CETAS 產生的數據表為目標的後續查詢效能。

查詢 Azure 數據

無伺服器 SQL 集區可讓您使用外部資料表和 OPENROWSET 函式來查詢 Azure 儲存體 或 Azure Cosmos DB 中的數據。 請確定您已在記憶體上設定適當的許可權。

查詢 CSV 數據

瞭解如何 查詢單一 CSV 檔案 或 資料夾和多個 CSV 檔案。 您也可以 查詢分割的檔案

查詢 Parquet 數據

瞭解如何使用巢狀類型查詢 Parquet 檔案。 您也可以 查詢分割的檔案。

查詢 Delta Lake

瞭解如何使用巢狀類型查詢 Delta Lake 檔案。

查詢 Azure Cosmos DB 數據

瞭解如何 查詢 Azure Cosmos DB 分析存放區。 您可以使用 在線產生器 ，根據範例 Azure Cosmos DB 檔產生 WITH 子句。 您可以在 Azure Cosmos DB 容器之上建立檢視 。

查詢 JSON 資料

瞭解如何 查詢 JSON 檔案。 您也可以 查詢分割的檔案。

建立檢視、數據表和其他資料庫物件

瞭解如何建立和使用檢視表和外部數據表，或設定數據列層級安全性。 如果您有 分割的檔案，請確定您使用 資料分割檢視。

複製與轉換資料 （CETAS）

瞭解如何使用 CETAS 命令將 查詢結果儲存至記憶體 。

下一步