Oracle/SqlLoder

SQLLoder

$ sqlldr <UID>/<PASS>@<識別子> control=<コントロールファイル名>

特定の書式(カンマ区切りなど)で記載されたテキストデータを高速にテーブルへ投入できる

 

コントロールファイル

OPTIONS (
    DIRECT = TRUE,     -- ダイレクトインサートモードを使用
)
LOAD DATA
INFILE 'ファイル名'
<投入モード> INTO TABLE テーブル名
FIELDS TERMINATED BY '区切り文字'
(
    カラム名 [追加指定]
    [,カラム名 [追加指定]]
)

OPTIONS

OPTIONS説明
DIRECTダイレクトパスロードの使用有無
MULTITHREADINGマルチスレッド処理を行うか(DIRECT = TRUEの場合のみ有効)
PARALLEL並列処理を行うか

LOAD DATA

LOAD DATA説明
INFILEデータを取り込むファイルを指定
WHEN <条件>指定した条件(sqlのwhere句と同じ形式)で指定したデータのみ投入する
LOG処理結果のログファイル(デフォルトは 制御ファイル名.log)
BADFILEデータ取込が失敗したレコードを記録するファイル名(デフォルトは 制御ファイル名.bad)
DISCARDFILE制御ファイルのWHEN句の条件に一致しないレコードを記録(デフォルトは 制御ファイル名.dis)
FIELDS TERMINATED BYデータを区切る文字を指定
投入モード説明
INSERTテーブルが空の場合にロードされる(空でない場合はエラー)
APPENDテーブルが空でも値が入っててもロードされる
REPLACEテーブルにデータがある場合は削除してからINSERT。エラー時はロールバックする
TRUNCATEテーブルをTRUNCATEしてからINSERT。ロールバックできないがREPLACEより高速
追加指定説明
POSITION(取込開始位置:取込終了位置)取込範囲を指定する
CHAR "decode(:カラム名, '',null, to_number(:カラム名))"空白項目をNULLに置き換えたい時
DECIMAL EXTERNAL数値として扱う
ZONED(桁数,小数点の桁数)浮動小数点として扱う
DATE "書式"DATE型として扱う
CONSTANT 定数ファイルからのデータを取り込まず定数として埋め込む


サンプル

テーブル作成

CREATE TABLE SCOTT.TEST ( 
    NAME        VARCHAR2(20),
    BIRTHDAY    DATE,
    AGE         NUMBER(3)
)

データ(TEST.DAT)

川澄,1987/11/29 05:42:11,25
琴乃宮,1987/02/15 02:22:12,25
松永,1987/03/02 14:12:15,25

コントロールファイル(TEST.CTL)

OPTIONS (
    DIRECT = TRUE,
)
LOAD DATA
INFILE 'TEST.DAT'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
(
    NAME,
    BIRTHDAY DATE "YYYY/MM/DD HH24:MI:SS",
    AGE DECIMAL EXTERNAL
)

データ投入

sqlldr scott/scott@orcl control=TSET.CTL

実行結果はログに出力される。.bat のファイルがある場合は投入失敗したレコードが記録されている

 

外部表

CSVファイルなどの外部ファイルから直接データを読み込んで表示することが出来るテーブル(テーブルの実体は持たない)
テーブルの実体はテキストファイルなので、SQL文での更新は不可。テキスト編集することで内容が更新される。

-- ディレクトリオブジェクトの作成
create directory OUTTBL AS '/home/oracle/outtbl';
-- ディレクトリオブジェクトへのアクセス権限を付与
grant read,write on directory OUTTBL to SCOTT;
-- テーブルの作成
create table SCOTT.TEST (
    NAME     CHAR(20),
    BIRTHDAY DATE
)
organization external (
    type oracle_loader
    default directory "OUTTBL"
    access parameters (
        records delimited by newline
        badfile     "OUTTBL":'BAD.log'
        discardfile "OUTTBL":'DISCARDFILE.log'
        logfile     "OUTTBL":'LOGFILE.log' 
        fields terminated by ','
        missing field values are NULL (
            NAME     CHAR(20),
            BIRTHDAY char date_format date mask "YYYY/MM/DD HH24:MI:SS"
        )
    ) location('TEST.CSV')
);
-- 作成したテーブルにアクセス(実際はテキストファイルにアクセスし内容を表示している)
select * from SCOTT.TEST;

NAME                 BIRTHDAY
-------------------- -------------------
 川澄                1987-11-29 05:42:11
 琴乃宮              1987-02-15 02:22:12
 松永                1987-03-02 14:12:15

トップ   編集 凍結解除 差分 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2016-01-20 (水) 00:53:50 (697d)