Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.
  • ちょっと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"
  }
]

  • 参考
  • CloudShell を起動してドラッグ&ドロップで転送する
    • 以下の赤枠のボタンで画面下に起動するので、そこにドロップすると転送できる スクリーンショット 2019-03-28 15.27.06.png
  • 以下のコマンドを 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 にすれば、あとはデフォルトのままで大丈夫そうです
  • 参考
  • 出力した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へのデータロードまでを手作業で繰り返す
  • すごい手作業だけど、結構わかりやすいのではって思ってる
naoki_koreeda
PHPerになりました。最近Google Cloud Platform/Golang/Node.js。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした