はじめに こんにちは。TIG 市川です。GCP連載2021 の2日目です。
本番運用しているデータをサクッと引っこ抜いてCSV錬金したり、DWHなどに連携していないデータを元にDataStudioでコネコネしたり・・・(負荷を気にせず)気軽にやりたいなぁ・・・というアナタに贈る記事になります。
おことわり
実際のシステムにおける本番データは正しく取り扱いましょう。運用ポリシー・セキュリティポリシーに従い、機密情報などを気軽に引っこ抜いてはいけません
サーバレスエクスポートとは?
https://cloud.google.com/blog/products/databases/introducing-cloud-sql-serverless-exports より引用
データベースからデータをエクスポートすることは運用上起こりえるタスクではありますが、その実、エクスポートの仕方を誤ると本番のワークロードに影響を及ぼす可能性があります。
しかし、データのすべてはデータベースに入っていて、欲しい、今すぐ分析したい・・・という時もあると思います。
我々エンジニアは可能な限り手間は省きたい生き物です。
別途データベースを建てたくない
わざわざGCEやローカルPCからSQLクエリ打ちたくない
データをGCSに持って行くのも面倒
本番ワークロード影響はあってはならない
などなどのワガママに対応したのが、このサーバレスエクスポートです。CloudSQLではデータベースからのエクスポートは対応していましたが、2020年9月にサーバレスエクスポートに対応しました!
サーバレスエクスポートではエクスポート専用の一時的なインスタンス を建て、そこからデータを抜くことで本番ワークロードに一切の影響を与えず、GCSにファイルをアップロードしてくれる優れものです。その上、SQL形式とCSV形式に対応し、カスタムSQLで狙ったデータだけ抜くこともできるかゆいところに手が届くサービスになっています。
事前準備 今回のケースでは特に準備することはありません。グローバルIPを持ったCloudSQLを建てたのみです。あとはエクスポート先のGCSバケットさえあればよいのです。
データベースはMySQLとPostgreSQLでサポートしています。(今回はPostgreSQLで試しました)
今回、データはPostgreSQLのサンプルデータベース を利用します。CloudShellなどで接続し、pg_restoreを使ってサクッとインポートしましょう。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 $ gcloud sql connect serverless-demo-db --user=postgres --quiet postgres=> CREATE DATABASE dvdrental; postgres=> \q $ wget https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip $ unzip dvdrental.zip $ pg_restore -h 34.123.239.237 -U postgres -d dvdrental ./dvdrental.tar $ gcloud sql connect serverless-demo-db --user=postgres --quiet --database=dvdrental dvdrental=> \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | actor | table | postgres public | address | table | postgres public | category | table | postgres public | city | table | postgres public | country | table | postgres public | customer | table | postgres public | film | table | postgres public | film_actor | table | postgres public | film_category | table | postgres public | inventory | table | postgres public | language | table | postgres public | payment | table | postgres public | rental | table | postgres public | staff | table | postgres public | store | table | postgres (15 rows)
クエリの作成 さて、では2007年03月の担当者別売上げ(っぽい)クエリを作ってみます。
今回は分析用にこのデータがすごく欲しいと仮定します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 WITH staff_detail as ( SELECT staff.staff_id, staff.first_name || ' ' || staff.last_name as staff_name, store.store_id FROM staff LEFT JOIN store ON store.store_id = staff.store_id ) SELECT staff_detail.staff_id, staff_detail.staff_name, staff_detail.store_id, payment.payment_date, payment.amount, payment.customer_id FROM payment LEFT JOIN staff_detail ON payment.staff_id = staff_detail.staff_id WHERE '2007/03/01' ::timestamp <= payment_date and payment_date < '2007/03/31' ::timestamp ;
データのサーバレスエクスポート実践! CLIからももちろん可能ですが、今回は画面からエクスポートを行ってみます。
SQLクエリ部分に先ほど記載したSQLをおもむろに貼り付けます。
一時インスタンスへのエクスポートをオフロードする にチェックを入れます。
エクスポートが開始されると概要画面に戻りぐるぐる回ります。
が、WITH句には対応していませんでした・・・
気を取り直してサブクエリ版のSQLを作成します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 SELECT staff_detail.staff_id, staff_detail.staff_name, staff_detail.store_id, payment.payment_date, payment.amount, payment.customer_id FROM payment LEFT JOIN ( SELECT staff.staff_id, staff.first_name || ' ' || staff.last_name as staff_name, store.store_id FROM staff LEFT JOIN store ON store.store_id = staff.store_id ) AS staff_detail ON payment.staff_id = staff_detail.staff_id WHERE '2007/03/01' ::timestamp <= payment_date and payment_date < '2007/03/31' ::timestamp ;
そして改めてエクスポートを行い、GCSを確認すると・・・
エクスポートされていますね!
サーバレスエクスポートでは一時インスタンスを建てるため、最低でも5分程度時間を要します。後はデータ量によってエクスポートまでの時間がかかります。今回はきっかり5分程度で終わりました。
この間、本番サーバではSQLの呼び出しなどは行われず、一切の影響なしにデータが抜けました!
なんてお手軽!
作成されたCSVはヘッダなしのカンマ区切りで作成されます。
1 2 $file -i Cloud_SQL_Export_2021-03-09-14-07-39.csvCloud_SQL_Export_2021-03-09-14-07-39.csv: text/plain; charset=us-ascii
まとめ 非常に簡単に本番影響を出さないデータエクスポートができました!
もちろん無料というわけにはいきませんが、ちょっとしたデータ集計や検証の為に利用価値が高いのではないでしょうか。
本番データを見たいけど、影響が。。。という方、是非試してみてください。
明日は澁川さんの20210311_PythonでCloud_Operationsの機能を使ってみる です。
参考 Google Cloud blog Japan Google公式ドキュメント