ファイルからADWにデータロードの性能違い

## はじめに

ファイルからOracleAutonomous Data Warehouse(ADW)インスタンスにデータロードする方法はいくつかあります。 ファイルの種類やお客様のビジネス要件に応じて、最も適切な方法を選択する必要があります。

この記事では、ファイルからADWインスタンスにデータをロードする方法について、下記の3つの方法の性能違いを評価します。

- [SQL Developer](https://www.oracle.com/tools/downloads/sqldev-downloads.html)
- [SQL\*Loader](https://www.oracle.com/database/technologies/instant-client/downloads.html)
- [DBMS_CLOUD.COPY_DATA](https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/dbms-cloud.html#GUID-9428EA51-5DDD-43C2-B1F5-CD348C156122) プロシージャ

SQL DeveloperとSQL * Loaderは、主にローカルデータをロードするのですが、評価を行っているため、すべてのテストが同じ環境で行われるようにする必要があります。そのため、Oracle Cloud Infrastructure(OCI)Compute instanceを使用してローカルマシンをエミュレートします。

- SQL Developer: compute instance から ADW にロード
- SQL*Loader: compute instance から ADW にロード
- `DBMS_CLOUD.COPY_DATA` プロシージャ: Oracle Object Storage から ADW にロード。 `DBMS_CLOUD.COPY_DATA` プロシージャは圧縮されたファイルもサポートしますので、下記2つシナリオを評価します。
  - 非圧縮 CSV
  - 圧縮 CSV (gzip)

この評価がどのように行われたかについて、興味がない場合、**[結論](#結論)**セクションにジャンプしてください。

## 事前準備

### テストデータ

このテストでは、以下のように 3,000,000 行のデータを含むCSVファイルを用意しました。 このCSVファイルはUTF-8でエンコードされており、ファイルサイズは非圧縮で 402 MB、 `gzip`で圧縮されたのは 121MB です。

各行には、数字、日付、英字、日本語の文字があります。 これらは、それぞれデータ型 `Number`、` Date`、および `Varchar2`としてADWにロードされます。

[Figure: data sample]

![data sample](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/d1117e03-3d62-da34-c17e-26454a74e854.png)

#### ファイルの格納位置

前述の通り、今回の計測は Compute instance と Oracle Object Storage 両方からデータを ADW にロードしますので、用意できた CSV ファイルと圧縮されたファイルは Compute instance と Object Storage Bucket に両方とも格納します。

- SQL Developer と SQL*Loader は Compute instance に格納されている `fakedata_3M_rows.csv` を infile として使います。
- `DBMS_CLOUD.COPY_DATA` プロシージャはObject Storage Bucket に格納されている `fakedata_3M_rows.csv` と `fakedata_3M_rows.csv.gz` を infile として使います。

#### ファイルの格納方法

今回のテストデータのサイズは大きくないから、ローカルで生成して、便利なツールで Compute instance や Object Storage Bucket にアップロードしても大丈夫です。

- Compute instance: [Cyberduck](https://cyberduck.io/)
- Object Storage: [OCI Web Console](https://docs.cloud.oracle.com/en-us/iaas/Content/GSG/Tasks/addingbuckets.htm)

テストデータのファイルサイズが大きであれば、ローカルで生成するのは推奨しません。Compute instance におけるテストデータを生成して、`oci-cli` で Object Storage にアップロードしたほうがいいと思います。

[Figure: object storage]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/77ce4a80-b699-289b-72fe-5a8e87b904ba.png)

### Compute instance

前述の通り、ローカル環境をエミュレートするための Compute instance が必要です。 下記リソースがある Compute instance で十分です。

- OCPU: 2
- Memory: 32 GB
- Network bandwidth: 32 GB

SQL Developerを使用するため、GUI環境をセットアップし、それに応じてVNCサービスを有効にする必要があります。

[Figure: compute instance]

![compute instance](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/ed2d2c8e-f23d-b6ef-db7b-122182a43555.png)


### ADW instance

今回は、下記リソースがあるADWインスタンスを作成して評価を行います。

- OCPU: 2
- Storage: 1 TB

ADWインスタンスが作成されたら、ウォレットファイルをダウンロードしてコンピューティングインスタンスに置いてください。

[Figure: ADW instance]

![ADW instance](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/f847ceec-a4c1-4759-b74c-26206ce6f6a6.png)


### SQL Developer

[ここ](https://www.oracle.com/tools/downloads/sqldev-downloads.html) からSQL Developerをダウンロードし、下部の手順に従ってインストールします。

SQL Developerをインストールしたら、ADWインスタンスへの接続を作成できます。

- `Configuration File`: ウォレットファイル
- `Username`: 接続先ユーザー
- `Service`: `high`

![SQL Developer](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/ed0c206b-8df5-a6ba-868a-3dbf38126a82.png)

### Oracle Instant Client

[ここ](https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html)からOracle Instant Clientをダウンロードし、下部の指示に従ってインストールします。

以下の3つのパッケージが必要です。

- Basic Package
- SQL\*Plus Package
- Tools Package

### テストスキーマ

デフォルトの `admin` スキーマにデータをロードできますが、これは推奨できる方法ではありません。そのため、新しいスキーマを作成します。

```sql
CREATE USER U66 IDENTIFIED BY <PASSWORD>;
GRANT DWROLE TO U66;
GRANT UNLIMITED TABLESPACE TO U66;
```

### Credential

`DBMS_CLOUD.COPY_DATA`プロシージャには、Object Storage にアクセスするための Credential が必要です。 したがって、最初に Credential を作成します。

新しく作成したユーザー `U66`として接続し、次のコマンドを実行して Credential を作成します。

認証トークンの取得方法については、[こちら](https://docs.cloud.oracle.com/en-us/iaas/Content/Registry/Tasks/registrygettingauthtoken.htm)をご参照ください。

```sql
SET DEFINE OFF
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'U66_CRED',
    username => '<OCI USER>',
    password => '<AUTH TOKEN>'
  );
END;
/

-- list the credential
SELECT credential_name, username, comments FROM all_credentials;
```

![list credential](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/d3623e63-e61d-7ece-a9ea-800cc5dc59db.png)

## SQL Developer

### テーブルの作成

次のステートメントを実行して、テストテーブルを作成します。

```sql
DROP TABLE FAKEDATA_3M_ROWS_UTF8_SQLDEV PURGE;

CREATE TABLE FAKEDATA_3M_ROWS_UTF8_SQLDEV (
    name VARCHAR2(1000),
    ssn VARCHAR2(1000),
    birthdate DATE,
    age NUMBER(3),
    job VARCHAR2(1000),
    address VARCHAR2(1000),
    mail VARCHAR2(1000)
);
```

[Figure: create table]

![create table](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/bd3a8bf7-8fde-efbe-ced3-0e1aeab52954.png)

### データのロード

新しく作成したテーブルを右クリックして、`Import Data`を選択します。

[Figure: import data]

![import data](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/e5870e0d-da36-5dc7-aa6c-f3474d2bed27.png)

csvファイルにヘッダーがないため、テストデータファイルのパスを選択し、`Header` チェックボックスの選択を解除します。

[Figure: data import step 1]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/296869bd-24c0-9937-ebf9-7ed071187bda.png)

`Import Method` をデフォルトの `Insert` のままにします。

[Figure: data import step 2]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/0e5ff1bc-de41-f339-1f7e-9f0f37d64818.png)

すべての列が選択されています。 `Next` をクリックして先に進みます。

[Figure: data import step 3]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/f3c12452-a487-75d2-ecff-8cf2c3976563.png)

csvファイルの内容に基づいてテーブルを作成したため、すべての列が問題なくテーブルの列にマップされます。

[Figure: data import step 4]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/7d4cb940-58c9-9650-c7fb-ddd0fb5ad3e7.png)

実行内容を確認し、`Finish` をクリックしてロードを開始します。

[Figure: data import step 5]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/b8167e35-8d81-8511-e441-6915cda24456.png)

進捗状況ウィンドウが表示され、経過時間とインポートされた行数が表示されます。

[Figure: import result]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/c9d10104-575d-bd6a-a792-b0a1801f09b6.png)

すべての行がインポートされると、次のようなウィンドウが表示されます。

[Figure: import complete]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/38d567b0-5637-da4d-e24b-63d673b84781.png)

