EmbulkでMySQLのデータをBigQueryにロードするのに便利なツールの開発

Embulkを利用してMySQLのデータをBigQueryにロードする際に便利なツールを開発したので紹介です。


Samidareっていいます! よろしくお願いします。Embulkの設定作成はお任せください!」

開発経緯

業務でログデータの解析基盤としてBigQueryを使用することになったのですが、本番環境のMySQLのデータもBigQueryで参照できるようにすると色々と夢が膨らむという事に気付きました。
Embulkを利用すると簡単にMySQLのデータをBigQueryにロードできるという情報を入手したので検証したところ、確かにこれは楽チンだという結論に至りました。ただ、問題点としてテーブルの数だけEmbulkの設定ファイルを作成し、テーブル毎にEmbulkを実行しなければならないという点です。
当初でもBigQueryにロードしたいテーブル数は20以上あり、今後対象のテーブルが増えることは容易に想像がつきました。対象テーブルが増える毎にEmbulkの設定作成、BigQueryにテーブル追加、実行設定修正という事態は避けたいところです。
そこでEmbulkの設定作成、BigQueryのスキーマ作成、Embulk実行を容易にするようなツールを作成できないか検討することになりました。

仕様・機能・特長

  • Ruby製でGemとして提供、利用できます(Java経験が長いのでRubyとしては微妙なコード)
  • MySQLのテーブルスキーマを基にEmbulk用のSQL、BigQueryのテーブルスキーマを生成してくれる
  • 設定ファイルはYAMLMySQLのDB接続設定と対象テーブルを列挙するだけ
  • 生成した設定でEmbulkを実行する
  • tinyintタイムゾーン問題を解決しています
  • テーブル毎に絞込み条件(SQLのWHERE句)を設定できる
  • 実行ステータスを返す(成功時はtrue、エラーがあった場合はfalseが返される)
  • リトライ回数を設定できる



利用方法

詳細な利用方法、機能はGitHubSamidareを参照ください。
ここでは大まかな設定内容と実行方法を紹介します。

・database.ymlの設定

MySQLのDB接続設定としてdatabase.ymlを作成します。対応するBigQueryのデータセットとしてbq_datasetを設定します。

db01:
  host: localhost
  username: root
  password: pswd
  database: production
  bq_dataset: mysql_db01
・table.ymlの設定

MySQLの対象テーブルの定義としてtable.ymlを作成します。対象テーブルを列挙するだけの簡単設定です。

db01:
  tables:
    - name: users
    - name: events
    - name: hobbies
・実行

以下の様なRubyスクリプトを作成して実行します。BigQueryの認証設定とSamidareが作成する設定ファイルの出力ディレクトリを指定します。

require 'samidare'

config = {
 'project_id' => 'BIGQUERY_PROJECT_ID',
 'service_email' => 'SERVICE_ACCOUNT_EMAIL',
 'key' => '/etc/embulk/bigquery.p12',
 'schema_dir' => '/var/tmp/embulk/schema',
 'config_dir' => '/var/tmp/embulk/config',
 'auth_method' => 'private_key'
}

client = Samidare::EmbulkClient.new
client.generate_config(config)
client.run(config)


今後の予定

当初予定した機能は実装しきったのでJava版のSuzukazeなどを作成しようかと思案しております。

H2 Databaseでの主キー生成、ユニークインデックス生成について

H2 databaseでの主キー生成、ユニークインデックス生成について調べる機会があったので
備忘のためにまとめてみました。

以下のようなテーブルを例として、主キー、ユニークインデックスの生成、削除、定義確認の
SQLを列挙しています。
なお、記述したSQLの大文字部分はH2の予約語になります。

CREATE TABLE IF NOT EXISTS multi_keys(
  id INT(10) DEFAULT 0 NOT NULL AUTO_INCREMENT,
  id2 INT(10) DEFAULT 0 NOT NULL,
  unique_id INT(10) DEFAULT 0 NOT NULL,
  real_value REAL DEFAULT 0 NOT NULL,
  com VARCHAR(200),
  test_date DATE DEFAULT CURRENT_DATE(),
  update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP() 
);

主キーの生成、削除のSQLは以下のようになります。
ちなみに、CONSTRAINT_NAMEは指定した名称が割り当てられるのですが、
INDEX_NAMEとしては「PRIMARY_KEY_6」のような自動生成された名称になります。
INDEX_NAMEに任意の名称をつける方法は分かりませんでした。

