Metabaseの質問(Question)での変数(Variable)の使い方や、ダッシュボードからのフィルター方法について、日本語だとあまり情報を見つけられなかったのでメモ。
環境
Metabase v0.33.2。日本語化されている前提。
導入時よりバージョンを上げた(docker-compose内のバージョンを上げただけ)。
変数の概要
質問の種類がネイティブクエリでないと、変数は使えない模様。
画面上部の「照会する」>「ネイティブクエリ」を選択するか、設定アイコン左の「SQLを書く」アイコンからSQL記述画面を開く。
ネイティブクエリ記述欄の「χ」アイコンが変数のヘルプ。公式ページはこちら。
SQL内に {{変数名}} を記述すると、それが変数となり、画面右に変数の設定が、画面上に変数の値の入力欄(変数フィルター)が表示される。
変数フィルターの順序は記述順(変数の設定の並び順)となり、変数を削除して再作成しない限り、並び替えはできない模様。変数フィルターをドラッグアンドドロップすると動かせるが、変わらない?
変数の設定項目
フィルターラベル
変数フィルターのキャプションを設定できる。
デフォルトでは「値を入力する...」や「選択...」などが使用されるため、分かりやすい名称を付けておくと使いやすい。
値タイプ
変数の型。現在は4種類選択可能。
テキストは文字列、番号は数値、日付は日時。
フィールドフィルターは特殊。質問にネイティブクエリ以外を利用した際に設定できる、テーブルの列に対するフィルターを作成する。
変数は、SQLとして有効なフォーマットで展開される模様。たとえば、値タイプが文字列であれば、シングルクォーテーションで囲まれて展開される。
マップするためのフィールド
フィールドフィルターを選択すると表示される項目。
任意のテーブル列を選択し、フィールドフィルターとマッピングする。
他の値タイプの変数はWHERE句以外でも使用できるが、フィールドフィルターはここで選択した列名を含むSQLが生成されるため、通常はWHERE句内で使用する。
フィルターウィジェットタイプ
フィールドフィルター以外はNoneで固定の模様。
フィールドフィルターの場合、「マップするためのフィールド」で選択した列が有効であればフィルターウィジェットタイプを選択可能。無効な列の場合、 There aren't any filter widgets for this type of field yet. および公式サイトへのリンクが表示される。
リンク先を見ればわかるが、フィールドフィルターを使うには、特定の列のタイプを設定しておく必要がある模様。ここでの列のタイプとは、データベースの列の型ではなく、Metabaseのデータモデルの列のタイプのこと。このあたり、少々ややこしい。
None以外を選択できない場合、管理者画面のデータモデルから列のタイプを変更するか、フィールドフィルター以外の値タイプに変更する。
変数設定の必須/任意
変数を記述した部分を [[ ～ ]] で囲むと、その変数の値が未設定ならばその行は解釈されなくなる。主にWHERE句で使用する。
通常は1行1変数で記述するが、複数の変数を記述してもよしなに解釈してくれる模様。
例えば、文字列で年月日が設定されている残念な列に対し、文字列タイプの変数を3つ定義して string_date LIKE CONCAT('', {{year}}, {{month}} [[, {{day}}]], '%') のように使うことで、年月または年月日で検索、といった記述もできる。
任意の変数利用時の注意
WHERE句の条件をすべて任意の変数とする場合、WHERE句の先頭に 1 = 1 や true を記述し、変数未設定でもSQLが実行できるようにしておく。
ただし、変数がすべてフィールドフィルターの場合、自動で 1=1 が付与されるらしく、設定しなくても動く。
変数の利用サンプル
質問の作成
サンプルDBのORDERSテーブルに対して、以下のSQLをネイティブクエリで記述。
select * from ORDERS where {{CREATED_AT}} [[and {{USER_ID}}]] [[and TOTAL >= {{TOTAL_MIN}}]] [[and TOTAL <= {{TOTAL_MAX}}]] order by CREATED_AT , USER_ID
それぞれの変数は以下のように設定。
- CREATED_AT: フィールドフィルター, ORDERD.CREATED_AT, 日付フィルター, 必要, デフォルト値は現在の月
- USER_ID: フィールドフィルター, ORDERD.USER_ID, ID, 任意, デフォルト値なし
- TOTAL_MIN, TOTAL_MAX: 番号, None, 任意, デフォルト値なし
右上の「保存」から、名前を付けて保存しておく。
ダッシュボードへの質問の追加
セットアップ直後だとダッシュボードがないので、画面上部のプラスアイコンから「新しいダッシュボード」で適当に作成しておく。
質問を追加したいダッシュボードを開き、鉛筆アイコンから編集画面を開く。
漏斗アイコンからフィルターを追加できるので、「時間」>「全オプション」(日付フィルター相当)と「ID」3つを追加。
続いて、漏斗アイコン左のプラスアイコンから、先ほど作成した質問を追加。
追加したフィルターの「編集」をクリックすると、質問の中央にセレクトボックスが表示される。クリックすると、編集中のフィルターに対応した変数を選択できる。
また、フィルターの編集で、キャプションやデフォルト値を設定できる。デフォルト値を設定すると、質問の変数のデフォルト値は、ダッシュボードのフィルターのデフォルト値で上書きされる。
日付フィルターはCREATED_ATに、ID3つはUSER_ID, TOTAL_MIN, TOTAL_MAXにそれぞれマッピングして、画面上部の「完了」ボタンで保存。「保存」ボタンでダッシュボードも保存する。
あとはダッシュボードを開き、フィルターを変更することで、ダッシュボード内の検索対象を変更できる。
今回はテーブル形式の質問1つだけだが、実際の運用では複数のグラフ形式の質問を同じフィルターに割り当て、フィルターの値を変更してグラフィカルにデータを確認している。
質問やダッシュボードのショートカット作成
作成したダッシュボードや質問は、一覧から「このアイテムを固定する」を選択すると、トップ画面に固定できる。
質問は直接開くと、検索結果右下の下矢印アイコンから検索結果をCSVやXLSX、JSONでダウンロードできる。ダッシュボードに追加する質問は基本グラフ形式にし、別途Excelなどで集計したい質問はテーブル形式で作成し、直接トップに固定しておくとよさげ。
ダッシュボードからのファイルダウンロードも、issueとしては上がっていた。
振り返り
フィールドフィルターを使うと、管理者画面のデータモデルから列のタイプを指定することで、かなり細かく設定できる模様。ただ、数が多すぎるので、使いこなすのは大変そう。
複数のフィルターをまとめたセグメントや、検索結果をテーブルのように扱え、再度フィルターを適応できるメトリクスなんて機能もある模様。
それと、地味に気になったのが、SQLエディタの入力保管が予約語は小文字、それ以外は大文字になるところ。普段は逆で記述する派なので、調整できないかな...
