【OCI】Data IntegrationでS3上のデータをADWにインポートしてみる

# はじめに

以前、Data Integrationの機能を使ってOCIオブジェクトストレージのデータを
ADWにインポートするというブログを書きましたが、その応用編として
AWS S3のデータをインポートするというのを試してみたいと思います。

■以前のブログ

https://qiita.com/atwits/items/aed51dc68afef9dc8b16

今回はS3にアップロードされたjsonファイル（salesデータ）をData Integrationを
使ってダウンロードしてきて、ファイルの中身を加工してからADWにインポートします。

＊前回からの変更点
・S3に接続するためのデータアセットの設定
・Order IDやタイムスタンプ等、jsonデータに存在しない値の設定

基本的にはS3用のデータアセットが追加になるぐらいで、
あとはデータフロー部分の処理が少し追加になる程度の変更です。

■構成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/6e8969ab-e62e-b691-60c2-8530737ec25e.png)

■データ変換の概要
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/e460e416-abcf-d6c1-d907-54a1879c6250.png)

■事前準備
・アカウント
Data Integration, ADW, オブジェクトストレージが使用できるOCIアカウントを準備
AWS側も同様にS3にアクセス可能なアカウントを準備

・アクセスキー
S3側でアクセスキーを作成

・ADW
事前にインスタンスを作成

・オブジェクトストレージ / S3
OCI, AWSとも事前にバケットを作成

・ネットワークの設定
今回は特にVCNを利用しません。

※OCI関連の設定について、詳細を確認したい場合は前回のブログを参考にしてください。

◆目次
1.jsonファイルのアップロード
2.スキーマ／テーブル作成
3.Data Integration設定
4.データ・フローの実行テスト

## 1. jsonファイルのアップロード

まず初めに、S3にアップロードするjsonファイルを準備します。

sales.json
```
[
  {
    "id": 1,
    "unit price": 200,
    "quantity": 10
  },
  {
    "id": 2,
    "unit price": 350,
    "quantity": 50
  },
    {
    "id": 3,
    "unit price": 1200,
    "quantity": 200
  },
    {
    "id": 4,
    "unit price": 3000,
    "quantity": 5
  },
    {
    "id": 5,
    "unit price": 5000,
    "quantity": 3
  }
]
```

AWSのコンソールにアクセスし、S3を選択して、対象のバケット内に
sales.jsonファイルをアップロード
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/df32df7e-c31d-da5c-22ea-3f5714eadaf8.png)

以上でjsonファイルのアップロードは終了です。

## 2.スキーマ／テーブル作成

続いてスキーマ／テーブルを作成しますので、
ADWの詳細ページからデータベース・アクションのSQLを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/0cf7eda9-8926-337a-3572-b722765c5315.png)

SQLが実行可能なワークシートが表示されるので
ADMINでスキーマ（diuser）を作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/58fb4847-8567-969f-0747-a674b466cae3.png)

・スキーマ（ユーザ）作成SQL
```
CREATE USER diuser IDENTIFIED BY "＜パスワード＞";
GRANT CREATE SESSION TO diuser;
GRANT CREATE TABLE TO diuser;
GRANT UNLIMITED TABLESPACE TO diuser;
```

テーブルも作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/344c57ab-a2c1-ce05-9c70-4c388a2b6d99.png)

・テーブル作成SQL
```
create table DIUSER.tblSales
(
  id number generated always as identity primary key,
  order_id varchar2(50),
  unit_price number(10, 2),
  sales_quantity number(10, 0),
  sales_amount number(10, 0),
  file_name varchar2(255),
  create_timestamp timestamp
)
```
※id列は自動採番になっています。

以上でスキーマ／テーブル作成は終了です。

## 3.Data Integration設定

続いてData Integrationの設定となります。
構成については以下のようなイメージとなります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/7abcc51b-5123-88f8-4b31-e24bd82420ce.png)

※今回オブジェクトストレージは直接的に使用しないのですが、
Data Integrationが内部的に使用するため
オブジェクトストレージもデータアセットとして
作成しておく必要があります。

