SQLite + Pythonユーザ定義関数組込で進捗ダメじゃないですになりました

概要

これまで「Hiveからデータ取得・簡単な加工→Pythonで加工・分析」
という流れで作業していたのですが、
Hive→SQLite→Pythonという流れにしたところ進捗が改善されたので、
SQLiteの簡単な使い方とPythonによるSQLユーザ定義関数の組込方法
についてメモを残しておきます。
特にユーザ定義関数の組込を自由に出来ると、
分析する際、相当楽になるということに気付きました。

SQLite挟むことで何がどう改善されたの？

Hiveはデカいデータをゴリゴリ取ってくる分には
SQLちょっと書くだけで済むので大変便利ですが、
初動遅いためちょこちょこ小さいデータを何度も取ろうとするとストレス溜まります。
そのため、これまではある程度のデータをまとめてHiveで落としてきて
Pythonで加工してから分析するという流れを取っていました。
ただ加工するために似たようなコード何度も書くのだるいし、
上手いこと書かないと結構処理に時間かかってしまうのでだるい、
ダブルダルくて進捗ダメでした。
そこで、Hiveから取ってきたデータを一旦SQLiteに入れてしまいさえすれば
1. 抽出・加工がSQLだけで出来る、
2. しかも結構高速*1
なので進捗ダメじゃないですになりました。
MySQLでええやんって言われたらまぁそりゃそうなんですが、
SQLiteはお手軽なので可愛い、可愛いは正義。
SQLiteは下記からバイナリ一つ落としてくるだけで利用出来ます。
面倒な環境設定などは必要無しなので手っ取り早くRDBMS使いたいって時に特に良い。
http://www.sqlite.org/download.html

SQLiteの簡単な使い方

sqlite3 test.db --test.dbという名前でDBファイルを生成。SQLiteはこのDBファイルにテーブル情報全部入ってて、これを渡すだけでデータまるごと渡せる、便利
.separator , --ファイルセパレータを,に設定
.import test.csv test_table --test.csvファイルをtest_tableに取り込み
-- なんとこの三行で下準備終わり

.mode csv --CSV形式で出力するという設定
.output output.csv --クエリの結果をoutput.csvに出力するという設定
select * from test_table; --このクエリの結果がoutput.csvに吐かれる
.output stdout --出力先を標準出力に戻す

PythonでSQLite用にユーザ定義関数を組み込む

# 実はこの記事なんて読む必要無くて下記参照すれば出来る
# https://python-doc-ja.readthedocs.org/en/latest/library/sqlite3.html
# 今回はSQLiteには標準搭載されていないmedianを組み込んでみる
import sqlite3
import numpy #科学計算用ライブラリ。様々な統計関数が用意されているのでSQLに組み込むと大変便利

class Median:
    def __init__(self):
        self.values = []
    def step(self, value):
        self.values.append(value)
    def finalize(self):
        return numpy.median(self.values)

con = sqlite3.connect("test.db", isolation_level=None)
con.create_aggregate("median", 1, Median)

c = con.cursor()
query = "select median(hoge) from test_table"
c.execute(query)
l = []
for row in c:
    print row

感想

とても簡単。
と思いきやmedian組み込むのに結構四苦八苦した。
始めはSQLだけでやろうとしたり
numpy使わず自前でmedian相当のコード書いてたりしたけど、
データサイズが8GBくらいになったら糞遅かったり途中で落ちたりしてかなり困ってた。
自前実装にそこまで拘る必要無い*2かなと思って
numpy.median使った瞬間データが10GBオーバーでも全然軽快に捌いてくれた*3

ユーザ定義関数自由に組み込めると何がハッピーかって、
Pythonは統計/機械学習のライブラリが充実しているため、
そのライブラリの関数をSQLに組み込みさえすれば
クエリ打つだけで分析が出来るようになるってことです。
しかも弊社では社内の一般ユーザ（＝非エンジニア）向けにデータを自由に取り出せるよう
クエリを打つ口を作っています。
そのクエリを打つシステムにコイツを組み込めば、
各ユーザに分析環境構築して貰う必要無く、
クエリ打つだけで誰でも統計手法が適用可能になるのでハッピー。
ありがとうSQLite、numpy！
お陰様で進捗ダメじゃないです！