大量データによる負荷テスト時、sqlloaderとシェルスクリプトを用いた自動データ取り込みの手法について

# 大量データによる負荷テスト：データ増加時の正常動作確認時のデータの取り込み方

## 前書き
トラフィック増大と同様に、データ増加時のパフォーマンス確認も重要な負荷テストの一環です。
今回は データ増加時のテスト にフォーカスし、DBごとの大量データ投入手法と、Oracle SQL*Loaderの自動化スクリプト構成をご紹介します。

## 各DBごとの大量データロード手法一覧

---

### 🔵 Oracle Database

| ツール・手法 | 特徴 |
|--------------|------|
| **SQL\*Loader (`sqlldr`)** | テキストファイル（CSV等）を高速にロードできるユーティリティ。制御ファイルで柔軟にマッピング可能。並列実行対応。 |
| **Data Pump Import (`impdp`)** | Oracle独自のバイナリダンプ形式を使用。構造込みで高速インポート可。テーブルやスキーマ単位で復元可能。 |
| **External Tables** | ファイルを仮想的にテーブルとして扱える。実データをDBに入れずにクエリ可能。 |
| **INSERT文一括投入** | 通常のSQLでも可能だが、大量データには不向き。 |

---

### 🟦 PostgreSQL

| ツール・手法 | 特徴 |
|--------------|------|
| **COPYコマンド** | ファイル（CSV/TSV）をテーブルに高速に取り込めるコマンド。PostgreSQLで最速。 |
| **`pg_restore`** | `pg_dump` でエクスポートしたダンプファイルを復元。構造とデータの同時復元が可能。 |
| **INSERT文一括投入** | 1000件以上になるとパフォーマンス低下。バルクINSERTで対応可。 |
| **外部ETLツール（pgloaderなど）** | SQLite/MySQLなど他DBからの取り込みに便利。 |

---

### 🟥 MySQL / MariaDB

| ツール・手法 | 特徴 |
|--------------|------|
| **`LOAD DATA INFILE`** | CSV/TSVファイルを高速にインポート。ファイル形式に注意（改行・エンコーディングなど）。 |
| **`mysqlimport`** | `LOAD DATA INFILE`のコマンドライン版。複数テーブルへの一括投入も可能。 |
| **ダンプファイル（`mysqldump`）の復元** | `INSERT`文形式のダンプを使って再構築可能。大量データでは時間がかかる。 |
| **ETL系：MySQL Workbench Import/Export** | GUIでの簡易操作に便利だが、大量データにはやや非効率。 |

---

### 🟨 Microsoft SQL Server

| ツール・手法 | 特徴 |
|--------------|------|
| **bcp (Bulk Copy Program)** | テキストファイルとテーブル間でデータを高速転送。CSV対応。 |
| **BULK INSERT** | SQL ServerのSQL構文でCSVを直接ロード可能。ファイルサーバーに配置が必要。 |
| **SQL Server Integration Services (SSIS)** | GUIベースのETLツール。高機能だが、構築コストあり。 |
| **INSERT文一括投入** | 開発用には手軽だが、パフォーマンスに注意。 |

---

### 🟧 SQLite

| ツール・手法 | 特徴 |
|--------------|------|
| **`.import`コマンド** | SQLite CLIでCSVなどをインポート。ファイル形式に注意。 |
| **INSERT文一括投入** | 軽量なDBなので、INSERTも十分使えるが、トランザクションをまとめた方が高速。 |
| **ツール連携（DB Browser for SQLiteなど）** | GUIからもインポート可能で便利。 |

---

### 🟩 その他（共通・汎用）

| ツール・手法 | 対応DB | 特徴 |
|--------------|--------|------|
| **Faker / Pythonスクリプト + INSERT or CSV出力** | 全般 | テストデータ生成後、任意形式でロード可能。 |
| **Apache Nifi / Talend / Pentaho** | 全般 | 複数DB対応のGUI型ETLツール。運用向き。 |
| **DBUnit / JMeter JDBC Sampler** | 全般 | テスト自動化と連携したロードに便利。 |