■機能概要
・ワークスペース
Data Integration全体を管理する仮想スペース

・プロジェクト
タスク（データフロー等）の設計のまとまり
※プログラムのソースコードのようなもの

・アプリケーション
タスクの実行環境
※プログラムの実行ファイルのようなもの

・データアセット
データソース、ターゲットに対する接続ポイント
  
・手順の流れ
[1] ワークスペースの作成
[2] アプリケーションの作成
[3] データアセットの作成
　- 一時保管用[オブジェクトストレージ]の接続ポイント作成
　- ターゲット[ADW]の接続ポイント作成
　- データソース[S3]の接続ポイント作成
[4] プロジェクトの作成
　- データフローの作成
　- タスクの作成
[5] プロジェクトタスクのアプリケーションへのPublish（公開）

それでは早速、Data Integrationの設定に入ります。

***
[1] ワークスペースの作成

左上メニュー[Ξ]からアナリティクスとAI → データ統合を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/817b87fe-c865-7736-c928-77ecb0a85239.png)

ワークスペースを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/6ba3d96b-18b4-b8a9-bfa9-c83582651dae.png)

ワークスペースの作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/9028557c-4727-293f-fff4-05fe76e87837.png)

プライベート・ネットワークの有効化のチェックを外して、作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/9e1eaeba-a39b-69e2-a168-e53f0dcabd3a.png)

※注意事項
Data IntegrationからS3にアクセスする時、Internet Gateway経由では
通信できない仕様（？）となっているため注意が必要です。

ワークスペースをVCNに配置しない：<font color="blue">S3アクセス可</font>
ワークスペースをVCN Public Subnetに配置 <strong>[Internet Gateway経由]</strong>：<font color="red">S3アクセス不可</font>
ワークスペースをVCN Private Subnetに配置 <strong>[NAT Gateway経由]</strong>：<font color="blue">S3アクセス可</font>

上記のことから今回はVCN上に配置しない構成としています。

***
[2] アプリケーションの作成

ワークスペースが作成されると一覧の表示されるので
名前のリンクを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/e3b9fbd9-5716-4c18-2cc4-81af4b374335.png)

アプリケーションを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/dd97d5c8-3463-9770-35ca-e9d5d8796bb4.png)

アプリケーションの作成の新規作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/c9d5d750-e161-85cb-dc45-66c9408f0d49.png)

ブランク・アプリケーションの作成の新規作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/9783b4d6-b95c-d0e8-1cbb-6d9dc41ad7d3.png)

任意の名前をつけて作成を選択
ここではdi_ap01
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/ab606a69-0972-2abc-25bb-eaa2d9cf191d.png)

[3] データアセットの作成
- 一時保管用[オブジェクトストレージ]の接続ポイント作成

続いてデータ・アセットの作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/8d3f5367-3964-cdca-dfb1-a7b16a426cda.png)

OCIオブジェクト・ストレージを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/5d558e5a-82bf-8c46-8fe0-f8ef63e580ab.png)

任意の名前を入力
ここではDA_ConOS01
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/a507536c-5dbc-e41a-e56a-71d18ccaa960.png)

接続テストで問題ないことを確認して作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/363bc416-e944-dec7-42eb-48d9afa9f156.png)

- ターゲット[ADW]の接続ポイント作成

ワークスペースのトップからデータ・アセットの作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/921bfba6-114e-5d2d-7b44-ab7c17042b04.png)

今回はOracle ADWを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/5bce1a48-6ed6-46c1-f609-cf91468d0c39.png)

任意の名前をつけて、タイプがOracle Autonomous Data Warehouseに
なっていることを確認
ここではDA_ConADW01
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/27642974-a07d-021d-2169-7bee1c2561de.png)

ウォレットをアップロードし、パスワードを設定
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/ad65b1c2-035e-7672-adc4-d2b57f6e56ee.png)

ユーザ名 [admin] ／パスワードを入力し、TNSを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/4ffc4460-42b7-9331-cda4-a18eafaf9d7f.png)

