トップページインストール,使い方オープンデータ,データファイル処理CSV ファイルに対する SQL 問い合わせの性能確認(Ubuntu で,SQLite 3,pandasql,csvkit,TicklishHoneyBee/csvdb を使用)

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`