本番データが急に欲しくなったアナタに贈る、CloudSQLのサーバレスエクスポートを試してみた

はじめに

こんにちは。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を使ってサクッとインポートしましょう。

# DB作成
$ 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月の担当者別売上げ（っぽい）クエリを作ってみます。

今回は分析用にこのデータがすごく欲しいと仮定します。

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を作成します。

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はヘッダなしのカンマ区切りで作成されます。

$file -i Cloud_SQL_Export_2021-03-09-14-07-39.csv
Cloud_SQL_Export_2021-03-09-14-07-39.csv: text/plain; charset=us-ascii

まとめ

非常に簡単に本番影響を出さないデータエクスポートができました！

もちろん無料というわけにはいきませんが、ちょっとしたデータ集計や検証の為に利用価値が高いのではないでしょうか。

本番データを見たいけど、影響が。。。という方、是非試してみてください。

明日は澁川さんの20210311_PythonでCloud_Operationsの機能を使ってみるです。

参考

Google Cloud blog Japan
Google公式ドキュメント