wikipedia ダンプデータをデータベースにインポートする手順

Nid: 1175

1 . データダウンロード

URLのリスト

$ lynx -listonly -dump "https://dumps.wikimedia.org/jawiki/latest/" | grep 'gz$\|z$\|bz2$' | grep -v history | cut -c 7- | tee jawiki.download.lst
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-abstract.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-abstract1.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-abstract2.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-abstract3.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-abstract4.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-abstract5.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-abstract6.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-all-titles-in-ns0.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-all-titles.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-category.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-categorylinks.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-change_tag.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-externallinks.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-geo_tags.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-image.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-imagelinks.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-iwlinks.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-langlinks.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-page.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-page_props.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-page_restrictions.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pagelinks.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles-multistream-index.txt.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles-multistream.xml.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles.xml.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles1.xml-p1p106175.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles2.xml-p106179p350785.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles3.xml-p350788p803163.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles4.xml-p803168p1541169.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles5.xml-p1541175p2534192.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles6.xml-p2534193p3869446.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles6.xml-p2534193p3873913.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-logging.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-logging1.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-logging2.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-logging3.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-logging4.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-logging5.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-logging6.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current.xml.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current1.xml-p1p106178.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current2.xml-p106179p350785.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current3.xml-p350788p803167.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current4.xml-p803168p1541170.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current5.xml-p1541171p2534192.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current6.xml-p2534193p3869446.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-meta-current6.xml-p2534193p3873913.bz2
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-protected_titles.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-redirect.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-site_stats.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-siteinfo-namespaces.json.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-sites.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-articles.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-articles1.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-articles2.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-articles3.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-articles4.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-articles5.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-articles6.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-meta-current.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-meta-current1.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-meta-current2.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-meta-current3.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-meta-current4.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-meta-current5.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-stub-meta-current6.xml.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-templatelinks.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-user_groups.sql.gz
https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-wbc_entity_usage.sql.gz

本文とカテゴリをダウンロード

$ wget -c https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles.xml.bz2 https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-category.sql.gz https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-categorylinks.sql.gz

2 . テーブルスキーマ

tables.sql

$ wget -c https://releases.wikimedia.org/mediawiki/1.30/mediawiki-1.30.1.tar.gz
$ tar tzvf mediawiki-1.30.1.tar.gz  | grep tables.sql
-rw-rw-r-- reedy/reedy    2123 2018-06-05 20:42 mediawiki-1.30.1/maintenance/archives/patch-linktables.sql
-rw-rw-r-- reedy/reedy   51060 2018-09-20 18:41 mediawiki-1.30.1/maintenance/mssql/tables.sql
-rw-rw-r-- reedy/reedy   50391 2018-09-20 18:41 mediawiki-1.30.1/maintenance/oracle/tables.sql
-rw-rw-r-- reedy/reedy   33929 2018-09-20 18:41 mediawiki-1.30.1/maintenance/postgres/tables.sql
-rw-rw-r-- reedy/reedy   69248 2018-09-20 18:41 mediawiki-1.30.1/maintenance/tables.sql
$ gunzip -c mediawiki-1.30.1.tar.gz | tar xvf - mediawiki-1.30.1/maintenance/tables.sql
mediawiki-1.30.1/maintenance/tables.sql

3 . MWDumper

MWDumperは、早いが内部リンクテーブルを更新しない。コンテンツだけが必要ならば、これで十分。

$ git clone https://phabricator.wikimedia.org/diffusion/MWDU/mwdumper.git
$ cd mwdumper
$ sudo apt install maven
$ mvn compile
$ mvn package
$ file target/mwdumper-1.25.jar
target/mwdumper-1.25.jar: Java archive data (JAR)
$ cd ..

4 . jarファイル

$ wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.47.tar.gz
$ tar tvzf mysql-connector-java-5.1.47.tar.gz | grep 'bin.jar$'
-rw-r--r-- 0/0         1007505 2018-08-07 06:59 mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar
$ gunzip -c mysql-connector-java-5.1.47.tar.gz | tar xvf - mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar
mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar

5 . データベース設定確認と変更

$ sudo mysql -u root -e "show variables like 'chara%';"
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
$ sudo mysql -u root -e "show global variables like 'innodb_lar%';"
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
$ sudo mysql -u root -e "show global variables like 'innodb_file_format';"
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
$ sudo mysql -u root -e "show global variables like 'innodb_default_row_format';"
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | compact |
+---------------------------+---------+
$ sudo mysql -u root -e "set global innodb_default_row_format = DYNAMIC;"
$ sudo mysql -u root -e "show global variables like 'innodb_default_row_format';"
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+

