AWS S3からOracle DBにデータを導入する

Last updated at 2024-08-30Posted at 2021-09-21

本ブログは、オラクル・クラウドの個人ブログの1つです。

初めに

前回の記事で、OCI Object Storageから、CSV/DUMPファイルをOracle DBに導入する方法を紹介しました。この方法は、AWS S3とAzure Blobにも適用します。

今回は、AWS S3にあるCSVとDUMPファイルをOracle DBに導入する方法をご紹介します。

利用条件
Oracle DB 19cのバージョンは、19.9以上です。 Oracle DB 21cのバージョンは、21.3以上です。

Autonomous DBにデータを導入するのは、とても簡単です。DBMS_CLOUDパッケージは既にインストールされているので、手動のインストールは不要です。下記のSTEP9から実施すればOKです。

※この例は、OCI上のDB19c(VM)インスタンスを利用しますが、この方法は、On-P/Cloud両方のDBに、適用します。

ステップ

STEP1~STEP8　前回の記事のステップと同様（省略）
STEP 9 - クレデンシャルの作成と検証
STEP10 - AWS S3からCSVファイルをDBにコピー
STEP11 - AWS S3からDUMPファイルをDBに導入

STEP 9 - クレデンシャルの作成と検証

AWS S3上のデータをアクセスするため、クレデンシャルは必要です。

アクセスキーの作成
IAMユーザのアクセスキーの作成方法は、こちらをご参照ください

My Security Credentials->Access keys (access key ID and secret access key)->Create New Access Key
作成後、キーをコピーして安全な場所に保管してください。

クレデンシャルの作成

対象ユーザ(USER01)でPDBにログインします。(既にDBMS_CLOUDの利用権限は付与済です。)

SQL> show con_name;

CON_NAME
------------------------------
DB19C_PDB1
SQL> show user;
USER is "USER01"
SQL>

DBMS_CLOUDパッケージを利用し、クレデンシャルを作成します。スクリプトは、以下のようです。

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'your credential name',
username => 'Access_Key_ID',
password => 'Secret_Access_Key'
);
END;
/

SQL> BEGIN
  2  DBMS_CLOUD.CREATE_CREDENTIAL(
  3  credential_name => 'AWS_CRED',
  4  username => 'Access_Key_ID(略)',
  5  password => 'Secret_Access_Key(略)'
  6  );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>

AWS S3のオブジェクトを取得

AWSコンソールより、対象オブジェクトのURLを取得します。

Object URL:　https://<bucket_name>.s3.<AZ_ID>.amazonaws.com/<object_name>
例：https://mysourcebucket001.s3.ap-northeast-1.amazonaws.com/channels.txt

"DBMS_CLOUD.LIST_OBJECTS"を使って、AWS S3のバケットをアクセスし、オブジェクトを一覧表示します。

コマンド：

select * from dbms_cloud.list_objects('AWS_CRED','https://mysourcebucket001.s3.ap-northeast-1.amazonaws.com/');

実施例：

SQL> col OBJECT_NAME format a15
SQL> col CHECKSUM format a35
SQL> col CREATED format a10
SQL> col LAST_MODIFIED format a40
SQL> set linesize 120
SQL> select * from dbms_cloud.list_objects('AWS_CRED','https://mysourcebucket001.s3.ap-northeast-1.amazonaws.com/');

OBJECT_NAME          BYTES CHECKSUM                            CREATED    LAST_MODIFIED
--------------- ---------- ----------------------------------- ---------- ----------------------------------------
channels.txt           104 f302635b0cb67f816c33fade42059c01               21-09-14 06:06:10.000000 +00:00

SQL>

STEP10 - AWS S3からCSVファイルをDBにコピー

この例は、以下のようなCSVファイルを利用します。(AWS S3に保存されます。)

バケット名：mysourcebucket001
オブジェクト名：channels.txt
オブジェクトの中身：

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others

事前にOracle DBにテーブルを作成しておきます。(ユーザ"USER01")
コマンド：

CREATE TABLE CHANNELS
   (channel_id CHAR(1),
    channel_desc VARCHAR2(20),
    channel_class VARCHAR2(20)
   );
/

実施例：

SQL> show user;
USER is "USER01"
SQL> CREATE TABLE CHANNELS
  2     (channel_id CHAR(1),
  3      channel_desc VARCHAR2(20),
  4      channel_class VARCHAR2(20)
  5     );

Table created.

DBMS_CLOUD.COPY_DATAを利用し、データをコピーします。
コマンド：

BEGIN
DBMS_CLOUD.COPY_DATA(
    table_name =>'CHANNELS',
    credential_name =>'AWS_Credential_Name',
    file_uri_list =>'AWS_S3_Object_URL',
    format => json_object('delimiter' value ',')
 );
END;
/

実施例：

SQL> BEGIN
  2   DBMS_CLOUD.COPY_DATA(
  3      table_name =>'CHANNELS',
  4      credential_name =>'AWS_CRED',
  5      file_uri_list =>'https://mysourcebucket001.s3.ap-northeast-1.amazonaws.com/channels.txt',
  6      format => json_object('delimiter' value ',')
  7   );
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>