ターゲットテーブルの行数を数えることができます。

```sql
SELECT COUNT(1) FROM FAKEDATA_3M_ROWS_UTF8_SQLDEV;
```

[Figure: count result]
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/68b38c16-364b-d2d4-f869-9a32fe33232c.png)

## SQL\*Loader

### コントロールファイル

以下は、SQL*Loader 用のコントロールファイルです。

```sh
OPTIONS (BINDSIZE=100000, SILENT=(FEEDBACK) )
load data
infile 'fakedata_3M_rows.csv' "str '\n'"
append
into table FAKEDATA_3M_ROWS_UTF8_SQLLDR
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
(
    name CHAR(1000),
    ssn CHAR(1000),
    birthdate DATE "RRRR-MM-DD",
    age,
    job CHAR(1000),
    address CHAR(1000),
    mail CHAR(1000)
)

```

### スクリプト

次のスクリプトには、主に3つのステップがあります。

1. テーブルの作成
2. データローディング
3. ログの確認

ログから、経過時間を取得できます。

```sh
#!/bin/sh

echo '[INFO] Create table ************************************'

sqlplus -FAST U66/<PASSWORD>@adw01_high <<!

set timing on
set lines 300
set pages 9999

SET DEFINE OFF
DROP TABLE FAKEDATA_3M_ROWS_UTF8_SQLLDR PURGE;

CREATE TABLE FAKEDATA_3M_ROWS_UTF8_SQLLDR (
    name VARCHAR2(1000),
    ssn VARCHAR2(1000),
    birthdate DATE,
    age NUMBER(3),
    job VARCHAR2(1000),
    address VARCHAR2(1000),
    mail VARCHAR2(1000)
);

!

echo '[INFO] SQL*Loader ************************************'

sqlldr userid=U66/<PASSWORD>@adw01_high \
    CONTROL=sqlldr_3m_rows.ctl \
    LOG=<DIRECTORY PATH>/sqlldr_3m_rows.log \
    BAD=<DIRECTORY PATH>/sqlldr_3m_rows.bad \
    direct=true
    skip=0

echo '[INFO] Check log ************************************'
tail -30 <DIRECTORY PATH>/sqlldr_3m_rows.log

```

