ストアドを使って、Webスクレイピングをしよう！

mysql libra udf scraping

こんにちは、SQLを愛してやまないmoriyoshiです。

ストアドプロシージャは、一連のSQL文をサブルーチンのようにDBサーバに記録しておき、後からそれを呼び出すことができるようにする仕組みです。近代的なRDBMSには標準的に備わっている機能といえます。

制御構造などもSQL文で記述することができるので、結果的に、あらゆるロジックをSQLのみで記述することができます。手続き型プログラミングにどっぷり浸かった現場の方から愛用されていると言われています。

今回は、ストアドプロシージャの応用として、Webスクレイピングを行なってみましょう。Webスクレイピングとは、特定のWebサイトにアクセスし、そのページの内容 (HTML) を取得、解析し、必要な情報を取り出すという一連の操作を自動化することです。Webスクレイピングを効果的に活用すると、人間がブラウザに向かって単純作業を繰り返す必要がなくなり、労働生産性を向上することができます。

図書館の蔵書データを、スクレイピングしよう！

ここでは、例として、図書館の蔵書データを検索し、テーブルに格納するというストアドプロシージャを書いてみましょう。

本記事で利用する環境は

  • Ubuntu (ウブントゥ) Linux (バージョン 10.04)
  • MySQL (バージョン 5)

です。

MySQL は予めインストールされているものとして話を進めます。

スクレイピングに必要なUDF (ユーザ定義関数) モジュールをインストールする

MySQLは、本来Webスクレイピングのために造られているわけではありませんので、指定されたURLにアクセスして内容を取得する関数や、HTMLを解析する関数などを標準で持ってはいません。ですが、MySQLにはUDF (user defined functions)という仕組みがあり、任意の関数をプラグインという形で、後から追加することができます。

mysql-udf-curlのインストール

mysql-udf-curlは、次のような関数をMySQLに追加するモジュールです。

curl_fetch(URL, [リクエストメソッド, [リクエスト内容] ])
URLで指定された場所にアクセスし、内容を文字列として返します。
curl_esc(文字列)
指定の文字列に含まれるURL特殊文字 ("&" など) をエスケープします。
curl_setopt(オプション名, 文字列)
リクエストにあたって、オプションを設定します。オプション名に指定できるのは USERAGENT, PROXY, PROXYUSERPWD, PROXYUSER, PROXYPASSWORD, INTERFACE, USERPWD, USERNAME, PASSWORD, HTTPAUTH です。
  1. autoconf、automake、libtool、gitコマンドとcURLライブラリをインストールします。(パッケージ名はautoconf, automake, libtool, git-core, libcurl3-dev)
  2. http://github.com/moriyoshi/mysql-udf-curl.gitよりgitコマンドでソースコードを取得します。
  3. ソースコードが格納されたディレクトリに移動します。
  4. autogen.sh を実行します。
  5. configure を実行します。必要があれば、--with-mysql オプションを与え、mysql のインストールプレフィクスを指定します。
  6. make を実行します。
  7. make install を実行します。
mysql-udf-libxml2のインストール

mysql-udf-libxml2は、次のような関数をMySQLに追加するモジュールです。

xml_parse(XMLまたはHTML, [HTMLフラグ, [エンコーディング, [オプション] ] ])
XMLまたはHTMLをパースして、その結果の要素ツリーをメモリーに格納し、結果を表すハンドルを返します。HTMLフラグが1の場合、与えられた文字列がHTML文章であるとみなします。
xml_select(ハンドル, XPath)
ハンドルで指定された要素ツリーにおいて、XPathを評価し、その結果を文字列化したものを返します。
xml_free(ハンドル)
ハンドルで指定された要素ツリーをメモリーから削除します。
  1. autoconf、automake、libtool、gitコマンドとlibxml2ライブラリをインストールします。(パッケージ名はautoconf, automake, libtool, git-core, libxml2-dev)
  2. http://github.com/moriyoshi/mysql-udf-libxml2.gitよりgitコマンドでソースコードを取得します。
  3. MySQLのインクルードファイルがあるディレクトリ (${prefix}/include/mysql) に、MySQLのソースコードから、my_tree.h と my_base.h をコピーします。
  4. ソースコードが格納されたディレクトリに移動します。
  5. autogen.sh を実行します。
  6. configure を実行します。必要があれば、--with-mysql オプションを与え、mysql のインストールプレフィクスを指定します。
  7. make を実行します。
  8. make install を実行します。

