こんにちは｡普段､trocco®︎カスタマーサクセスをしているものです｡
今回はtrocco®︎ユーザーの方向けに､転送元trocco®︎から抽出できるデータを用いたテーブル作成の例をお送りします｡
転送元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の方いましたら､是非教えてください!!!!