ミツモア Tech blog

「ミツモア」を運営する株式会社ミツモアの技術ブログです

Redash importdataを完全にGAS化した

※こちらはミツモアAdvent Calendar 2023の20日目の記事です。

お久しぶりです。ミツモアデータマネージャの古田です。
ミツモアではBIツールとして、Redashを導入しています
ビジネスチームは、Redashを通して日々様々なモニタリング、分析を実施しています。
今回はそのデータの活用を維持するために作成したツールについてご紹介いたします

Outline

  1. Redashとimportdata
  2. importdataGAS開発のきっかけ
  3. importdataGASとは
  4. 導入した効果
  5. 副次的に生まれた効果
  6. 工夫したポイントや今後の改善予定

Redashとimportdata

Redashとは様々なデータソースからSQLで作成されたqueryを元に可視化を行うことのできるOSS Webアプリケーションです。ミツモアでは主にBigQueryをデータソースとしたBIツールとして導入し、可視化およびデータ利活用をしています。
特にビジネスサイドでは、このRedashの出力結果をGoogle Spreadsheets(以下、スプレッドシート)へ自動的に連携するため、下記のようなimportdata関数を多用していました。
Redashのパラメータがある場合とない場合で記述方法が変わります

=IMPORTDATA("https://<redash domain>/api/queries/<queryId>/results.csv?api_key=<APIKEY>")

このAPIはRedashのパラメータを渡すことで、データの絞り込みや期間の指定をすることが可能になっています。

// 例1：パラメータなし
?api_key=<APIKEY>
// 例2：パラメータあり
?api_key=<APIKEY>&p_timetype=month&p_categoryName=エアコンクリーニング&p_serviceName=引越し

importdataGAS開発のきっかけ

Redashのバージョンアップを行った際、スプレッドシート側でimportdata関数を用いれば実行できていた　例2：パラメータあり　がエラーになるようになりました。データのスプレッドシート連携が設定ができなくなると、データ利活用が阻害されてしまいます。データドリブンを目指す会社にとってこれほど打撃の大きいことはありません。そのため、importdataの代わりとなる仕組みを開発することにしました。幸運なことに、貼り付け先の拡張機能であるGoogle Apps Script(GAS)にて作成することができそうだったため、そちらで作成するとにしました。

importdataGAS

importdataGASでは次のように利用できるようにしました

例1：パラメータあり

importdata関数自体は利用できますがせっかくなのでimportdataGASでも利用できるようにしました
元のRedashURLが次のケースについて比較してみます："https: //< redash domain>/queries/<queryId>"

利用方法
Before =IMPORTDATA("https: //< redash domain >/api/queries/< queryId >/results.csv?api_key=< APIKEY >")
After 次をスプレッドシートに入力
- Writer：import設定者
- Title：任意の文字列を記載して良い
- Redash No：Redash query id
- paste spreadsheetsID：貼り付け先スプレッドシートID
- https://docs.google.com/spreadsheets/d/<スプレッドシートID>/edit#gid=シートID
- targetSheet name：貼り付け先スプレッドシートのシート名
- paste range column：貼り付け先スプレッドシートの行番号
- paste range row：貼り付け先スプレッドシートの列番号
- paste type(delete type)：更新方法(後述)

※After補足：下図のようにimportさせたい項目を入力する仕組みにしました

importdataGAS without parameter

例2：パラメータあり

元のRedashURLが次のケースについて比較してみます：https: //< redash domain >/queries/< queryId >?p_parameter1=2023-01-01&p_parameter2=2023-12-31

利用方法
Before ※利用できない
=IMPORTDATA("https: //< redash domain >/api/queries/< queryId >/results.csv?api_key=< APIKEY >&p_parameter1=Value1&p_parameter2=Value2")
After 次をスプレッドシートに入力
- Writer：import設定者
- Title：任意の文字列を記載して良い
- Redash No：Redash query id
- paste spreadsheetsID：貼り付け先スプレッドシートID
- targetSheet name：貼り付け先スプレッドシートのシート名
- paste range column：貼り付け先スプレッドシートの行番号
- paste range row：貼り付け先スプレッドシートの列番号
- paste type(delete type)：更新方法(後述)
- parameter1：1つ目のクエリパラメータ ※順不同
- value1：1つ目のパラメータに渡したい値 ※表示形式は`書式なしテキスト`に設定する必要がある。`自動`で設定されていた場合、日付を入れた場合にUNIX Timeとして処理されてしまうため
- parameter2：2つ目のクエリパラメータ
- value2：2つ目のパラメータに渡したい値
- parameter3：3つ目のクエリパラメータ
- value3：3つ目のパラメータに渡したい値
- …パラメータは現在15個まで対応できるようにしてあります

