WikipediaデータをINNER JOIN（内部結合）でまとめる

未分類 > WikipediaデータをINNER JOIN（内部結合）でまとめる

前回に引き続きウィキペディアデータの中からテーブルの内部結合によりpage_id、page_title、old_textの３カラムにまとめる方法について解説します。

既に前回の解説どおりにwikidbデータベースを作成し、page、revision、textテーブル内にウィキペディアデータがインポートされている前提で解説していきます。

source dict_tables.sql;

mysqlコマンドラインからdict_tables.sqlを読み込ませ内部結合用のテーブルを定義します。
dict_tables.sqlファイルはpage_id、page_title、old_textの３カラムに特化したテーブルを定義するファイルです。

INSERT INTO dict (page_id, page_title, old_text)
SELECT page_id, page_title, old_text FROM page
INNER JOIN text ON page_latest = old_id AND page_namespace = 0;

テーブル内部結合（INNER JOIN）でpageテーブルのpage_idカラムとpage_titleカラム、textテーブルのold_textカラムのデータのみを抽出し新たなテーブル(dict)上で結合させます。

具体的にはタイトルと文章とを紐づけているpage_latestとold_idを元に結合させ、page_namespaceの値が0のもの、つまりアップロード情報、コメント依頼、削除依頼・・・等の管理ページ情報を省いた、純粋なページ情報のみを対象に、INSERT INTOで新たなdictテーブルの同一レコード上に並べて配置させます。

ページタイトルとテキストを同一レコード上に配置させることでし整合性を保ったまま編集が行えるようにします。

新たなdictテーブル上で結合させる理由は万が一、編集に失敗した場合でも元のテーブルを残しておくことで何度でもやり直しができるようにするためです。

上記のようになれば完了です。やはり処理にはそれなりに時間を要します。

SELECT page_title FROM dict LIMIT 10;

試しにpage_titleカラムのデータを１０件表示させデータが取得できることを確認しましょう。

編集が終了したらdictテーブルサイズを参照しレコード長の平均値を変更します。

下記のスクリプトを貼り付けENTERキーを押してください。

select
    table_name,
    engine,
    table_rows,
    avg_row_length,
    floor((data_length+index_length)) as all_Byte,  #総容量
    floor((data_length)) as data_Byte,  #データ容量
    floor((index_length)) as index_Byte   #インデックス容量
from
    information_schema.tables
where
    table_schema=database()
order by
    (data_length+index_length) desc;

dictテーブルのavg_row_lengthを参照してレコード長の平均値を変更します。

ALTER TABLE dict MAX_ROWS=10000000, AVG_ROW_LENGTH=14474;

avg_row_lengthが14474の場合。

mysqldump -uroot -p -d wikidb dict &gt; dict_tables.sql

シェルコマンドラインに戻りmysqldumpコマンドを使い本番サーバー用のテーブル定義ファイルを出力し、既存のdict_tables.sqlファイルを上書きします。

最後にデータ本体を分割ダンプで出力します。

ダンプ用のBash スクリプトはhttps://www.mk-mode.com/octopress/2015/10/26/mariadb-dump-only-data-by-split/で公開されているスクリプトを参考にさせて頂き、全テーブルではなく、特定のテーブルだけを分割ダンプするよう変更させて頂いたものです。

db_table_data_only_dump_split.shファイルを開き、mysqlのユーザ名とパスワードを入力、mysqldumpのパスは環境に応じて書き換えてください。

chmod +x db_table_data_only_dump_split.sh
./db_table_data_only_dump_split.sh

分割ダンプを実行
シェルコマンドラインから実行します。
「using a password on the command line・・・」の警告が出ますが処理は続行されます。
初期設定で16,000レコードづつダンプされていき、編集内容にもよりますが、おおよそ、トータルで22個くらいのファイル数になるかと思います。

ダンプレコード数はdb_table_data_only_dump_split.shファイル内の設定により変更が可能です。

mysql本番サーバーの受け皿にもよりますが、ファイル容量平均40MBくらいに落ち着かせたほうが、比較的、コンスタントにインポートしていけるかと思います。

とうぜんながらダンプ処理にはかなりの時間を要します。

ダンプが終了したらウィキペディアデータと連携させるワードプレスデータが入っているデータベースにテーブル定義ファイルをインポートした後、ダンプファイルをインポートしていきます。
（数が数だけに一度にすべてをインポートする必要はありません）

インポートが終わりましたらdictテーブルを、連携させるワードプレスの接頭辞を付けたテーブル名に変更してください。

接頭辞はワードプレスインストールルートのwp-config.phpファイル内で確認できます。

接頭辞がwp177_の場合はテーブル名をwp177_dictに変更します。

最後にワードプレスインストールルートのwp-includesフォルダ内のwp-db.phpファイルの268行目付近の$tables変数に代入される配列内の最後に’dict’をカンマ区切りで追加してください。
接頭辞を付ける必要はありません。

これでウィキペディアデータとワードプレスとの連携が可能となります。

次回は「ワードプレス側からウィキペディアデータへクエリを送りデータを取得して表示させる」について解説します。

WEBRYDAYS