📝
Metabase のクエリ実行履歴を紐解く

lwma
2024/12/26に公開
metabase
tech
 概要弊社では BI ツールとして Metabase を採用しており、日々サービスデータに対して多様なクエリが実行されています。しかし、これまで Audit ログの管理ができておらず、誰がどのようなクエリをなんの目的で実行しているのか把握できていませんでした。最初は Metabase の Audit Log 機能を利用することを考えましたが、この機能は有償版（Pro または Enterprise 版）でのみ利用可能であることが分かりました。そこで、OSS 版でも実行履歴を取得できる方法がないか調査したので、その結果を以下にまとめます。
今回の目的は、実行された SQL を解析し、機密情報が格納されたフィールドにアクセスしているかを調査することです。そのため、SQL をパースして SELECT されているフィールドを特定する必要がありました。SQL パーサーには Python の sqlglot を採用しましたが、SQL をパースするためには、実行可能で正しい SQL 構文である必要があります。このため、事前に Metabase 独自の構文を変換する必要があり、その実装に苦労したので、その点も含めてまとめてみます。
なお、機密情報が格納されたフィールドに関しては、Metabase に連携しているアプリケーション DB のメタデータに基づいており、詳細な説明は割愛します。また、本記事では、Metabase 独自の構文を含むものを「クエリ」、そのクエリを実行可能な SQL に置換したものを「SQL」と表現します。

 データ構造Metabase のデーターベースの中で、クエリの実行履歴に関わるのは主に以下のようなテーブルです。
core_user
Metabase ユーザーに関するデータ

query
実行したクエリに関するデータ

query_execution
実行したクエリに関するメタデータ

report_card
保存したクエリに関するデータ

native_query_snippet
クエリに設定したスニペットに関するデータ

実行したクエリに関するデータは、query テーブルの query カラムに json 文字列として格納されています。以下はその json 構造の一部です。
{
  "native": {
    "query": "[executed_sql]",
    "template_tags": {
      "[tag_name]": {
        "type": "[text|date|dimension|snippet|card|...]",
        "default": "[default_value]",
        ...
      },
      ...
    }
  },
  "parameters": [
    {
    "value": "[input_value]",
    "target": ["[parmeter_type]", ["template-tag", "[tag_name]"]]
    },
    ...
  ]
}
query キーには、実際に実行されたクエリが格納されていますが、このクエリには Metabase 独自の変数やスニペットなどの特殊な構文が含まれています。設定された変数やスニペットの定義は、template_tags キーに格納されています。変数に指定された値は parameters キーに保存されていますが、スニペットについては template_tags にスニペット ID が格納されており、その ID を使って native_query_snippet テーブルのデータと照合することで詳細を取得できます。

 Metabase 独自のクエリ仕様Metabase 独自の構文が埋め込まれているため、実行可能な SQL を取得するには、これらのデータを使用して独自構文を SQL に変換する必要があります。ここでは、そのために必要な独自構文に関する仕様をいくつか紹介します。

 オプショナルな変数指定Metabase には変数指定をオプショナルにする構文があります。以下のようにすることで id 変数が指定された場合のみ WHERE 句が有効になるというものです。
SELECT
  ...
FRON
  ...
[[ WHERE a = {{id}} ]]
では、[[...]] 内で複数の変数が使用されている場合はどうなるのでしょうか。この場合、すべての変数が指定された場合にのみ、[[...]] 内の内容が有効になります。たとえば、以下の例では、id、name、category のすべてが指定されると WHERE 句が有効になります。そのため、template_tags や parameters を基に、[[...]] 内の変数に値が指定されているかを確認する必要があります。
SELECT
  ...
FRON
  ...
[[
WHERE
  id = {{ id }}
AND
  name = {{ name }}
AND
  category = {{ category }}
]]

 フィールドフィルターMetabase の変数でフィールドフィルタータイプを指定すると、変数を特定のテーブルフィールドにマッピングすることで、以下のように = を省略することが可能です。そのため、template_tags からフィールドフィルタータイプの変数を特定し、適切に置換する処理が必要です。
SELECT
  ...
FRON
  ...

WHERE {{ name }} -- name = {{ name }}

 保存済みクエリ（質問）Metabase のクエリには、保存済みのクエリ（質問）を埋め込むことができます。たとえば、保存済みクエリに ID 123 が割り当てられている場合、それを別のクエリの一部として埋め込むことが可能です。この機能はスニペットに似ていますが、スニペットよりも粒度が大きく、単体で SQL として成立している点が特徴です。ただし、埋め込む保存済みクエリに変数やスニペット、さらには別の保存済みクエリを含めることはできないため、再帰的な置換処理は不要です。
-- 保存したクエリ（#123）
SELECT
  ...
FROM
  ...
WHERE
  ...
--

SELECT
  ...
FROM (
  {{ #123 }}
) TABLE_A
WHERE
  ...

 sqlglot の問題その他、sqlglot（バージョン 25.31.4 時点）では対応できなかった問題がいくつかありました。例えば、列の別名として全角数字が使用されている場合、sqlglot ではエラーが発生します。この問題については、全角数字を漢数字に置換することで対応しました。また、一部の INTERVAL 句についても、sqlglot では適切にパースできないケースがありました。この問題に関しては、適切な処理方法を見つけることができなかったため、該当部分をログに出力し、目視で確認する形を取りました。

 まとめその他、改行や特殊文字の扱い、正規表現の使用、置換の順序など、実行可能な SQL に変換するために細かな調整が必要でした。現在のところ、パース処理は問題なく動作していますが、ここで紹介した仕様の正確性や完全性を保証するものではありません。そのため、本記事の内容はあくまで参考としてお読みいただき、まずは有償版の Audito Log 機能の利用を検討してみてください。
Linc'well, inc.Publication
医療スタートアップ Linc'well のエンジニアの記事を集めています。
概要

データ構造

Metabase 独自のクエリ仕様

オプショナルな変数指定

フィールドフィルター

保存済みクエリ（質問）

sqlglot の問題

まとめ

Discussion