コピー後、テーブルの中身を確認します。

SQL> select * from CHANNELS;

C CHANNEL_DESC         CHANNEL_CLASS
- -------------------- --------------------
S Direct Sales         Direct
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others

SQL>

ここまで、CSVファイルの導入例は完了です。

STEP11 - AWS S3からDUMPファイルをDBに導入

CSVのようなテキストファイルだけではなく、オブジェクト・ストレージ上のDUMPファイルもDBに導入できます。

実施ステップ：

DBMS_CLOUD.GET_OBJECTで、AWS S3からDUMPファイルを取得し、DBのディレクトリに保存します。
impdpコマンドで、通常のインポート処理を実施します。

事前準備
DUMPファイル用のディレクトリを作成し、権限を対象ユーザ(USER01)に付与します。
("/u01/app/oracle/dmp"を事前に作成しておきます。)

SQL> show user;
USER is "SYSTEM"
SQL> CREATE OR REPLACE DIRECTORY TEST_DIR AS '/u01/app/oracle/dmp/';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO USER01;

Grant succeeded.

SQL>

DUMPファイルの作成処理とAWS S3へのファイル転送は、ここで省略します。
AWSコンソールで確認：

SQLPlusから、以下のコマンドで確認できます。

select * from dbms_cloud.list_objects('AWS_CRED','https://mysourcebucket001.s3.ap-northeast-1.amazonaws.com/');

実施例:

SQL> show user;
USER is "USER01"
SQL> col OBJECT_NAME format a15
SQL> col CHECKSUM format a35
SQL> col CREATED format a10
SQL> col LAST_MODIFIED format a40
SQL> set linesize 120
SQL> select * from dbms_cloud.list_objects('AWS_CRED','https://mysourcebucket001.s3.ap-northeast-1.amazonaws.com/');

OBJECT_NAME          BYTES CHECKSUM                            CREATED    LAST_MODIFIED
--------------- ---------- ----------------------------------- ---------- ----------------------------------------
channels.dmp        176128 9c8b52350d09f5ab2f4bed2ace7b140b               21-09-14 11:38:36.000000 +00:00
channels.txt           104 f302635b0cb67f816c33fade42059c01               21-09-14 06:06:10.000000 +00:00

SQL>

DUMPファイルの取得
DBMS_CLOUD.GET_OBJECTで、DUMPファイルを取得し、ディレクトリに保存します。
("USER01"で実行)

コマンド：
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name =>'AWS_Credential_Name',
object_uri =>'AWS_S3_Object_URL',
directory_name => 'Directory_Name');
END;
/

もし以下のようなエラーが発生した場合、ユーザ"C##CLOUD$SERVICE"にディレクトリへのアクセス権限を付与すれば回避できます。


ERROR at line 1:
ORA-20000: ORA-29289: directory access denied
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 921
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 2411
ORA-06512: at line 2

実施例：

SQL> show user
USER is "SYSTEM"
SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO C##CLOUD$SERVICE;

Grant succeeded.

SQL>

"USER01"でDBMS_CLOUD.GET_OBJECTを実施します。

SQL> show user
USER is "USER01"
SQL> BEGIN
  2       DBMS_CLOUD.GET_OBJECT(
  3       credential_name => 'AWS_CRED',
  4       object_uri => 'https://mysourcebucket001.s3.ap-northeast-1.amazonaws.com/channels.dmp',
  5       directory_name => 'TEST_DIR');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

取得後、ディレクトリの下にDUMPファイルが作成されます。（既存ファイルがある場合、上書きされます。）


[oracle@db19c dmp]$ pwd
/u01/app/oracle/dmp
[oracle@db19c dmp]$ ls -l channels.dmp
-rw-r--r-- 1 oracle asmadmin 176128 Sep 14 12:18 channels.dmp
[oracle@db19c dmp]$

インポート処理
これから、impdpでインポート処理を実施します。（この例は、カラのテーブルにインポートします。）

[oracle@db19c dmp]$ impdp USER01/password@DB19C_PDB1 tables=CHANNELS directory=TEST_DIR dumpfile=channels.dmp logfile=impdp_channels.log ignore=y

Import: Release 19.0.0.0.0 - Production on Tue Sep 14 12:23:12 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "USER01"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USER01"."SYS_IMPORT_TABLE_01":  USER01/********@DB19C_PDB1 tables=CHANNELS directory=TEST_DIR dumpfile=channels.dmp logfile=impdp_channels.log table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "USER01"."CHANNELS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER01"."CHANNELS"                         6.031 KB       5 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "USER01"."SYS_IMPORT_TABLE_01" successfully completed at Tue Sep 14 12:23:27 2021 elapsed 0 00:00:11

[oracle@db19c dmp]$

インポート実施後、テーブルの内容を確認します。

SQL> show user;
USER is "USER01"
SQL> select * from channels;

C CHANNEL_DESC         CHANNEL_CLASS
- -------------------- --------------------
S Direct Sales         Direct
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others

SQL>

ここまで、DUMPファイルの導入例は完了です。

以上

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up