Zenn
DATUM STUDIOPublicationへの投稿
🤖

TROCCO で TROCCO のユーザー棚卸を自動化する

あれ
に公開
API
Snowflake
TROCCO
tech

はじめに

ひたすら楽して棚卸したい。

TROCCO のユーザー棚卸をできるだけ楽に手早くやりたい。だって棚卸をやってる時間で TROCCO をもっと触りたいんですもん。あと、手作業でユーザー一覧を作ったら1ユーザーだけ漏れていました！とか、変にオペミスするのも嫌ですよね。

で、外部のサービスでもユーザー棚卸の自動化ができるかもなのですが、TROCCO って TROCCO 自身で転送を自動実行できるじゃないですか。
せっかくなので TROCCO 自身の機能でやってみましょう！

ということで、TROCCO の転送機能を使って TROCCO のユーザー棚卸を自動化してみます。

やること

  • TROCCO ユーザー情報 → どっかへの転送設定を作成
    • 「転送元：HTTP(S)」でTROCCO API にあるユーザー一覧取得を実行 [1]
    • 転送先：どっか転送していい場所に転送
      • 今回は勝手知ったる Snowflake を利用します
      • Snowflake なので、半構造化データの加工を Snowflake でやっていますが、半構造化データを処理できない転送先の場合は TROCCO 側で処理が必要ですね
  • 上の転送設定をスケジュール実行する
    • 実行頻度はよしなに
  • 必要に応じて棚卸の記載場所（Excelなりスプシなり）に転記してくれよな

やってみる

TROCCO API KEY作成

「外部連携」>「TROCCO API KEY」から

「新規作成」で API KEY 作成。表示されているとおり、必ず API KEY を控えておきましょう。

動作確認

ひとまず手元で実行してみる

こんな感じ。

