この記事はfreeeデータに関わる人たち Advent Calendar 2020 3日目のエントリーです。
概要
freeeでデータ基盤エンジニアしている者です。
今回は「Googleスプレッドシート(以下スプレッドシート)のIMPORTDATAってイイ感じよね。けど…」という話。
3分くらいで読了できるライトな内容です。
ちなみに昨年もアドベントカレンダー書いてまして、記事はコチラ。Digdagについて書きました。
先にまとめ
- IMPORTDATAを業務で使いたい
- が、ある理由でIMPORTDATAが使えない
- IMPORTDATA相当の機能をGoogle App Script(以下GAS)で作ってスプレッドシートアドオンとして社内公開
- このIMPORTDATAもどきを業務で使って捗ったという話
前置き
- 弊社では全従業員にもれなく組織アカウントとして個別にGoogleアカウントが付与される
- 我がデータ基盤チームメンバーはいずれもGAS経験はほぼゼロ状態だった
- GCPもそんなに普段使わない。AWSはガッツリ
- TOTPの仕組みとかHttpRequest/Responseの詳細な内容は省きます
IMPORTDATAについて
Googleスプレッドシートには様々な関数が用意されていて、IMPORTDATAもその一つ。
詳しい使い方は公式Docを見てほしいのですが、要は引数に指定したURLデータを引っ張ってきてシートにインポートしてくれる、そんな便利奴です。
Redashとの連携
弊社ではデータの分析・可視化ツールの一つとしてRedashを利用しています。ただ、
複雑なSQL書きたくない！スプレッドシートに一度出力してそちらで加工したい！
といったような目的から、
Redashで簡単なSELECT文を書く
↓
クエリを保存
↓
出力結果URLをIMPORTDATAの引数に指定してスプレッドシートに持ってくる
といった連携技を行いたいという声があります。
課題
さて、上述の方式でデータを引っ張ってくる場合、スプレッドシートのアクセスをRedash側で許可しないと実現できません。ただ当然ながらセキュリティを考慮して、組織のGoogleアカウント以外では実行されないように配慮する必要があります。
残念ながらIMPORTDATA関数を使う限りはそのセキュリティ機構を組み込むのが厳しいため、
(こうすればできたんじゃない？というのがあったらｺｯｿﾘｵｼｴﾃｸﾀﾞｻｲ)
IMPORTDATAを使わずにスプレッドシートにRedashのデータを連携する手段を検討することにしました。
やったこと
こうして我々はIMPORTDATAを代替する’なにかしら’を作ることになったのですが、
最終的なアウトプットが以下となります。
簡単ですが、どういう振る舞いを実現しているのかを3〜5行添えてます。
スプレッドシートアドオン開発
- ユーザがこのアドオンを起動して、出現するポップアップにRedashのURLを入力
- Google Cloud Functions(後述。移行GCF)に用意した関数をcallして、TOTP Tokenを受け取る
- 中継サーバー(後述)にRedash URLとTOTP Tokenを投げる
TOTP Token生成用のGCF関数開発
- スプレッドシートアドオンからのRequestを受ける
- 後述する中継サーバーと同じseed, TimeZoneを使ってTOTP Tokenを生成しアドオンへのResponseに付与
- 組織アカウント配下のユーザーにのみ、このGCFのCall権限を付与
中継サーバーの構築
- ECSでサーバーを建立し、そのフロントにALBを構える
- スプレッドシートアドオンからRequestを受けとる
- GCFと同じseed, TimeZoneを使ってTOTP Tokenを生成し、アドオンから受け取ったTokenと照合
- 照合した結果、Tokenが有効であればRequestをRedashにProxy
- RedashからのResponseをスプレッドシートアドオンに返す
つまりこんな感じの全体構成
ポイント
スプレッドシートアドオン
IMPORTDATAと可能な限り同等の振る舞いを提供する目的で作成したので、
例えば、
- 指定のシートにデータを貼り付けられるようにする
- 指定のセルを開始位置としてデータを貼り付けられるようにする
- データのリフレッシュを可能にする
- 前提としてRedash側の結果がスケジュール等で更新されてる必要はある
といった機能を盛り込むため、管理用シート(操作厳禁)を作成してそこに
- 行の開始位置
- 列の開始位置
- Redashからユニークに払い出されるクエリID
などの情報を記録することで、上記の機能を実現しました。
↓スプレッドシートアドオン起動した様子↓
↓Redashから取得する対象データと表示位置を指定する様子↓
中継サーバー
役割としては簡易的なProxyとTOTPの機能のみなのでコレと言ったポイントは特にないです。が、
強いて挙げるなら、あるあるですが、TOTPは時間経過とともに有効なTokenが更新されていくので、
Requestを投げるタイミングによってはGCFから受け取ったTokenが中継サーバーに投げた時点ではすでにinvalidとなっている可能性を考慮して、一個古いTokenを許可する仕様としました。
アドオンに足りないところ
Redashはこんな感じ↓で動的に値を指定できるパラメータ付SQLが書けるのですが、
現状このようなクエリには対応できておらず、インポートを実行してもエラーとなってしまいます。
これができるようになるとさらに利用の幅が広がりそうなので、そのうち対応したいです。
もう一度まとめ
- GASで作ったスプレッドシートアドオン、TOTPToken、中継サーバーのあわせ技で、IMPORTDATAに変わってセキュアにRedashからデータを取ってこれる仕組みを作った
- 機能としては最小限なので今後に期待
- チーム的には経験薄めな GAS, GCF, TOTP に触れる良い機会だった
雑感
- 今年も夜なべして書いてしまった。
- もし来年も書く機会があれば前日正午には準備ができているオトナな余裕を持ちたい
- これを書いたことにより今年の仕事を納めた感があります。良いお年を。
予告
明日は2日目に登板したnagomiso3がまさかの中1日で再登板です。乞うご期待。
追伸
freeeデータ基盤メンバー諸君へ
間違っているところあったらこっそり教えてください。年明けぐらいにはきっと直します。