Wikipediaデータ MySQLを経由してBigQueryにLoadする。シェルスクリプトバージョン

概要

以前、GMOアドマーケティング Advent Calendar 2019の11日目に記載したWikipediaデータ MySQLを経由してBigQueryにLoadするのシェルスクリプト化です。DB接続情報とGCPのプロジェクト用の変数に設定を入れると動作します。

参考記事

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

環境

MySQL、GCPプロジェクト、Google Cloud SDK環境が事前に準備されていることとしております。

#!/usr/bin/env bash

#GCPプロジェクト
PROJECT=''

#DB接続情報
USER=''
PASS=''
HOST=''
PORT=''
DATABASE='wikipedia'

MYSQL="mysql -u ${USER} -p${PASS} -h ${HOST} --port ${PORT}"

if [ -z "$USER" ]; then
  echo 'Please set user information'
  exit 0
elif [ -z "$PASS" ]; then
  echo 'Please set pass information'
  exit 0
elif [ -z "$HOST" ]; then
  echo 'Please set host information'
  exit 0
elif [ -z "$PORT" ]; then
  echo 'Please set port information'
  exit 0
elif [ -z "$PROJECT" ]; then
  echo 'Please set project information'
  exit 0
fi

cd /tmp/

#wikipediaのdump取得
#ページ情報
echo 'download jawiki-latest-page.sql.gz'
wget https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-page.sql.gz
#カテゴリ間リンク情報
echo 'download jawiki-latest-categorylinks.sql.gz'
wget https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-categorylinks.sql.gz
#ページ間リンク情報
echo 'download jawiki-latest-pagelinks.sql.gz'
wget https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pagelinks.sql.gz

#解凍
echo 'unzip jawiki-latest-pagelinks.sql.gz'
gzip -d /tmp/jawiki-latest-page.sql.gz
echo 'unzip jawiki-latest-categorylinks.sql.gz'
gzip -d /tmp/jawiki-latest-categorylinks.sql.gz
echo 'unzip jawiki-latest-pagelinks.sql.gz'
gzip -d /tmp/jawiki-latest-pagelinks.sql.gz

#DB作成
echo 'CREATE DATABASE'
echo "CREATE DATABASE IF NOT EXISTS ${DATABASE}" | $MYSQL

#テーブル削除
echo 'DROP TABLE page'
echo "DROP TABLE IF EXISTS ${DATABASE}.page" | $MYSQL
echo 'DROP TABLE categorylinks'
echo "DROP TABLE IF EXISTS ${DATABASE}.categorylinks" | $MYSQL
echo 'DROP TABLE pagelinks'
echo "DROP TABLE IF EXISTS ${DATABASE}.pagelinks" | $MYSQL

#レコード挿入
echo 'import jawiki-latest-page.sql'
$MYSQL $DATABASE < /tmp/jawiki-latest-page.sql
echo 'import jawiki-latest-categorylinks.sql'
$MYSQL $DATABASE < /tmp/jawiki-latest-categorylinks.sql
echo 'import jawiki-latest-pagelinks.sql'
$MYSQL $DATABASE < /tmp/jawiki-latest-pagelinks.sql

#csv 吐き出し
echo 'export jawiki_page.csv'
$MYSQL -e "SELECT * FROM ${DATABASE}.page;" | perl -pe 's;,;、;g' | perl -pe 's;\t;,;g' > /tmp/jawiki_page.csv
echo 'export jawiki_categorylinks.csv'
$MYSQL -e "SELECT * FROM ${DATABASE}.categorylinks;" | perl -pe 's;,;、;g' | perl -pe 's;\t;,;g' > /tmp/jawiki_categorylinks.csv
echo 'export jawiki_pagelinks.csv'
$MYSQL -e "SELECT * FROM ${DATABASE}.pagelinks;" | perl -pe 's;,;、;g' | perl -pe 's;\t;,;g' > /tmp/jawiki_pagelinks.csv

#圧縮
echo 'zip jawiki_page.csv'
gzip /tmp/jawiki_page.csv
echo 'zip jawiki_categorylinks.csv'
gzip /tmp/jawiki_categorylinks.csv
echo 'zip jawiki_pagelinks.csv'
gzip /tmp/jawiki_pagelinks.csv

#Big Query Load
echo 'bq load jawiki_page'
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

echo 'bq load categorylinks'
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

echo 'bq load pagelinks'
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