komagataのブログ

やりたいこと

BigQueryに日別に集計結果のログテーブルを作っています。

例：logs_20160101

その集計結果をCloud SQLに追加したい。

例：logs

日次バッチで動かしていて、毎日BigQueryから集計結果をCloud SQLのlogsテーブルに追加したい。

• 今日：logs_20160101(BigQuery) -> logs(Cloud SQL)
• 明日：logs_20160102(BigQuery) -> logs(Cloud SQL)
• 明後日：logs_20160103(BigQuery) -> logs(Cloud SQL)

やろうとしたこと

BigQueryからGoogle Cloud Storage(GCS)にCSVファイルでエクスポートして、Cloud SQLにインポートする。

$ bq extract datasetname.logs_20160101 gs://bucket-name/logs_20160101.csv

BigQueryからGCSへのエクスポートはできた。

しかしCloud SQLにCSVでファイルを読み込むことができない。

ローカルでmysqlimportを使うバターン

$ gsutil cp gs://bucket-name/logs_20160101.csv /tmp/logs_20160101.csv
$ mysqlimport --fields-terminated-by=',' -hxxx.xxx.xxx.xxx -uroot -ppassword database_name /tmp/logs_20160101.csv
mysqlimport: Error: 1045, Access denied for user 'root'@'%' (using password: YES), when using table: logs

権限が無い。

ローカルでmysqlクライアントからLOAD DATA INFILEを使うパターン

mysql -uroot -ppassword -hxxx.xxx.xxx.xxx database-name
mysql> LOAD DATA INFILE '/tmp/logs_20160101.csv' INTO TABLE logs;
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

権限が無い。

mysqlimportとLOAD DATA INFILEはmysqlのFILE権限が無いとできない。しかしCloud SQLではその権限が無い。権限付与ができない。

LOAD DATA LOCAL INFILEを使うパターン

mysql> LOAD DATA LOCAL INFILE '/tmp/logs_20160101.csv' INTO TABLE logs;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

このmysql versionでは使えない。

CSV以外のフォーマットを使う方法

mysqldump形式やSQL形式のファイルをインポートする方法はいくつかありそうだが、BigQueryではそれらの形式でのエクスポートはサポートしていない。

その他

Cloud ConsoleのGUIではインポートできるが、バッチで実行するのには使えない。

最終手段は普通にAPIを使うプログラムを書くというものだが、「Cloud SQLでCSVインポートぐらいできないわけがない」という思いがあり、踏み出せない。

Stack Overflowに投稿してみた。しかし英語で伝えられてる気がしないので状況の整理の意味も込めてブログにも書いてみました。

How to import table from BigQuery to Cloud SQL - Stack Overflow

あれ、マニュアルにはあるのにWITHが使えない。

WITH foo AS (SELECT 1) 
SELECT * FROM foo

Error: Encountered " "WITH" "WITH "" at line 1, column 1. Was expecting: <EOF>

デフォルトではLegacy SQLを使うようになってるのでチェックを外す。

Yes.

TIL: Dialect = 方言

postgresでいうgenerate_series的なのは無いようなのでベタに書く。

SELECT * FROM
  (SELECT 1 as n),
  (SELECT 2 as n),
  (SELECT 3 as n),
  (SELECT 4 as n),
  (SELECT 5 as n),
  (SELECT 6 as n),
  (SELECT 7 as n),
  (SELECT 8 as n),
  (SELECT 9 as n),
  (SELECT 10 as n)

ホントにこれでいいのかな？

テーブル作成・データインポートは一気にできる。

$ bq load --source_format=CSV my_dataset.posts gs://my-bucket/posts.csv.gz "id:INTEGER,title:STRING,created_at:TIMESTAMP"

• ローカルからアップしてのインポートは遅く、Cloud Storageからのインポートの方が断然速い。
• gzip圧縮したCSVに対応してるので料金・速度の面からもやっといたほうがいい。
• スキーマは引数で指定できる。
• TIMESTAMPは色々なフォーマットをパースしてくれる。（今回はUNIXTIMESTAMP形式を読み込ませた。）Data Types  |  BigQuery Documentation  |  Google Cloud Platform

GCSへのアップは速いのに、BigQueryへの直接アップが遅いのはズルい。

スキーマを作る。

posts.json:

[
  {
     "name": "id",
     "type": "INTEGER"
  },
  {
     "name": "title",
     "type": "STRING"
  },
  {
     "name": "created_at",
     "type": "TIMESTAMP"
  }
]

テーブルを作る。

$ bq mk -t my_dataset.posts ./posts.json

便利じゃのう。