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

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

##目次

• 参考記事
• 環境
• wikipadia -> MySQL
  • wikipediaのページ情報、カテゴリ間リンク情報、ページ間リンク情報のdump取得
  • 解凍
  • DB作成
  • レコード挿入
• MySQL -> Big Query
  • csv 吐き出し
  • 圧縮
  • Big Query Load
• まとめ

##参考記事
Wikipediaの大量データをMySQLに保存する

##環境
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触ってみた"について のお話です。
お楽しみに！