SQL*LoaderやSqoopによる外部表のデータロード

#最初に

最近、HadoopやOracleでのデータ入出力の仕事をしています。
その際に問題になる大量データのロード・アンロードについてのまとめを記載します。

#アジェンダ
１．SQL*Loader
２．Sqoop
３．外部表（GreenplumDB）
４．外部表（Oracle）

#１．SQL*Loader

`SQL*Loader`はOracleへのデータ投入時に使用します。
CSV形式などのテキストファイルからデータベース内にデータを取り込むためのツールです。

`SQL*Loader`のインプットとして、**入力ファイル**と**制御ファイル**が必要です。

イメージ図
![SQLLDR図.png](https://qiita-image-store.s3.amazonaws.com/0/101675/b4cc233d-3a63-5cc5-c8f5-dc06da0d33da.png)


制御ファイルは取り込み対象の**CSVファイル名**や取り込みデータの**編集仕様**を記載します。
また、**BATファイル名**や**LOGファイル名**も指定可能です。

```sql:SqlLoaderの制御定義
LOAD DATA
INFILE		'TEST1.DAT' "fix 10"
BADFILE		'TEST.BAD'
DISCARDFILE	'TEST1.DSC'
APPEND INTO TABLE TEST1
(
	TESTCD POSITION(1:4)	CHAR
,	TESTNM POSITION(5:10)	CHAR
)
```

SQL*Loaderを使用するときの注意点としては下記2つです
　１．入力ファイルのフォーマットを押さえておく（データ投入ならば当たり前か…）
　２．**クライアント側にOracle Clientをインストール**しておく必要がある。

私が最初に始めたときは２の対処法にハマりました。
環境不備の場合は場合は下記に従いOracle Clientをインストールしましょう。
http://hatsune.wankuma.com/Oracle/oic/install.html

しかし、インストールする時間がない場合はどうしたらよいでしょうか？
その場合はOracle Clientが入っているサーバを探してリモートで接続することも可能です。

環境が揃えば下記のコマンドでデータ投入完了です。

```sh:SQL*Loaderコマンド
sqlldr user/pass@host:port/<SID> control='TEST.CTL' log='TEST1.log'
```

取込時間は20万件でも5分以内に終わっているので、なかなか速いです。
しかし、レコードが少ないならばメンテマクロ作成した方が良いです。


#２．Sqoop

SqoopはHadoopとDBでのデータやりとりに使用します。

CSV形式などのテキストファイルをデータベースに取り込んだり（エクスポート）
データベースからテキストファイルに出力したり（インポート）できます。

イメージ図
![【Quita】自動退避.png](https://qiita-image-store.s3.amazonaws.com/0/101675/b969b865-b7b5-3d30-4ef5-f7b6ebf39a9c.png)
※DBはGreenplumDBとしています。

インプットは**入力ファイルのみ**です。
Sqoopはコマンド実行時DBにアクセスをした際に、カラム情報等を取得してJavaファイルを作成します。
そのため、SQL*Loaderのような制御ファイルが不要となります。

下記のコマンドを実行して、データ投入を行います。

```sh:SqoopによるHadoopからのデータ出力
sqoop export \
  --connect jdbc:oracle:thin:@host:port/<SID> \
  --table TEST_SQOOP \
  --username <user_name> \
  --password <password>
  --export-dir /user/work/sqoop/aaa \
  --outdir /home/work/test/bin
  --bindir /home/work/test/bin
  --fields-terminated-by ','
  --escaped-by '\\'
  --input-null-string ''
  --input-null-non-string ''
  --optionally-enclosed-by '\"'
```

データ出力コマンドは下記です。

```sh:SqoopによるHadoopへのデータ投入
sqoop import \
  -Dorg.apache.sqoop.credentials.loader.class=org.apache.sqoop.util.password.CryptoFileLoader \
  -Dorg.apache.sqoop.credentials.loader.crypto.passphrase=sqoop \
  --connect jdbc:oracle:thin:@host:port/<SID> \
  --table TEST_SQOOP \
  --target-dir /user/word/XX/aaa \
  --username <user_name> \
  --password-file <pass.txt> \
  -m 1 \
  --delete-target-dir
```

DB接続や読み込むCSVファイルをオプションとして指定します。
したがって、sqoopコマンド実行時は非常に長いコマンドを打つ必要があります。

Javaファイルが残ったり、エラー時の裏側のJOBの挙動に関して手動でデータ投入する分には問題ないのですが、
システムに組み込む場合は、考慮する必要があります。


#３．外部表（GreenplumDB）

GreenPlumDBとOracleの外部表に関してまとめます。
Greenplumn自体の説明は下記をご参考にして下さい。
http://cn.teldevice.co.jp/product/detail/greenplum_database/feature

GreenPlumDBでは、外部表というものを経由してデータをアンロード・ロードすることが出来ます。

外部表には以下の2種類があります。
1. 読込外部表：ロード
2. 書込外部表：アンロード

見た目は、`Create`文と似ていますが、テキストファイルを読込む（書込む）`LOCATION`というものが存在します。

## 読込外部表
```sql:読込外部表（ロード）
create external table ei_test(
 a char(1)
 ,b numeric(2,0)
)
LOCATION
(
  'gpfdist://smdw:XXXX/test.csv.gz'
)
;
```

## 書込外部表
```sql:書込外部表(アンロード)
create writable external table eo_test(
 a char(1)
 ,b numeric(2,0)
)
LOCATION
(
  'gpfdist://smdw:XXXX/test.csv'
)
;
```

読込外部表を用いればDBとマウントしているNASに置いてあるCSVファイルをSELECT出来ます。

```sql:外部表の読み込み
select * from ei_test;
```

書込外部表を用いれば
`SELECT-INSERT`してDBとマウントしているNASにCSVファイルが作成されます。

```sql:外部表へ書き込み
insert into eo_test select * from test;
```

#４．外部表（Oracle）

Oracleも同様に、読込外部表が存在します。

```sql:Oracleの外部表
CREATE TABLE TEST(
         A NUMBER(1,0)
        ,B CHAR(4 CHAR)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER		--オラクルローダー指定
    DEFAULT DIRECTORY XXX	--ディレクトリオブジェクトを指定
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE	--改行を行区切りと認識
            NOBADFILE					--badファイル生成しない
            NODISCARDFILE				--discardファイル生成しない
            NOLOGFILE					--logファイル生成しない
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
            MISSING FIELD VALUES ARE NULL
    )
    LOCATION(
         'test_1.csv'
    )
)
REJECT LIMIT 0
;
```

読込外部表を用いればDBとマウントしているNASに置いてあるCSVファイルをSELECT出来ます。
GreenplumDBと同様のクエリです。

```sql:外部表の読み込み
select * from TEST;
```

GreenplumDBの外部表と異なる点のまとめです

* GreenplumDB：gpfdist指定
* Oracle：ディレクトリオブジェクト指定



#まとめ

SQL*LoaderとOracle外部表を比べると作業工程はどちらもあまり変わらない印象です。
環境によって、どちらの手法で取り込むかを考える必要があります。

また、Sqoopを使用してみたときの処理速度は、1件でも10万件でも取り込む速度はほぼ変わりませんでした。
1件でも数分かかったので、件数が少ないものに関してはSqoopは向いていないと思われます。

ビックデータが流行ってきているなか、Sqoopはまだまだ使用ケースが少ないですが（ネットに日本語版が転がっていないのですが）今後は使われてくるのではないかと個人的に思っています。