※After補足：パラメータなしの項目に加えて下図のようにパラメータを入力します

importdataGAS with parameter portion of parameters

利用したGAS関数 下記にメインどころの関数を紹介していきます。例示するものはパラメータありの場合に用いている関数群です

SQLの結果を更新するようにredashにリクエストする関数。引数のparamsは事前に?p_parameter1=2023-01-01&p_parameter2=2023-12-31のような形式に整形しておきます。

function refresh_with_parameter(redash_query_id, params) {
  var url = REDASH_HOST + "/api/queries/" + redash_query_id + "/refresh" + params;
  var json = fetch_json_with_parameter('post', url);
  return json.job;
}

SQLの結果が更新されるまで待機する関数。SQLの複雑さによってはrefresh_with_parameter関数を実行しても即結果が返ってくるわけではないため、実行結果が返ってきたことを確認する必要があります。

function poll_job_with_parameter(job) {
  var k = 0
  while(k < WAIT_TIMEOUT) {
    var url = REDASH_HOST + "/api/jobs/" + job.id;
    var json = fetch_json_with_parameter('get', url);
    if(json.job.status == 3 || json.job.status == 4) {
      return json.job;
    }
    k++;
    Utilities.sleep(1000);
  }
  return job;
}

JSONを取得する関数。Redashの結果はJSONとして受け取ります。

function fetch_json_with_parameter(method, url) {
  var response = UrlFetchApp.fetch(url, {
    'method': method,
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'Key ' + USER_API_KEY,
    },
  });
  const headers = response.getHeaders();
  return JSON.parse(response.getContentText());
}

JSONをcsv文字列に編集する関数。貼り付け先はスプレッドシートなのでcsv形式にしておきます。後述するsplit関数も考慮し、xx-separated-values形式に変換しておく必要がありました。

function jsonToCsv_with_parameter(json, delimiter) {
    var header = Object.keys(json[0]).join(delimiter) + "\n";
    var body = json.map(function(d){
        return Object.keys(d).map(function(key) {
            return d[key];
        }).join(delimiter);
    }).join("\n");
    return header + body;
}

シートとプログラムの準備が終わったら、次に用意したそれぞれのシートに対して実行トリガーを設定します。

Apps Script trigger
サンプル画像は、Dailyで実行させたい場合のトリガー設定画面です。この例ですと午前10時〜11時のどこかで実行されるような仕様になっており、分単位までは設定できないことが注意点です。ただし、一度実行されると2回目以降は1回目とほぼ同じ分に実行されるため、本当に実行したいタイミングで実行されるように何度かトリガーを設定しなおしたりしましたww
Apps Script trigger detail

導入した効果

importdataのハードルが下がる

importdata関数を用いていた時代には、パラメータ付きで関数の引数を書き換える必要などがあったり利用者に混乱が多々見受けられていました。ですが、こちらを導入してからは最初の書き方さえ覚えれば何をどこのセルに記載すれば良いかが明確なため、混乱も問い合わせもなく現在ではなんと!!! 445 個も定期実行設定されています(※20231218時点. エラー含む)

また、URLを貼り付けると必要な要素に分解されるような関数も下記のように作成し、利用者の手順が3つ(URL貼り付けと分解された要素をコピペする)だけとなっています

URL separater

いつ更新されたのかがすぐにわかる

importdata関数を用いていた際には更新に失敗しても古い情報が残り続け、いつ更新されたものかがわからないという問題がありました。現在では Last updatedAt を取得するようにしており、データ品質の向上が達成されるようになっています。

エラーメッセージの拡充

開発するとどのような理由でエラーになるのかが明確になり、現在では8種類のエラーがわかるようになっています。これでsomething wrongよりも詳しいエラー内容がわかるようになり、利用へのハードルが下がりました

  • パラメータを設定してください
  • 何かしらの入力が足りません
  • シートが存在しない
  • clearする際にエラーになりました
  • redashへのアクセス時の何かしらのエラー。パラーメータを確認してください
  • 指定された名前のシートが存在しない、もしくはシートにアクセスできません
  • 貼付け先シートが重すぎます（clearする際にエラーになりました）
  • 貼付け先シートが重すぎます（Pasteする際にエラーになり、貼付け先のクリアにも失敗しました）

