巨大なバッチを分割して構成する〜SQLバッチフレームワークBricolage〜を使ってみた #bricolage

テーブルの名前をちょっとだけ変えてcreate tableしたりデータをinsert intoすることがしたくなり、なにか良いツールはあるかな？と探していたところ、Cookpadの青木さんが「たべみる」で使われているbricolageを見つけました。

不思議と使ったという情報がみつからず、使用した記録をまとめました。
bricolageの利用例など是非教えてください。

参照サイト

試した環境

OS: OSX 10.11.1
Ruby: 2.2.3
PostgreSQL: 9.4.5
MariaDB: 10.0.21

導入

PostgreSQLとMySQL(mariadb)等が必要になるようなので事前に導入しておきます。

本体はgem install bricolageで導入できます。

gem install bricolage 
Successfully installed bricolage-5.12.2
Parsing documentation for bricolage-5.12.2
Done installing documentation for bricolage after 1 seconds
1 gem installed

環境構築

bricolageはディレクトリ構成が決まっています。例では次のようなディレクトリ構成にしました。
詳細はbricolageのWikiにあるConfigurationを参照してください。

.
|-- config
|   |-- development
|   |   `-- database.yml # 開発用データベース設定
|   `-- production
|       `-- database.yml # 本番用データベース設定
`-- subsys # ジョブファイルを置くディレクトリ
    |-- hello_world.job   
    |-- hello_world.sql
    |-- sample-create.ct
    `-- sample-create.job

この中でdatabase.ymlは次のような形式になっています。

database.yml

psql: # この名前がdata-soureで指定した名前に対応付く
  type: psql
  host: localhost
  port: 5432
  database: dbname
  username: username
  password: password

サンプル1 Hello Woldを出力するSQLの実行

Hello Worldを出力するだけの簡単なジョブファイル

hello_world.job

class: sql
sql: hello.sql
data-source: psql

hello_world.sql

SELECT 'Hello bricolage World!' AS Message;

コマンドを実行するには、bricolage --job hello_world.jobのようにジョブファイルを指定します。

bricolage --job hello_world.job 
2015-11-11 12:58:06 +0900: INFO: development environment
2015-11-11 12:58:06 +0900: INFO: command: psql --no-psqlrc --host=localhost --port=5432 --username=user db_name --echo-all -v ON_ERROR_STOP=true -f /var/folders/rc/t8w2gc117sxghrk6pdkfq3qc0000gp/T/1447214286_14743_3fe9a5c5e1d4_27074 --no-password
\timing on
Timing is on.
-- /path/to/bricolage_home/subsys/hello_world.sql
SELECT 'Hello bricolage World!' AS Message;
        message         
------------------------
 Hello bricolage World!
(1 row)

Time: 0.589 ms
2015-11-11 12:58:06 +0900: INFO: status: 0 (pid 15467 exit 0)
2015-11-11 12:58:06 +0900: INFO: 0.02 secs
2015-11-11 12:58:06 +0900: INFO: SUCCESS

サンプル2 create tableを行うジョブを実行する

sample-create.job

class: create
table-def: sample-create.ct
drop: true # テーブルを作る前にdrop tableを実行する
analyze: true # テーブルを作ったあとにanalyzeを実行する
data-source: psql

$tableは--dest-table:で定義した変数が展開されてcreate table sampleという名前になります。SQLの中に埋め込むだけでなく、YAMLファイルに変数を定義することもできるようです。

sample-create.ct

--dest-table: sample

create table $dest_table
( x int
, y int
, z int
);

実際に実行されるSQLがどのようになるか、dry-runを実行します。

bricolage -n --job sample-create.job 
\timing on

\set ON_ERROR_STOP false
drop table sample cascade;
\set ON_ERROR_STOP true

-- /path/to/bricolage_home/subsys/sample-create.ct
create table sample
( x int
, y int
, z int
);

analyze sample;

実際にcreate tableを実行します。

bricolage --job sample-create.job 
2015-11-11 13:01:13 +0900: INFO: development environment
2015-11-11 13:01:13 +0900: INFO: command: psql --no-psqlrc --host=localhost --port=5432 --username=user db_name --echo-all -v ON_ERROR_STOP=true -f /var/folders/rc/t8w2gc117sxghrk6pdkfq3qc0000gp/T/1447214473_16210_3fef8445e1d4_625 --no-password
\timing on
Timing is on.
\set ON_ERROR_STOP false
drop table sample cascade;
DROP TABLE
Time: 19.592 ms
\set ON_ERROR_STOP true
-- /path/to/bricolage_home/subsys/sample-create.ct
create table sample
( x int
, y int
, z int
);
CREATE TABLE
Time: 5.934 ms
analyze sample;
ANALYZE
Time: 0.926 ms
2015-11-11 13:01:13 +0900: INFO: status: 0 (pid 16934 exit 0)
2015-11-11 13:01:13 +0900: INFO: 0.05 secs
2015-11-11 13:01:13 +0900: INFO: SUCCESS

その他コマンド

どんなジョブクラスがあるか一覧を表示する

bricolage --list-job-classでどんなジョブクラスがあるかを確認することができます。
また、ジョブクラス一覧にも記載されています。

bricolage --list-job-class 
create
exec
insert
insert-delta
load
my-export
my-migrate
noop
rebuild-drop
rebuild-rename
s3-put
sql
streaming_load
td-delete
td-export
unload
wait-file

ジョブクラスのヘルプを見る

bricolage ジョブクラス名 --helpで各ジョブクラスのヘルプを見ることができます。

ジョブクラスcreateのヘルプ

bricolage create --help
Usage: bricolage create [job_class_options]
        --table-def=PATH             CREATE TABLE file.
        --dest-table=[SCHEMA.]TABLE  [optional] Target table name.
        --drop                       [optional] DROP table before CREATE.
        --analyze                    [optional] ANALYZE table after SQL is executed.
        --data-source=NAME           [optional] Main data source. [default: sql]
    -v, --variable=NAME=VALUE        Set variable.
        --help                       Shows this message and quit.
        --version                    Shows program version and quit.

ジョブクラスsqlのヘルプ

bricolage sql --help
Usage: bricolage sql [job_class_options]
        --sql-file=PATH              SQL file.
        --dest-table=[SCHEMA.]TABLE  [optional] Target table name.
        --src-table=VAR:[SCHEMA.]TABLE
                                     [optional] Source table name.
        --truncate                   [optional] TRUNCATE table before SQL is executed.
        --analyze                    [optional] ANALYZE table after SQL is executed.
        --vacuum                     [optional] VACUUM table after SQL is executed.
        --vacuum-sort                [optional] VACUUM SORT table after SQL is executed.
        --data-source=NAME           [optional] Main data source. [default: sql]
    -v, --variable=NAME=VALUE        Set variable.
        --help                       Shows this message and quit.
        --version                    Shows program version and quit.

TODO

ERBの埋め込み例
他の変数の使い方

巨大なバッチを分割して構成する 〜SQLバッチフレームワークBricolage〜 を使ってみた