・生成
ALTER TABLE multi_keys ADD CONSTRAINT multi_leys_pk PRIMARY KEY(id, id2);

・削除
ALTER TABLE multi_keys DROP PRIMARY KEY;

・定義確認
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINTS /* 制約情報 */
ORDER BY TABLE_NAME, CONSTRAINT_NAME;

SELECT * FROM INFORMATION_SCHEMA.INDEXES /* インデックス情報 */
ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION;

主キーとは別にユニークインデックスを張るには以下のようになります。

・生成
CREATE UNIQUE INDEX IF NOT EXISTS multi_keys_idx ON multi_keys (unique_id);

・削除
DROP INDEX multi_keys_idx;

・定義確認
SELECT * FROM INFORMATION_SCHEMA.INDEXES /* インデックス情報 */
ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION;

MySQLのデータディクショナリの活用例

テーブル名からDDLを生成するライブラリ作成の過程で
MySQLデータディクショナリを活用したので活用例を記しておきます。
なお、MySQL 5.6.15にて動作を確認しております。
まずは基本的な部分から。

・テーブル一覧
SELECT * FROM INFORMATION_SCHEMA.TABLES
ORDER BY table_schema, table_name

・各テーブルの列定義一覧
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY table_schema, table_name, column_name

・制約情報一覧(主キーや外部キーなど)
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
ORDER BY table_schema, table_name, constraint_name, ordinal_position

実際には以下のようにスキーマやテーブル名、列の定義内容で絞り込んで使う機会が多いかと。
テーブル名、列の定義順などでソートするにはtable_name、ordinal_positionを
ORDER BY句に指定します。

・テーブル名に「log」を含むテーブル一覧(スキーマ絞り込み)
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'develop'
AND   table_name like '%log%'
ORDER BY table_name

・自動インクリメントが定義されている列とテーブルの一覧
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE EXTRA = 'auto_increment'
ORDER BY table_schema, table_name, column_name

・列の主要な属性に絞り込んだ一覧(列の定義順でソート)
SELECT
  table_name, ordinal_position, column_name, data_type,
  column_default, is_nullable, character_maximum_length,
  numeric_precision, numeric_scale
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'develop'
ORDER BY table_name, ordinal_position

・各テーブルの制約情報一覧(スキーマで絞り込み)
SELECT table_name, constraint_name, ordinal_position, column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE table_schema = 'develop'
ORDER BY table_name, constraint_name, ordinal_position

Windows7でのgem install mysql2エラーへの対処

Windows環境へRuby on Railsをセットアップする際にmysql2のインストールで
ハマったので対処方法を後世のために記しておきます。
なお、以下の環境における内容になります。

[環境情報]
OS:Windows 7
Ruby:2.0.0p353
Rails:4.0.2
MySQL:5.6.15


RubyRailsのインストールまでは順調に進むのですがMySQLのライブラリである
mysql2をインストールしようとすると以下のようなエラーが発生します。

gem install mysql2


ERROR:  Error installing mysql2:
	ERROR: Failed to build gem native extension.

(中略)

Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers.  Check the mkmf.log file for more details.  You may
need configuration options.

Provided configuration options:
	--with-opt-dir
	--without-opt-dir
	--with-opt-include
	--without-opt-include=${opt-dir}/include
	--with-opt-lib
	--without-opt-lib=${opt-dir}/lib
(後略)

関連するライブラリやヘッダーファイルが無いと怒られているので
「--with-mysql-dir」オプションを指定してMySQLサーバーなどをインストールしている
ディレクトリを指定します。

しかし、今度は別のエラーが発生します。

gem install mysql2 --platform=ruby '--with-mysql-dir="(MySQLのディレクトリ)"'


ERROR:  Error installing mysql2:
	ERROR: Failed to build gem native extension.

(中略)

[RUBY_HOME]\lib\ruby\gems\2.0.0\gems\mysql2-0.3.15\ext\mysql2/result.c:530: undefined reference to `mysql_error@4'
[RUBY_HOME]\lib\ruby\gems\2.0.0\gems\mysql2-0.3.15\ext\mysql2/result.c:547: undefined reference to `mysql_fetch_fields@4'
result.o: In function `rb_mysql_result_free_result':
[RUBY_HOME]\lib\ruby\gems\2.0.0\gems\mysql2-0.3.15\ext\mysql2/result.c:76: undefined reference to `mysql_free_result@4'
result.o: In function `rb_mysql_result_each':
[RUBY_HOME]\lib\ruby\gems\2.0.0\gems\mysql2-0.3.15\ext\mysql2/result.c:499: undefined reference to `mysql_num_rows@4'
result.o: In function `rb_mysql_result_free_result':
[RUBY_HOME]\lib\ruby\gems\2.0.0\gems\mysql2-0.3.15\ext\mysql2/result.c:76: undefined reference to `mysql_free_result@4'
collect2.exe: error: ld returned 1 exit status
make: *** [mysql2.so] Error 1

