Javaではてなブックマークのデータ解析入門(sqliteã¸ã®æ ¼ç´ç·¨)

こんにちは、らくからちゃです。

前回に引き続きまして、はてなブックマークからブックマークのデータをJavaを使って抽出し、解析を行う方法のプログラムの作り方についてお話したいと思います。本稿は、以下の記事の続きとなりますので、ご承知おき下さい。

さて前回は、URLではなく、特定の『サイト』に付けられたブックマークを一斉に取得する方法についてご説明いたしました。この方法を行えば、過去記事につけられたブックマークの全てのデータを抽出することも出来るかと思います。

ただ、ある程度大量のデータを扱うのであれば、テキストファイルで処理するのはちょった大変ですよね？そこで、今回は、取得したデータを、簡易データベースである『sqlite』に格納し、効率的に分析する方法についてご紹介したいと思います。

ところで皆さん、データベースというと、どういったものを思い浮かべますか？ウェブ系の人であればMySQL、業務系の人であればOracle、事務系の人であればAccessなんかを想像されるかもしれませんね。世の中には色々なデータベース・ソフトウェアは有りますが、個人でちょっとしたデータを扱うにはsqliteが圧倒的におすすめです。いろいろと理由はあるのですが、

1. 無償で使える
2. パフォーマンスが爆速
3. サーバの構築が不要

で使えることが大きなメリットですね。パフォーマンスについては、『Firefoxの内部データを管理したり組み込みで使ったりしてるアレでしょ？』ってイメージしか無かったのですが、数十万行単位のデータでも比較的さくさく扱えます。（参考:パフォーマンス比較 Cassandra、Mongodb、SQLite、H2、MySQL、Postgres - C/pHeR Memo - Java とか。Eclipse とか。)

また、サーバを構築する必要がなく、データのバックアップや移行も『コピペでOK』という、ずぼらなDIYエンジニアには最適です。

前回は、『HTMLでHelloWorldって表示させることのが出来ればOKです！』と言いましたが、今回はそれに加えて簡単なSQLの知識が必要になります。（まあ、なくとも動かすまでは出来ると思いますが）。ざっくり言うと、『select文でHelloWorldって表示させることが出来るくらいの知識』ですかね。（なんやねんそれ）ちょっと自信がない人は、この辺の本もどうぞ！

スッキリわかる SQL 入門 ドリル215問付き! (スッキリシリーズ)

posted with ヨメレバ

中山 清喬,飯田 理恵子 インプレス 2013-04-19

Amazonで購入

Kindleで購入

楽天ブックスで購入

hontoで購入

図書館で探す

 

それじゃあ、

