共用方式為

Azure SQL Database 彈性查詢概觀 (預覽)

  • 發行項

適用於：Azure SQL 資料庫

彈性查詢功能 （預覽版） 可讓您執行跨越 Azure SQL Database 中多個資料庫的 Transact-SQL （T-SQL） 查詢。 它可讓您執行跨資料庫查詢以存取遠端資料表，以及將 Microsoft 和第三方工具 (Excel、Power BI、Tableau 等) 連接到具有多個資料庫的資料層。 這項功能可讓您將查詢擴增到大型資料層，並將結果透過商務智慧 (BI) 報告視覺化。

為何要使用彈性查詢

Azure SQL Database

完全在 T-SQL 中查詢 Azure SQL Database 中的所有資料庫。 這可讓您執行遠端資料庫的唯讀查詢，並且讓目前的 SQL Server 客戶可選擇使用三和四部分的名稱或 SQL Database 的連結伺服器來移轉應用程式。

適用於所有服務層級

Azure SQL Database 的所有服務層級都支援彈性查詢。 請參閱下面＜預覽限制＞一節中較低服務層級的效能限制。

將參數推送到遠端資料庫

彈性查詢現在可以將 SQL 參數發送至遠端資料庫以供執行。

預存程序執行

使用 sp_execute _remote 執行遠端預存程序呼叫或遠端函式。

彈性

具有彈性查詢的外部資料表可以參考具有不同結構描述或資料表名稱的遠端資料表。

彈性查詢案例

目標是協助查詢案例便利進行，其中由多個資料庫提供資料列給單一整體結果。 查詢可以由使用者或應用程式直接撰寫，或透過連接到資料庫的工具來間接撰寫。 建立報表、使用商業 BI 或數據整合工具，或任何無法變更的應用程式時，這特別有用。 透過彈性查詢，您可以在 Excel、Power BI、Tableau 或 Cognos 等工具中使用熟悉的 SQL Server 連線體驗，以查詢多個資料庫。 彈性查詢可讓您透過 SQL Server Management Studio 或 Visual Studio 所發出的查詢，輕鬆存取整個資料庫集合，並協助更方便從 Entity Framework 或其他 ORM 環境執行跨資料庫查詢。 圖 1 顯示的案例中，現有的雲端應用程式 (使用 彈性資料庫用戶端程式庫) 根據相應放大的資料層建置，而彈性查詢用於跨資料庫報告。

圖 1 相應放大的資料層上使用的彈性查詢

用於擴展數據層的 Elastic 查詢的螢幕快照。

彈性查詢的客戶案例可依下列拓撲區分特性：

  • 垂直數據分割 - 跨資料庫查詢 （拓撲 1）：數據會在數據層中的數個資料庫之間垂直分割。 一般而言，不同的資料表集位於不同的資料庫。 這表示不同資料庫的結構描述不同。 比方說，庫存的所有資料表都位於一個資料庫上，而所有會計相關資料表則位於另一個資料庫上。 此拓撲的常見使用案例會要求使用者跨多個資料庫中的資料表進行查詢或編譯報表。

  • 水平資料分割 - 分區化 (拓撲 2)：資料會以水平方式分割，以將資料列分散在相應放大的資料層中。 使用此方法時，所有參與資料庫的結構描述都相同。 此方法也稱為 分區化。 使用 (1) 彈性資料庫工具程式庫或 (2) 自行分區化可以執行和管理分區化。 彈性查詢用於查詢或編譯跨多個分區的報表。 分區通常是彈性集區中的資料庫。 只要資料庫共用通用架構，您就可以將彈性查詢視為一次查詢彈性集區中所有資料庫的有效方式。

注意

彈性查詢最適合可在外部來源端執行大部分處理 (篩選、彙總) 的報告案例。 它不適用於從遠端資料庫傳輸大量數據的 ETL 作業。 對於繁重的報告工作負載或有更多複雜查詢的資料倉儲案例，也請考慮使用 Azure Synapse Analytics。

