サイトマップ サイト内検索 まとめ

CSV ファイルに対する SQL 問い合わせの性能確認（Ubuntu で，SQLite 3，pandasql，csvkit，TicklishHoneyBee/csvdb を使用）

別ページで，単一の CSV ファイルに対する SQL 問い合わせの実行法で，手軽にできるものをまとめているが，その性能を確認したいときの手順を，このページで示す．

250M, 500M, 1000M, 2000M の CSVファイルを使い，性能確認を行う．同じ処理を3回実行し，その平均を取るようにする．実験のたびに，ディスクキャッシュをクリアする．

SQLite 3, csvkit など，複数の方法を，同一条件で実験する．

このページで紹介しているソフトウェア類の利用条件等は，利用者で確認すること．

前準備

CSV ファイルの準備

CSV データの合成（Python, random-csv-generator を使用）を行い，CSVデータを合成しておく．

ここで用いているソフトウェア類のインストールや利用例については，別ページ »で説明

ファイル読み込み性能の確認（Python を使用）

ファイルを読み込むだけの Python プログラムである．これで，ファイル読み込み性能を確認できる． 

#/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
}

function doit() {
    sync
    sync
    sync
    sync
    sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    start=`date +%s.%N`
    cat $1 > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}
# 250M 
elapsed1=`doit T250M_1.csv`
elapsed2=`doit T250M_1.csv`
elapsed3=`doit T250M_1.csv`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
elapsed1=`doit T500M_1.csv`
elapsed2=`doit T500M_1.csv`
elapsed3=`doit T500M_1.csv`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
elapsed1=`doit T1000M_1.csv`
elapsed2=`doit T1000M_1.csv`
elapsed3=`doit T1000M_1.csv`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
elapsed1=`doit T2000M_1.csv`
elapsed2=`doit T2000M_1.csv`
elapsed3=`doit T2000M_1.csv`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`

SQLite 3 を用いる方法

SQLite 3 のインストール

SQLite 3は，リレーショナルデータベース管理システム．

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
}

function doit() {
rm -f /var/tmp/testdb.db
rm -f doit.sql
cat >doit.sql<<EOF
pragma cache_size=-20000000;
.separator ,
.import /var/tmp/$1.csv $1
.exit
EOF
cat doit.sql | sqlite3 /var/tmp/testdb.db
    sync
    sync
    sync
    sync
    sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    start=`date +%s.%N`
echo "select region, count(*) from $1 group by region;" | sqlite3 /var/tmp/testdb.db > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}
# 250M 
elapsed1=`doit T250M_1`
elapsed2=`doit T250M_1`
elapsed3=`doit T250M_1`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
elapsed1=`doit T500M_1`
elapsed2=`doit T500M_1`
elapsed3=`doit T500M_1`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
elapsed1=`doit T1000M_1`
elapsed2=`doit T1000M_1`
elapsed3=`doit T1000M_1`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
elapsed1=`doit T2000M_1`
elapsed2=`doit T2000M_1`
elapsed3=`doit T2000M_1`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`

pandasql 

#/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
}

function doit() {
    sync
    sync
    sync
    sync
    sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
cat >hoge.py<<EOF
import pandas as pd
from pandasql import sqldf, load_meat, load_births
T = pd.read_csv('$1.csv')
pysqldf = lambda q: sqldf(q, globals())
a = pysqldf("select region, count(*) from T group by region;")
EOF
    start=`date +%s.%N`
    python3 hoge.py > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}
# 250M 
elapsed1=`doit T250M_1`
elapsed2=`doit T250M_1`
elapsed3=`doit T250M_1`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
elapsed1=`doit T500M_1`
elapsed2=`doit T500M_1`
elapsed3=`doit T500M_1`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
elapsed1=`doit T1000M_1`
elapsed2=`doit T1000M_1`
elapsed3=`doit T1000M_1`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
elapsed1=`doit T2000M_1`
elapsed2=`doit T2000M_1`
elapsed3=`doit T2000M_1`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`

csvkit を用いる方法 

#/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
}

function doit() {
    sync
    sync
    sync
    sync
    sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    start=`date +%s.%N`
    textql -sql "select region, count(*) from $1 group by region" -header $1.csv > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}
# 250M 
elapsed1=`doit T250M_1`
elapsed2=`doit T250M_1`
elapsed3=`doit T250M_1`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
elapsed1=`doit T500M_1`
elapsed2=`doit T500M_1`
elapsed3=`doit T500M_1`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
elapsed1=`doit T1000M_1`
elapsed2=`doit T1000M_1`
elapsed3=`doit T1000M_1`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
elapsed1=`doit T2000M_1`
elapsed2=`doit T2000M_1`
elapsed3=`doit T2000M_1`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`

csvkit を用いる方法 

#/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
}

function doit() {
    sync
    sync
    sync
    sync
    sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    start=`date +%s.%N`
    csvsql --query "select region, count(*) from $1 group by region" $1.csv > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}
# 250M 
elapsed1=`doit T250M_1`
elapsed2=`doit T250M_1`
elapsed3=`doit T250M_1`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
elapsed1=`doit T500M_1`
elapsed2=`doit T500M_1`
elapsed3=`doit T500M_1`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
elapsed1=`doit T1000M_1`
elapsed2=`doit T1000M_1`
elapsed3=`doit T1000M_1`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
elapsed1=`doit T2000M_1`
elapsed2=`doit T2000M_1`
elapsed3=`doit T2000M_1`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`

TicklishHoneyBee/csvdb 

#/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
}

function doit() {
    sync
    sync
    sync
    sync
    sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    start=`date +%s.%N`
    csvdb -e  "select region, count(*) from $1.csv group by region" > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}
# 250M 
elapsed1=`doit T250M_1`
elapsed2=`doit T250M_1`
elapsed3=`doit T250M_1`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
elapsed1=`doit T500M_1`
elapsed2=`doit T500M_1`
elapsed3=`doit T500M_1`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
elapsed1=`doit T1000M_1`
elapsed2=`doit T1000M_1`
elapsed3=`doit T1000M_1`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
elapsed1=`doit T2000M_1`
elapsed2=`doit T2000M_1`
elapsed3=`doit T2000M_1`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`

q を用いる方法 

#/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
}

function doit() {
    sync
    sync
    sync
    sync
    sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    start=`date +%s.%N`
    q -H -d , "select region, count(*) from $1.csv group by region" > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}
# 250M 
elapsed1=`doit T250M_1`
elapsed2=`doit T250M_1`
elapsed3=`doit T250M_1`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
elapsed1=`doit T500M_1`
elapsed2=`doit T500M_1`
elapsed3=`doit T500M_1`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
elapsed1=`doit T1000M_1`
elapsed2=`doit T1000M_1`
elapsed3=`doit T1000M_1`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
elapsed1=`doit T2000M_1`
elapsed2=`doit T2000M_1`
elapsed3=`doit T2000M_1`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`