(後略)


※[RUBY_HOME]はRubyがインストールされているディレクトリを表します


途方にくれましたが、以下の手順で解決できました。
「--with-mysql-dir」全体を「'」、ライブラリパスを「"」で囲うのをお忘れずに。


(1)以下のzipファイルを取得し、任意の場所に解凍する。
http://cdn.mysql.com/Downloads/Connector-C/mysql-connector-c-noinstall-6.0.2-win32.zip

(2)mysql-connector-c-noinstall-6.0.2-win32\lib\libmysql.dllを[RUBY_HOME]\binにコピーする。

(3)(1)で解凍したライブラリのディレクトリを指定してmysql2のインストールを実行。

gem install mysql2 --platform=ruby -- '--with-mysql-dir="(解凍ディレクトリ)\mysql-connector-c-noinstall-6.0.2-win32"'


参考にしたのは以下のサイトになります、感謝。
http://www.oiax.jp/rails/zakkan/ruby_2_0_mysql_windows.html

JavaScriptの真偽値判定について

JavaScriptの真偽値判定が独特な挙動を示すのでまとめてみました。
静的型付言語を主に扱っていると驚かされますね。


Flexigridのソースを眺めていたら以下のようなコードがあって「?」となったのですが
JavaScriptブーリアン型への暗黙の型変換の仕組を知って合点がいきました。
onSubmitはサーバーへのAjax通信時に呼び出されるイベントなのですが
イベントハンドラの有無は利用者に委ねられています。

以下の要件を満たすためにこのような実装になっているようです。
ポイントはonSubmitにイベントハンドラが割当られるとif文がtrueになる点です。
イベントハンドラは関数オブジェクトになるのですがJavaScriptでは
ブーリアン型への暗黙の型変換によりオブジェクトはtrueとして扱われます。


・初期状態ではイベントハンドラがない
・利用者がイベントハンドラを設定した場合はそれを実行する
・通信前の状態チェックなどが実行されるイベントなので戻り値は真偽値になるべき


var onSubmit = false;

(中略)

if (onSubmit) {
    var gh = onSubmit();
    if (!gh) {
        return false;
    }
}

※説明のために実際のコードとは細部が異なります


ここでJavaScriptの真偽値判定の仕様が気になってきます。
急いでパーフェクトJavaScriptを見返すことに。
まとめると以下の値の場合はfalseとして扱われます。
逆に言えば、この値以外の場合はtrueです!
オブジェクトもtrueなんですよ!!


・数値 0、NaN
・null
・undifined
・空文字列


というわけで本当なのかと以下のコードで検証してみました。
検証結果は各値のコメント部分になります。
文字列は文字列長で判定しているのですかね。
変数や関数未定義でのundefined→falseはハマりそうですね。

function checkBoolean(val) {
    if (val) {
        console.log(val + ' is true');
    } else {
        console.log(val + ' is false');
    }
}

var obj = {};
checkBoolean(obj); // true

checkBoolean(NaN); // false
checkBoolean(undefined); // false
checkBoolean(null); // false

var hoge;
checkBoolean(hoge); // false

checkBoolean(0); // false
checkBoolean(1); // true
checkBoolean(0.1); // true
checkBoolean(0.0); // false

checkBoolean(""); // false
checkBoolean(" "); // true
checkBoolean("a"); // true

jQueryのFlexigridのflexOptions、paramsについて

今更ながらにFlexigridです。
データロード時に任意のパラメーターをサーバーサイドに
渡す方法が見つからなかったのでまとめてみました。
flexOptions、paramsはあまり使われていないのですかね。


以下のような画面で入力された内容を元に
データを絞り込んで描画するというケースは多いかと思います。
Flexigridのデータロードも$.ajax関数で行っているので
dataに値を渡せれば良いわけです。
Flexigridにはparamsがあり、これがdataへの受け渡し口になっています。
paramsにはflexOptionsで値をセットします。

f:id:cobot00:20130928144034p:plain


[今回のポイント]
・flexOptions
・params
・onSubmit

