可視化ツールのMetabaseでMongoDBクエリを書く
MongoDBに保存しているデータを可視化BIツールMetabaseでグラフ化するのを試してみました。
その際にMongoDBのQueryに苦戦したので、得られた知見を残しておきます。
この記事はMetabaseの説明はあまりなく、MongoDBクエリの説明が中心です。C#でのMongoDB操作もよかったらみてください。
基本構造
Metabase上でMongoDBのクエリーを書くときは次のような基本形に落ち着きました。
[
{$project: {
_id: 0
datetime: 1
type: 1
}}
{$match: {{target_datetime}} }
{$group: {
_id: "$type"
mode: "$type"
count: {$sum: 1}
}}
{$sort: {
mode:1
}}
]
MongoDBをそもそも使いこなした事がなくて、$projectも知らなかったです。
まずQueryは[配列]で囲む必要があります。MongoDBのaggregate（統計）というもののようです。
これまではfindくらいしか使ってないので、最初の一歩が凄い時間かかりました。
Metabase上だとjsonの区切りカンマを書かなくても良かったので省略するようにしました。
これは後述するMetabaseのフィルター機能を書くときに見やすくなるからです。
あと文字列をクォテーションで囲むかどうかはよく分かりません。
通常だと囲まなかったらエラーになった気もしますが、Metabase上だと大丈夫だったのでValue側だけ囲むようにしてみました。
同じくよくわかってないのは値側に出る$始まりのフィールド名です。
おそらく参照なんだろうと思ってますが、どうなんでしょうか。
本格的にMongoDBを使うことになったら再度勉強したいです。
このブログを書きながら思ったのですが、実はjsonじゃなくてyamlだったりするんだろうか。
カンマやクォーテーションがなくても良いところがそんな気にさせました。
$project
$projectは多分projection（投影）のことと思います。
ドキュメントから必要なフィールドを取り出します。
フィールド名: 1とすると取り出せますが、_idは自動で取り出されるので_id: 0として非表示にしています。
また階層のあるフィールドをフラットにすることもでき
新規フィールド名: {getField: {input: "$親フィールド", field: "取り出すフィールド"}}
と書きます。これはversion 5.0以降で使えるようになった機能でした。
version 4.4だと使えなかったので試したら、
新規フィールド名: "$親フィールド.取り出すフィールド"
とシンプルにかけました。
似たようなものでフィールド名を書き換えるエイリアスもできます。
alias: {$concat: "Original"}
でした。ググったときはalias: "$Original"というのもできそうなんですが、エラーになったのでconcat使ってます。
(使える時もある、不思議)
このように関数(?)も使えて
新フィールド名: {$dateToString: {date: "$ISO日時フィールド", format: "%Y-%m-%d"}}
のように変換することも可能です。
あと、制御構造も書けました。
ifとswitchを使ってます。
ここのを全部適当に混ぜるとこんな感じ。
{"_id": 100, "fieldB": "param1", "fieldDatetime": "2021-10-01T12:34:56", "fieldI": 1, "fieldS": 1}
{"_id": 101, "fieldB": "param1", "fieldDatetime": "2021-10-01T12:34:56", "fieldI": 1, "fieldS": 2}
{"_id": 102, "fieldB": "param1", "fieldDatetime": "2021-10-01T12:34:56", "fieldI": 1, "fieldS": 2}
{"_id": 103, "fieldB": "param1", "fieldDatetime": "2021-10-01T12:34:56", "fieldI": 0, "fieldS": 2}
{"_id": 104, "fieldB": "param2", "fieldDatetime": "2021-10-02T12:34:56", "fieldI": 0, "fieldS": 1}
{"_id": 105, "fieldB": "param2", "fieldDatetime": "2021-10-02T12:34:56", "fieldI": 0, "fieldS": 1}
{"_id": 106, "fieldB": "param2", "fieldDatetime": "2021-10-02T12:34:56", "fieldI": 2, "fieldS": 1}
{"_id": 107, "fieldB": "param2", "fieldDatetime": "2021-10-03T12:34:56", "fieldI": 2, "fieldS": 1}
{"_id": 108, "fieldB": "param1", "fieldDatetime": "2021-10-03T12:34:56", "fieldI": 2, "fieldS": 1}
{"_id": 109, "fieldB": "param1", "fieldDatetime": "2021-10-03T12:34:56", "fieldI": 2, "fieldS": 2}
:
これを
[
#コメント部分は削除が必要と思います。
{$project: {
_id: 0 # _idの自動取り出しをやめるときは0、通常の指定取り出しは1
field_b: {$concat: "$fieldB"} # 別名で取り出し
fieldDate: {$dateToString: {date: "fieldDatetime", format: "%Y-%m-%d"}} # 日時から日付部分だけ取り出すとか
field_if: {
$cond: { # 条件式使用宣言
if: { # if文
$eq: ["$fieldI", 0]
}
then: 0
else: "FAILURE"
}
}
fieldStr: {
$switch: { # swtich文
branches: [ # 分岐宣言
{ case: {$eq: ["$fieldS", 1]}, then: "CaseA"} # case
{ case: {$eq: ["$fieldS", 2]}, then: "CaseB"}
]
default: "CaseDef" # その他
}
}
}}
]
こうして
#日時が日付だけになり、数値を文字列化
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "SUCCESS", "fieldStr": "CaseB"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "SUCCESS", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "SUCCESS", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseB"}
:
こうじゃ。
$match
$matchは取り出し条件を指定します。
この部分はこれまで使ったことのあるfindと同じような指定です。
ハマったのはMetabase側の機能で変数が使えて結果をフィルターできます。
{{変数名}}と波括弧２重で囲むと変数になります。
未指定時には[[〜]]で囲っておけば消えるのを利用して、SQLのコメントを使ってデフォルト値をQuery側に埋め込むことも可能なようです。
[[{{未指定変数}} #]] defaultValue #が行コメント開始の場合こんな感じになります。
Metabase側の機能でデフォルト値を指定することも可能なので、今回使ってません。
というより、ダッシュボードでフィルターすることを考えるとデフォルト値はない方が分かりやすいと思ってます。
また変数の中でもField Filterタイプが便利ですが、少し特殊です。
Field Filterタイプの場合、Metabase側が条件式も含めた文字列を生成してくれるため、単独の$matchにする必要があります。
未指定時に変になりそうですが、付近の$matchも一緒に消してくれるので大丈夫です。
さらにField Filter時に指定したフィールドと大文字小文字も一致するフィールドに対して条件を適用するのも注意が必要です。
例えば通常フィールド名をnameとしてるけど、フィルターに使ったデータはUI用に使っていてNameと違っているときにはまりました。（まぁこれは少し考えれば納得です。条件式の対象フィールドが自動挿入されるのでそういうものだと理解しました。）
あとMetabaseにはQueryのスニペットを作る機能があります。
複数のQueryで使いまわす共通の文を登録しておくと、後で一括で変更することもできて便利そうです。
が、スニペットに変数を入れていても正しく認識してくれません（将来的には対応されるかなぁ）。
ダッシュボードから共通で使うフィルターをスニペットにしたかった。。。
使った$matchをまとめると
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "SUCCESS", "fieldStr": "CaseB"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "SUCCESS", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "SUCCESS", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseB"}
:
これを
[
{$match: {{target_date}} } # Field Filterの相対日付とか
{$match: { # 2つ以上$matchが使える
[["field_if" : {$regex: {{if_regex}} } ]] # 正規表現に使う文字列を変数とする
# 未指定時は[[〜]]がコメントアウトされて空の$matchになるが大丈夫
}}
]
こうして
# field_ifのif_regexに'FAILURE'を指定したとして
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseB"}
:
こうじゃ。
$groupと$sort、$limit、$sample
$groupはデータ集計時に使用します。
_idで指定したフィールドを基準に集計し、$sumとかで個数をカウントする事ができます。
$firstや$lastを使えば、集計されたデータから一つだけ取り出すこともできますし、
$pushを使えば、集計される全行のデータを保持することも可能です。
集計後の処理で良く使う並び替えや上位のみ取得もできます。
並び替えは$sortを使い、昇順は1で降順は-1を指定します。
上位のみ取得は降順でソート後に$limitを使えば可能です。
また$unwindコマンドで１行を複数行に展開することもできます。
$pushと組み合わせて使用しました。
あと$sampleコマンドというのがあり、適当に指定した数だけデータを取り出してくれます。
テストするときに最初に{$sample: {size: 10}}とかしておくと数が絞られて負荷軽減になると思います。
（ランダムなので、たまたま$matchに一致しないのばかり抽出されて結果が無しになることもある）
こんな感じで使いました。
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseB"}
:
これを
[
{$group: {
_id: { # 集計基準のフィールドを指定
uniqDate: "$fieldDate"
uniqB: "$field_b"
}
total_count: {$sum: 1} # 集計された合計数
items: {$push: {fieldS: "$fieldStr"}} # itemsフィールドに全fieldSを格納
}}
{$sort: {"total_count": -1}} # 個数を降順
{$limit: 5} # Top 5
{$unwind: "$items"} # 集計されたitemsを展開(全fieldSが再度別行に分解される)
{$project: {
_id: 0
fieldDate: {$getField: {field: "uniqDate", input: "$_id"}}
fieldStr: {$getField: {field: "fieldS", input: "$items"}} # フラット化
total_count: "$total_count"
}}
{$group: {
# また別の集計...
# １回目は積み重ねグラフの並び順を綺麗にするため
# ここで本来の集計して、表示
}}
]
こうして
# 2つ目の$project後のデータ。total_countでソートされ上位のみになってる
{"fieldDate": "2021-10-01", "fieldStr": "CaseA", "total_count": 3}
{"fieldDate": "2021-10-01", "fieldStr": "CaseB", "total_count": 3}
{"fieldDate": "2021-10-01", "fieldStr": "CaseB", "total_count": 3}
{"fieldDate": "2021-10-03", "fieldStr": "CaseB", "total_count": 2}
{"fieldDate": "2021-10-03", "fieldStr": "CaseA", "total_count": 2}
こうじゃ。
$addFields
$addFieldsを使うと$projectせずにフィールドを追加できます。
先の例を拡張してみるとこんな感じです。
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseB"}
:
これを
[
{$group: {
_id: { # 集計基準のフィールドを指定
uniqDate: "$fieldDate"
uniqB: "$field_b"
}
total_count: {$sum: 1} # 集計された合計数
items: {$push: {fieldS: "$fieldStr"}} # itemsフィールドに全fieldSを格納
}}
{$sort: {"total_count": -1}} # 個数を降順
{$limit: 5} # Top 5
{$unwind: "$items"} # 集計されたitemsを展開(全fieldSが再度別行に分解される)
{$project: {
_id: 0
fieldDate: {$getField: {field: "uniqDate", input: "$_id"}}
fieldStr: {$getField: {field: "fieldS", input: "$items"}} # フラット化
total_count: "$total_count"
}}
{$group: {
# また別の集計...
# １回目は積み重ねグラフの並び順を綺麗にするため
# ここで本来の集計して、表示
}}
{$addFields: {
newField: {$condat: ["$fieldStr", " @ ", "$total_count"]}
}}
]
こうして
# newFieldが追加されている
{"fieldDate": "2021-10-01", "fieldStr": "CaseA", "total_count": 3, "newField": "CaseA @ 3"}
{"fieldDate": "2021-10-01", "fieldStr": "CaseB", "total_count": 3, "newField": "CaseB @ 3"}
{"fieldDate": "2021-10-01", "fieldStr": "CaseB", "total_count": 3, "newField": "CaseB @ 3"}
{"fieldDate": "2021-10-03", "fieldStr": "CaseB", "total_count": 2, "newField": "CaseB @ 2"}
{"fieldDate": "2021-10-03", "fieldStr": "CaseA", "total_count": 2, "newField": "CaseA @ 2"}
こうじゃ。
$unionWith
$unionWithを使うと他のコレクションと和集合ができます。
例えば、集計結果を表示するときに、抜け番を作りたくない時に使いました。
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param2", "fieldDate": "2021-10-02", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param2", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-03", "field_if": "FAILURE", "fieldStr": "CaseB"}
:
これを
[{ $match: { "fieldDate": { $lte: { $date: "2021-10-01" } } } }]
こうして
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
こうじゃろ？
ここに別のコレクションがあるじゃろ？
{"field_b": "param1"}
{"field_b": "param2"}
{"field_b": "param3"}
これを
[
{$match: {
"fieldDate": {$lte: {$date: "2021-10-01"}}
}}
{$unionWith: {
coll: "other" # 別のコレクション名
pipeline: [ # pipeline
{$project: { # $matchや$groupなども書ける
_id: 0
field_b: 1
}}
]
}}
]
こうして
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseA"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1", "fieldDate": "2021-10-01", "field_if": "FAILURE", "fieldStr": "CaseB"}
{"field_b": "param1"}
{"field_b": "param2"}
{"field_b": "param3"}
こうじゃ。
Discussion