楽天公開レシピデータセットのデータベース変換ツール

概要

  • 楽天株式会社が公開しているレシピデータセットをデータベース形式に変換するためのWindows用ツールです．
  • SQLiteを対象としていますが，本ツールで作成される中間ファイル（データベースへのインポート用テキストファイル）の形式をテキストエディタで微修正すれば，MySQL等その他のデータベースにも対応可能です．
  • こちらで公開されているものとは別物です．

ダウンロード

構成物

  • data_converter.php： 楽天公開レシピデータセットを元に，SQLiteデータベースへのインポート用データファイル群を生成するプログラム
  • create_database.sql： SQLiteデータベースの作成コマンド
  • auto.bat： data_converter.phpを実行した後にSQLiteへのデータインポートを実行するバッチファイル
  • README.html： 本ファイル

作成されるデータベースの構成

momentsテーブル

  • どんな時用ID： id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  • どんな時用： moment TEXT NOT NULL

costsテーブル

  • 費用の目安ID： id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  • 費用の目安： cost TEXT NOT NULL

timesテーブル

  • 調理時間の目安ID： id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  • 調理時間の目安： time TEXT NOT NULL

tsukureposテーブル

  • つくレポID： id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  • レシピID： recipe_id INTEGER DEFAULT NULL
  • ユーザID： user_id INTEGER DEFAULT NULL
  • おすすめコメント： report TEXT NOT NULL
  • オーナーコメント： reply TEXT NOT NULL
  • 投稿日： report_date DATE DEFAULT NULL

materialsテーブル

  • 材料ID： id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  • タイトル： title TEXT NOT NULL

recipesテーブル

  • レシピID：id INTEGER PRIMARY KEY NOT NULL
  • ユーザID： user_id INTEGER DEFAULT NULL
  • 大カテゴリ： category1 INTEGER DEFAULT NULL
  • 中カテゴリ： category2 INTEGER DEFAULT NULL
  • 小カテゴリ： category3 INTEGER DEFAULT NULL
  • 表示用タイトル： display_title TEXT NOT NULL
  • きっかけ： motivation TEXT DEFAULT NULL
  • 紹介文： comment TEXT DEFAULT NULL
  • 料理画像ファイル名： image_path TEXT DEFAULT NULL
  • 料理名： title TEXT NOT NULL
  • タグ： tag1 ～ tag4 INTEGER DEFAULT NULL
  • ワンポイント情報： know_how TEXT DEFAULT NULL
  • 調理時間の目安ID： time_id INTEGER DEFAULT NULL
  • どんな時用ID： moment_id TEXT DEFAULT NULL
  • 費用の目安ID： cost_id TEXT DEFAULT NULL
  • 何人分： serves TEXT DEFAULT NULL
  • レシピ公開日： report_date DATE DEFAULT NULL
  • 材料1ID： material1_id INTEGER DEFAULT NULL
  • 分量1： volume1 TEXT DEFAULT NULL
  • ・・・（略）・・・
  • 材料36ID： material36_id INTEGER DEFAULT NULL
  • 分量36： volume36 TEXT DEFAULT NULL
  • 手順1： step1 TEXT DEFAULT NULL
  • ・・・（略）・・・
  • 手順50： step50 TEXT DEFAULT NULL

動作環境

Windows 7以降

  • 32bit/64bitどちらでも動作するはずです．
  • Linux/Mac OSでもdata_converter.php自体はLinux環境でも動作するはずです（auto.batをBash等に書き換えましょう）．

PHP 5.5.15

  • 試していませんが5.4でも動作すると思います．
  • 32bit/64bitどちらでも動作するはずです．
  • 最新版はこちらから： http://windows.php.net/download/
  • （2014年8月9日現在： 『VC11 x86 Thread Safe (2014-Jul-24 01:03:48)』→『Zip』[18.58MB]）

SQLite 3.8.5

  • 極力同じバージョンをご使用下さい．
  • 最新版はこちらから： http://www.sqlite.org/download.html
  • （2014年8月9日現在： 『Precompiled Binaries for Windows』→『sqlite-shell-win32-x86-3080500.zip』(294.87 KiB)

楽天レシピデータ 2012年7月5日版

  • それ以降でも動作するかもしれませんが，データ構造が変わると動作しなくなる可能性があります．
  • 参考： http://rit.rakuten.co.jp/opendataj.html