垂直資料分割 - 跨資料庫查詢

若要開始撰寫程式碼，請參閱 開始使用跨資料庫查詢 (垂直資料分割)。

彈性查詢可讓 SQL Database 中的資料庫使用 SQL Database 中其他資料庫的資料。 這可讓來自一個資料庫的查詢參考 SQL Database 中任何其他遠端資料庫的資料表。 第一個步驟是定義每個遠端資料庫的外部資料來源。 外部資料來源已定義於本機資料庫中，您想要從中取得遠端資料庫上資料表的存取權。 遠端資料庫不需要進行任何變更。 在不同資料庫有不同結構描述的典型垂直資料分割案例中，彈性查詢可用來實作常見使用案例，例如存取參考資料和跨資料庫查詢。

重要

您必須擁有 ALTER ANY EXTERNAL DATA SOURCE 許可權。 此權限包括在 ALTER DATABASE 權限中。 需要 ALTER ANY EXTERNAL DATA SOURCE 許可權才能參考基礎數據源。

參考資料：拓撲是用來管理參考資料。 在下圖中，具有參考數據的兩個數據表 （T1 和 T2） 會保留在專用資料庫上。 利用彈性查詢，您現在可以在遠端從其他資料庫存取資料表 T1 和 T2，如圖所示。 如果參考資料表很小或參考資料表的遠端查詢有選擇性述詞，則使用拓撲 1。

圖 2 垂直資料分割 - 使用彈性查詢來查詢參考資料

垂直分割的螢幕快照 - 使用彈性查詢來查詢參考數據。

跨資料庫查詢：彈性查詢可促成需要跨 SQL Database 中多個資料庫進行查詢的使用案例。 圖 3 顯示四個不同的資料庫：CRM、庫存、HR 和產品。 在其中一個資料庫中執行的查詢也需要存取另一個或其他所有資料庫。 利用彈性查詢，您可以在上述每個資料庫上執行一些簡單的 DDL 陳述式，針對此案例設定您的資料庫。 進行此一次性設定之後，存取遠端資料表就像從 T-SQL 查詢或從 BI 工具參考本機資料表一樣簡單。 如果遠端查詢未傳回大型結果，則建議使用此方法。

圖 3 垂直資料分割 - 使用彈性查詢來查詢各種資料庫

垂直分割的螢幕快照 - 使用彈性查詢來查詢各種資料庫。

下列步驟會針對垂直資料分割案例設定彈性資料庫查詢，這些案例需要存取位於 SQL Database 中遠端資料庫上的資料表 (SQL Database 須具有相同結構描述)：

執行 DDL 語句之後，您可以存取遠端資料表 mytable 就像是本機數據表一樣。 Azure SQL Database 會自動開啟遠端資料庫的連線、處理您對遠端資料庫的要求，以及傳回結果。

水平資料分割 - 分區化

使用彈性查詢在分區化 (即水平分割) 的資料層執行報告工作時，需要彈性資料庫分區對應來代表資料層的資料庫。 一般而言，這種情節中只會使用單一分區對應，並以具有彈性查詢功能 (前端節點) 的專用資料庫作為報告查詢的進入點。 只有這個專用的資料庫需要存取分區對應。 圖 4 說明此拓撲及其彈性查詢資料庫和分區對應的組態。 如需有關彈性資料庫用戶端程式庫和建立分區對應的詳細資訊，請參閱 分區對應管理。

圖 4 水平資料分割 - 使用彈性查詢來報告分區化資料層

水平分區的螢幕快照 - 使用 Elastic Query 來報告跨分片數據層。

注意

彈性查詢資料庫 (前端節點) 可以是個別的資料庫，或是裝載分區對應的相同資料庫。 無論您選擇何種組態，請確定該資料庫的服務層級和計算大小夠高，足以處理預期的登入/查詢要求數目。

下列步驟會針對水平資料分割案例設定彈性資料庫查詢，這些案例需要存取 (通常) 位於 SQL Database 中數個遠端資料庫的一組資料表：

