久しぶりにお仕事でSQL*Loaderを使ったのですが、お作法を色々忘れてしまい、プチ嵌りしました。次回また同じ目に合わないように小ネタをまとめておきます。
SQL*LoaderではCSV形式などのテキストファイルからデータを取り込むわけですが、場合によってはインプットファイルの内容が保証されてなかったり、型変換が必要だったりと、データベースに取り込むまで一筋縄ではいかないことがございます。
実行コマンド（基本）
SQL*Loaderの実行コマンド。細かい制御は「制御ファイル」で指定する
sqlldr userid=”【ユーザ】/【パスワード】” control=’【制御ファイルパス】’
制御ファイルとは
細かい情報はOracleのオンラインマニュアル等参考にしましょう。
制御ファイルは１～３の3つのセクションで構成されています。
取り込みデータの場所の指定や、取り込み時の条件、項目のマッピングや、型変換を定義します。
こんな時：区切り文字、囲み文字を指定
ほぼ必ず指定する項目。
区切り文字は「FIELDS TERMINATED」、囲み文字は「OPTIONALLY ENCLOSED」で任意の文字を定義します。
“バナナ”,”リンゴ”
といった「カンマ区切り、ダブルクォーテーション囲み」のデータを取り込む場合は、
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
と指定する。
こんな時：値があったりなかったりする項目が存在
インプットのファイルに値が設定されない項目がある場合、以下のオプションを設定することでNULLを突っ込んでくれる。
TRAILING NULLCOLS
こんな時：DATE型やTIMESTAMP型のデータベースにデータを入れる
例：CREATE_DATEという列にDATE型に変換してデータを格納する場合
CREATE_DATE CHAR “TO_DATE(:CREATE_DATE,YYYYMMDD)”
YYYYMMDD部分の書式はcsvファイルに合わせる。
数値（浮動小数含む）は暗黙的に型変換してくれるものもあり。明示的に変換を指定する場合はTO_NUMBERする。
こんな時：テーブルとcsvファイルが合わない場合
項目数が一致しない場合は以下の指定をすることでインプットファイルとデータベースの項目をマッピングすることが出来ます。順序の入れ替えは調査中です。
csvファイルの項目が多いケース：
csvファイルの項目で、データベースに取り込む必要のない項目をを読み飛ばして利用したい場合です。
“dummy” filler
「dummy」の部分は何でもよいですが、項目読み飛ばしであることがわかるようなワードを埋め込んでおくと、他の人が見たときにもわかりやすいので良いかと思います。
データベースの項目が多いケース：
一部の項目にだけcsvファイルの値をロードしたい場合、テーブルにデフォルト値（空文字など）を入れて項目数をマッピングさせることが出来ます。
【項目名】 CONSTANT ‘ ‘
カンマの数がテーブルの項目数と一致するかどうか事前に確認する方法
データのサイズが数万件程度までだったらExcelを使ったり、テキストエディタで項目数(またはカンマの個数)を計測することも可能ですが、ローダーを使うときは何十万～何百万件といったデータを扱うこともあるのでこれらでは厳しい。
こんな時はawkを使うのが便利です。
cat 【ファイル名】｜awk -F ‘,’ ‘{print NF}’ | sort | uniq | wc -l
※-F 区切り文字を指定するためのオプション
NFは組み込み変数で「フィールド数」を表します。
このコマンドの意味は「各行のフィールド数を表示⇒ソート⇒重複削除」した結果をカウントしています。
したがって結果が１であればファイル内の項目数が綺麗に整っていることになります。
最後の「wc-l」を取っ払うと「ファイル内のデータ項目数」が表示されます。
この辺りは好みで使い分けると良いかと思います。