下記の項目を入力・選択し、接続テストで問題がないことを確認して
作成を選択
オブジェクト・ストレージ・データ・アセット: 前段で設定したアセット (DA_ConOS01)
接続: デフォルト
コンパートメント: 任意
バケット: 事前に作成しておいたバケット（ここではbucketDI）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/28b3948f-4fa6-c125-ac8d-c6b928bb26cf.png)

- データソース[S3]の接続ポイント作成

ワークスペースのトップからデータ・アセットの作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/921bfba6-114e-5d2d-7b44-ab7c17042b04.png)

検索ボックスにS3を入れて絞り込んでから選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/0c2f98b5-d475-453b-bb53-7dd33ca0754b.png)

以下項目を入力し、下にスクロール
名前: (任意) ここではDA_ConS3
タイプ: Amazon S3
リージョン: (バケットのリージョン) ここではap-northeast-1（東京リージョン）
アカウントID: AWSのアカウントID
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/5c9c8bcd-dfe7-4bc6-dd02-c1a54ecf836c.png)

以下の通り設定し、接続のテストが成功したら作成を選択
名前: デフォルト接続
識別子: DEFAULT_CONNECTION
アクセス・キー: AWSのアクセスキー
秘密キー: AWSのシークレットアクセスキー
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/c695b10f-7f82-7f36-106a-5e5dd6145ae0.png)

以上でオブジェクトストレージ、ADW、S3のデータアセットができました。

***
[4] プロジェクトの作成

ワークスペースのトップに戻り、プロジェクトを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/47e3bddb-c715-9df3-ab68-0bfda3421219.png)

プロジェクトの作成-新規作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/f48d94cc-4991-2e2e-cbcf-34baed5de85d.png)

任意の名前をつけて、作成を選択
ここではpjDI
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/e22b2217-ab50-56be-a0a6-b5f63301fa4a.png)

・データ・フロー

データ・フローを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/7ba7a70d-47d8-fee7-89e0-cc1eacedea8c.png)

データ・フローの作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/75593018-2719-864d-63c5-dc282afc2f01.png)

データフローが起動したら、SOURCE, EXPRESSION, TARGETを配置後にリンク
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/d52a24ad-ebf4-215d-2d93-d809fa6b2573.png)

SOURCEのプロパティの詳細を開き、以下の通り設定
データ・アセット: CA_ConS3
接続: デフォルト接続
スキーマ: S3のバケット
データ・エンティティ: sales.json ※ファイル形式はjsonを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/3f1ef169-045b-3a6a-a578-eceeaab119f5.png)

拡張オプションタブのファイル・メタデータを属性としてフェッチオプションも選択
※ファイル名を取得する際に必要なオプション
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/8f19b62b-27d0-0b02-8c62-c2c14696bd54.png)

次にEXPRESSIONのプロパティの詳細を開き、式の追加を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/96e34e8f-f300-bfb5-e189-a192d50ad09b.png)

・ORDER_IDの設定
式の追加では以下を入力
識別子: EXPRESSION_ORDER_ID
データ型: VARCHAR
長さ: 50 (長さは状況に応じて変更)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/a68a584a-a4b4-f496-82bf-1110f521bf64.png)

式ビルダーでは以下の項目を右ペインにドラッグ＆ドロップ
ファンクション - Unique ID - UUID()
検証して問題なければ追加を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/81a62a68-8a0a-cb56-9026-d637f2aa7228.png)

・SALES_AMOUNTの設定
再度式の追加で以下を入力
識別子: EXPRESSION_SALES_AMOUNT
データ型: INTEGER
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/769f7cfc-0f6f-5a2d-b5bd-e3f499a2ff85.png)

式ビルダーでは以下の項目を右ペインにドラッグ＆ドロップ
受信 - SALES_JSON - unit price & quantity
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/aabdf9ad-0915-7a30-3af5-cd7379899c0c.png)

単価（unit price）× 数量（quantity）= 売上（SALES_AMOUNT）を算出

```
EXPRESSION_1.SALES_JSON.unit_price * EXPRESSION_1.SALES_JSON.quantity
```
上記、検証して問題なければ追加を選択

