SQLite 3 への CSV ファイルインポートと性能確認手順(Ubuntu 上)
CSV ファイルインポートとは,CSVファイルをリレーショナルデータベースのテーブルに効率的にインポートする重要な処理である.
- ここで扱う CSV ファイルは, 先頭行には,各列の属性名が記述されているものとする.
- 1つのCSVファイルを1つのテーブルに最適な形でインポートする.
- CSVファイルのレコード内の値の順序は厳密に維持する.
本ページで紹介しているソフトウェア類の利用条件等については,利用者自身で確認する必要がある.
前準備
SQLite 3 について
SQLite 3 の詳細情報: 別ページ »に整理
CSV ファイルの準備
- テスト用CSV データの生成方法: 別ページ »で詳しく解説
CSV ファイルインポートのテスト実行(SQLite 3 を使用)
以下のパラメータを適切に設定して実行する.
- 「T1000M_1.csv」は対象となる CSV ファイル名
- 「T1000M_1」は作成するテーブル名
- 「t1000m_1.db」は SQLite 3 データベースファイル名
実行時にエラーメッセージが表示されないことを確認することが重要である.
# インポートのプログラム cat >import.sql<T1000M_1.csv T1000M_1 .exit EOF # クリーンアップ(clean up) echo -n > t1000m_1.db rm -f t1000m_1.db # インポート(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


性能測定の実施
以下のプログラムを実行し,dstat を用いてシステムリソースとパフォーマンスを総合的に分析する.
# キャッシュのクリア(PageCache, dentries and inodes) function cache_clear() { /usr/bin/sync /usr/bin/sync /usr/bin/sync /usr/bin/sync /usr/bin/sync sleep 3 sudo sysctl -w vm.drop_caches=3 > /dev/null echo 3 | sudo tee -a /proc/sys/vm/drop_caches > /dev/null return } # 性能を測定するプログラム,上記と同一 cat >import.sql<T1000M_1.csv T1000M_1 .exit EOF # クリーンアップ(clean up),上記と同一 echo -n > t1000m_1.db rm -f t1000m_1.db # 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 バウンド(ディスク読み込みに余裕がある状態)であり,後半は I/O バウンド(プロセッサに余裕がある状態)となっていることが明確に観察できる.これは,データベースの処理特性を理解する上で重要な知見である.

