TROCCOの不要な設定を自動で消せるようにしてみた

はじめに

前回DWHの不要なテーブルが定期的に削除されるように設定してみました

DWHだけでなくTROCCO側も検証のために作成した一時的な設定が削除されずに残り続ける...といった状況を防ぐため、同じ様に自動で削除できないか考えてみました。

なお、本記事ではTROCCOの詳細な設定方法などについては触れていません。
TROCCOの設定方法が知りたい方は下記記事などを参考ください！

今回のゴール

TROCCOの不要な設定が定期的に自動で削除される

実装方法を考える

今回は、TROCCOのワークフローの設定を整理していこうと思います。
TROCCO APIが色々用意されていて使えそう...！

設定の要/不要 の判断は、ワークフローの設定に付与できる ラベル で行うことにします。
ラベルの情報は、 ワークフロー詳細取得 APIを使うことで取得できます。
ただ、ワークフロー詳細取得API はワークフローID毎に実行が必要です。
そこで、各ワークフローのIDをはじめにすべて取得し、ワークフロー詳細取得APIをループ実行することで自動化します。

最終的な設定は下記のようなワークフローになります

工程
① ワークフロー一覧取得API を使って各ワークフローのIDを取得する
② ①で取得した情報を使いやすい形に加工する
③ ワークフロー詳細取得APIを使う設定をする
④ ③で取得した情報から、特定のラベルが付与されていない設定を絞り込んでリスト化する
⑤ ①～④の設定をワークフローにまとめてループ実行などの設定をする
⑥GASを使ってワークフロー削除APIを実行する

①ワークフロー一覧取得APIを使って各ワークフローのIDを取得する

TROCCOをAPIを使って設定していきます
ここで使用するAPIは下記です

TROCCOの転送元HTTP(S)に設定していきます。
躓いたのは入力ファイル形式とAPIキーの設定でした。

入力ファイル形式は、JSON Lines を指定します。
HTTPヘッダは添付画像のように Token APIキー を設定します。

Token の前に半角スペースが必要なのでご注意ください。

DWHは特別な設定は必要ありません。

データ転送を実行すると、下記のようなデータが取得できます。

1カラムにワークフローIDとワークフロー名が複数格納されています。
このままだと後の工程でワークフローIDのみを使えないため、idカラムとnameカラムにそれぞれ情報を格納するように加工していきます。

②取得した情報を使いやすい形に加工する

TROCCOのデータマート定義を使用してクエリを発行します。
Snowflakeの場合は下記のようなクエリになります。

select
    VALUE:"id" as pipeline_definition_id,
    VALUE:"name" as pipeline_definition_name
FROM テーブル名,
LATERAL FLATTEN(input => "items");

クエリを実行すると、下記のようにidとnameがそれぞれのカラムに格納されて利用しやすくなりました。

ワークフロー一覧取得APIに関わる部分はここまでです！

③ワークフロー詳細取得APIを使う設定をする

続いて、ワークフロー詳細取得 でラベルの情報を含むワークフローの詳細情報を取得します。
使用するAPIは下記です。

後でループ実行の設定を行うためカスタム変数を定義しておきます

値 は任意のワークフローIDを入力してください。
sample などを入力するとデータ転送設定の STEP2 プレビュー で失敗します。

定義したカスタム変数は、URLの末尾（ワークフローIDを指定する部分）にセットします

入力ファイル形式とHTTPヘッダの設定は、①のワークフロー一覧取得APIと同じで問題ありません。
転送先DWHの転送モードはUPSERTを選択肢、マージキーはidにします

転送モードを 洗い替え にすると、ループ実行のたびにテーブルが洗い替えされることになります。
ループ実行終了後に、1ワークフロー分の情報のみテーブルに書き込まれた状態となります。

データ転送を実行すると、下記のようなデータが取得できます。（スクリーンショットに写しきれていないですが、ラベルの情報も含まれています）

④特定のラベルが付与されていない設定を絞り込んでリスト化する

labelsカラムに削除対象のラベルが含まれていないワークフローのIDのみをリスト化します
今回GASを使ってワークフローの削除APIを実行するため、スプレッドシートにリストを作成します。

TROCCOのワークフローでもHTTPリクエストを実行できますがHTTPメソッドは GET か POST のみ選択可能です。
ワークフロー削除APIの DELETE は実行できないためGASを使って実行します。

