[Autonomous Database] Data TransformsでObject StorageからAutonomous Databaseの連携を試してみた

はじめに

Autonomous Database Serverlessにはさまざまなツール群が付属しています。Data Transformsもその1つであり、ELTによるデータ連携が可能です。
本記事ではObject Storageにあるファイルemp.csvに変換をかけて、Autonomous DatabaseのDEPT表と結合して新しい表FLOW_OUTP1を作成するというデータ・フローの作成、実行を試してみました。Object Storageを利用する場合とData Transformsの利用例という点で役に立つところがあるとうれしいです。なお、Object Storageを使ったときの注意点はおわりにでまとめています。

1.準備

1-1.Data Transformsの準備

こちらのチュートリアルの事前準備を参考に、Autonomous Databaseのデータベース・ユーザtestuserにData Transformsを使用できるようにします。

1-2.testuserで資格証明とDEPT表を作成

Object StorageからAutonomous Databaseをターゲットにするためには、資格証明を作成する必要があるとあったので、testuserで資格証明をCRED_TESTUSERという名前で作成します。指定するユーザ名とパスワードに指定する認証トークンについてはこちらのチュートリアルの2-1.OCIオブジェクトストレージへのアクセス情報を取得をご覧ください。

 BEGIN  
 DBMS_CLOUD.CREATE_CREDENTIAL(
                         CREDENTIAL_NAME  => 'CRED_TESTUSER',
                         USERNAME         => 'myUsername',
                         PASSWORD         => 'LPB>Ktk(1M1SD+a]+r'
 );
 END;

次にtestuserでDEPT表を作成します。

CREATE TABLE DEPT
  (
  DEPTNO NUMBER(2),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
  CONSTRAINT dept_pk PRIMARY KEY (deptno)
  )
/ 
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

2.ソース、ターゲットへの接続の作成

Data Transformsにtestuserでログインし、メニューのConnectionsでCreate Connectionをクリックして、Connectionを作成します。

2-1.Autonomous Databaseの接続

Autonomous Database用のConnectionをconn_atp1という名前で作成します。作成方法はチュートリアルの3-1 Autonomous Databaseへの接続をご確認ください。

2-2.Object Storageの接続

Object Storage用のConnectionを作成します。Select TypeでOracle Object Storageを選択して、Nextをクリックします。

表示された画面で以下を環境に合わせて入力します。

• Connection Name : conn_obj
• Object Storage URL : https://objectstorage.Region.oraclecloud.com/n/TenancyName/b/bucketName/o
• Region、Namespace、Bucketは入力値から導出され自動入力されます
• Connectivity : Swift Connectivity
• User Name：OCIユーザ名
• Token: 認証トークン

入力後、Test Connectionで接続の成功を確認し、Createをクリックします。

ConnectivityにCredentialの選択がありますが、既存のユーザーのクレデンシャルを指定してもTest Connectionで以下のエラーになりました。内部ユーザのODI_REPO_USERのクレデンシャルとなるようなので実質指定できないと考えてよさそうです。
ORA-20004: Credential "ODI_REPO_USER"."ODI" does not exist

3.Data Entityのインポート

次にデータ・フローで使用するデータ・エンティティをインポートします。Data TransformsホームのメニューのData Entitiesで、Import Data Entitiesをクリックします。

3-1.Autonomous DatabaseのData Entityのインポート

チュートリアルの3-2. データベース内のデータをインポートするを参考に、DEPTをインポートします。

3-2.Object StorageのData Entityのインポート

Object Storageには以下のCSV形式とJSON形式のファイルがあります。

emp.csvの内容はこちらのとおりです。

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,1980-12-17,800.00,,20
7900,JAMES,CLERK,7698,1981-12-03,950.00,,30
7876,ADAMS,CLERK,7788,1983-01-12,1100.00,,20
7521,WARD,SALESMAN,7698,1981-02-22,1250.00,500.00,30
7654,MARTIN,SALESMAN,7698,1981-09-28,1250.00,1400.00,30
7934,MILLER,CLERK,7782,1982-01-23,1300.00,,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500.00,0.00,30
7499,ALLEN,SALESMAN,7698,1981-02-20,1600.00,300.00,30
7782,CLARK,MANAGER,7839,1981-06-09,2450.00,,10
7698,BLAKE,MANAGER,7839,1981-05-01,2850.00,,30
7566,JONES,MANAGER,7839,1981-04-02,2975.00,,20
7788,SCOTT,ANALYST,7566,1982-12-09,3000.00,,20
7902,FORD,ANALYST,7566,1981-12-03,3000.00,,20
7839,KING,PRESIDENT,,1981-11-17,5000.00,,10

Data TransformsホームのメニューのData Entitiesで、Import Data Entitiesをクリックします。Mask fileterd Filesで確認すると、csvのファイルだけが表示されます。JSON形式はサポートされていないからだと思われます。

Mask fileterd Filesには何も指定せず、Startをクリックすると、ジョブが実行され、表示されているファイルをすべてImportすることができます。ジョブ完了後、Data Entitiesの画面をリフレッシュするとインポートしたデータエンティティが表示されます。

右端の3点リーダーから、選択したData EntityのEdit、Previewが可能です。

Data Entitiesはインポートだけでなく新規に作成することも可能です。新規に作成したData EntityはData FlowやData Loadで利用できます。

4.Data Flowの作成

ホームのメニューのProjectsで、Create Data Flowをクリックし、データ・フローを作成します。

続けて、Add a Schemaが表示されるので利用するスキーマとして、作成したObject Storageのconnectionの情報を設定します。

4-1.EMPの変換