データベース作成方法

  1. PHPおよびSQLite3へのパスを通して下さい（バッチファイル内の当該箇所にフルパスを記述しても可）．
  2. recipe_xxxx_yyyymmdd.txtファイルがある場所に本ツールの全ての構成物を設置し，auto.batを実行して下さい．
  3. rakuten_recipe.db（約665MB）が作成されていれば成功です．
  • テスト環境（Windows 7/8.1，Core i7-3770K @3.5GHz，32GB，SSD）では2分強で実行完了します．
  • 実行後にsortedフォルダおよびdatフォルダが自動生成されます．
  • sortedフォルダには，recipe_xxxx_yyyymmdd.txtファイル内の各行に対して1列目（id）で順序保存ソートしたものが出力されます．
  • datフォルダには，SQLiteデータベースへのインポート用データファイルが入っています．
  • 必要に応じて参照または削除して下さい．
  • データベースへの変換の際に「"」を「\"」に置換するようにしています．

チュートリアル

データの検索

  • データベースから特定のレコード群を抽出するにはWHERE句を使用したSQL文を発行します．
  • 例えば，料理名に「ハンバーグ」を含むレシピデータを抽出するには下記を実行します． 
    
sqlite> SELECT * FROM recipes WHERE title LIKE '%ハンバーグ%';

データの件数計算

  • 料理名に「ハンバーグ」を含むレシピデータの件数のみを調べたい場合は下記を実行します． 
    
sqlite> SELECT count(*) FROM recipes WHERE title LIKE '%ハンバーグ%';

複数テーブルからのデータ検索（その１）

  • データベース内の複数のテーブルを結合して特定カラムのみのレコード群を抽出する場合はINNER JOIN句またはLEFT OUTER JOIN句を使用したSQL文を発行します．
  • 例えば，recipesとtsukureposの両テーブルを結合して，レシピID，表示用タイトル，つくレポおすすめコメント，つくレポオーナーコメントをタブ区切りで抽出するには下記を実行します．
    • 
sqlite> .separator \t
sqlite> SELECT recipes.id, recipes.display_title, tsukurepos.report, tsukurepos.reply FROM recipes INNER JOIN tsukurepos ON recipes.id = tsukurepos.recipe_id;
  • ※ つくレポが存在するレシピのみ抽出されます．
  • ※ 全てのレシピを抽出したい場合は，INNER JOINをLEFT OUTER JOINに変えてください．

複数テーブルからのデータ検索（その２）

  • recipesとmaterialsの両テーブルを結合して，レシピID，表示用タイトル，料理名，材料1～材料36（IDではなくタイトル），分量1～分量36，手順1～手順50をタブ区切りで抽出するには下記を実行します． 
    
