Performance difference of the ways to load data from file into ADW instance

## At the beginning

There are several ways to load data from file into Oracle Autonomous Data Warehouse (ADW) instance. Depends on the file type and customer's business requirement, we need to choose the most appropriate way to do the data loading.

In this article, we will evaluate the performance difference of the following ways to load data from file into ADW instance.

- [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) procedure

Actually, SQL Developer and SQL\*Loader are mainly focus on loading local data into ADW instance. But since we are doing an evaluation, we need to ensure all the testing are on the same bench, so we use a Oracle Cloud Infrastructure (OCI) compute instance to emulate the local machine.

- SQL Developer will load data from compute instance to ADW instance.
- SQL\*Loader will load data from compute instance to ADW instance.
- `DBMS_CLOUD.COPY_DATA` procedure will load data from Oracle Object Storage to ADW instance. Since `DBMS_CLOUD.COPY_DATA` procedure supports loading compressed data, we will test two scenarios
  - Uncompressed csv
  - gzip compressed csv

If you are not interested on the details of how this evaluation was conducted, you can jump to the **[Conclusion](#conclusion)** section directly.

## Preparation

### Test Data

For this testing, I prepared a CSV file with 3,000,000 rows of data like below. This CSV file is encoded with UTF-8 and the file size is 402 MB uncompressed, 121 MB compressed with `gzip`.

In each row, there are number, date, English characters, and Japanese characters. They will be loaded into ADW instance as data type `Number`, `Date` and `Varchar2` respectively.

[Figure: data sample]

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

#### Where to place the test data

As mentioned above, we are going to evaluate the loading performance from compute instance and Object Storage. So we need to put the csv file and the compressed file to these places respectively.

- SQL Developer & SQL*Loader: put the `fakedata_3M_rows.csv` in the compute instance as infile
- `DBMS_CLOUD.COPY_DATA` procedure: put the `fakedata_3M_rows.csv` and `fakedata_3M_rows.csv.gz` in the Object Storage Bucket as infile

#### How to place the test data

Since the file size of the test data is not large, so we can just generate the test data in you local environment and use any tool to upload it to the compute instance and Object Storage Bucket.

Here are the ways I used this time.

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

If the size of the data file is very large, I don't recommended you to generate the data file locally. Because you will have to spend more time on uploading. Therefore, generating the data file in the compute instance and uploading the file to the Object Storage with `oci-cli` is recommended way.

[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

As mentioned at the beginning, we need a compute instance to emulate the local environment. A compute instance with following resource should be enough.

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

Because we will use SQL Developer, we need to setup GUI environment and enable VNC service accordingly.

[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

This time, we create an ADW instance with following resource.

- OCPU: 2
- Storage: 1 TB

Once the ADW instance is provisioned, download and put the wallet file to the compute instance.

[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

Download SQL Developer from [here](https://www.oracle.com/tools/downloads/sqldev-downloads.html) and install it by following the instructions at the bottom.

Once SQL Developer is installed, we can create a new connection to the ADW instance.

- `Configuration File` is the wallet file we downloaded from the ADW instance.
- `Username` is the user we want to connect to
- `Service` we select `high`

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

### Oracle Instant Client

Download Oracle Instant Client from [here](https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html) and install it by following the instruction at the bottom.

We need following 3 packages.

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

### Test schema

We can load data into the default `admin` schema, but obviously this is not a good practice. So we create a new schema for this testing.

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

### Credential

`DBMS_CLOUD.COPY_DATA` procedure needs a credential to access Object Storage to perform the data loading. So we need to create the credential first.

Connect as the newly created user `U66` and perform the following commands to create a credential.

Please check [here](https://docs.cloud.oracle.com/en-us/iaas/Content/Registry/Tasks/registrygettingauthtoken.htm) for how to get an auth token.

```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

### Create table

Execute following statements to create the test table.

```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)

### Load data

Right click the newly created table and select `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)

Select the path of the test data file and unselect the `Header` checkbox, because we don't have header in the csv file.

[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)

Keep the `Import Method` as default `Insert`. This is the method we are going to test.

[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)

All the columns are selected to import. Click Next to move forward.

[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)

Since we created the table based on the content of the csv file, so all the columns are mapped to the table columns without any problem.

[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)

Review the configuration and click `Finish` to start the loading.

[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)

A progress window will pop-up and show us the elapsed time and the number of imported rows.

[Figure: import result]

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

Once all the rows are imported, a window like below will pop-up.

[Figure: import complete]

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

We can count the number of rows in the target table.

```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

### Control file

The following is the control file for the loading.

```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)
)

```

### Main script

There are mainly three steps in following script.

1. create table
2. load data into the table
3. check the loading log

From the log, we can get the elapsed time of this loading.

```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

### Uncompressed CSV

First, let's load the uncompressed csv file. Please check the following script for details.

```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;
!

```

### Compressed CSV (gzip)

When using `DBMS_CLOUD.COPY_DATA` procedure to load a compressed data file, we can use the `compression` option to specify the compression type of the source file.

Check [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) for more details.

```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;
!

```

## Conclusion

| 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 |

According to the test result above, we can see that for the data volume in our case, using `DBMS_CLOUD.COPY_DATA` procedure to import the uncompressed csv file is the fastest way.

But we also need to consider that what if the data volume is very large. Compressing the data file can help us to reduce the file size and reduce the time spent on network transfer accordingly. From my perspective, compress or not, it depends on the file size and your business requirement. We need to find out a balance point in all of these considerations.

Anyway, putting the data file into Object Storage before importing it into ADW instance is always recommended. In this way, we can not only improve the import efficiency, but also save more local storage and improve the data file performance.