UDFを定義する

コマンドラインからmysqlコマンドを実行し、次の操作を行ないます。

CREATE FUNCTION curl_fetch RETURNS STRING SONAME 'curl.so';
CREATE FUNCTION curl_esc RETURNS STRING SONAME 'curl.so';
CREATE FUNCTION curl_setopt RETURNS INTEGER SONAME 'curl.so';
CREATE FUNCTION xml_parse RETURNS INTEGER SONAME 'xml-libxml2.so';
CREATE FUNCTION xml_select RETURNS STRING SONAME 'xml-libxml2.so';
CREATE FUNCTION xml_free RETURNS INTEGER SONAME 'xml-libxml2.so';

実際の操作は、次のような形で進行します。

mysql> CREATE FUNCTION curl_fetch RETURNS STRING SONAME 'curl.so';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION curl_esc RETURNS STRING SONAME 'curl.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION curl_setopt RETURNS INTEGER SONAME 'curl.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION xml_parse RETURNS INTEGER SONAME 'xml-libxml2.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION xml_select RETURNS STRING SONAME 'xml-libxml2.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION xml_free RETURNS INTEGER SONAME 'xml-libxml2.so';
Query OK, 0 rows affected (0.00 sec)

ストアドプロシージャを作成する

次のようなファイルを作成し、適当な場所にsutoado.sqlという名前をつけて保存します。
このファイルは、libra_searchというストアドプロシージャを作成するものです。

DELIMITER //
CREATE PROCEDURE libra_search(kw CHAR(255))
BEGIN
  DECLARE PATH CHAR(255) DEFAULT '/html/body/div[@id="container"]/div[@id="contentOut"]/div[@id="content"]/table/tr';
  DECLARE rowpath CHAR(255) DEFAULT '';
  DECLARE hdl INTEGER DEFAULT 0;
  DECLARE count INTEGER DEFAULT 0;
  DECLARE i INTEGER DEFAULT 0;
  DECLARE dummy INTEGER DEFAULT 0;
  SET dummy = curl_setopt('USERAGENT', 'Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.6) Gecko/20100628 Ubuntu/10.04 (lucid) Firefox/3.6.6');
  SET hdl=xml_parse(curl_fetch('http://www.library.okazaki.aichi.jp/tosho/asp/Book_Kensaku_g.asp', 'POST', CONCAT('hidKensakuF=1&Page=1&lang=jpn&sel1=1&key1=', curl_esc(kw), '&sel2=2&key2=&sel3=3&key3=&sel4=4&key4=&andor=0&SortKubun=1&isbn=&bookz=1&taiz=1&year-from=&year-to=&GengoKubun=no')), 1);
  SET count=xml_select(hdl, CONCAT('count(', PATH, ')'));
  SET i=2;
  WHILE i <= count DO
    SET rowpath=CONCAT(PATH, '[', i, ']');
    INSERT INTO libra (title, url, author, publisher, year, category) VALUES (
      xml_select(hdl, CONCAT(rowpath, '/td[2]')),
      xml_select(hdl, CONCAT(rowpath, '/td[2]/a/@href')),
      xml_select(hdl, CONCAT(rowpath, '/td[3]')),
      xml_select(hdl, CONCAT(rowpath, '/td[4]')),
      xml_select(hdl, CONCAT(rowpath, '/td[5]')),
      xml_select(hdl, CONCAT(rowpath, '/td[6]')));
     SET i=i+1;
  END WHILE;
  SET dummy = xml_free(hdl);