・FILE_NAMEの設定
続けて式の追加で以下を入力
識別子: EXPRESSION_FILE_NAME
データ型: VARCHAR
長さ: 50 (長さは状況に応じて変更)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/67e8efcf-15b4-c96f-073a-30b73002fd83.png)

式ビルダーでは以下の項目を右ペインにドラッグ＆ドロップ
受信 - SALES_JSON - file_name
検証して問題なければ追加を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/bd81ff30-3fee-7f4b-afbe-e851b2ccb100.png)

・CREATE_TIMESTAMPの設定
最後の式の追加で以下を入力
識別子: EXPRESSION_CREATE_TIMESTAMP
データ型: TIMESTAMP
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/28ce871b-193a-72a1-55c8-46455d0f54cf.png)

式ビルダーでは以下の項目を右ペインにドラッグ＆ドロップ
ファンクション - Date/Time - CURRENT_TIMESTAMP
検証して問題なければ追加を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/f908592d-9fd7-a442-a554-be153464c9e3.png)

データフローの最後ステップとしてTARGETのプロパティの詳細を開き、以下の通り設定
データ・アセット: CA_ConADW
接続: デフォルト接続
スキーマ: DIUSER (事前に作成しておいたスキーマ)
データ・エンティティ: TBLSALES（事前に作成しておいたテーブル）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/bc2c72e7-fa15-996f-73dc-f873fc152c18.png)

マップタブを開くと左側にjsonファイルや先程設定したEXPRESSIONの項目が
表示されているので、ドラッグ＆ドロップで右側のTBLSALESとマッピングさせる
※既に自動マッピングされているものはそのままにするか、クリアして再マッピング
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/1cfb2061-19a5-b62f-bd1c-45f11842e7c6.png)

最終的に以下のようにマッピング
・TBLSALES
**ID**:  <font color="red">マッピングしない（テーブルで自動採番されるため）</font>
**ORDER_ID**: <font color="blue">EXPRESSION_ORDER_ID</font>
**UNIT_PRICE**: <font color="blue">unit_price</font>
**SALES_QUANTITY**: <font color="blue">quantity</font>
**SALES_AMOUNT**: <font color="blue">EXPRESSION_SALES_AMOUNT</font>
**FILE_NAME**: <font color="blue">file_name</font>
**CREATE_TIMESTAMP**: <font color="blue">EXPRESSION_CREATE_TIMESTAMP</font>

最後に検証をして問題がなければデータフローの作成は完了です。
保存して終了します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/d36e5587-660f-8691-cdc2-721395396122.png)

・タスク
タスク - タスクの作成 - 統合を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/8c489ea1-df7f-9231-5482-b6cf634c251c.png)

任意の名前、データ・フローを選択して作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/d12c2ea9-1514-fdab-223d-81836cb6f299.png)

一覧のタスクの右端にあるプロパティからアプリケーションに公開を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/0e29a980-3285-d05f-0157-185eb434b5cb.png)

事前に作成したアプリケーション(di_ap01)を選択して公開を選択
しばらくするとアプリケーションに公開される
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/dab494ec-9ab1-1cdc-dc84-ea3dbd86861d.png)

以上でData Integration設定は終了です。

## 4.データ・フローの実行テスト

最後にData Integration設定が正常にできているか確認をします。

アプリケーション - di_ap01 - タスクを実行
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/018b1fc4-afff-c119-7b19-6af6ed9f9751.png)

実行結果が正常に完了していたら場合はステータスが成功となる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/722d8ab9-02bd-e5aa-8c46-6a6dd76a50d1.png)

※もちろんスケジュールに登録すれば定期的に実行させることも可能です。

最後にデータが登録されているかテーブルを検索
select * from DIUSER.TBLSALES;
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/8b8954d3-83dc-d0a1-f4e9-263c12e2280e.png)

以上でデータ・フローの実行テストは終了です。

## おわり
以上、Data Integrationを使えばS3とのデータ連携や
データの加工も比較的簡単にできることが分かりました。