## DBMS_CLOUD.COPY_DATA

### 非圧縮 CSV

まず、非圧縮のcsvファイルをロードしましょう。 詳細については、次のスクリプトをご参照ください。

```sh
#!/bin/sh

echo '[INFO] Create table ************************************'

sqlplus -FAST U66/<PASSWORD>@adw01_high <<!

set timing on
set lines 300
set pages 9999

SET DEFINE OFF
DROP TABLE FAKEDATA_3M_ROWS_UTF8_DBMS_CSV PURGE;

CREATE TABLE FAKEDATA_3M_ROWS_UTF8_DBMS_CSV (
    name VARCHAR2(1000),
    ssn VARCHAR2(1000),
    birthdate DATE,
    age NUMBER(3),
    job VARCHAR2(1000),
    address VARCHAR2(1000),
    mail VARCHAR2(1000)
);

!

echo '[INFO] Copy data ************************************'

sqlplus -FAST U66/<PASSWORD>@adw01_high <<!

set timing on
set lines 300
set pages 9999

BEGIN
  DBMS_CLOUD.COPY_DATA(
    schema_name => 'U66',
    table_name =>'FAKEDATA_3M_ROWS_UTF8_DBMS_CSV',
    credential_name =>'U66_CRED',
    file_uri_list =>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<TENANCY>/b/<BUCKET>/o/fakedata_3M_rows.csv',
    format => '{
      "type":"CSV",
      "dateformat":"YYYY-MM-DD",
      "timestampformat":"YYYY-MM-DD HH:MI:SS.FF",
      "ignoremissingcolumns":"true"
    }'
  );
END;
/

select count(1) from FAKEDATA_3M_ROWS_UTF8_DBMS_CSV;
!

```