CSV ファイルインポートにおけるファイルサイズと処理時間の関係性(SQLite 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 /usr/bin/sync /usr/bin/sync /usr/bin/sync /usr/bin/sync sleep 3 sudo sysctl -w vm.drop_caches=3 > /dev/null echo 3 | sudo tee -a /proc/sys/vm/drop_caches > /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 function sqlite3_import() { # クリーンアップ(clean up) echo -n > $3 rm -f $3 rm -f sqlite3_import.sql # インポートのプログラム cat >sqlite3_import.sql</dev/null current=`date +%s.%N` echo `elapsed $start $current` return } # 250M cache_clear elapsed1=`sqlite3_import T250M_1.csv T250M_1 t250m_1.db` file_clean_up t250m_1.db cache_clear elapsed2=`sqlite3_import T250M_1.csv T250M_1 t250m_1.db` file_clean_up t250m_1.db cache_clear elapsed3=`sqlite3_import T250M_1.csv T250M_1 t250m_1.db` file_clean_up t250m_1.db echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` # 500M cache_clear elapsed1=`sqlite3_import T500M_1.csv T500M_1 t500m_1.db` file_clean_up t500m_1.db cache_clear elapsed2=`sqlite3_import T500M_1.csv T500M_1 t500m_1.db` file_clean_up t500m_1.db cache_clear elapsed3=`sqlite3_import T500M_1.csv T500M_1 t500m_1.db` file_clean_up t500m_1.db echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` # 1000M cache_clear elapsed1=`sqlite3_import T1000M_1.csv T1000M_1 t1000m_1.db` file_clean_up t1000m_1.db cache_clear elapsed2=`sqlite3_import T1000M_1.csv T1000M_1 t1000m_1.db` file_clean_up t1000m_1.db cache_clear elapsed3=`sqlite3_import T1000M_1.csv T1000M_1 t1000m_1.db` file_clean_up t1000m_1.db echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` # 2000M cache_clear elapsed1=`sqlite3_import T2000M_1.csv T2000M_1 t2000m_1.db` file_clean_up t2000m_1.db cache_clear elapsed2=`sqlite3_import T2000M_1.csv T2000M_1 t2000m_1.db` file_clean_up t2000m_1.db cache_clear elapsed3=`sqlite3_import T2000M_1.csv T2000M_1 t2000m_1.db` file_clean_up t2000m_1.db echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3` # 4000M cache_clear elapsed1=`sqlite3_import T4000M_1.csv T4000M_1 t4000m_1.db` file_clean_up t4000m_1.db cache_clear elapsed2=`sqlite3_import T4000M_1.csv T4000M_1 t4000m_1.db` file_clean_up t4000m_1.db cache_clear elapsed3=`sqlite3_import T4000M_1.csv T4000M_1 t4000m_1.db` file_clean_up t4000m_1.db echo 4000M: `avg $elapsed1 $elapsed2 $elapsed3` # 8000M cache_clear elapsed1=`sqlite3_import T8000M_1.csv T8000M_1 t8000m_1.db` file_clean_up t8000m_1.db cache_clear elapsed2=`sqlite3_import T8000M_1.csv T8000M_1 t8000m_1.db` file_clean_up t8000m_1.db cache_clear elapsed3=`sqlite3_import T8000M_1.csv T8000M_1 t8000m_1.db` file_clean_up t8000m_1.db echo 8000M: `avg $elapsed1 $elapsed2 $elapsed3` # 12000M cache_clear elapsed1=`sqlite3_import T12000M_1.csv T12000M_1 t12000m_1.db` file_clean_up t12000m_1.db cache_clear elapsed2=`sqlite3_import T12000M_1.csv T12000M_1 t12000m_1.db` file_clean_up t12000m_1.db cache_clear elapsed3=`sqlite3_import T12000M_1.csv T12000M_1 t12000m_1.db` file_clean_up t12000m_1.db echo 12000M: `avg $elapsed1 $elapsed2 $elapsed3` # 16000M cache_clear elapsed1=`sqlite3_import T16000M_1.csv T16000M_1 t16000m_1.db` file_clean_up t16000m_1.db cache_clear elapsed2=`sqlite3_import T16000M_1.csv T16000M_1 t16000m_1.db` file_clean_up t16000m_1.db cache_clear elapsed3=`sqlite3_import T16000M_1.csv T16000M_1 t16000m_1.db` file_clean_up t16000m_1.db echo 16000M: `avg $elapsed1 $elapsed2 $elapsed3` # 20000M cache_clear elapsed1=`sqlite3_import T20000M_1.csv T20000M_1 t20000m_1.db` file_clean_up t20000m_1.db cache_clear elapsed2=`sqlite3_import T20000M_1.csv T20000M_1 t20000m_1.db` file_clean_up t20000m_1.db cache_clear elapsed3=`sqlite3_import T20000M_1.csv T20000M_1 t20000m_1.db` file_clean_up t20000m_1.db echo 20000M: `avg $elapsed1 $elapsed2 $elapsed3` # 24000M cache_clear elapsed1=`sqlite3_import T24000M_1.csv T24000M_1 t24000m_1.db` file_clean_up t24000m_1.db cache_clear elapsed2=`sqlite3_import T24000M_1.csv T24000M_1 t24000m_1.db` file_clean_up t24000m_1.db cache_clear elapsed3=`sqlite3_import T24000M_1.csv T24000M_1 t24000m_1.db` file_clean_up t24000m_1.db echo 24000M: `avg $elapsed1 $elapsed2 $elapsed3` # 28000M cache_clear elapsed1=`sqlite3_import T28000M_1.csv T28000M_1 t28000m_1.db` file_clean_up t28000m_1.db cache_clear elapsed2=`sqlite3_import T28000M_1.csv T28000M_1 t28000m_1.db` file_clean_up t28000m_1.db cache_clear elapsed3=`sqlite3_import T28000M_1.csv T28000M_1 t28000m_1.db` file_clean_up t28000m_1.db echo 28000M: `avg $elapsed1 $elapsed2 $elapsed3` # 32000M cache_clear elapsed1=`sqlite3_import T32000M_1.csv T32000M_1 t32000m_1.db` file_clean_up t32000m_1.db cache_clear elapsed2=`sqlite3_import T32000M_1.csv T32000M_1 t32000m_1.db` file_clean_up t32000m_1.db cache_clear elapsed3=`sqlite3_import T32000M_1.csv T32000M_1 t32000m_1.db` file_clean_up t32000m_1.db echo 32000M: `avg $elapsed1 $elapsed2 $elapsed3` # 36000M cache_clear elapsed1=`sqlite3_import T36000M_1.csv T36000M_1 t36000m_1.db` file_clean_up t36000m_1.db cache_clear elapsed2=`sqlite3_import T36000M_1.csv T36000M_1 t36000m_1.db` file_clean_up t36000m_1.db cache_clear elapsed3=`sqlite3_import T36000M_1.csv T36000M_1 t36000m_1.db` file_clean_up t36000m_1.db echo 36000M: `avg $elapsed1 $elapsed2 $elapsed3` # 40000M cache_clear elapsed1=`sqlite3_import T40000M_1.csv T40000M_1 t40000m_1.db` file_clean_up t40000m_1.db cache_clear elapsed2=`sqlite3_import T40000M_1.csv T40000M_1 t40000m_1.db` file_clean_up t40000m_1.db cache_clear elapsed3=`sqlite3_import T40000M_1.csv T40000M_1 t40000m_1.db` file_clean_up t40000m_1.db echo 40000M: `avg $elapsed1 $elapsed2 $elapsed3`
