SQL*Loader（エスキューエル・ローダー）は、Oracleデータベースに大量のデータを簡単にロードするためのツールです。たとえば、CSVファイルやテキストファイルに保存されたデータをOracleデータベースのテーブルに取り込むことができます。特に、データ移行やバッチ処理で大量データを取り扱う際に非常に便利です。
基本的な仕組み
SQL*Loaderでは、次の3つのファイルを使用してデータをロードします。
- 制御ファイル（Control File）：データをどのテーブルに、どのようにロードするかを指定します。
- データファイル（Data File）：実際にロードするデータが格納されたファイルです。通常はCSVやテキスト形式のファイルです。
- ログファイル（Log File）：ロード処理の結果やエラー内容を記録するファイルです。
テーブルの作成
まずは、データを格納するためのテーブルをOracleデータベースに作成する必要があります。今回は、従業員情報を格納するためのemployeesというテーブルを作成します。
テーブル作成のSQL文
以下のSQL文を使用して、データベースに従業員テーブルを作成します。
【作成例】CREATE TABLE employees (
employee_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER(10, 2)
);
employee_id: 従業員ID、主キーとして一意性を持たせます。first_name: 従業員の名前、最大50文字の文字列。last_name: 従業員の姓、最大50文字の文字列。hire_date: 採用日を表す日付データ。salary: 年収、10桁の数値（小数点以下2桁まで）。
[oracle@v19single ~]$ sqlplus scott/tiger
SQL*Plus: Release 19.0.0.0.0 - Production on 土 10月 19 12:52:58 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
最終正常ログイン時間: 土 10月 19 2024 12:51:45 +09:00
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
に接続されました。
SQL> CREATE TABLE employees ( ★表を作成
2 employee_id NUMBER(10) PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50),
5 hire_date DATE,
6 salary NUMBER(10, 2)
7 );
表が作成されました。
SQL> desc employees
名前 NULL? 型
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(10)
FIRST_NAME VARCHAR2(50)
LAST_NAME VARCHAR2(50)
HIRE_DATE DATE
SALARY NUMBER(10,2)
SQL>
データファイルの作成
次に、SQL*LoaderでロードするデータをCSVファイルとして用意します。以下は、従業員の情報を持つサンプルのCSVファイルです。
サンプルCSVファイル: data.csv
【作成例】1001,John,Doe,2022-01-15,50000
1002,Jane,Smith,2021-11-23,60000
1003,Emily,Jones,2023-07-10,55000
1004,Michael,Brown,2020-06-30,45000
1005,Olivia,Davis,2019-09-14,70000
このCSVファイルには従業員ID、名前、姓、採用日、年収の情報が含まれています。
[oracle@v19single ~]$ vi data.csv
[oracle@v19single ~]$ cat data.csv
1001,John,Doe,2022-01-15,50000
1002,Jane,Smith,2021-11-23,60000
1003,Emily,Jones,2023-07-10,55000
1004,Michael,Brown,2020-06-30,45000
1005,Olivia,Davis,2019-09-14,70000
[oracle@v19single ~]$
viコマンドの使用方法は以下の記事をご確認ください。
※Windowsの場合はテキストエディタ等のアプリでファイルを作成すれば問題ありません。
制御ファイルの作成
次に、SQL*Loaderに対して、どのデータをどのようにデータベースにロードするかを指定するために、制御ファイルを作成します。
※オラクルデータベース内で使用されている制御ファイルとは異なります。
制御ファイルの例: control_file.ctl
【作成例】LOAD DATA
INFILE 'data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
employee_id,
first_name,
last_name,
hire_date DATE "YYYY-MM-DD",
salary
)
INFILE 'data.csv': データファイルとしてdata.csvを使用します。INTO TABLE employees: データをロードするテーブルはemployeesです。FIELDS TERMINATED BY ',': フィールドはカンマで区切られています。hire_date DATE "YYYY-MM-DD": 採用日はYYYY-MM-DDの形式で指定されます。
[oracle@v19single ~]$ vi control_file.ctl
[oracle@v19single ~]$ cat control_file.ctl
LOAD DATA
INFILE 'data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
employee_id,
first_name,
last_name,
hire_date DATE "YYYY-MM-DD",
salary
)
[oracle@v19single ~]$
SQL*Loaderの実行
すべての準備が整ったら、SQL*Loaderを使ってデータをロードします。以下のコマンドを実行してください。sqlldr <ユーザー名>/<パスワード> control=<制御ファイル名> log=<ログファイル名>
例: sqlldr scott/tiger control=control_file.ctl log=loader.log
userid: Oracleデータベースに接続するためのユーザー名とパスワードを指定します（例:scott/tiger）。control: 制御ファイルのパス（例:control_file.ctl）。log: ロード処理のログファイル（例:loader.log）。
[oracle@v19single ~]$ sqlldr scott/tiger control=control_file.ctl log=loader.log
SQL*Loader: Release 19.0.0.0.0 - Production on 土 10月 19 12:55:01 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
使用パス: 従来型
コミット・ポイントに達しました - 論理レコード件数5
表EMPLOYEES:
5 行は正常にロードされました。
確認するログ・ファイル:
loader.log
ロードの詳細を参照してください。
[oracle@v19single ~]$ sqlplus scott/tiger
SQL*Plus: Release 19.0.0.0.0 - Production on 土 10月 19 12:55:34 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
最終正常ログイン時間: 土 10月 19 2024 12:55:21 +09:00
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
に接続されました。
SQL> set lin 1000 pages 1000
SQL> col first_name for a20
SQL> col last_name for a20
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DAT SALARY
----------- -------------------- -------------------- -------- ----------
1001 John Doe 22-01-15 50000 ★挿入されている
1002 Jane Smith 21-11-23 60000 ★
1003 Emily Jones 23-07-10 55000 ★
1004 Michael Brown 20-06-30 45000 ★
1005 Olivia Davis 19-09-14 70000 ★
よくあるエラーと対処法
SQL*Loaderを使っているときに発生する一般的なエラーとその対処法をいくつか紹介します。
- ORA-01722: 数値変換エラー
- 原因: データファイルの数値フィールドに数値以外の文字が含まれている場合に発生します。
- 対策: データファイルをチェックし、数値フィールドに適切なデータが入っているか確認してください。
- ORA-12899: 列の値が大きすぎる
- 原因: データファイルの列データがテーブル定義のサイズを超えています。
- 対策: テーブル定義のサイズを確認し、データが収まるように修正してください。
実践で役立つオプション
SQL*Loaderには多くの便利なオプションが用意されています。以下は、その中でも特によく使われるオプションです。
- DIRECTオプション：大量データを高速にロードするためのオプションです。データ移行など大量データを扱う場合に有効です。
sqlldr userid=username/password control=control_file.ctl direct=true - BADファイル：データロードに失敗したレコードを記録するファイルです。エラーの調査に役立ちます。
- SILENTオプション：実行中のメッセージを非表示にするためのオプションです。ログが不要な場合やバッチ処理時に便利です。
まとめ
SQL*Loaderは、Oracleデータベースに大量のデータを効率的にロードするための強力なツールです。この記事で紹介した基本的な使い方や、エラーへの対処法を覚えることで、様々なデータロードシナリオに対応できるようになります。実際にサンプルのCSVファイルを使ってデータをロードすることで、ぜひその使い方に慣れてみてください。
[参考]
ユーティリティ – 7 SQL*Loaderの使用方法の理解
コメント