手作業でいいからなるべく簡単にMySQLからBigQueryにデータを取り込みたい

• ちょっとBigQuery試してみたい
• データはMySQLにある
• 自動化とかは後々
• BigQueryは分割テーブルがいい
• ひとまず過去数日分のデータを登録してみたい

データセットを作る

• コンソールから、ぽちぽち作る
  • ここでは test_load_dataset を作成したとして以下へ

テーブルを作成する

• ローカルにスキーマ定義JSONを作る
  • 例として table_action_logs.json の定義は以下

[
  {
    "name": "ip", 
    "type": "STRING"
  }, 
  {
    "name": "path", 
    "type": "STRING"
  }, 
  {
    "name": "account_type", 
    "type": "STRING"
  }, 
  {
    "name": "account_id", 
    "type": "STRING"
  }, 
  {
    "name": "action", 
    "type": "STRING"
  }, 
  {
    "name": "detail", 
    "type": "STRING"
  },
  {
    "name": "created", 
    "type": "DATETIME"
  }
]

• 参考
  • https://cloud.google.com/bigquery/docs/schemas?hl=ja
• CloudShell を起動してドラッグ&ドロップで転送する
  • 以下の赤枠のボタンで画面下に起動するので、そこにドロップすると転送できる
• 以下のコマンドを CloudShell で実行

bq mk --time_partitioning_type=DAY test_load_dataset.action_logs table_action_logs.json

bq mk --time_partitioning_type=DAY [データセット名].[作成したいテーブル名] [スキーマ定義JSON]

• ※ちなみに作成に成功してもブラウザを読み込み直さないと表示されなかった

データをCSVで用意する

• phpMyAdminで、定義と同じ形になるようにクエリを投げて、CSVでエクスポートする
  • もちろんCSVができればいいだけなので、phpMyAdminを使わないといけないわけではないです
  • ※文字コードは UTF-8
  • ※BigQuery側は日付分割のテーブルなので、1日分の指定をWHERE句に入れる
  • 例として、以下は action_logs テーブルから 2019/3/27 分のデータだけを取得したクエリ

SELECT
  ip, path, account_type, account_id, action, detail, created
FROM
  `action_logs`
WHERE
  created >= '2019-03-27 00:00:00'
AND 
  created < '2019-03-28 00:00:00'

• クエリ実行した後、画面の下の方にある エクスポート をクリック
• フォーマットを CSV にすれば、あとはデフォルトのままで大丈夫そうです
• 参考
  • https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv?hl=ja
• 出力したCSVを Google Cloud Storage にアップロードする
  • ここでは例として、 bigquery_load_data_koreeda というバケットがあり、 test_data というフォルダがあったとして、
  • そこに action_logs_20190327.csv をいうファイルをアップロードしたとします
• ここまででデータの用意完了

BigQueryにデータをロードする

• 以下のコマンドをCloudShellで実行

bq load test_load_dataset.action_logs\$20190327 gs://bigquery_load_data_koreeda/test_data/action_logs_20190327.csv table_action_logs.json

bq load [データセット名].[テーブル名]\$[データを登録したい日付] gs://バケット名/フォルダ名/ファイル名 スキーマ定義JSON

データの確認

• 以下のクエリをBigQueryのクエリエディタで実行する
  • ※とりあえず SELECT * しないように ip, created を取得してみて確認

SELECT 
  ip, created
FROM 
  `ttr-bigquery.test_load_dataset.action_logs` 
WHERE 
  _PARTITIONTIME = TIMESTAMP("2019-03-27") 
LIMIT 1000

• データが取得できればOK
• 登録したい日数の分、データCSVの作成〜BigQueryへのデータロードまでを手作業で繰り返す
• すごい手作業だけど、結構わかりやすいのではって思ってる