sqlite> .separator \t
sqlite> SELECT recipes.id, recipes.display_title, recipes.title, m1.title, recipes.volume1, m2.title, recipes.volume2, m3.title, recipes.volume3, m4.title, recipes.volume4, m5.title, recipes.volume5, m6.title, recipes.volume6, m7.title, recipes.volume7, m8.title, recipes.volume8, m9.title, recipes.volume9, m10.title, recipes.volume10, m11.title, recipes.volume11, m12.title, recipes.volume12, m13.title, recipes.volume13, m14.title, recipes.volume14, m15.title, recipes.volume15, m16.title, recipes.volume16, m17.title, recipes.volume17, m18.title, recipes.volume18, m19.title, recipes.volume19, m20.title, recipes.volume20, m21.title, recipes.volume21, m22.title, recipes.volume22, m23.title, recipes.volume23, m24.title, recipes.volume24, m25.title, recipes.volume25, m26.title, recipes.volume26, m27.title, recipes.volume27, m28.title, recipes.volume28, m29.title, recipes.volume29, m30.title, recipes.volume30, m31.title, recipes.volume31, m32.title, recipes.volume32, m33.title, recipes.volume33, m34.title, recipes.volume34, m35.title, recipes.volume35, m36.title, recipes.volume36 step1, step2, step3, step4, step5, step6, step7, step8, step9, step10, step11, step12, step13, step14, step15, step16, step17, step18, step19, step20, step21, step22, step23, step24, step25, step26, step27, step28, step29, step30, step31, step32, step33, step34, step35, step36, step37, step38, step39, step40, step41, step42, step43, step44, step45, step46, step47, step48, step49, step50 FROM recipes LEFT OUTER JOIN materials m1 ON recipes.material1_id = m1.id LEFT OUTER JOIN materials m2 ON recipes.material2_id = m2.id LEFT OUTER JOIN materials m3 ON recipes.material3_id = m3.id LEFT OUTER JOIN materials m4 ON recipes.material4_id = m4.id LEFT OUTER JOIN materials m5 ON recipes.material5_id = m5.id LEFT OUTER JOIN materials m6 ON recipes.material6_id = m6.id LEFT OUTER JOIN materials m7 ON recipes.material7_id = m7.id LEFT OUTER JOIN materials m8 ON recipes.material8_id = m8.id LEFT OUTER JOIN materials m9 ON recipes.material9_id = m9.id LEFT OUTER JOIN materials m10 ON recipes.material10_id = m10.id LEFT OUTER JOIN materials m11 ON recipes.material11_id = m11.id LEFT OUTER JOIN materials m12 ON recipes.material12_id = m12.id LEFT OUTER JOIN materials m13 ON recipes.material13_id = m13.id LEFT OUTER JOIN materials m14 ON recipes.material14_id = m14.id LEFT OUTER JOIN materials m15 ON recipes.material15_id = m15.id LEFT OUTER JOIN materials m16 ON recipes.material16_id = m16.id LEFT OUTER JOIN materials m17 ON recipes.material17_id = m17.id LEFT OUTER JOIN materials m18 ON recipes.material18_id = m18.id LEFT OUTER JOIN materials m19 ON recipes.material19_id = m19.id LEFT OUTER JOIN materials m20 ON recipes.material20_id = m20.id LEFT OUTER JOIN materials m21 ON recipes.material21_id = m21.id LEFT OUTER JOIN materials m22 ON recipes.material22_id = m22.id LEFT OUTER JOIN materials m23 ON recipes.material23_id = m23.id LEFT OUTER JOIN materials m24 ON recipes.material24_id = m24.id LEFT OUTER JOIN materials m25 ON recipes.material25_id = m25.id LEFT OUTER JOIN materials m26 ON recipes.material26_id = m26.id LEFT OUTER JOIN materials m27 ON recipes.material27_id = m27.id LEFT OUTER JOIN materials m28 ON recipes.material28_id = m28.id LEFT OUTER JOIN materials m29 ON recipes.material29_id = m29.id LEFT OUTER JOIN materials m30 ON recipes.material30_id = m30.id LEFT OUTER JOIN materials m31 ON recipes.material31_id = m31.id LEFT OUTER JOIN materials m32 ON recipes.material32_id = m32.id LEFT OUTER JOIN materials m33 ON recipes.material33_id = m33.id LEFT OUTER JOIN materials m34 ON recipes.material34_id = m34.id LEFT OUTER JOIN materials m35 ON recipes.material35_id = m35.id LEFT OUTER JOIN materials m36 ON recipes.material36_id = m36.id;
  • ※ material1_id～material36_idまであるため長いSQL文になり，処理コストもそれなりです．
  • ※ メモリ使用量にご注意下さい．

検索結果のファイル出力

  • 出力結果をファイル（例：output.txt）に保存する場合は，SQL文を記述したテキストファイル（例：sample.sql）を作成して，下記を実行します． 
    
sqlite3 rakuten_recipe.db < sample.sql > output.txt

更新履歴

  • 2014/08/14： Ver. 1.2
    • データインポート後に空文字をNULL値に変換するようにした
    • データベースのスキーマを変更
  • 2014/08/11： Ver. 1.1
    • 一部のレシピで材料または素材が抜けていたのを修正
  • 2014/08/09： Ver. 1.0
    • 公開

その他

  • 個人利用の範囲で本ツールを改変しても構いませんが，二次配布は禁じます．
  • 本ツールの利用により発生したいかなる損害に対しても，作者は一切の責任を負いません．
  • 質問等は下記までお願いします．
    • 中京大学 道満 恵介
    • kdoman[at]sist.chukyo-u.ac.jp

ページのトップへ戻る