END//
DELIMITER ;

ストアドプロシージャを実行する前準備をする

次に、データベース testをなければ作成し、そこにlibraというテーブルを作成します。上記のストアドプロシージャは、libraテーブルにスクレイピング結果を格納します。

mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 0 row affected (0.01 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE `libra` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `title` varchar(255) DEFAULT NULL,
    -> `url` varchar(255) DEFAULT NULL,
    -> `author` varchar(255) DEFAULT NULL,
    -> `publisher` varchar(255) DEFAULT NULL,
    -> `year` date DEFAULT NULL,
    -> `category` varchar(255) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

ストアドプロシージャを読み込みます。

mysql> \. sutoado.sql
Query OK, 0 rows affected (0.00 sec)

ストアドプロシージャを呼び出す

ストアドプロシージャを呼び出すには、CALL 文を利用します。

CALL プロシージャ名(引数, ...)

実際に実行してみましょう。

mysql> CALL libra_search('テスト');
Query OK, 1 row affected, 7 warnings (53.49 sec)

mysql> SELECT * FROM libra\G
*************************** 1. row ***************************
       id: 1
    title:
ＢＪＴ　ＢＵＳＩＮＥＳＳ　ＪＡＰＡＮＥＳ
      url: ./syousai_g.asp?TosCode=00608049640
   author: 加藤　清方
publisher: 日本貿易振興機構
     year: 2020-06-02
 category: Y 810.7
*************************** 2. row ***************************
       id: 2
    title:
Ｏ　Ｔｅｓｔａｍｅｎｔｏ
      url: ./syousai_g.asp?TosCode=00608038477
   author: Ｅｒｉｃ　Ｖａｎ　Ｌｕｓｔｂａｄｒｅ
publisher: Ｓｅｘｔａｎｔｅ
     year: 0000-00-00
 category: Y 933.7
*************************** 3. row ***************************
       id: 3
    title:
ＩＴアーキテクトのやってはいけない　設計                    （日経ＢＰムック）
      url: ./syousai_g.asp?TosCode=00608131810
   author: 日経ＳＹＳＴＥＭＳ
publisher: 日経ＢＰ社
     year: 2020-09-12
 category:   336.5
*************************** 4. row ***************************
       id: 4
    title:
ＩＴプロジェクトを成功に導くリスク・マネ
      url: ./syousai_g.asp?TosCode=00600195089
   author: 岡村　正司
publisher: 日経ＢＰ社
     year: 2020-08-03
 category: P 007.6
*************************** 5. row ***************************
       id: 5
    title:
愛の奇蹟　（ワンダー・オブ・ラヴ）
      url: ./syousai_g.asp?TosCode=00645010388
   author: リー　ケイコ
publisher: Ｓｏｎｙ　Ｒｅｃｏｒｄｓ
     year: 0000-00-00
 category:   2A
*************************** 6. row ***************************
       id: 6
    title:
朝の通勤一時間で覚える語源別ＴＯＥＩＣテ                    （講談社＋α文庫）
      url: ./syousai_g.asp?TosCode=00161331302
   author: 小池　直己
publisher: 講談社
     year: 2020-00-01
 category: B 834
*************************** 7. row ***************************
       id: 7
    title:
朝の通勤一時間で覚えるＴＯＥＩＣテスト英                    （講談社＋α文庫）
      url: ./syousai_g.asp?TosCode=00161203395
   author: 小池　直己
publisher: 講談社
     year: 0000-00-00
 category: B 834
*************************** 8. row ***************************
       id: 8
    title:
新しい学力テストを読み解く　ＰＩＳＡ／Ｔ
      url: ./syousai_g.asp?TosCode=00600243060
   author: 田中　耕治
publisher: 日本標準
     year: 2020-08-06
 category:   375.1
*************************** 9. row ***************************
       id: 9
    title:
あなたにこの問題が解けますか！！　和算術
      url: ./syousai_g.asp?TosCode=00500233223
   author: 柳谷　晃
publisher: 冬青社
     year: 2020-05-07
 category:   419.1
*************************** 10. row ***************************
       id: 10
    title:
あなたの愛の傷（トラウマ）テスト　４つの
      url: ./syousai_g.asp?TosCode=00500491975
   author: マックス・リュッシャー
publisher: 集英社
     year: 0000-00-00
 category:   141.6
*************************** 11. row ***************************
       id: 11
    title:
あなたの寿命予測テスト　長生きのためのア
      url: ./syousai_g.asp?TosCode=00500657665
   author: 松山　淳
publisher: すばる舎
     year: 2020-06-04
 category: H 498.3
*************************** 12. row ***************************
       id: 12
    title:
アメリカン・デス・トリップ              上
      url: ./syousai_g.asp?TosCode=00161498542
   author: ジェイムズ・エルロイ
publisher: 文芸春秋
     year: 2020-01-09
 category:   933.7
*************************** 13. row ***************************
       id: 13
    title:
アメリカン・デス・トリップ              下
      url: ./syousai_g.asp?TosCode=00161498551
   author: ジェイムズ・エルロイ
publisher: 文芸春秋
     year: 2020-01-09
 category:   933.7
*************************** 14. row ***************************
       id: 14
    title:
ありがとうポール・モーリア　グレイテスト
      url: ./syousai_g.asp?TosCode=00645066258
   author: モーリア　ポール
publisher: Ｕｎｉｖｅｒｓａｌ
     year: 0000-00-00
 category:   5B
*************************** 15. row ***************************
       id: 15
    title:
あわてるにんタマ、テストでコケる！？　ら                    （こどもおはなしラン
      url: ./syousai_g.asp?TosCode=00120306899
   author: 尼子　騒兵衛
publisher: ポプラ社
     year: 0000-00-00
 category:   913
*************************** 16. row ***************************
       id: 16
    title:
囲碁急所の一着　どちらに打つのが本手です                    （マンツーマン・ブッ
      url: ./syousai_g.asp?TosCode=00500310687
   author: 小山　靖男
publisher: 山海堂
     year: 2020-05-05
 category: N 795
*************************** 17. row ***************************
       id: 17
    title:
いちばんやさしいソフトウェアテストの本                      （技評ＳＥ新書）
      url: ./syousai_g.asp?TosCode=00600377742
   author: 石原　一宏
publisher: 技術評論社
     year: 2020-09-02
 category: I 007.6
*************************** 18. row ***************************
       id: 18
    title:
１級販売士キーワード解説１３８　資格取得
      url: ./syousai_g.asp?TosCode=00319474002
   author: 酒巻　貞夫
publisher: 同友館
     year: 2020-03-06
 category:   673.3
*************************** 19. row ***************************
       id: 19
    title:
井伏鱒二全集                            第６巻
      url: ./syousai_g.asp?TosCode=00160652938
   author: 井伏　鱒二
publisher: 筑摩書房
     year: 0000-00-00
 category:   918.6
*************************** 20. row ***************************
       id: 20
    title:
いま“人気の”＆“必要な”商品・サービス２００４
      url: ./syousai_g.asp?TosCode=00339986264
   author: &#160;
publisher: 日本消費者協会
     year: 2020-04-06
 category:   545.8
20 rows in set (0.00 sec)

おお、ちゃんとスクレイピングできましたね！

まとめ

ストアドプロシージャはもともと強力な機能ですが、そこにUDFを組み合わせることで、さらに可能性が広がるということがわかりました。

みなさんも、ストアドプロシージャを使って、いろいろなサイトをクロールしてみたりしてくださいね！
便利なUDFを作ったら、ぜひ教えてください！

ではまた！