副次的に生まれた効果

importdataGASを導入した結果、Redash結果をスプレッドシートへ連携する促進がされるとともに副次的に次のような効果もありました

誰がどのRedashを活用しているのか、利用しているのかがすぐにわかる

今まではRedashサーバのログを確認しても、どこかしらのスプレッドシートからimportdata関数を用いてRedashへアクセスがされている、ということだけがわかる状態でした。しかし、今ではimportdataの実施は共有のスプレッドシートをもとに行われているため、全社員がその利用についてアクセスか瞬時にできるようになりました。
また、データチームとしても既存Redashの修正を行うに直接修正なのかforkした方が良いのかなども瞬時に判断できたり利用しなくなったであろうimportの取り締まりを行えるようになりました。

工夫したポイントや今後の改善予定

工夫したポイント

split関数の利用

定期実行を行うとき、出力結果が変わることがあります。例えば、中身の値/sortのランダム性/出力行数の増減などです。スプレッドシートで貼り付けを行う際、行数や列数が変わることを適切に追従させるために貼り付け列を1行だけにすることにしました。出力結果はコンマ区切りであるので、スプレッドシートのsplit関数を用いてカンマ区切りでseparateするように設計してあります。デメリットとしてはstring_aggなどでseparationをカンマに設計できなくなることです。が、これは運用で一定カバーできるのでよしとしました

更新の際に既存の出力結果をどう削除するのか

上述したように、仕様として1行1セルに値を書き込むことで貼り付けを実現しています。更新をする際、既存の書き込まれた値をどう処理するのかが問題になります。
出力行数が増減する場合と増減しない場合があったり、同一列へ複数の貼り付けを行いたいなどの要望があったため、次のような削除タイプを用意しました

  1. 指定セル以下すべて削除
  2. 指定セル以下を、貼り付け行分のみ削除

paste type

分割実行

import対象が行数や列数の多いの場合、重たすぎて一括でスプレッドシートへ貼り付けを行うことができずにエラーになることがありました。なので、1000行づつや2000行づつに区切ってスプレッドシートへの貼り付けの実施を行うように変更しました。

トリガー設定

定期実行頻度をhourly, daily, monthlyに区分し、実行コストをなるべく抑えました。
Apps Scriptはトリガーを設定することができるので活用しており、現在はすべて古田がトリガーをセットしています
また、実行時間には30分と限りがあるため区分別に複数シートを用意し、シート別にトリガー管理しています。

dateなどのmasterシートの用意

月次レポート、週次レポート、当日のみ などパラメータを駆使したい場合がよくあるため、よく利用される日時を=today()のような関数であらかじめ作成してあるmasterシートを用意しました

エラー通知

現在はimportエラーが発生した場合Slackへ通知がいくように設定してあります。
※正し、timeoutによる強制終了の場合は通知なし

今後の改善予定

トリガー上限対応

Apps Scriptのトリガーには1ユーザあたり20トリガーまでと上限が決まっています。
今後、400と言わず1000, 2000個にimport定期実行設定数が膨れ上がった際にはトリガーが上限に達してしまう可能性があるため、スケールには耐えれません
そのため、将来的には各部署単位などでトリガーセットを行うようになることを想定しています。

split関数の撤廃

split関数は、再実行時の削除には大いに役立ちますが、逆にスプレッドシート内での計算コストが高くつきます。split関数を用いているがゆえにスプレッドシートの計算量が膨大になりtimeoutやスプレッドシートへ貼り付けする速度が遅くなるというデメリットがあります。split関数を撤廃し、貼り付けを高速化するようにしたいと考えています。

そんなミツモアでは様々な職種のエンジニアを積極的に採用しています。ぜひチェックしてみてください！！

採用ページ: https://meetsmore.com/company/recruit
募集要項: https://herp.careers/v1/meetsmore/3cQ_H41s6tZu

Reference

RedashのデータをGoogleスプレッドシートに自動で反映する方法
GASでredashの最新の結果を取得してスプレッドシートに反映する
GAS×Redashで月18時間の工数削減を実現！ ～API操作は難しいけど便利だった～