データ転送設定にて、転送元にDWHを指定し、IDのみを転送するクエリを設定します。
転送元がSnowflakeの場合は下記のようなクエリになります。

select "id"
FROM ワークフロー詳細取得APIが格納されるテーブル
WHERE "labels" not like '%削除対象から除外するラベル%'

転送先はスプレッドシートを指定します。

①～④の設定をワークフローにまとめてループ実行などの設定をする

ここまで設定した①～④をスタートから順にワークフローにまとめます。

③のワークフロー詳細取得APIについてはループ実行の設定も加えます。
編集をクリックし、カスタム変数でループ実行 にチェックを入れます。
②の結果を格納したDWHのクエリ結果でループを選択し、②のワークフローIDが格納されているカラムを指定します。

ワークフローを保存し、実行すると下記のようにスプレッドシートに削除対象のIDが格納されます。

ワークフローのスケジュール機能を使って、定期的にリストを更新するようにします

⑥GASを使ってワークフロー削除APIを実行する

今回は、④でデータを格納したスプレッドシートにスクリプトを設定します。

スプレッドシートに格納した削除対象のワークフローIDを、ワークフロー削除APIのID指定部分に入れてカラム名を除いた2行目から最後の行までループ実行をするように設定します。

スプレッドシートを開き、拡張機能から App Script を選択します。

今回もコードはChatGPTに教えてもらいました。

function deleteFromSpreadsheet() {
  var apiKey = ScriptProperties.getProperty("APIKEY"); // プロジェクトの設定 ＞ スクリプトプロパティ にてAPIキーを設定しています
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("④のシート名");
  var LastRow = ss.getLastRow(); //シートの最終行を指定
  var ids = sheet.getRange(2,1,LastRow).getValues().flat().filter(String); // A列の2行目から最終行までのIDを取得する（空白除外）

  var baseUrl = "https://trocco.io/api/pipeline_definitions/"; // APIのベースURL
  var headers = {
    "Authorization": apiKey,
    'accept' : ""
  };

  ids.forEach(function(id, index) {
    var url = baseUrl + id; // ID を URL に埋め込む

    try {
      var options = {
        method: "delete",
        headers: headers,
        muteHttpExceptions: true
      };

     var response = UrlFetchApp.fetch(url, options);
var statusCode = response.getResponseCode();
var result = statusCode >= 200 && statusCode < 300 ? "成功" : "失敗";

Logger.log("ID " + id + " の削除: " + result);

} catch (e) {
Logger.log("ID " + id + " でエラー発生: " + e.message);
}
  });
}

実行してみてもエラーが発生する場合は、気になる部分に印をつけることでデバックを行うことができます。下記のようにつけてデバックを行うと、変数にどのような値が展開されているかが確認できました。

GASではメニューの トリガー から、実行スケジュールを設定することができます。
TROCCOのワークフローのスケジュールより後に実行されるように設定すると、リストの更新から削除までスムーズに進められます

これで全部の作業はおわりです！お疲れ様でした

おわりに

不要な設定の削除について、はじめはワークフロー削除のAPIもTROCCOのワークフローに組み込めると思っていましたが実際設定し始めるとHTTPメソッドのプルダウンにDELETEがないことに気づきました。
これまでDELETEのAPIを使ったことがなかったので気にしたことがなく勉強になりました。

TROCCO API をTROCCOで使う場合や、変数で指定するときにうまくAPIキーを設定できない事が多かったです。
APIキーと一緒に Token の文字を含める必要があるのですが、これの前に半角スペースを入れる必要があるようで要注意ポイントだな、と思いました。

GASの存在は知らない状態でのスタートでした。Pythonでどうにかなるか…？などいろいろ考えましたがスケジュールで定期的に実行する部分の設定が難しそうだなと感じていました。
GASではスケジュール部分をGUIで簡単に設定できて良かったです。
また、GASでは普通に実行したときに細かいログが確認できずエラーの解消に手間取りました。デバック機能の使い方がわかってからはスムーズにエラーの解消ができました。

TROCCOの検証環境の整備を始めるまで、コードを書いたことがなかったので自動化なんかできないと思っていましたが実際やってみるとGAS部分の設定は1日でできました！
いい感じにできて嬉しいです。