EMPに対してSALの値が0～1000はLOW、1000から3000をMID、3000以上をHIGHとしたSAL_LEVELの列を作成するという変換を行います。
左のData EntitiesからEMPをキャンバスにドラッグします。次に上のData Transformの項目からExpressionをドラッグしたあと、EMPからExpressionへドラッグしてリンクします。

次にExpressionを選択し、右端のプロパティを拡大します。
新しい属性SAL_LEVELを追加します。Attributesで+をクリックすると、一番下にNEW_ATTRが追加されるので、NameをSAL_LEVELに変更し、Data TypeはVARCHARにします。

属性を追加した後、Column Mappingで入力のEMPの属性とのマッピングを行います。Auto Mapを利用すると、By Name（名前）かBy Position（位置）で自動マッピングができます。By Nameをクリックすると、SAL_LEVEL以外がマッピングされました。

SAL_LEVELは手動でマッピングします。SAL_LEVELの編集アイコンをクリックし、Expression Editorを表示します。SourcesのEMPからSALをExpressionの欄にドラッグし、CASE句を使って条件を記述してOKをクリックしました。

すべての属性がマッピングされたことを確認して、プロパティを縮小します。

4-2.DEPTとの結合

次にAutonomous DatabaseのDEPT表と結合します。
右のData EntitiesにあるAdd a Schemaをクリックし、Autonomous DatabaseのConnectionを追加します。

左のData EntitiesからDEPTをキャンバスにドラッグします。次に上のData Transformの項目からJoinをドラッグしたあと、Expression、DEPTからJoinへドラッグしてリンクします。

Joinを選択し、右端のプロパティを拡大します。
AttributesのJoin Conditionで、DEPTNOで結合する条件を記載して、縮小します。Expressionのときと同様にExpression Editorでの編集ができます。

SourcesにはリンクしたExpression、DEPTだけでなく、Other SourceとしてEMPも表示がされます。

4-3.結果表の作成

変換、結合した結果をAutonomous Databaseに新しい表FLOW_OUT1として作成します。
Joinを選択して、右上のグリッド状の表のアイコンをクリックします。

作成する表の定義を行います。

次に構成する列を定義します。デフォルトでは使用できるすべての属性が表示されます。

不要な属性の削除や名前の変更、データ型の変更を行います。今回はExpressionのDEPTNOとDEPTのDEPTNOがそれぞれExpression_DEPTNO、DEPT_DEPTNOとあるため、どちらも削除することにします。属性にチェックをして、ゴミ箱アイコンをクリックします。

Nextをクリックし、Previewで確認後Saveします。

保存をすると、EMPからExpressionへのリンクにTransfarが表示されます。

Transfarを選択すると、右側のプロパティでOptionの編集ができます。Credential nameがデフォルトではODIとなっているため、あらかじめ作成していた資格証明の名前に変更します。

5.検証と実行

フローの作成が完了したので、保存をしてから、Validateで検証を行います。

検証が成功しましたら、Code Simulationで生成されるコードを確認してみます。

内容を見てみると、次のような流れでした。

• ステージングの表の作成、ターゲットの表を作成
  
• ファイルのURIを生成
  
• DBMS_CLOUD.COPY_DATAを使って、ステージングの表にデータをロード。TransfarのOptionはこちらで指定されるもののようです。
  
• ステージング表からターゲット表へ挿入
  
  実行をしてみます。
  
  ジョブの状況は、データ・フローの右側に表示がされます。Current StatusがrunningからDoneになり、成功しました。もしエラーになった場合は、Execution Jobに表示されているジョブのリンクをたどってエラーを確認して対応します。
  
  キャンバス上のFLOW_OUT1を選択して、Previewを確認すると、データが正しく挿入されていることが確認できました。
  

おわりに

Data Transformsを使って、Object StorageのファイルをAutonomous Databaseに変換、結合して連携してみました。Object Storageをソースとして利用する場合は以下の注意がドキュメントにあります。

• Oracle Object Storage接続を使用してデータをData Transformsにインポートするには、パブリックIPアドレスを使用して計算ノードにアクセスする必要があります。プライベート IP アドレスを使用して Object Storage サービスにアクセスする場合は、インターネットにアクセスできることを確認してください。
• Oracle Object StorageからAutonomous Databaseへ、またはその逆にデータをロードするためにサポートされているファイル形式はCSVです。
• サポートされているデータ型は、数値、倍精度浮動小数点型、文字列型、日付型です。
• データ・ロードは、Oracle Object Storage接続ではサポートされていません。
  Oracle Object Storage接続をマッピングのターゲットとして使用することはできません。
• Oracle Object Storage接続を作成するには、Oracle Cloud Infrastructureユーザー名と認証トークンが必要です。認証トークンを生成する方法については、「認証トークンの取得」を参照してください。
• Object StorageからAutonomous Databaseにデータをマップするデータ・フローを作成する場合はターゲット・スキーマに資格証明を作成する必要があります。

また2023/9に実行してみたところでは日本語は扱えませんでした。emp_jp.csvは日本語データが入ったファイルだったのですが、Previewで参照するとこちらのように表示がされません。

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7839,中島 亜希子,社長,,81-11-17,5000,,10
7698,伊藤 明子,マネージャー,7839,81-05-01,2850,,30
7782,坂本 明,マネージャー,7839,81-06-09,2450,,10
7566,関口 晃,マネージャー,7839,81-04-02,2975,,20
7788,新井 敦子,アナリスト,7566,82-12-09,3000,,20

外部表としてアクセスする場合は日本語も表示されたので、Database Actionsのデータロードのデータのリンクを利用して外部表を作成してからData Transformsで利用する、Data Transformsで完結したい場合は、PL/SQLを記述することができるWorkflowを利用するなどの対応が必要になると思います。

参考資料

• Transform Data Using Data Studio
• 403 : Data Transformsを使ってみよう
• 102: ADBにデータをロードしよう(Database Actions)