JavaScriptの記述は以下のようになります。
なお、今回の記述ではsetLoadParamsが2回実行されます。
通常はonSubmitだけの記述になるはずですが
状況によってパラメーターを変えたいといったケースでは
onSubmitではflexOptionsを呼び出さず、個別に処理を記述することになるかと。

(function($){
    var AJAX_URL = 'Ajaxの通信先サーバーを指定しよう';
    var ID_TEST_GRID = '#test_grid';

    $(function() {
        init();
    });

    function init() {
        $("#btn_load").click(function(){
            load();
        });

        $(ID_TEST_GRID).flexigrid({
            url: AJAX_URL,
            method: 'POST',
            dataType: 'json',
            onSubmit: setLoadParams, // Ajaxでの通信前に実行されるイベント
            width: 250,
            height: 150,
            autoload: false,
            buttons:[
                {name: 'これでもロード可能', bclass: 'loadBtn', onpress: load},
                {separator: true}]
        });
    }

    function load() {
        // onSubmitを指定していない場合はflexReloadを実行前にパラメーターをセットする
        // 今回のケースではonSubmitでの実行と合わせてsetLoadParamsは2回実行されます
        setLoadParams();

        $(ID_TEST_GRID).flexReload();
    }

    function setLoadParams() {
        // 「params」の値は配列で渡す必要があります
        var loadCond = [
            {name: 'cond1', value: $('#cond1').val()},
            {name: 'cond2', value: $('#cond2').val()}
        ];

        // 「params」は$.ajax関数の「data」の値を受け渡すためのプロパティ
        $(ID_TEST_GRID).flexOptions({
            params: loadCond
        });

        /* onSubmitに指定している場合はtrueを返さないと
           flexigrid.jsのpopulate関数内でAjax通信が実行されません
           
           ※populate関数内の実装
            if (p.onSubmit) {
                var gh = p.onSubmit();
                if (!gh) {
                    return false;
                }
            }
        */
        return true;
    }
    
})(jQuery);


ちなみにHTMLは以下のようになります。

<HTML>
<HEAD>
    <meta http-equiv="Content-Type" content="text/html; charset="utf-8">
    <meta http-equiv="Content-Script-Type" content="text/javascript" />
    <link rel="stylesheet" href="./css/flexigrid.css" />
    <script type="text/javascript" src="./scripts/jquery-1.8.2.js"></script>
    <script type="text/javascript" src="./scripts/flexigrid.js"></script>
    <script type="text/javascript" src="./scripts/flexigrid_test.js"></script>
    <TITLE>test</TITLE>
</HEAD>
<BODY>
    <br>
    <div>条件1 &nbsp;<input id="cond1" type="text" /></div>
    <div>条件2 &nbsp;<input id="cond2" type="text" /></div>
    <br>
    <input type="button" id="btn_load" value="条件を指定してロード"/>
    <br>
    <br>
    <table id="test_grid">
        <thead>
            <tr>
                <th width="50">列A</th>
                <th width="50">列B</th>
                <th width="50">列C</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>A-1</td>
                <td>B-1</td>
                <td>C-1</td>
            </tr>
            <tr>
                <td>A-2</td>
                <td>B-2</td>
                <td>C-2</td>
            </tr>
        </tbody>
    </table>
</BODY>
</HTML>

jarコマンドでマニフェストファイルだけ取り出す

普段何気に利用しているjarのバージョンが気になって
マニフェストファイルを確認しようと思ったら意外と面倒で
簡単に実現できる方法はないか色々と試してみました。
結論としては以下のコマンドでマニフェストファイルだけ抽出できますね。
「xオプション+対象ファイル指定」がポイントです。


jar xf hoge.jar META-INF/MANIFEST.MF


意外と「xオプション+対象ファイル指定」の使用例を見かけなかったので
いくつか試してみました。


hoge.jar
  │
  └─frist
     ├──hoge1st.class
     │
     └──second
          ├──hoge2nd.class
          ├──fuga2nd.class
          │
          └──third
               ├──hoge3rd.class
               └──fuga3rd.class


上記のようなパッケージ構成のjarにおいて

jar xf hoge.jar first/Hoge1st.class

を実行するとfirstフォルダとその直下にHoge1st.classが出力されます。

ちなみに以下の場合だとthirdパッケージも含めてsecond以下が全て出力されます。

jar xf hoge.jar first/second


残念ながら、任意のパッケージ以下のクラスのみを出力する
というのはどうも出来ないようです。
上記の構成でいうとsecondパッケージ以下のクラスのみを
出力するといったケースです。