チュートリアル:SQL Server ビッグデータクラスターから Oracle にクエリを実行する

適用対象: SQL Server 2019 (15.x)

Important

Microsoft SQL Server 2019 ビッグデータクラスターは廃止されました。 SQL Server 2019 ビッグデータクラスターのサポートは、2025 年 2 月 28 日の時点で終了しました。詳細については、Microsoft SQL Server プラットフォームの発表ブログ投稿とビッグデータオプションを参照してください。

このチュートリアルでは、SQL Server 2019 ビッグデータクラスターから Oracle データにクエリを実行する方法について説明します。このチュートリアルを実行するには、Oracle サーバーにアクセスできる必要があります。外部オブジェクトに対して読み取り特権を持つ Oracle ユーザーアカウントが必要です。 Oracle プロキシユーザー認証がサポートされています。アクセスできない場合は、このチュートリアルで、SQL Server ビッグデータクラスター内の外部データソースに対してデータ仮想化がどのように機能するかを理解できます。

このチュートリアルでは、以下の内容を学習します。

外部 Oracle データベースのデータ用の外部テーブルを作成する。
このデータを、マスターインスタンスの価値の高いデータと結合する。

Tip

必要に応じて、このチュートリアルのコマンド用のスクリプトをダウンロードして実行できます。手順については、GitHub のデータ仮想化のサンプルを参照してください。

Prerequisites

ビッグデータツール
- 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 マスターインスタンスのサーバーダッシュボードを表示します。 [New Query] を選択します。
次の 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 プロキシ認証

Oracle は、きめ細かいアクセス制御を備えるプロキシ認証をサポートしています。プロキシユーザーは、その資格情報を使用して Oracle データベースに接続し、データベース内の別のユーザーの権限を借用します。

プロキシユーザーは、権限が借用されているユーザーと比較してアクセスが制限されるように構成することができます。たとえば、プロキシユーザーは、権限が借用されているユーザーの特定のデータベースロールを使用して接続することができます。複数のユーザーがプロキシ認証を使用して接続している場合でも、プロキシユーザーを介して Oracle データベースに接続しているユーザーの ID は接続に保持されます。 Oracle では、これを利用してアクセス制御を実施し、実際のユーザーに代わって実行されたアクションを監査することができます。

oracle プロキシユーザーの使用が必要なシナリオの場合は、前の手順の 4 と 5 を次のものに置き換えてください。

Oracle サーバーに接続するためのデータベーススコープ資格情報を作成します。次のステートメントで、Oracle サーバーに適切な 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_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>');

Note

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;

Clean up

このチュートリアルで作成されたデータベースオブジェクトを削除するには、次のコマンドを使用します。

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

Next steps

データプールにデータを取り込む方法を学習します:

データをデータプールに読み込む

最終更新: 2020-10-01