6 . 準備したファイル

$ ls --size -S1 jawiki-latest-pages-articles.xml.bz2 jawiki-latest-category.sql.gz jawiki-latest-categorylinks.sql.gz mediawiki-1.30.1/maintenance/tables.sql mwdumper/target/mwdumper-1.25.jar mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar
2.6G jawiki-latest-pages-articles.xml.bz2
182M jawiki-latest-categorylinks.sql.gz
6.1M mwdumper/target/mwdumper-1.25.jar
2.9M jawiki-latest-category.sql.gz
984K mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar
 68K mediawiki-1.30.1/maintenance/tables.sql

7 . データベース作成とインポート

$ sudo mysql -u root -e "CREATE DATABASE jawiki;"

テーブル作成

$ sudo mysql -u root jawiki < mediawiki-1.30.1/maintenance/tables.sql

本文データインポート 10時間ぐらい。

$ java -jar mwdumper/target/mwdumper-1.25.jar --format=sql:1.25 ~/downloads/jawiki-latest-pages-articles.xml.bz2 | sudo mysql jawiki
...
2,324,000 pages (59.933/sec), 2,324,000 revs (59.933/sec)
2,325,000 pages (59.925/sec), 2,325,000 revs (59.925/sec)
2,326,000 pages (59.924/sec), 2,326,000 revs (59.924/sec)
2,327,000 pages (59.923/sec), 2,327,000 revs (59.923/sec)
2,327,898 pages (59.907/sec), 2,327,898 revs (59.907/sec)

カテゴリ。categorylinksは数時間。

$ gunzip -c jawiki-latest-category.sql.gz | sudo mysql -u root jawiki --
$ gunzip -c jawiki-latest-categorylinks.sql.gz | sudo mysql -u root jawiki --

8 . データ確認

MariaDB [jawiki]> select table_name, table_rows from information_schema.TABLES where table_schema = 'jawiki' order by table_rows desc limit 10;
+---------------+------------+
| table_name    | table_rows |
+---------------+------------+
| categorylinks |    7514235 |
| text          |    2686111 |
| page          |    2302155 |
| revision      |    2092671 |
| redirect      |     690266 |
| category      |     233091 |
| valid_tag     |          0 |
| log_search    |          0 |
| ip_changes    |          0 |
| uploadstash   |          0 |
+---------------+------------+
10 rows in set (0.00 sec)
MariaDB [jawiki]> 
SELECT
    p.page_id AS "page_id",
    CAST(p.page_title AS CHAR(10000) CHARACTER SET utf8) AS "page_title",
    r.rev_text_id AS "revision_id",
    t.old_id AS "text_id",
    left(t.old_text,50)
FROM
    page p
        INNER JOIN revision r
            ON p.page_latest = r.rev_id
        INNER JOIN text t
            ON r.rev_text_id = t.old_id
limit 5 offset 2000;
+---------+-----------------------------+-------------+----------+----------------------------------------------------+
| page_id | page_title                  | revision_id | text_id  | left(t.old_text,50)                                |
+---------+-----------------------------+-------------+----------+----------------------------------------------------+
|    2610 | 2010年代                    |    70706915 | 70706915 | {{Otheruses||日本ローカルの事柄|2010年▒             |
|    2612 | 定型詩                      |    59312594 | 59312594 | '''定型詩'''(ていけいし、{{lang-en|ryth           |
|    2614 | 解決済みのバグ報告          |    18446452 | 18446452 | {{Archives|Wikipedia:バグの報告}}
ここに▒           |
|    2616 | 田沢湖                      |    70736019 | 70736019 | {{Infobox 湖
|名称 = 田沢湖
|画像 =[[File:         |
|    2617 | 活火山                      |    70040564 | 70040564 | '''活火山'''(かつかざん、かっかざ▒                 |
+---------+-----------------------------+-------------+----------+----------------------------------------------------+
5 rows in set (0.00 sec)

カテゴリのリストアップ、記事タイトルとその属するカテゴリ。

9 . DBサイズ

DB最適化後

$ sudo ncdu /var/lib/mysql
   16.6 GiB [##########] /jawiki

DB最適化前

   19.8 GiB [##########] /jawiki

10 . バックアップとリストア

$ sudo mysqldump jawiki > dump_jawiki.sql
$ sudo mysql jawiki < dump_jawiki.sql