SQLite 3 への CSV ファイルインポートと性能確認手順（Ubuntu 上）

【概要】

CSVファイルインポートとは、CSVファイルをリレーショナルデータベースのテーブルに取り込む処理である。本ページでは、Ubuntu上でSQLite 3へCSVをインポートし、性能を測定する手順を示す。

本ページで紹介するソフトウェアの利用条件は、利用者自身で確認する必要がある。

【目次】

【サイト内の関連ページ】

SQLite 3 活用ガイド

1. 前準備

SQLite 3 について

SQLite 3の詳細は別ページ »にまとめている。

CSV ファイルの準備

テスト用CSVデータの生成方法は別ページ »で解説している。

2. CSVファイルインポートのテスト実行

以下のパラメータを設定して実行する。

実行時にエラーメッセージが表示されないことを確認する。

CSVの先頭行は列名なので、.import の前にテーブルを作成し、--skip 1 で先頭行を読み飛ばす。SQLite 3の.importは、対象テーブルが存在する場合、先頭行もデータとして取り込むため、この指定が必要である。

# インポートのプログラム
cat >import.sql< t1000m_1.db
rm -f t1000m_1.db
# テーブル定義（csvsql で生成した T1000M_1.sql を読み込む）
sqlite3 t1000m_1.db < T1000M_1.sql
# インポート（import）
cat import.sql | time sqlite3 t1000m_1.db
rm -f import.sql
# 問い合わせ（query）
echo "select * from T1000M_1 limit 10;" | sqlite3 t1000m_1.db
echo "select region, count(*) from T1000M_1 group by region;" | sqlite3 t1000m_1.db
echo "select birth_cap from T1000M_1 where birth_cap > 80000 limit 10;" | sqlite3 t1000m_1.db
CSVインポートのテスト実行結果
インポート後のSQL問い合わせ結果

性能測定の実施

以下のプログラムを実行し、dstat（システムリソースを継続的に表示するツール）でCPU・ストレージ・メモリの使用状況を分析する。

# キャッシュのクリア（PageCache, dentries and inodes）
function cache_clear() {
    /usr/bin/sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    return
}

# 性能を測定するプログラム，上記と同一
cat >import.sql< t1000m_1.db
rm -f t1000m_1.db
# テーブル定義
sqlite3 t1000m_1.db < T1000M_1.sql

# dstat データを可視化するプログラムの準備
WIDTH=48
HEIGHT=20
sudo apt -y install dstat
rm -f dstatplot.py
wget https://www.kkaneko.jp/data/rdb/dstatplot.py

# キャッシュのクリア
cache_clear

# 48 は表示の横幅
# 20 は表示の縦幅
# 表示開始までに「表示の横幅」秒の待機が必要
dstat -tcdylm -C 0,1,2,3,4,5,6,7 | python3 dstatplot.py $WIDTH $HEIGHT &
sleep $WIDTH

# インポート（import），上記と同一
cat import.sql | time sqlite3 t1000m_1.db
rm -f import.sql

数値0から7はプロセッサコアを表す。R, Wはストレージの読み書き、mはメモリ使用量を表す。

処理の前半はCPUバウンド（処理速度がCPUで律速される状態）、後半はI/Oバウンド（処理速度がディスク入出力で律速される状態）となることが観察できる。

dstatによる性能測定の可視化結果

3. ファイルサイズと処理時間の関係

#!/bin/bash
function elapsed() {
    echo `python3 -c "import sys; print( float(sys.argv[2]) - float(sys.argv[1]) )" $1 $2`
    return
}

function avg() {
    echo `python3 -c 'import sys; print("%.3f" % ((float(sys.argv[1]) + float(sys.argv[2]) + float(sys.argv[3])) / 3.0) )' $1 $2 $3`
    return
}

# キャッシュのクリア（PageCache, dentries and inodes）
function cache_clear() {
    /usr/bin/sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    return
}

function file_clean_up() {
echo -n > $1
rm -f $1
}

# $1: CSV file name, $2: Table name, $3: SQLite 3 Database name, $4: SQL file name
function sqlite3_import() {
# クリーンアップ（clean up）
echo -n > $3
rm -f $3
rm -f sqlite3_import.sql

# テーブル定義
sqlite3 $3 < $4

# インポートのプログラム
cat >sqlite3_import.sql< /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}

for size in 250 500 1000 2000 4000 8000 12000 16000 20000 24000 28000 32000 36000 40000; do
    csv="T${size}M_1.csv"
    tbl="T${size}M_1"
    db="t${size}m_1.db"
    sql="T${size}M_1.sql"
    cache_clear
    e1=`sqlite3_import $csv $tbl $db $sql`
    file_clean_up $db
    cache_clear
    e2=`sqlite3_import $csv $tbl $db $sql`
    file_clean_up $db
    cache_clear
    e3=`sqlite3_import $csv $tbl $db $sql`
    file_clean_up $db
    echo "${size}M: `avg $e1 $e2 $e3`"
done
ファイルサイズと処理時間の関係を示すグラフ