❯ curl --request GET \
  --url 'https://trocco.io/api/users?limit=50' \
  --header 'Authorization: Token {{API KEY}}' \
  --header 'accept: application/json' | jq .
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  2964  100  2964    0     0  33088      0 --:--:-- --:--:-- --:--:-- 33303
{
  "items": [
    {
      "id": 9477,
      "email": "<メールアドレス>",
      "role": "admin",
      "can_use_audit_log": false,
      "is_restricted_connection_modify": false,
      "last_sign_in_at": "2025-04-01T01:26:08.000+09:00",
      "created_at": "2024-10-17T19:44:04.000+09:00",
      "updated_at": "2025-04-03T14:46:30.000+09:00"
    },
    :（以下省略）

転送設定（ユーザー一覧）

HTTP・HTTPS → Snowflake 転送設定を作成します。

転送元 HTTP・HTTPSの設定

基本設定はこんな感じに。

項目 値
OAuth 2.0 利用しない（チェックなし）
URL https://trocco.io/api/users?limit=50
limit は1回で取得するユーザー数
HTTPメソッド GET
入力ファイル形式 JSONPath
JSONPath
（入力ファイル形式が「JSONPath」のとき）		 $['items']
ページング設定 カーソルベース
レスポンスデータに含まれるカーソルへのパス（JSONPath記法） $['next_cursor']
リクエスト時にカーソルをセットするパラメータ名 cursor

HTTPヘッダ

キー 値
Authorization Token {{API KEY}}
※{{API KEY}}は先に作成した TROCCO API KEY に置き換え
※保存時は「マスキング」を有効にしておきましょう
accept application/json

ページング設定の動作確認

ユーザー一覧が2ページ以上にわたる状態で実行してみるテスト

試しに API のリクエストパラメータで limit=10 にして実行してみると、3行目・4行目のあたりで、複数ページを取得していることが確認できます。

：（中略）
2025-04-04 05:07:51.427 +0000 [INFO] (0013:task-0000): {speedometer: {active: 0, total: 0.0b, sec: 0.00, speed: 0.0b/s, records: 0, record-speed: 0/s}}
2025-04-04 05:07:51.467 +0000 [INFO] (0013:task-0000): GET "https://trocco.io/api/users?limit=10"
2025-04-04 05:07:51.658 +0000 [INFO] (0013:task-0000): GET "https://trocco.io/api/users?limit=10&cursor=eyJpZCI6MTA5Nzl9"
2025-04-04 05:07:51.715 +0000 [INFO] (0013:task-0000): JSONPath = $['items']
2025-04-04 05:07:51.862 +0000 [INFO] (pool-4-thread-1): TransactionIsolation=unknown
2025-04-04 05:07:51.862 +0000 [INFO] (pool-4-thread-1): Uploading file id embulk_snowflake_QrODIigd to Snowflake (494 bytes 11 rows)
2025-04-04 05:07:52.274 +0000 [INFO] (pool-4-thread-1): Uploaded file embulk_snowflake_QrODIigd (0.51 seconds)
2025-04-04 05:07:52.358 +0000 [INFO] (pool-4-thread-2): TransactionIsolation=unknown
2025-04-04 05:07:52.358 +0000 [INFO] (pool-4-thread-2): Running COPY from file embulk_snowflake_QrODIigd
2025-04-04 05:07:53.398 +0000 [INFO] (pool-4-thread-2): Loaded file embulk_snowflake_QrODIigd (1.04 seconds for COPY)
2025-04-04 05:07:53.457 +0000 [INFO] (0013:task-0000): Loaded 1 files.
2025-04-04 05:07:53.458 +0000 [INFO] (0013:task-0000): {speedometer: {active: 0, total: 1.6kb, sec: 2.03, speed: 800b/s, records: 11, record-speed: 5/s}}
2025-04-04 05:07:53.547 +0000 [INFO] (pool-5-thread-1): TransactionIsolation=unknown
2025-04-04 05:07:53.547 +0000 [INFO] (pool-5-thread-1): Uploading file id embulk_snowflake_wtK3Nili to Snowflake (20 bytes 0 rows)
2025-04-04 05:07:53.726 +0000 [INFO] (pool-5-thread-1): Uploaded file embulk_snowflake_wtK3Nili (0.27 seconds)
2025-04-04 05:07:53.797 +0000 [INFO] (pool-5-thread-2): TransactionIsolation=unknown
2025-04-04 05:07:53.797 +0000 [INFO] (pool-5-thread-2): Running COPY from file embulk_snowflake_wtK3Nili
2025-04-04 05:07:54.635 +0000 [INFO] (pool-5-thread-2): Loaded file embulk_snowflake_wtK3Nili (0.84 seconds for COPY)
2025-04-04 05:07:54.681 +0000 [INFO] (0013:task-0000): Loaded 1 files.
2025-04-04 05:07:54.907 +0000 [INFO] (0001:transaction): {done:  1 / 1, running: 0}
：（中略）

転送先 Snowflakeの設定

こちらは転送できれば何でもよかったりします。今回はこうしておく。

項目 値
ウェアハウス M_KAJIYA_WH
データベース M_KAJIYA_DB
スキーマ PUBLIC
テーブル TROCCO_API_USERS
更新 追記（TRUNCATE INSERT）（お好み）

データプレビュー・詳細設定

カラム名と型はお好みで。今回はそのまま。

で、保存します。

連携実行

実行します。成功したので Snowflake を見に行きます。


連携されています。

ワークフロー設定（ユーザー一覧＋チーム一覧＋チーム詳細）

ユーザー情報のほかにチーム情報も欲しいですね。
せっかくだからワークフロー化します。

チーム一覧

チーム一覧取得の連携設定はこんな感じ。

チーム一覧取得（yaml）
---
id: 328601
name: kajiya_test_troccoapi_teams_snowflake
description: TROCCO API → Snowflake に転送
user:
  id: 9477
  email: <ユーザー名>
transfer_option:
  transfer_type: normal
  in:
    type: http
    url: https://trocco.io/api/teams?limit=50
    method: GET
    user_agent: ''
    charset: UTF-8
    open_timeout: 2000
    read_timeout: 10000
    max_retries: 5
    retry_interval: 10000
    request_interval: 0
    input_direct: true
    success_codes:
    - '200'
    request_headers:
      Authorization: "*****"
      accept: application/json
    params: []
    cursor:
      request_parameter_cursor_name: next_cursor
      response_parameter_cursor_json_path: "$['next_cursor']"
    custom_variables: []
    parser:
      type: jsonpath
      root: "$['items']"
      default_timezone: UTC
      columns:
      - name: id
        type: long
      - name: name
        type: string
      - name: description
        type: string
      - name: created_at
        type: timestamp
        format: "%Y-%m-%dT%H:%M:%S.%L%:z"
      - name: updated_at
        type: timestamp
        format: "%Y-%m-%dT%H:%M:%S.%L%:z"
    decoders: []
  out:
    type: snowflake
    snowflake_connection:
      id: 1233
      name: test_kajiya
    warehouse: M_KAJIYA_WH
    database: M_KAJIYA_DB
    schema: PUBLIC
    table: TROCCO_API_TEAMS
    retry_limit: 12
    retry_wait: 1000
    max_retry_wait: 1800000
    mode: truncate_insert
    default_timezone: UTC
    column_options:
    empty_field_as_null: true
    batch_size: 50
    delete_stage_on_error: false
    custom_variables: []
  data_settings:
    columns:
    - name: id
      src: id
      default: ''
      type: long
    - name: name
      src: name
      default: ''
      type: string
    - name: description
      src: description
      default: ''
      type: string
    - name: created_at
      src: created_at
      default: ''
      type: timestamp
      format: "%Y-%m-%dT%H:%M:%S.%L%:z"
    - name: updated_at
      src: updated_at
      default: ''
      type: timestamp
      format: "%Y-%m-%dT%H:%M:%S.%L%:z"
  is_runnable_concurrently: false
  retry_limit: 0
labels: []
job_schedules: []
notifications: []

チーム詳細

次に チーム詳細取得 の連携設定を作成します。

チーム詳細取得（yaml）

$teaam_id$ はワークフローのカスタム変数ループで使用。ループ実行するので、転送モードは既存レコードを削除しないよう UPSERT (MERGE) （マージキー： ID（チームごとに割り当てられるID）にしてみる。

---
id: 328653
name: kajiya_test_troccoapi_desc_team_snowflake
description: TROCCO API → Snowflake に転送
user:
  id: 9477
  email: <ユーザー名>
transfer_option:
  transfer_type: normal
  in:
    type: http
    url: https://trocco.io/api/teams/$teaam_id$
    method: GET
    user_agent: ''
    charset: UTF-8
    open_timeout: 2000
    read_timeout: 10000
    max_retries: 5
    retry_interval: 10000
    request_interval: 0
    input_direct: true
    success_codes:
    - '200'
    request_headers:
      Authorization: "*****"
      accept: application/json
    params: []
    cursor:
      request_parameter_cursor_name: cursor
      response_parameter_cursor_json_path: "$['next_cursor']"
    custom_variables:
    - type: string
      name: "$teaam_id$"
      value: '1288'
    parser:
      type: jsonpath
      root: "$"
      default_timezone: UTC
      columns:
      - name: id
        type: long
      - name: name
        type: string
      - name: description
        type: string
      - name: created_at
        type: timestamp
        format: "%Y-%m-%dT%H:%M:%S.%L%:z"
      - name: updated_at
        type: timestamp
        format: "%Y-%m-%dT%H:%M:%S.%L%:z"
      - name: members
        type: json
    decoders: []
  out:
    type: snowflake
    snowflake_connection:
      id: 1233
      name: test_kajiya
    warehouse: M_KAJIYA_WH
    database: M_KAJIYA_DB
    schema: PUBLIC
    table: TROCCO_API_TEAM_DETAIL
    retry_limit: 12
    retry_wait: 1000
    max_retry_wait: 1800000
    mode: merge
    default_timezone: UTC
    column_options:
    empty_field_as_null: true
    batch_size: 50
    delete_stage_on_error: false
    merge_keys:
    - id
    custom_variables: []
  data_settings:
    columns:
    - name: id
      src: id
      default: ''
      type: long
    - name: name
      src: name
      default: ''
      type: string
    - name: description
      src: description
      default: ''
      type: string
    - name: created_at
      src: created_at
      default: ''
      type: timestamp
      format: "%Y-%m-%dT%H:%M:%S.%L%:z"
    - name: updated_at
      src: updated_at
      default: ''
      type: timestamp
      format: "%Y-%m-%dT%H:%M:%S.%L%:z"
    - name: members
      src: members
      default: ''
      type: json
  is_runnable_concurrently: false
  retry_limit: 0
labels: []
job_schedules: []
notifications: []

ワークフローはこのようにしてみる。

カスタム変数ループは、「チーム一覧取得」で取得したチーム一覧から ID を取得してループするように設定。

Snowflake 側で、ユーザー一覧・チーム一覧・チーム詳細を結合して集計するビューを作成しておく。

ビュー
create or replace view M_KAJIYA_DB.PUBLIC.TROCCO_USERS
as
with flatten_team as (
    select
        "id" as team_id,
        m.value:email as email,
        m.value:role as role,
        m.value:user_id as user_id,
    from
        M_KAJIYA_DB.PUBLIC.TROCCO_API_TEAM_DETAIL as d
        , LATERAL FLATTEN(
            INPUT => PARSE_JSON(d."members")
        ) as m
),
users as (
    select
        "id" as user_id
        , "email" as emali
        , "role" as role
        , "created_at" as created_at
    from
        M_KAJIYA_DB.PUBLIC.TROCCO_API_USERS
),
teams as (
    select
        "id" as team_id
        , "name" as name
    from
        M_KAJIYA_DB.PUBLIC.TROCCO_API_TEAMS
),
final as (
    select
        users.emali as "メールアドレス"
        , users.role as "権限"
        , LISTAGG(DISTINCT teams.name, '/') as "チーム"
        , users.created_at as "作成日"
    from users
    left outer join
        flatten_team
    on
        users.user_id = flatten_team.user_id
    left outer join
        teams
    on
        flatten_team.team_id = teams.team_id
    group by
        users.emali
        , users.role
        , users.created_at
)
select * from final;

結果

こんな感じで、各ユーザーの権限・所属チームが取得できます。

あとはこのワークフローを定期実行すればOKですね！（今回は省略）

おわりに

この記事では TROCCO のユーザー棚卸を自動化する方法の一例をご紹介しました。

もしかしてこれって、他のシステムのユーザー棚卸も自動化できたりしますよね...？ということで、他にも TROCCO を利用して楽をする方法を思いついたら、やってみようと思います。

脚注

  1. 「転送元：TROCCO」もあるけど、ユーザー一覧は対象外なので TROCCO API を使います ↩︎

DATUM STUDIO
DATUM STUDIOPublication

DATUM STUDIOはデータとテクノロジーを活用し、クライアントのビジネスに持続的な成長と新たな可能性をもたらす会社です。 ツール導入や分析コンサルティングなどを通じて支援します。 特にデータマイニング領域では、独自アルゴリズムの設計やカスタマイズを強みとし、課題やデータ構造に応じたアプローチで価値を創出します。

Discussion