## 1. シンプル構成（アップロード＆実行ユーザーが同一）

### 前提条件
- SQL*Loader実行ユーザーにSSH可能
- アップロード先と実行ユーザーが同じ
- ディレクトリ権限等は事前に設定済み
- SSHの公開鍵を事前にリモートサーバーに設定済み

### スクリプト構成
- ローカルからのアップロード
- sqlldr実行

```sh
#!/bin/bash

# ===== 設定セクション =====
REMOTE_USER="your_user"                 # SSHユーザー名
REMOTE_HOST="your.server.com"          # SSHホスト
REMOTE_DIR="/home/your_user/data"      # サーバー側のアップロード先ディレクトリ（フルパス）
LOCAL_DATA_FILE="data.csv"             # ローカルのデータファイル
REMOTE_CONTROL_FILE="/home/your_user/ctl/control.ctl"  # サーバー上に既存の制御ファイルのフルパス
REMOTE_DATA_PATH="$REMOTE_DIR/$LOCAL_DATA_FILE"        # アップロード先のデータファイルのフルパス
LOG_FILE="load.log"                    # SQL*Loader のログファイル名（任意）

# ===== アップロード =====
echo "📤 アップロード中: $LOCAL_DATA_FILE -> $REMOTE_DATA_PATH"
scp "$LOCAL_DATA_FILE" "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DATA_PATH"
if [ $? -ne 0 ]; then
  echo "❌ アップロード失敗"
  exit 1
fi

# ===== SQL*Loader 実行 =====
echo "🚀 SQL*Loader 実行中..."
ssh "$REMOTE_USER@$REMOTE_HOST" << EOF
  sqlldr userid=your_db_user/your_db_pass control="$REMOTE_CONTROL_FILE" data="$REMOTE_DATA_PATH" log="$LOG_FILE"
EOF

if [ $? -eq 0 ]; then
  echo "✅ sqlldr 実行成功"
else
  echo "❌ sqlldr 実行失敗"
fi
```
### 実行構成図
この構成下で、実行がどのような流れなのかを以下に図解します。
```
ローカルPC
+--------------------------+
|- data.csv                |
|- upload_and_load.sh      |
|                          |
|scp でアップロード           |
+--------------------------+
|
▼ SQL*Loader実行サーバ
--------------------------------+
|- /home/your_user/data/        |
|    └─ data.csv                |
|- /home/your_user/ctl/         |
|    └─ control.ctl             |
|                               |
|sqlldr 実行：                   |
|  sqlldr userid=...@DB         |
|          control=...          |
|          data=...             |
+-------------------------------|
|  
|
▼ Oracle DBサーバ
+---------------------------+
| - データベースに取り込み     |
|   (sqlldr 経由で直接挿入)  |
+---------------------------+
```



## 2. 実行ユーザー分離構成（運用寄り）

### 前提条件
- SSHログインユーザーとsqlldr実行ユーザーは別
- su経由で切り替えられるよう設定されている
- root権限の使用は不可
- SSHの公開鍵を事前にリモートサーバーに設定済み 

### スクリプト構成
- ローカルからアップロードスクリプト
- user1でファイル配置＆chmod
- user2で実行スクリプト（手動or定期ジョブ）

