對不同結構描述的雲端資料庫執行查詢（預覽版）

  • 發行項

適用於：Azure SQL Database

Query across tables in different databases

垂直分割的資料庫在不同的資料庫上使用不同的數據表集合。 這表示不同資料庫的結構描述不同。 比方說，庫存的所有資料表都位於一個資料庫上，而所有會計相關資料表則位於另一個資料庫上。

必要條件

  • 使用者必須擁有 ALTER ANY EXTERNAL DATA SOURCE 權限。 這個權限包含在 ALTER DATABASE 權限中。
  • 需有 ALTER ANY EXTERNAL DATA SOURCE 權限，才能參考基礎資料來源。

概觀

注意

與水平數據分割不同，這些 DDL 語句不相依於透過彈性資料庫用戶端連結庫定義具有分區對應的數據層。

  1. CREATE MASTER KEY
  2. CREATE DATABASE SCOPED CREDENTIAL
  3. CREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLE

建立資料庫範圍的主要金鑰和認證

彈性查詢使用認證連結到遠端資料庫。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master_key_password';
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]  WITH IDENTITY = '<username>',  
SECRET = '<password>';

注意

請確定 <username> 不含任何 「@servername」 後置詞。

建立外部資料來源

語法：

<External_Data_Source> ::=
CREATE EXTERNAL DATA SOURCE <data_source_name> WITH
    (TYPE = RDBMS,
    LOCATION = '<fully_qualified_server_name>',
    DATABASE_NAME = '<remote_database_name>',  
    CREDENTIAL = <credential_name>
    ) [;]

重要

TYPE 參數必須設定為 RDBMS。

範例

下列範例說明如何針對外部數據源使用 CREATE 語句。

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
   WITH
      (
         TYPE=RDBMS,
         LOCATION='myserver.database.windows.net',
         DATABASE_NAME='ReferenceData',
         CREDENTIAL= SqlUser
      );

擷取目前的外部資料來源清單：

select * from sys.external_data_sources;

外部資料表

語法：

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name  
    ( { <column_definition> } [ ,...n ])
    { WITH ( <rdbms_external_table_options> ) }
    )[;]

<rdbms_external_table_options> ::=
    DATA_SOURCE = <External_Data_Source>,
    [ SCHEMA_NAME = N'nonescaped_schema_name',]
    [ OBJECT_NAME = N'nonescaped_object_name',]

範例

CREATE EXTERNAL TABLE [dbo].[customer]
   (
      [c_id] int NOT NULL,
      [c_firstname] nvarchar(256) NULL,
      [c_lastname] nvarchar(256) NOT NULL,
      [street] nvarchar(256) NOT NULL,
      [city] nvarchar(256) NOT NULL,
      [state] nvarchar(20) NULL
   )
   WITH
   (
      DATA_SOURCE = RemoteReferenceData
   );

從目前的資料庫擷取外部資料表清單：

select * from sys.external_tables;

備註

彈性查詢會擴充現有的外部數據表語法，以定義使用 RDBMS 類型外部數據源的外部數據表。 垂直資料分割的外部資料表定義涵蓋下列層面：

  • 架構：外部數據表 DDL 會定義查詢可以使用的架構。 外部數據表定義中提供的架構必須符合儲存實際數據之遠端資料庫中數據表的架構。
  • 資料層參考：外部資料表 DDL 指的是外部資料來源。 外部數據源會指定儲存實際數據表數據之遠端資料庫的伺服器名稱和資料庫名稱。

如上一節所述，使用外部數據源建立外部數據表的語法如下：

DATA_SOURCE 子句會定義用於外部數據表的外部數據源（也就是垂直數據分割中的遠端資料庫）。

SCHEMA_NAME和OBJECT_NAME子句允許將外部數據表定義對應至遠端資料庫上不同架構中的數據表，或分別對應至具有不同名稱的數據表。 如果您想要在遠端資料庫上將外部資料表定義為目錄檢視或 DMV，或遠端資料表名稱已在本機取得的任何其他情況，此對應會很有用。

下列 DDL 語句會從本機目錄卸除現有的外部數據表定義。 這不會影響遠程資料庫。

DROP EXTERNAL TABLE [ [ schema_name ] . | schema_name. ] table_name[;]

CREATE/DROP EXTERNAL TABLE 的許可權：外部數據表 DDL 需要 ALTER ANY EXTERNAL DATA SOURCE 許可權，這也需要參考基礎數據源。

安全性考量

可存取外部資料表的使用者可以在外部資料來源定義中所提供的認證下，自動取得基礎遠端資料表的存取權。 請謹慎管理外部資料表的存取權，以避免外部資料來源的認證意外提高權限。 一般的 SQL 權限可用來授與或撤銷外部資料表的存取權，如同一般資料表那樣。

範例：查詢垂直分割的資料庫

下列查詢會針對訂單和訂單行，以及客戶的遠端數據表，執行兩個本機數據表之間的三向聯結。 這是彈性查詢參考數據使用案例的範例：

    SELECT
     c_id as customer,
     c_lastname as customer_name,
     count(*) as cnt_orderline,
     max(ol_quantity) as max_quantity,
     avg(ol_amount) as avg_amount,
     min(ol_delivery_d) as min_deliv_date
    FROM customer
    JOIN orders
    ON c_id = o_c_id
    JOIN  order_line
    ON o_id = ol_o_id and o_c_id = ol_c_id
    WHERE c_id = 100

用於遠端 T-SQL 執行的預存程序：sp_execute_remote

彈性查詢也會介紹可供直接存取分區的預存程序。 預存程序稱為 sp_execute _remote，可用來在遠端資料庫上執行遠端預存程序或 T-SQL 程式碼。 它需要以下參數：

  • 資料來源名稱 (nvarchar)：RDBMS 類型的外部資料來源名稱。
  • 查詢 （nvarchar）：要於遠端資料庫上執行的 T-SQL 查詢。
  • 參數宣告 (nvarchar) - 選用：含有查詢參數 (如 sp_executesql) 中所用參數的資料類型定義的字串。
  • 參數值清單 - 選用：以逗號分隔的參數值清單 (如 sp_executesql)。

sp_execute_remote 會使用叫用參數中提供的外部資料來源，在遠端資料庫上執行指定的 T-SQL 陳述式。 它會使用外部資料來源的認證連結 shardmap 管理員資料庫和遠端資料庫。

範例：

    EXEC sp_execute_remote
        N'MyExtSrc',
        N'select count(w_id) as foo from warehouse'

工具的連線能力

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

最佳作法

  • 藉由在其 Azure SQL 資料庫防火牆組態中啟用 Azure 服務的存取，確保彈性查詢端點資料庫已獲得遠端資料庫的存取權。 必要： 請確定外部資料來源定義中提供的認證可成功登入遠端資料庫，並且有足夠的權限可存取遠端資料表。
  • 效能：彈性查詢最適合可在遠端資料庫上完成大部分運算的查詢。 使用可在遠端資料庫上評估的選擇性篩選述詞，或可在遠端資料庫上完全執行的聯結，通常可以獲得最佳查詢效能。 其他查詢模式可能需要從遠端資料庫載入大量的資料，而且執行效能可能會很差。

下一步