Amazon AthenaクエリでGoogleスプレッドシートにデータ貼り付け

Amazon Athenaは真の女神でS3のデータを縦横無尽に取ってこれて、しかも女神なので請求する費用もほんの少しだけです。
ここではGoogleスプレッドシートからAthenaにクエリした結果を貼り付ける方法を説明します。
一度仕組みを作ってしまえばクエリ文を変えて使い回しまくりです。

構成

GASからhttp postを投げてAPI Gateway経由でlambdaにselect文を渡す。
lambdaはSDK使ってAthenaを読んで結果をhttp responseとして返す、という構成です。
lambdaをちょびっとだけいじるとselectでなくinsert intoにしたりselectする相手をRDSにしたりもできちゃいそうですね。

AWSからlambdaでGoogle Sheetsにデータを投げつけるのは認証とかあってまあまあ面倒臭そうなのでやりたくない。
逆にここで紹介する構成ではセキュリティがあまり強くないのでそこはご容赦くださいと言いたいです。

構成要素を説明していきます。
なおソースコードはgithubにも置きました。

Google Apps Script

一番重要です。

関数queryAndPasteではUrlFetchApp.fetchを使ってAPI GatewayにHTTP POST投げて返ってきたベタテキストcsvをパースして、目標のシートに貼り付けします。
設定項目としては、もちろんクエリ文のテキストと貼り付け対象のシート名に加えてプロパティサービスに API Gatewayのinvoke URLとapi-key(次で説明)の二つを設定します。
この二つだけがセキュリティを守る壁になりますので漏洩させてはなりません。
セキュリティをもっと固くするならIAM認証を考えますが、GASでSDKが使えないのでスクラッチで認証部分を書かなければならずまあまあつらそうです。

関数queryAthenaと分割している理由は、クエリ文を変えながらqueryAndPasteを使いまわすためです。
queryAthenaの中で何度もqueryAndPasteを書いてqueryAthenaはonOpenでもTime-basedでも好きなトリガーで回してください。

API Gateway

次の内容のリソースを作成します。(箇条書きでないなにかうまい表現方法他にないのかな?)

  1. API Keysを作成し、Usage Plansと紐付ける。参考
  2. REST APIでPOSTリソースを作成する
  3. Method Request: API Key Requiredをtrueにする
  4. Integration Request:
    1. Integration type: Lambda Function
    2. Use Lambda Proxy integration: check
    3. Lambda Function: 次ステップで作るlambda function
  5. Method Response: Response Body for 200のContent typeをtext/csvに(必須ではない)

でDeployしてください。
特に注意すべき点はありません。
ここで取得したinvoke urlとapi-keyを上のGASに設定してあげてください。

Lambda

pythonで書きました。

Athenaはサービスの性質上クエリを投げたら結果が出るまで自分で待たなければならないので、Status==’SUCCEED’になるまでループを回すようにしてみました。
最大30秒待機するようにしていますが、もし足りないようならtry_maxを増やしてください。

設定項目はAthenaの仮出力バケットとパスで、それぞれtmp_bucket, tmpfolderです。
Athenaは入力も出力もS3だけですので、事前に出力用S3バケットを作成しておいてください。