教學課程：從 SQL Server 巨量資料叢集查詢 Oracle

發行項
05/04/2023

適用於：SQL Server 2019 (15.x)

重要

Microsoft SQL Server 2019 巨量資料叢集附加元件將會淘汰。 SQL Server 2019 巨量資料叢集的支援將於 2025 年 2 月 28 日結束。平台上將完全支援含軟體保證 SQL Server 2019 的所有現有使用者，而且軟體將會持續透過 SQL Server 累積更新來維護，直到該時間為止。如需詳細資訊，請參閱公告部落格文章與 Microsoft SQL Server 平台上的巨量資料選項。

本教學課程示範如何查詢 SQL Server 2019 巨量資料叢集中的 Oracle 資料。若要執行本教學課程，您要能夠存取 Oracle 伺服器。需要具有外部物件讀取權限的 Oracle 使用者帳戶。支援 Oracle Proxy 使用者驗證。如果您無法存取，本教學課程可讓您了解 SQL Server 巨量資料叢集中，外部資料來源的資料虛擬化運作方式。

在本教學課程中，您會了解如何：

為外部 Oracle 資料庫中的資料建立外部資料表。
將此資料與主要執行個體中的高價值資料聯結。

提示

如果您想要的話，也可以下載並執行用於本教學課程中命令的指令碼。如需指示，請參閱 GitHub 上的資料虛擬化範例。

必要條件

巨量資料工具
- kubectl
- Azure Data Studio
- SQL Server 2019 延伸模組
將範例資料載入巨量資料叢集

建立 Oracle 資料表

下列步驟會在 Oracle 中建立名為 INVENTORY 的範例資料表。

連線到您要用於本教學課程的 Oracle 執行個體和資料庫。

執行下列陳述式，建立 INVENTORY 資料表：

 CREATE TABLE "INVENTORY"
 (
     "INV_DATE" NUMBER(10,0) NOT NULL,
     "INV_ITEM" NUMBER(10,0) NOT NULL,
     "INV_WAREHOUSE" NUMBER(10,0) NOT NULL,
     "INV_QUANTITY_ON_HAND" NUMBER(10,0)
 );

 CREATE INDEX INV_ITEM ON HR.INVENTORY(INV_ITEM);

將 inventory.csv 檔案的內容匯入此資料表。此檔案是由必要條件一節中的範例建立指令碼所建立。

建立外部資料來源

第一個步驟是建立可存取 Oracle 伺服器的外部資料來源。

在 Azure Data Studio 中，連線到巨量資料叢集的 SQL Server 主要執行個體。如需詳細資訊，請參閱連線到 SQL Server 主要執行個體。
按兩下 [伺服器] 視窗中的連線，顯示 SQL Server 主要執行個體的伺服器儀表板。選取 [新增查詢]。
執行下列 Transact-SQL 命令，將內容變更為主要執行個體中的 Sales 資料庫。
```
USE Sales
GO
```

建立資料庫範圍認證以連線到 Oracle 伺服器。在下列陳述式中，為您的 Oracle 伺服器提供適當的認證。

CREATE DATABASE SCOPED CREDENTIAL [OracleCredential]
WITH IDENTITY = '<oracle_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_user_password,nvarchar(100),manager>';

建立指向 Oracle 伺服器的外部資料來源。

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (LOCATION = 'oracle://<oracle_server,nvarchar(100)>',CREDENTIAL = [OracleCredential]);

選用：Oracle Proxy 驗證

Oracle 支援 Proxy 驗證，以提供精細的存取控制。 Proxy 使用者會使用其認證來連線到 Oracle 資料庫，並在資料庫中模擬另一位使用者。

相較於模擬的使用者，Proxy 使用者可以設定為具有有限的存取權。例如，您可以使用所模擬使用者的特定資料庫角色，允許 Proxy 使用者進行連線。透過 Proxy 使用者連線到 Oracle 資料庫的使用者身分識別會保留在連線中，即使有多個使用者使用 Proxy 驗證進行連線也一樣。這可讓 Oracle 強制執行存取控制，並代表實際的使用者稽核所採取的動作。

如果您的案例需要使用 Oracle Proxy 使用者，請以下列內容取代先前的步驟 4 和 5。

建立資料庫範圍認證以連線到 Oracle 伺服器。在下列陳述式中，為您的 Oracle 伺服器提供適當的 Oracle Proxy 使用者認證。

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
WITH IDENTITY = '<oracle_proxy_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_proxy_user_password,nvarchar(100),manager>';

建立指向 Oracle 伺服器的外部資料來源。

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (LOCATION = 'oracle://<oracle_server,nvarchar(100)>',
CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
CREDENTIAL = [OracleProxyCredential]);

建立外部資料表

接下來，透過 Oracle 伺服器上的 INVENTORY 資料表，建立名為 iventory_ora 的外部資料表。

CREATE EXTERNAL TABLE [inventory_ora]
    ([inv_date] DECIMAL(10,0) NOT NULL, [inv_item] DECIMAL(10,0) NOT NULL,
    [inv_warehouse] DECIMAL(10,0) NOT NULL, [inv_quantity_on_hand] DECIMAL(10,0))
WITH (DATA_SOURCE=[OracleSalesSrvr],
        LOCATION='<oracle_service_name,nvarchar(30),xe>.<oracle_schema,nvarchar(128),HR>.<oracle_table,nvarchar(128),INVENTORY>');

注意

查詢 Oracle 時，資料表名稱和資料行名稱會使用 ANSI SQL 引號識別項。因此，名稱會區分大小寫。在外部資料表定義中指定名稱時，請務必完全符合 Oracle 中繼資料中的資料表和資料行名稱大小寫。

查詢資料

執行下列查詢，將 iventory_ora 外部資料表中的資料與本機 Sales 資料庫中的資料表聯結。

SELECT TOP(100) w.w_warehouse_name, i.inv_item, SUM(i.inv_quantity_on_hand) as total_quantity
  FROM [inventory_ora] as i
  JOIN item as it
    ON it.i_item_sk = i.inv_item
  JOIN warehouse as w
    ON w.w_warehouse_sk = i.inv_warehouse
 WHERE it.i_category = 'Books' and i.inv_item BETWEEN 1 and 18000 --> get items within specific range
 GROUP BY w.w_warehouse_name, i.inv_item;

清除

使用下列命令，移除本教學課程所建立的資料庫物件。

DROP EXTERNAL TABLE [inventory_ora];
DROP EXTERNAL DATA SOURCE [OracleSalesSrvr] ;
DROP DATABASE SCOPED CREDENTIAL [OracleCredential];

後續步驟

了解如何將資料內嵌至資料集區：

將資料載入資料集區