### 圧縮 CSV (gzip)

`DBMS_CLOUD.COPY_DATA`プロシージャを使用して圧縮データファイルをロードする場合、` compression`オプションでソースファイルの圧縮タイプを指定できます。

詳細は [DBMS_CLOUD Package Format Options](https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/format-options.html#GUID-08C44CDA-7C81-481A-BA0A-F7346473B703) にご参照ください。

```sh
#!/bin/sh

echo '[INFO] Create table ************************************'

sqlplus -FAST U66/<PASSWORD>@adw01_high <<!

set timing on
set lines 300
set pages 9999

SET DEFINE OFF
DROP TABLE FAKEDATA_3M_ROWS_UTF8_DBMS_GZ PURGE;

CREATE TABLE FAKEDATA_3M_ROWS_UTF8_DBMS_GZ (
    name VARCHAR2(1000),
    ssn VARCHAR2(1000),
    birthdate DATE,
    age NUMBER(3),
    job VARCHAR2(1000),
    address VARCHAR2(1000),
    mail VARCHAR2(1000)
);

!

echo '[INFO] Copy data ************************************'

sqlplus -FAST U66/<PASSWORD>@adw01_high <<!

set timing on
set lines 300
set pages 9999

BEGIN
  DBMS_CLOUD.COPY_DATA(
    schema_name => 'U66',
    table_name =>'FAKEDATA_3M_ROWS_UTF8_DBMS_GZ',
    credential_name =>'U66_CRED',
    file_uri_list =>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<TENANCY>/b/<BUCKET>/o/fakedata_3M_rows.csv.gz',
    format => '{
      "type":"CSV",
      "compression":"gzip",
      "dateformat":"YYYY-MM-DD",
      "timestampformat":"YYYY-MM-DD HH:MI:SS.FF",
      "ignoremissingcolumns":"true"
    }'
  );
END;
/

select count(1) from FAKEDATA_3M_ROWS_UTF8_DBMS_GZ;
!

```

## 結論

| Import Tool          | Data format | Source           | Target |     Elapsed |
| :------------------- | ----------- | ---------------- | ------ | ----------: |
| SQL Developer        | csv         | Compute instance | ADW    | 00:02:07.00 |
| SQL\*Loader          | csv         | Compute instance | ADW    | 00:00:29.18 |
| DBMS_CLOUD.COPY_DATA | csv         | Object Storage   | ADW    | 00:00:14.25 |
| DBMS_CLOUD.COPY_DATA | csv.gz      | Object Storage   | ADW    | 00:00:30.03 |

上記のテスト結果によって、今回のデータボリュームでは、 `DBMS_CLOUD.COPY_DATA`プロシージャを使用して非圧縮のcsvファイルをインポートするのが最速の方法であることがわかります。

ただし、データ量が非常に多い場合はどうなるかについても考慮する必要があります。 データファイルを圧縮すると、ファイルサイズを削減し、それに応じてネットワーク転送にかかる時間も削減できます。 個人的な見解では、圧縮するかどうかは、ファイルサイズとビジネス要件によって異なります。 これらすべての考慮事項のバランスポイントを見つける必要があります。

とにかく、ADWインスタンスにインポートする前に、データファイルをオブジェクトストレージに置くことは常にお勧めします。 このようにして、インポートの効率を向上させるだけでなく、より多くのローカルストレージを節約し、データファイルの永続性も向上させます。