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 直接查詢模式下提供互動式體驗。
• 並行上限不受限制，取決於查詢複雜度和掃描的資料量。 一個無伺服器 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 集區在讀取 Parquet 檔案時所需的時間和儲存體要求都比較少。
• 如果查詢是以單一大型檔案為目標，則好處是可將其分割成多個較小的檔案。
• 請嘗試將 CSV 檔案大小保持在 100 MB 到 10 GB 之間。
• 單一 OPENROWSET 路徑或外部資料表 LOCATION 的檔案大小應相同。
• 藉由將分割區儲存至不同的資料夾或檔案名稱來分割您的資料。 請參閱使用 filename 和 filepath 函式以特定的分割區為目標。

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

請確定您的 Azure Cosmos DB 分析儲存體位於和 Azure Synapse 工作區相同的區域中。 跨區域查詢可能會造成巨大延遲。 在連接字串中使用區域屬性可明確指定分析存放區的放置區域 (請參閱使用無伺服器 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 集區依賴統計資料來產生最佳的查詢執行計畫。 使用 OPENROWSET 時，會針對 Parquet 檔案中的資料行自動建立統計資料以及 CSV 檔案。 目前，系統不會在使用外部資料表時，自動針對 CSV 檔案中的資料行建立統計資料。 請以手動方式為您在查詢中使用的資料行建立統計資料，尤其是在 DISTINCT、JOIN、WHERE、ORDER BY 和 GROUP BY 中使用的資料行。 請查看無伺服器 SQL 集區中的統計資料，以取得詳細資料。

資料類型

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

使用適當的資料類型

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

• 使用能夠容納最大可能值的最小資料大小。
  • 如果字元值的長度上限為 30 個字元，請使用長度為 30 的字元資料類型。
  • 如果所有字元資料行的值都是固定大小，請使用 char 或 nchar。 否則的話，請使用 varchar 或 nvarchar。
  • 如果整數資料行的最大值為 500，請使用 smallint，因為其為可容納此值的最小資料類型。 如需詳細資訊，請參閱 整數資料類型範圍。
• 可能的話，請使用 varchar 和 char，而不是 nvarchar 和 nchar。
  • 如果您要從使用 UTF-8 編碼的 Parquet、Azure Cosmos DB、Delta Lake 或 CSV 中讀取資料，請使用 varchar 類型搭配一些 UTF8 定序。
  • 如果您要從 CSV 非 Unicode 檔案 (例如，ASCII) 中讀取資料，請在沒有 UTF8 定序的情況下使用 varchar 類型。
  • 如果您要從 CSV UTF-16 檔案中讀取資料，請使用 nvarchar 類型。
• 請盡可能使用以整數為基礎的資料類型。 排序、聯結和群組依據作業在整數上完成的速度比在字元資料上更快。
• 如果您使用結構描述推斷，請檢查推斷的資料類型，並以較小的類型明確地覆寫這些類型 (如果可能的話)。

檢查推斷的資料類型

結構描述推斷可協助您快速撰寫查詢和探索資料，且不需要知道檔案結構描述。 這項便利的代價是，推斷的資料類型可能會大於實際的資料類型。 當來源檔案中的資訊不足以確保能使用適當的資料類型時，就會發生此差異。 例如，Parquet 檔案不包含有關字元資料行長度上限的中繼資料。 因此，無伺服器 SQL 集區會將其推斷為 varchar (8000)。

您可以使用系統預存程式 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 來列出儲存體帳戶中的檔案，從第一個星號 (*) 開始。 其會排除不符合指定路徑的檔案。 如果最多符合指定路徑中第一個萬用字元的檔案有很多個，則減少檔案的初始清單可改善效能。

使用 filename 和 filepath 函式以特定的分割區為目標

資料通常會組織成分割區。 您可以指示無伺服器 SQL 集區來查詢特定的資料夾和檔案。 這麼做會減少查詢需要讀取和處理的檔案數目和資料量。 還有一個額外的好處，就是您可以獲得更好的效能。

如需詳細資訊，請閱讀 filename 和 filepath 函式，並參閱查詢特定檔案的範例。

提示

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

用於分割區刪除的 filepath 和 filename 函式目前不支援外部資料表，但針對 Azure Synapse Analytics 而自動為每個 Apache Spark 中所建立資料表建立的外部資料表除外。

如果您儲存的資料未分割，請考慮加以分割。 如此一來，您就可以使用這些函式來最佳化以這些檔案為目標的查詢。 當您查詢已分割的 Apache Spark 以從無伺服器 SQL 集區取得 Azure Synapse 資料表時，查詢只會自動將必要的檔案設為目標。

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

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

Parquet 檔案中字元資料行的述詞下推僅支援用於 Latin1_General_100_BIN2_UTF8 定序。 您可以使用 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 命令將查詢結果儲存至儲存體。

後續步驟

• 請檢閱疑難排解無伺服器 SQL 集區一文中的解決方案和常見問題。
• 如果您使用專用 SQL 集區而非無伺服器 SQL 集區，請參閱專用 SQL 集區的最佳做法，取得特定的指南。
• Azure Synapse Analytics 的常見問題
• 將權限授與工作區受控識別