【trocco®︎ユーザー向け】転送元trocco®︎ ジョブ実行履歴から日々の運用に役立つテーブルを作る~【データマート定義編】

こんにちは｡普段､trocco®︎カスタマーサクセスをしているものです｡

今回はtrocco®︎ユーザーの方向けに､転送元trocco®︎から抽出できるデータを用いたテーブル作成の例をお送りします｡


# 転送元trocco®︎とは

https://documents.trocco.io/docs/ja/data-source-trocco?highlight=trocco%C2%AE%EF%B8%8E#%E3%83%87%E3%83%BC%E3%82%BF%E3%83%9E%E3%83%BC%E3%83%88%E5%AE%9A%E7%BE%A9

troccoで保持しているデータを転送できる機能です｡
転送元troccoでは、以下のいずれかのデータを転送できます。

- データカタログ
    - データカタログに保持しているメタデータを転送できます。
- 転送設定・データマート定義・ワークフロー定義
    - 過去に実行されたジョブに関する履歴データを転送できます。

今回は転送元trocco®︎を用いてデータマート定義の履歴データをBigQueryに転送し､データマートの実行状況がわかるテーブルを作っていきます!!

# 「どのデータマート定義がどれだけ時間がかかっているのか」がわかるテーブルをつくるよ～

## 今回の要件
先月分のデータマート実行履歴を集計・可視化する!!

## 今回やること

- その1 【転送設定】転送元trocco®︎→BigQueryにデータ転送
- その2 【データマート定義】 「どのデータマート定義がどれだけ時間がかかっているのか」をクエリで書いてみる
- その3 【ワークフロー定義】転送設定とデータマート定義をまとめていい感じに使いやすくする

# その1 【転送設定】転送元trocco®︎→BigQueryにデータ転送

## trocco®︎で転送設定を作成します｡

今回は直近2ヶ月分の履歴データを毎月洗い替えで取得するように設定してみました｡

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/1b29d7fc-6fd4-520d-371a-614cdd9afbef.png)




# その2 【データマート定義】 「どのデータマート定義がどれだけ時間がかかっているのか」をクエリで書いてみる


## こんなクエリを書いてみました～

```sql

WITH
  month_row AS (
  SELECT
    created_at,
    FORMAT_TIMESTAMP("%Y%m",TIMESTAMP_TRUNC(created_at,MONTH)) AS created_at_ym,
    datamart_definition_url,
    finished_at,
    started_at,
    TIMESTAMP_DIFF(finished_at,started_at,SECOND) AS diff_sec,
    executor_type AS type
  FROM
    `primenumber-data-platform.test_miki_0131.trocco_datamart_job`
  WHERE
    status = 'succeeded'
    AND DATE_TRUNC(DATE_SUB(CURRENT_DATE(),INTERVAL 1 month),month) = DATE_TRUNC(DATE(created_at),MONTH)),
  month_summary AS (
  SELECT
    created_at_ym,
    datamart_definition_url,
    SUM(diff_sec) AS sum_diff_sec
  FROM
    month_row
  GROUP BY
    datamart_definition_url,
    created_at_ym )
SELECT
  created_at_ym,
  datamart_definition_url,
  sum_diff_sec,
  CONCAT( EXTRACT(HOUR
    FROM
      MAKE_INTERVAL(SECOND => sum_diff_sec)), '時間', EXTRACT(MINUTE
    FROM
      MAKE_INTERVAL(SECOND => sum_diff_sec)), '分', EXTRACT(SECOND
    FROM
      MAKE_INTERVAL(SECOND => sum_diff_sec)), '秒' ) AS hms
FROM
  month_summary
ORDER BY
  sum_diff_sec desc
```
テーブルの全レコードに対してGROUP BYをかけて､後から年月でフィルタをかける書き方でも良いですし､そっちのほうが楽ではあるのですが
履歴データという性質上レコード数が多いことが想定されるので､あえて最初のwith句で絞ってみました｡


あと､秒から時・分・秒への変換パターンは知っておくと結構便利です｡
秒数を集計した後､EXTRACTとMAKE_INTERVALを使って時・分・秒のそれぞれのパートに切り出すって感じ｡

**こちら↓の記事で知ってからいつもコピペで使いまわしてます｡**

https://qiita.com/k_0120/items/f79c9eb7343b74057810

時分秒は文字列なので､order by は元の秒数でかけましょう｡


## クエリをtrocco®︎のデータマート定義に登録して､実行してみる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/7dc87f1d-04c7-b250-3480-f9e005607ad8.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/de97a11a-a12d-6910-301a-384d7889d459.png)

わ～い～成功した～
（ところでジョブ画面の「設定内容」を押すと､右側にいい便利な情報が出るのみなさん知ってましたか…?）


## BigQuery側のテーブルをみてみる


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/ec91eff4-2073-0cda-1ed1-b9c9aefbe2b5.png)
先月､どのデータマート定義が時間がかかっているのかを確認できるテーブルができました
データマート定義のうち､どのクエリがパフォチューのしがいがあるのか､あたりをつけるのが楽になりそうでいい感じ｡



# その3 【ワークフロー定義】転送設定とデータマート定義をまとめていい感じに使いやすくする

その1､その2で目的は果たせているのですが､日々の運用を考えると
ワークフローで転送設定とデータマート定義をまとめちゃったほうが良いので､そうします｡

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/448441e2-74e2-bfc8-1608-9b6e0b459016.png)

（特に説明はないです）
お好きなタイミングでスケジュール設定をしたり､通知設定を加えたら完了!!



# おしまい!!

いかがでしたでしょうか～｡

転送元trocco®︎から抽出できる各種履歴データは､色々な使い道がありそうですね｡
今回は用途・クエリ共に簡単な内容になってしまいましたが､取得するデータの種類やクエリのがんばり次第ではもっとtrocco®︎運用に役立ちそうなデータがとれそう!!

転送元trocco®︎､「弊社ではこんな使い方してますよ～」というtroccerの方いましたら､是非教えてください!!!!