転送元trocco®︎とは

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

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

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

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

今回の要件

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

今回やること

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

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

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

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

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

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


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を使って時・分・秒のそれぞれのパートに切り出すって感じ｡

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

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

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

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

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

先月､どのデータマート定義が時間がかかっているのかを確認できるテーブルができました
データマート定義のうち､どのクエリがパフォチューのしがいがあるのか､あたりをつけるのが楽になりそうでいい感じ｡

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

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

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

おしまい!!

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

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

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

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