GAミント至上主義

Web Monomaniacal Developer.

Google Cloud SQLでクエリーキャッシュを有効化する

Cloud SQL(MySQL)では、デフォルトでクエリ-キャッシュは無効化されており、countを何回たたいても遅いので有効化したかった。

ブラウザ上でフラグの追加をするとなぜか出来ないので、コマンドライン上でやる。

下記コマンドを実行。再起動が必要なので注意と時間が必要。

カンマ区切りで必要なものを同時にすべて指定しないと、指定していないものは消されてしまうので注意。

gcloud beta sql instances patch umicos --database-flags query_cache_type=1,query_cache_size=1073741824,query_cache_limit=33554432

メモリ量の数字は適当。

これで2回目以降は圧倒的に早くなった。

mysql> SELECT count(*) FROM member.member;
+----------+
| count(*) |
+----------+
|  2135809 |
+----------+
1 row in set (0.69 sec)

mysql> SELECT count(*) FROM member.member;
+----------+
| count(*) |
+----------+
|  2135809 |
+----------+
1 row in set (0.04 sec)

mysql> SELECT count(*) FROM member.member;
+----------+
| count(*) |
+----------+
|  2135809 |
+----------+
1 row in set (0.04 sec)

それにしてもCloud SQLは便利だけどちょっと高いなぁ


db-n1-standard-1だと継続利用価格で$0.0878/時だから

$0.0878 * 24 * 30 = $63.216

気軽にポコポコ立てるのはちょっと気になってしまう。

普通にGKEでn1-standard-1*1 + HDD50GBだと月31ドルぐらいだから、Cloud SQLを使うことで倍になる。

Cloud SQL独自の問題ではまることもあるし、拡張性とかいらない社内ツール用とかだったらGKEで立てた方がいいかも。

Google Cloud SQLでCSVインポートをコマンドでできない問題

Cloud SQLをデータ分析の一時テーブル的に使おうとしているけど、ブラウザ上のCloud Consoleではうまくいくのに、自分のマシンからgcloudコマンドでやるとエラーが連発する。

WEBブラウザ上は下記のような画面で、選択時に必要な権限を付加するとか、CSVなのかSQLのダンプなのか選べたり、テーブルを指定できる。

f:id:uyamazak:20170822104938p:plain

これでやると成功する

2017/08/22 10:41:45	インポート	gs://*******/member.csv.gz からインポートしました。

gcloudコマンドでそれっぽいコマンドのヘルプ

% gcloud beta sql instances import --help

NAME
    gcloud beta sql instances import - imports data into a Cloud SQL instance
        from Google Cloud Storage

SYNOPSIS
    gcloud beta sql instances import INSTANCE URI [--async]
        [--database=DATABASE, -d DATABASE] [GCLOUD_WIDE_FLAG ...]

DESCRIPTION
    (BETA) Note: authorization is required. For more information on importing
    data into Google Cloud SQL see
    https://cloud.google.com/sql/docs/import-export/importing.

POSITIONAL ARGUMENTS
     INSTANCE
        Cloud SQL instance ID.

     URI
        Path to the MySQL dump file in Google Cloud Storage from which the
        import is made. The URI is in the form gs://bucketName/fileName.
        Compressed gzip files (.gz) are also supported.

FLAGS
     --async
        Do not wait for the operation to complete.

     --database=DATABASE, -d DATABASE
        The database (for example, guestbook) to which the import is made. If
        not set, it is assumed that the database is specified in the file to be
        imported.

GCLOUD WIDE FLAGS
    These flags are available to all commands: --account, --configuration,
    --flatten, --format, --help, --log-http, --project, --quiet, --trace-token,
    --user-output-enabled, --verbosity. Run $ gcloud help for details.

NOTES
    This command is currently in BETA and may change without notice. This
    variant is also available:

        $ gcloud sql instances import

CSVSQLかを選択するオプションが見当たらない。もちろんテーブルも指定できないのでCSVは使えないふいんき(ry


それでもCloud Storage上のCSV(gz圧縮)をインポートしようとするとまず権限エラーが出る

2017/08/22 10:29:29	インポート	 gs://**********/member.csv.gz: Access denied for account ************@speckle-umbrella-2.iam.gserviceaccount.com (permission issue?)	
2017/08/22 10:27:26	インポート	 gs://**********/member.csv.gz: Access denied for account ************@speckle-umbrella-2.iam.gserviceaccount.com (permission issue?)	
2017/08/22 10:27:05	インポート	 gs://**********/member.csv.gz: Access denied for account ************@speckle-umbrella-2.iam.gserviceaccount.com (permission issue?)

これはブラウザでCloud Storageの画面からログに表示されたユーザーに権限追加したら消えた。このユーザーはどのタイミングで変わるのかは分からない。

と、現時点でCloud SQLCSVインポートを自動で行うには、Storageの権限問題と、CSV使えない問題が出てしまうので、PythonかなんかでMySQLとしてつなげて、CSV読み込んで流し込むスクリプトを作る必要がありそう。めんどい。

Google Data Studioでbizoceanのセグメント別に会員数を表示

データ活用プロジェクトoceanusの一環で、営業さん等が会員データを手早く、安全に確認できるツールを作った。

データを準備をしたのは私だけど、Data Stuidoのレポートを作ったのは私ではない。6月入社のmasui(仮)さんだ。


f:id:uyamazak:20170822101309p:plain


真ん中に表示されているようなチェックボックスをいじるだけで、その条件の会員数や、そのほかの割合もすぐ表示される。

営業さんが行っている会員向けメールマガジンの提案用、という目的でヒアリングをして、この形となった。


現在、BigQueryからデータを読み込んでいるが、BigQueryに入れた時点で名前、メールアドレスなどの漏れたらヤバいデータは削除してあるので、安心して使える。

以前は、メルマガ配信用システムをなんとか利用していたが、動作も遅いし、見た目もごちゃごちゃだし、個人情報も表示されてしまうので不安の種になっていた。


今の問題としては、条件が多すぎるので、チェックボックスを1クリックするだけで数~十数クエリがBigQueryに飛んでしまい、ポチポチ続けていると「割り当てエラーです」
と出て、すぐ下のような画面になる

f:id:uyamazak:20170822101759p:plain

エラーの詳細

データセットの割り当てポリシーを超過しました。

営業先でこれが起きたら致命的だ。

もうちょっとシンプルだった初期バージョンではDAtastudioのキャッシュに乗ってくれて、どんどん早くなったけど、複雑になったせいか今のバージョンではいつまでたってもキャッシュしてくれない。

あとデータ分析用の会員DBが300MB程度とはいえ、BQのスキャン料金も気になる。

そのため、データスタジオ用にBigQueryに入れているものをMySQLか何かにも入れれれば、快適になるのでは?と現在作業を進めている。

まず、SSDのCloud SQLで試しに作り、それでも遅いようなら、自分でインメモリテーブルのMySQLをDockerなどで構築する予定だ。

BigQueryは分散している構造上1TBを超えるような大型一括処理には向いているが、数百MB単位の処理を細々と行うには不向きな気がする。


bizoceanはまだまだ小さな会社でそんな予算もないけど、こういう社内が働きやすくなるとともに、技術の勉強になるものはどんどん作っていきたい。