#### リモートからのアップロード処理
```sh
#!/bin/bash

REMOTE_USER="user"
REMOTE_HOST="remote_host"
LOCAL_BASE="/local/source/dir"
REMOTE_BASE="/remote/target/dir"
INTERVAL=10  # 秒間隔でチェック

while true; do
  # ローカルディレクトリ配下のすべてのファイルを取得
  mapfile -t LOCAL_FILES < <(find "$LOCAL_BASE" -type f)

  for LOCAL_FILE in "${LOCAL_FILES[@]}"; do
    # 相対パスを取得（LOCAL_BASEの後ろを切り出し）
    REL_PATH="${LOCAL_FILE#$LOCAL_BASE/}"

    # リモート側の対象ファイルパス
    REMOTE_FILE="$REMOTE_BASE/$REL_PATH"
    REMOTE_DIR=$(dirname "$REMOTE_FILE")

    # リモートファイルが存在するかチェック
    if ! ssh "$REMOTE_USER@$REMOTE_HOST" "test -f \"$REMOTE_FILE\""; then
      echo "[INFO] $REMOTE_FILE が削除されました。再送信を試みます。"

      # リモート側ディレクトリを作成
      ssh "$REMOTE_USER@$REMOTE_HOST" "mkdir -p \"$REMOTE_DIR\""

      # ファイルをアップロードし、chmod 777 を付ける
      if [ -f "$LOCAL_FILE" ]; then
        scp "$LOCAL_FILE" "$REMOTE_USER@$REMOTE_HOST:$REMOTE_FILE"
        ssh "$REMOTE_USER@$REMOTE_HOST" "chmod 777 \"$REMOTE_FILE\""
        echo "[INFO] $REMOTE_FILE をアップロード＆chmod 777 完了"
      else
        echo "[WARN] ローカルに $LOCAL_FILE が存在しません。スキップします。"
      fi
    fi
  done

  sleep "$INTERVAL"
done
```

#### sqlldr実行処理（リモートサーバーに配置）
```sh
#!/bin/bash

WATCH_DIR="/path/to/watch"
TARGET_FILE="data_file.csv"
CONTROL_FILE="/path/to/control_file.ctl"
LOG_FILE="/path/to/load.log"
MAX_RUNS=5
SLEEP_SECONDS=10

run_count=0

echo "=== SQL*Loader監視開始 ==="

while [ "$run_count" -lt "$MAX_RUNS" ]; do
    if [ -f "${WATCH_DIR}/${TARGET_FILE}" ]; then
        echo "$(date): ${TARGET_FILE} を検出、SQL*Loader を実行します。"

        sqlldr userid=username/password control="${CONTROL_FILE}" data="${WATCH_DIR}/${TARGET_FILE}" log="${LOG_FILE}" > /dev/null 2>&1

        if [ $? -eq 0 ]; then
            echo "$(date): SQL*Loader 実行成功。"
            ((run_count++))
            mv "${WATCH_DIR}/${TARGET_FILE}" "${WATCH_DIR}/${TARGET_FILE}.done_${run_count}"
        else
            echo "$(date): SQL*Loader 実行失敗。"
        fi
    fi

    sleep "$SLEEP_SECONDS"
done

echo "=== 最大実行回数に到達したため処理を終了します ==="
```

### リモートサーバー内のシステム構成図

この構成下で、ファイルの監視・受信とSQL*Loader実行がどのように連携しているかを以下に図解します。

```
┌────────────────────────────────────────────────────────┐
│                    リモートサーバー                   │
└────────────────────────────────────────────────────────┘
        ▲                          ▲
        │ SSH（公開鍵）           │ SSH（公開鍵）
        │                          │
        │                          ▼
        │                  ┌────────────────────────────┐
        │                  │ [1] SSHログイン（user1）    │
        │                  └────────────────────────────┘
        │                          │
        │                          ▼
        │                  ┌────────────────────────────┐
        │                  │ su で root に昇格           │
        │                  └────────────────────────────┘
        │                          │
        │                          ▼
        │                  ┌────────────────────────────┐
        │                  │ su - user2 に切り替え       │
        │                  └────────────────────────────┘
        │                          │
        │                          ▼
        │                  ┌────────────────────────────┐
        │                  │ スクリプトを起動（loop）   │
        │                  │ - ファイル検出               │
        │                  │ - sqlldr 実行               │
        │                  └────────────────────────────┘
        │
        ▼
┌────────────────────────────────────────────────────────┐
│ [0] ファイルアップロード処理のスクリプト起動（ローカルPC→user1）│
│ - scpにてデータファイル送信                                │
│ - user1 が受け取り、共通ディレクトリに配置（chmod）           │
└────────────────────────────────────────────────────────┘
```
こちらのスクリプトを使えば、大量データの取り込みも、バックグラウンドで処理してくれるはずです。
ぜひ使用してみてください。