教學課程：從 SQL Server 巨量數據叢集查詢 Oracle

2020-10-01

適用於：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 資料庫的使用者身分識別仍會保留在連線中。這可讓 Oracle 強制執行訪問控制，並稽核代表實際使用者採取的動作。

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

建立資料庫範圍認證以連線到 Oracle 伺服器。在下列語句中，將適當的 Oracle Proxy 使用者認證提供給 Oracle 伺服器。

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 的外部數據表。

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 元數據中資料表和欄位名稱的確切大小寫。

查詢資料

執行下列查詢，將外部數據表中的數據 inventory_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];

後續步驟

瞭解如何將資料匯入資料池：

將數據載入數據集區

共用方式為