Metabaseの利用人数をレポートする方法

この記事を読んでわかること

Metabaseの利用者数を出すためのクエリ

背景

Metabaseを利用している際に「このBIどのくらいの人に使われているんだろう・・・？」って思ったことはありませんか？自分はあります。案外使われているのか、どこの部署に使われていて、どこの部署には使われていないのか理解することでBIの改善に関しても考えやすく、改善効果を評価できるようにもなりそうです。

やり方

Metabaseはクエリの実行ログに特殊なコメントを付けてくれる

Metabaseは実行時に以下のようなコメントをSQLにつけて実行してくれます。
そのコメント内にはMetabase上のUserID情報が付与されます（以下の例だとXXとなっているところ）

-- Metabase:: userID: XX queryType: native queryHash: d4xxxxxxxxxxxxxxx

正規表現マッチで抜き取りましょう

cast(regexp_extract(query, r"-- Metabase:: userID: ([0-9]*) queryType") as int64) as user_id

creation_timeはUTCなのでJSTに変換

creation_timeはUTCなのと、後ほど日付ごとにまとめるので、DATE型に変換

DATE(creation_time, 'Asia/Tokyo') as date_jst,

"region-"は参照するBigQueryのデータセットがある場所に書き換えること

クエリ履歴に残るSQL情報を参照するにはINFORMATION_SCHEMA.JOBS_BY_PROJECTを参照します。
ただしこのテーブルは特定のアクセス方法があり、リージョンの指定をする必要があります、これをリージョン装飾子と呼びます。リージョン装飾子は以下のような構文で書くことができます。

region-REGION

例えばasia-northeast1だと

`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

リージョン装飾子として使えるデータセットのリージョン名はデータセットのリージョンを確認しながらこちらを参考にしてみると良いと思います。以下のクエリではasia-northeast1にあるデータセットへのクエリ実行を対象にしています。

データセットのロケーション  |  BigQuery  |  Google Cloud

全クエリ

with data as(
select
  DATE(creation_time, 'Asia/Tokyo') as date_jst,
  DATETIME(creation_time, 'Asia/Tokyo') as datetime_jst,
  query,
  cast(regexp_extract(query, r"-- Metabase:: userID: ([0-9]*) queryType") as int64) as user_id
from
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
),
data2 as (
  select
    date_jst,
    user_id
  from data
  group by date_jst, user_id
),
data3 as(
select
  date_jst,
  user_id
from data2
order by date_jst asc
)
, users as(
  select *
  from
    unnest(
    (
      select
      array<struct<user_id INT64,name STRING>>[
        (1,"taro.nihon"),
        (2,"yokoha.kanagawa"),
        (3,"shibuya.tokyo"),
	(4,"fuji.yamanashi")
      ] AS user_datas
    )
  )
),
final as (
  select
    data3.date_jst,
    data3.user_id,
    users.name
  from data3
  join users
  on data3.user_id = users.user_id
)

select
  date_jst,
  user_id,
  name,
  1 as use_flag
from final
where date_jst > (CURRENT_DATE() -30)