Wikipediaデータ MySQLを経由してBigQueryにLoadする #MySQL

概要

Big Queryで分析する際にWikipediaのデータを使いたかったのでMySQLを経由してBigQueryにLoadしてみました。

参考記事

環境

MySQL 5.7.19
macOS Sierra バージョン 10.12.6

wikipadia -> MySQL

wikipediaのdump取得

#ページ情報
wget https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-page.sql.gz
#カテゴリ間リンク情報
wget https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-categorylinks.sql.gz
#ページ間リンク情報
wget https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pagelinks.sql.gz

解凍

gzip -d jawiki-latest-page.sql.gz
gzip -d jawiki-latest-categorylinks.sql.gz
gzip -d jawiki-latest-pagelinks.sql.gz

DB作成

mysql> create database jawikipedia;

レコード挿入

mysql -u root jawikipedia -p < jawiki-latest-page.sql
mysql -u root jawikipedia -p < jawiki-latest-categorylinks.sql
mysql -u root jawikipedia -p < jawiki-latest-pagelinks.sql

MySQL -> Big Query

csv 吐き出し

mysql -u root jawikipedia -p -e "SELECT * FROM jawikipedia.page;" | perl -pe 's;,;、;g' | perl -pe 's;\t;,;g' > /tmp/jawiki_page.csv

mysql -u root jawikipedia -p -e "SELECT * FROM jawikipedia.categorylinks;" | perl -pe 's;,;、;g' | perl -pe 's;\t;,;g' > /tmp/jawiki-categorylinks.csv

mysql -u root jawikipedia -p -e "SELECT * FROM jawikipedia.pagelinks;" | perl -pe 's;,;、;g' | perl -pe 's;\t;,;g' > /tmp/jawiki-pagelinks.csv

圧縮

gzip ~/jawiki_page.csv
gzip ~/jawiki_categorylinks.csv
gzip ~/jawiki_pagelinks.csv

Big Query Load

bq load \
  --null_marker="" \
  --allow_quoted_newlines \
  --allow_jagged_rows \
  --source_format CSV \
  --quote "" \
  --max_bad_records 10000 \
  --replace \
  $PROJECT:$DATABASE.page \
  /tmp/jawiki_page.csv.gz \  page_id,page_namespace:integer,page_title,page_restrictions,page_is_redirect:integer,page_is_new:integer,page_random,page_touched,page_links_updated,page_latest:integer,page_len:integer,page_content_model,page_lang

bq load \
  --null_marker="" \
  --allow_quoted_newlines \
  --allow_jagged_rows \
  --source_format CSV \
  --max_bad_records 10000 \
  --replace \
  $PROJECT:$DATABASE.categorylinks \
  /tmp/jawiki_categorylinks.csv.gz \
  cl_from,cl_to,cl_sortkey,cl_timestamp,cl_sortkey_prefix,cl_collation,cl_type

bq load \
  --null_marker="" \
  --allow_quoted_newlines \
  --allow_jagged_rows \
  --source_format CSV \
  --max_bad_records 1000000 \
  --replace \
  $PROJECT:$DATABASE.pagelinks \
  /tmp/jawiki_pagelinks.csv.gz \
  pl_from,pl_namespace:integer,pl_title,pl_from_namespace:integer

まとめ

Wikipediaのデータを使ってBig Queryで分析するには非常にありがたいデータです。
定期的にWikipediaのデータが更新されるのでBig Queryのデータも自動で更新できるように
スクリプト化したらQiitaの記事を更新したいと思います。

明日は@KazuakiMさんの"Microsoft Edge 80触ってみた"についてのお話です。
お楽しみに！

Wikipediaデータ MySQLを経由してBigQueryにLoadする

概要

目次