LoginSignup
1
0
@letian

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

Last updated at Posted at 2021-09-21

初めに

前回の記事で、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に、適用します。

実施ステップ

image.png

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

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

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

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

クレデンシャルの作成

対象ユーザ(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を取得します。
image.png

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に導入できます。

実施ステップ：

  1. DBMS_CLOUD.GET_OBJECTで、AWS S3からDUMPファイルを取得し、DBのディレクトリに保存します。
  2. 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コンソールで確認：
image.png

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ファイルの導入例は完了です。

以上

関連記事
オラクル・クラウド関連の個人ブログ
オブジェクト・ストレージからOracle DB 19c/21cにデータを導入する

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0