いっくぞー( ･`д･´)

データベースファイルの作成

さて、今回はちょっと事前準備に時間がかかります。今回の例ではMacOSXでの例を挙げていますが、WindowsやLinuxの環境でも、似たようなキーワードで検索して頂ければ、だいたい問題なくいけるかと思います。

まず最初に、SQLiteStudioをインストールします。

• SQLiteStudio

これは、sqlite形式のデータベースファイルを確認することが出来るツールです。別に気に入ったものがあればそっちを使ってもらえればいいのですが、GNOME環境で動いたので、気に入って使っています（笑）。ObjectBrowserみたいな感じのツールですね。

ダウンロードして開くと、こんな感じの画面になると思います。

この画面の左から３番目のボタンを押すと、データベースファイルの作成画面になります。

データベースファイル名はHatenaBookmark.dbにでもしておきましょう。

ファイルが作成できたら、白紙記号のボタンから、下記のSQL文を実行します。

giste7740dd833e179fe29fa

これでひとまずOKです。

ライブラリの取得と下ごしらえ

お次は、いつもの通りライブラリを追加します。今回使うのはこちら。

• xerial / sqlite-jdbc / Downloads — Bitbucket

とりあえず最新のライブラリを取得しておきましょう。あとは、前回同様ライブラリを追加いたしましょう。

こんな感じですね。あと引数に、作成したファイルのパスを追加して下さい。

Windowsとかであれば、C:¥Users¥user¥Desktop¥HatenaBookmark.dbとかですかねー。場所は、アクセスできる場所であれば、どこでも大丈夫だと思います。

ソースコードの貼り付けと実行

さて、これで後は『コピペでOK！』ですね。

gist270b10bc6e4deedff851

ソースコード解説

では簡単に解説を。ひとまず、39行目から43行目でデータベースへのコネクションを開始しています。

String dbUrl = "jdbc:sqlite:" + args[2];
String driver = "org.sqlite.JDBC";
DriverManager.setLoginTimeout(10000);
Class.forName(driver).newInstance();
sconn = DriverManager.getConnection(dbUrl);
sconn.setAutoCommit(false);

追加した引数で、指定した接続先につなごうとしているわけですね。とりあえず自動コミットモードはオフにしておきました。

お次に、BOOKMARKS表のデータを一旦リフレッシュします。

pstmt = sconn.prepareStatement("delete from BOOKMARKS");
pstmt.executeUpdate();

そして最後に72行目から80行目がデータのInsert文ですね。先ほどのDelete文では余り意味はありませんが、このSQLは、prepareStatementという形式で書かれています。この形式で書かれている場合、SQL文内に"?"と書いておけば、setStringなどの命令で、その?の出現回数の後の値を置き換えることが出来ます。

 

pstmt = sconn.prepareStatement("insert into BOOKMARKS(URL,EID,BOOKMARKUSER,STARCOUNT,TIMESTAMP,COMMENT,TAG) values(?,?,?,?,?,?,?) ");
pstmt.setString(1, page.getUrl());
pstmt.setString(2, page.getEid());
pstmt.setString(3, bookmark.getUser());
pstmt.setInt(4, 0);
pstmt.setDate(5, new java.sql.Date(bookmark.getTimestamp().getTime()));
pstmt.setString(6, bookmark.getComment());
pstmt.setString(7, bookmark.getTags());
pstmt.executeUpdate();

 オラクルでいうバインド変数みたいなもんですね。見た目もすっきりしますし、パフォーマンス的にもこちらのほうが早くなるかと思います。

でっかいデータはデータベースを使おう！

さて、これでしばらく待って、テーブルのデータを確認すると、こんな感じで表示されると思います。

今回、取得した過去４０記事のデータを確認すると、全部で4,710件のブックマークデータが取得できました。やっぱり、これくらい大きいデータを解析するのであれば、データベースに入れておけば便利ですよね〜。

例えば、『誰がブックマークしてくれたんだろう？』ということを確認するためには、こんな感じのSQL文を流せば簡単ですよね。

gistefa75d9d1a04cc62a31a

SQLの実行結果から、『過去にブックマークしてくれた人ベスト１０』を作るとこんな感じになりました。

ユーザー	件数
id:lacucaracha	40
id:keisolutions	29
id:hisatsugu79	23
id:cild	21
id:cyberglass	21
id:hatebupost	21
id:Daisuke-Tsuchiya	19
id:SikisimaHisayuki	19
id:hitode99	18
id:treve2013	16

セルクマが断トツ（笑）。こういったデータを元に色々と解析していったら楽しめそうですね♪JavaもSQLも、無味乾燥なサンプルデータを使うよりも、実際のデータを弄ったほうが勉強がはかどりますね。

ここから、ブログの記事にするもよし、ネットサービスを開発するもよし、互助会の探索活動をしてみるもよし、色々とデータとしていじりがいがあります（笑）。みなさまも、よければ是非！

もし何か、不明な点やお手伝いできることがあれば、どうぞtwitterにてご質問はいつでも承っています（返すのは気が向いた時ですけど）ので、なんなりと！

ではでは、今日はこの辺で。