執行這些步驟之後，您就可以存取水準分割數據表 mytable 就像是本機數據表一樣。 Azure SQL Database 會自動開啟遠端資料庫 (實際儲存資料表的位置) 的多個平行連線、處理對於遠端資料庫的要求，以及傳回結果。 如需水平資料分割案例所需步驟的詳細資訊，請參閱 水平資料分割的彈性查詢。

若要開始撰寫程式碼，請參閱開始使用彈性查詢進行水平資料分割 (分區化)。

重要

在大量資料庫上成功執行彈性查詢的關鍵在於，每個資料庫在查詢執行期間的可用性。 如果其中一個資料庫無法使用，整個查詢就會失敗。 如果您打算一次查詢數百個或數千個資料庫，則請確定您的用戶端應用程式已內嵌重試邏輯，或考慮利用彈性工作，以及查詢較小的資料庫子集，將每個查詢的結果合併至單一目的地。

T-SQL 查詢

一旦您已定義外部資料來源和外部資料表，您可以使用一般 SQL Server 連接字串來連接到您定義外部資料表的資料庫。 然後，您可以透過您的外部數據表，在該連線上執行 T-SQL 語句，但需遵守本文稍後所述的限制。 您可以在文件中找到 T-SQL 查詢的詳細資訊和範例，關於 水平數據分割 和 垂直數據分割。

工具的連線能力

您可以使用一般 SQL Server 連接字串，將您的應用程式、BI 或資料整合工具連接到具有外部資料表的資料庫。 請確定 SQL Server 可支援做為您的工具的資料來源。 連線之後，請參考彈性查詢資料庫和該資料庫中的外部資料表，就如同您會使用您的工具連接的任何其他 SQL Server 資料庫一樣。

重要

只有在使用 SQL Server 驗證連線時，才支援彈性查詢。

Cost

彈性查詢會算在 Azure SQL Database 的成本內。 支援遠端資料庫設置在與彈性查詢端點不同的資料中心，但從遠端資料庫輸出的數據將按照 Azure 費率定期收取費用。

預覽限制

  • 在較小型的資源以及標準和一般用途服務層級上，執行第一個彈性查詢最多可能需要幾分鐘的時間。 需要這些時間才能載入彈性查詢功能；較高的服務層級和計算大小可改善載入效能。

  • 尚不支援從 SSMS 或 SSDT 撰寫外部資料源或外部資料表的腳本。

  • SQL Database 的匯入/匯出尚不支援外部數據源和外部數據表。 如果您需要使用匯入/匯出，請在匯出前卸除這些物件，然後在匯入後予以重新建立。

  • 彈性查詢目前僅支援以唯讀方式存取外部資料表。 不過，您可以在定義外部資料表的資料庫上使用完整的 Transact-SQL 功能。 這可以很有用，例如，使用 SELECT <column_list> INTO <local_table>來保存臨時結果，或在彈性查詢資料庫上定義參考外部數據表的預存程序。

  • 除了 nvarchar（max）之外，外部數據表定義不支援 LOB 類型（包括空間類型）。 因應措施是，您可以在遠端資料庫上建立檢視，將 LOB 類型轉換成 nvarchar（max），在檢視上定義外部數據表，而不是基表，然後將它轉換回查詢中的原始 LOB 類型。

  • 結果集中的 nvarchar(max) 資料類型會停用在彈性查詢實作中使用的進階批次處理技術，並可能大幅降低查詢效能，甚至在某些非標準使用案例中，效能可能下降一個數量級甚至兩個數量級，特別是當查詢傳輸大量非聚合數據時。

  • 目前不支援外部資料表的資料行統計資料。 支援資料表統計資料，但必須以手動方式建立。

  • Azure SQL Database 中的外部資料表不支援資料列指標。

  • 彈性查詢僅適用於 Azure SQL Database。 您無法使用它來查詢 SQL Server 實例。

  • 針對外部資料來源目標的資料庫，目前不支援私人連結。

相關內容