本文へスキップ
GitHubでこのページを編集

SQLテンプレート

Jinjaテンプレート​

SQL LabとExploreは、クエリでJinjaテンプレートをサポートしています。テンプレートを有効にするには、superset_config.pyでENABLE_TEMPLATE_PROCESSING 機能フラグを有効にする必要があります。テンプレートが有効になっている場合、Pythonコードを仮想データセットとExploreのフィルターおよびメトリックコントロールのカスタムSQLに埋め込むことができます。デフォルトでは、Jinjaコンテキストで次の変数が使用可能になります。

  • columns: クエリでグループ化する列
  • filter: クエリに適用されるフィルター
  • from_dttm: 選択された時間範囲の開始datetime値（未定義の場合はNone）（バージョン5.0以降で非推奨、代わりにget_time_filterを使用してください）
  • to_dttm: 選択された時間範囲の終了datetime値（未定義の場合はNone）（バージョン5.0以降で非推奨、代わりにget_time_filterを使用してください）
  • groupby: クエリでグループ化する列（非推奨）
  • metrics: クエリ内の集計式
  • row_limit:クエリの行数制限
  • row_offset: クエリの行オフセット
  • table_columns: データセットで使用可能な列
  • time_column:クエリの時間列（未定義の場合はNone）
  • time_grain: 選択された時間粒度（未定義の場合はNone）

たとえば、仮想データセットに時間範囲を追加するには、次のように記述します。

SELECT *
FROM tbl
WHERE dttm_col > '{{ from_dttm }}' and dttm_col < '{{ to_dttm }}'

Jinjaのロジックを使用して、クエリを時間範囲フィルターのクリアに対して堅牢にすることもできます。

SELECT *
FROM tbl
WHERE (
    {% if from_dttm is not none %}
        dttm_col > '{{ from_dttm }}' AND
    {% endif %}
    {% if to_dttm is not none %}
        dttm_col < '{{ to_dttm }}' AND
    {% endif %}
    1 = 1
)

末尾の1 = 1は、時間フィルターが設定されていない場合でもWHERE句に値が存在することを保証します。多くのデータベースエンジンでは、これはtrueに置き換えることができます。

Jinjaパラメーターは、クエリ内では*二重*角括弧で、ロジックブロック内では*単一*角括弧で呼び出されることに注意してください。

Jinjaコンテキストにカスタム機能を追加するには、Supersetの設定（superset_config.py）でJINJA_CONTEXT_ADDONSを定義することにより、環境でデフォルトのJinjaコンテキストをオーバーロードする必要があります。この辞書で参照されるオブジェクトは、Jinjaコンテキストが使用可能になる場所でユーザーが使用できるように利用可能になります。

JINJA_CONTEXT_ADDONS = {
    'my_crazy_macro': lambda x: x*2,
}

Jinjaテンプレートのデフォルト値は、SQL Labユーザーインターフェースの「パラメーター」メニューで指定できます。UIでは、一連のパラメーターをJSONとして割り当てることができます。

{
  "my_table": "foo"
}

パラメーターは、Jinjaテンプレート構文を使用してSQLで使用可能になります（例：SELECT * FROM {{ my_table }}）。SQL Labテンプレートパラメーターは、TEMPLATE PARAMETERSとしてデータセットに保存されます。

Jinjaテンプレートで使用されるフィルターをテストするために使用できる特別な_filtersパラメーターがあります。

{
  "_filters": [
    {
      "col": "action_type",
      "op": "IN",
      "val": ["sell", "buy"]
    }
  ]
}
SELECT action, count(*) as times
FROM logs
WHERE action in {{ filter_values('action_type')|where_in }}
GROUP BY action

_filtersはデータセットに保存されません。SQL Lab UI内でのみ使用されます。

デフォルトのJinjaテンプレートに加えて、SQL Labは、Supersetの設定でCUSTOM_TEMPLATE_PROCESSORSを設定することにより、自己定義のテンプレートプロセッサもサポートしています。この辞書内の値は、指定されたデータベースエンジンのデフォルトのJinjaテンプレートプロセッサを上書きします。次の例は、正規表現解析によるマクロテンプレートの処理ロジックを実装するカスタムPrestoテンプレートプロセッサを設定します。Jinjaテンプレートの{{ }}スタイルではなく、$スタイルのマクロを使用します。

CUSTOM_TEMPLATE_PROCESSORSで設定することにより、Prestoデータベース上のSQLテンプレートは、デフォルトのものではなくカスタムのものによって処理されます。

def DATE(
    ts: datetime, day_offset: SupportsInt = 0, hour_offset: SupportsInt = 0
) -> str:
    """Current day as a string."""
    day_offset, hour_offset = int(day_offset), int(hour_offset)
    offset_day = (ts + timedelta(days=day_offset, hours=hour_offset)).date()
    return str(offset_day)

class CustomPrestoTemplateProcessor(PrestoTemplateProcessor):
    """A custom presto template processor."""

    engine = "presto"

    def process_template(self, sql: str, **kwargs) -> str:
        """Processes a sql template with $ style macro using regex."""
        # Add custom macros functions.
        macros = {
            "DATE": partial(DATE, datetime.utcnow())
        }  # type: Dict[str, Any]
        # Update with macros defined in context and kwargs.
        macros.update(self.context)
        macros.update(kwargs)

        def replacer(match):
            """Expand $ style macros with corresponding function calls."""
            macro_name, args_str = match.groups()
            args = [a.strip() for a in args_str.split(",")]
            if args == [""]:
                args = []
            f = macros[macro_name[1:]]
            return f(*args)

        macro_names = ["$" + name for name in macros.keys()]
        pattern = r"(%s)\s*\(([^()]*)\)" % "|".join(map(re.escape, macro_names))
        return re.sub(pattern, replacer, sql)

CUSTOM_TEMPLATE_PROCESSORS = {
    CustomPrestoTemplateProcessor.engine: CustomPrestoTemplateProcessor
}

SQL Labには、プラグ可能なバックエンドを備えたライブクエリ検証機能も含まれています。設定ファイルに次のようなブロックを追加することにより、どの検証実装がどのデータベースエンジンで使用されるかを設定できます。

FEATURE_FLAGS = {
    'SQL_VALIDATORS_BY_ENGINE': {
        'presto': 'PrestoDBSQLValidator',
    }
}

使用可能な検証者とその名前は、sql_validatorsにあります。

使用可能なマクロ​

このセクションでは、Supersetの事前に定義されたJinjaマクロについて説明します。

現在のユーザー名

{{ current_username() }}マクロは、現在ログインしているユーザーのusernameを返します。

Supersetの設定でキャッシングを有効にしている場合、デフォルトでは、キャッシュキーを計算する際にSupersetによってusername値が使用されます。キャッシュキーは、将来キャッシュヒットがあるかどうかを判断し、Supersetがキャッシュされたデータを取得できるかどうかを決定する一意の識別子です。

Jinjaコードに次のパラメーターを追加することにより、キャッシュキーの計算にusername値を含めないようにすることができます。

{{ current_username(add_to_cache_keys=False) }}

現在のユーザーID

{{ current_user_id() }}マクロは、現在ログインしているユーザーのアカウントIDを返します。

Supersetの設定でキャッシングを有効にしている場合、デフォルトでは、キャッシュキーを計算する際にSupersetによってアカウントid値が使用されます。キャッシュキーは、将来キャッシュヒットがあるかどうかを判断し、Supersetがキャッシュされたデータを取得できるかどうかを決定する一意の識別子です。

Jinjaコードに次のパラメーターを追加することにより、キャッシュキーの計算にアカウントid値を含めないようにすることができます。

{{ current_user_id(add_to_cache_keys=False) }}

現在のユーザーメールアドレス

{{ current_user_email() }}マクロは、現在ログインしているユーザーのメールアドレスを返します。

Supersetの設定でキャッシングを有効にしている場合、デフォルトでは、キャッシュキーを計算する際にSupersetによってメールアドレス値が使用されます。キャッシュキーは、将来キャッシュヒットがあるかどうかを判断し、Supersetがキャッシュされたデータを取得できるかどうかを決定する一意の識別子です。

Jinjaコードに次のパラメーターを追加することにより、キャッシュキーの計算にメールアドレス値を含めないようにすることができます。

{{ current_user_email(add_to_cache_keys=False) }}

カスタムURLパラメーター

{{ url_param('custom_variable') }}マクロを使用すると、任意のURLパラメーターを定義し、SQLコードで参照することができます。

具体的な例を以下に示します。

  • SQL Labで次のクエリを記述します。

    SELECT count(*)
    FROM ORDERS
    WHERE country_code = '{{ url_param('countrycode') }}'

  • Supersetをドメインwww.example.comでホスティングしており、スペインの同僚に次のSQL Lab URL www.example.com/superset/sqllab?countrycode=ESを、アメリカの同僚に次のSQL Lab URL www.example.com/superset/sqllab?countrycode=USを送信します。

  • スペインの同僚の場合、SQL Labクエリは次のようにレンダリングされます。

    SELECT count(*)
    FROM ORDERS
    WHERE country_code = 'ES'

  • アメリカの同僚の場合、SQL Labクエリは次のようにレンダリングされます。

    SELECT count(*)
    FROM ORDERS
    WHERE country_code = 'US'

キャッシュキーへの値の明示的な追加

{{ cache_key_wrapper() }}関数は、キャッシュキーの計算で使用される値の累積リストに値を追加するようにSupersetに明示的に指示します。

この関数は、独自の自作関数の戻り値をキャッシュキーにラップする場合にのみ必要です。より詳細な情報はこちらを参照してください。

この関数は、current_user_id()関数とcurrent_username()関数呼び出しにおけるuser_id値とusername値のキャッシングを制御します（キャッシングを有効にしている場合）。

フィルター値

{{ filter_values() }}を使用して、特定のフィルターの値をリストとして取得できます。

これは、次の場合に役立ちます。

  • フィルターコンポーネントの列名がselect文の列名と一致しないクエリをフィルターコンポーネントを使用してフィルター処理する場合
  • パフォーマンスのためにメインクエリ内のフィルターを使用できるようにする場合

具体的な例を以下に示します。

SELECT action, count(*) as times
FROM logs
WHERE
    action in {{ filter_values('action_type')|where_in }}
GROUP BY action

where_inフィルターは、filter_values('action_type')からの値のリストを、IN式に適した文字列に変換します。

特定の列のフィルター

{{ get_filters() }}マクロは、指定された列に適用されたフィルターを返します。filter_values()と同様に値を返すことに加えて、get_filters()マクロはExplore UIで指定された演算子を返します。

これは、次の場合に役立ちます。

  • SQL句でIN演算子以外の演算子を処理する場合
  • フィルターのカスタムSQL条件の生成を処理する場合
  • 速度のためにメインクエリ内でフィルターを使用できるようにする場合

具体的な例を以下に示します。

 WITH RECURSIVE
    superiors(employee_id, manager_id, full_name, level, lineage) AS (
    SELECT
        employee_id,
        manager_id,
        full_name,
    1 as level,
    employee_id as lineage
    FROM
        employees
    WHERE
    1=1

    {# Render a blank line #}
    {%- for filter in get_filters('full_name', remove_filter=True) -%}

    {%- if filter.get('op') == 'IN' -%}
        AND
        full_name IN {{ filter.get('val')|where_in }}
    {%- endif -%}

    {%- if filter.get('op') == 'LIKE' -%}
        AND
        full_name LIKE {{ "'" + filter.get('val') + "'" }}
    {%- endif -%}

    {%- endfor -%}
    UNION ALL
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name,
    s.level + 1 as level,
    s.lineage
        FROM
            employees e,
        superiors s
        WHERE s.manager_id = e.employee_id
    )

    SELECT
    employee_id, manager_id, full_name, level, lineage
    FROM
    superiors
    order by lineage, level

時間フィルター

{{ get_time_filter() }}マクロは、特定の列に適用された時間フィルターを返します。これは、デフォルトでは時間フィルターが外部クエリに配置されるため、仮想データセット内で時間フィルターを処理する場合に役立ちます。多くのデータベースとクエリエンジンは、時間フィルターが外部クエリではなく内部クエリに配置されている場合、クエリをより適切に最適化できるため、パフォーマンスを大幅に向上させることができます。

このマクロは、次のパラメーターを取ります。

  • column: 時間列の名前。ダッシュボードネイティブ時間範囲フィルター（存在する場合）からの時間範囲を参照するには、未定義のままにします。
  • default: 時間フィルターが存在しない場合、または値が「フィルターなし」の場合にフォールバックするデフォルト値
  • target_type: ターゲットデータベースで認識されるターゲット時間型（例：TIMESTAMP、DATE、またはDATETIME）。columnが定義されている場合、形式は列の型にデフォルト設定されます。これは、返されるTimeFilterオブジェクトのfrom_exprプロパティとto_exprプロパティの形式を作成するために使用されます。
  • strftime: カスタム時間フォーマットのためにdatetimeのstrftimeメソッドを使用する形式。（有効なフォーマットコードについてはドキュメントを参照してください）。定義されている場合、target_typeは無視されます。
  • remove_filter: trueに設定すると、フィルターを処理済みとしてマークし、外部クエリから削除します。フィルターを内部クエリにのみ適用する必要がある場合に役立ちます。

戻り値の型には、次のプロパティがあります。

  • from_expr: 時間フィルターの開始（存在する場合）
  • to_expr: 時間フィルターの終了（存在する場合）
  • time_range: 適用された時間範囲

Supersetメタストアのlogsテーブルを使用した具体的な例を以下に示します。

{% set time_filter = get_time_filter("dttm", remove_filter=True) %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
{% set time_range = time_filter.time_range %}
SELECT
  *,
  '{{ time_range }}' as time_range
FROM logs
{% if from_expr or to_expr %}WHERE 1 = 1
{% if from_expr %}AND dttm >= {{ from_expr }}{% endif %}
{% if to_expr %}AND dttm < {{ to_expr }}{% endif %}
{% endif %}

dttm列にLast weekの時間フィルターを使用して、単純なCOUNT(*)をメトリックとするテーブルチャートを作成すると仮定すると、Postgresで次のクエリがレンダリングされます（時間フィルターのフォーマットと、外部クエリでの時間フィルターの非存在に注意してください）。

SELECT COUNT(*) AS count
FROM
  (SELECT *,
          'Last week' AS time_range
   FROM public.logs
   WHERE 1 = 1
     AND dttm >= TO_TIMESTAMP('2024-08-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
     AND dttm < TO_TIMESTAMP('2024-09-03 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')) AS virtual_table
ORDER BY count DESC
LIMIT 1000;

defaultパラメーターを使用すると、テンプレート化されたクエリを簡素化できます。エンドポイントは常に定義されるためです（固定の時間範囲を使用するには、default="2024-08-27 : 2024-09-03"などを使用することもできます）。

{% set time_filter = get_time_filter("dttm", default="Last week", remove_filter=True) %}
SELECT
  *,
  '{{ time_filter.time_range }}' as time_range
FROM logs
WHERE
  dttm >= {{ time_filter.from_expr }}
  AND dttm < {{ time_filter.to_expr }}

データセット

dataset マクロを使用すると、物理データセットと仮想データセットのクエリを実行できます。これは、データセットに計算カラムとメトリックを定義しており、その定義をアドホックSQL Labクエリで再利用する場合に便利です。

マクロを使用するには、まずデータセットのIDを見つける必要があります。これは、すべてのデータセットを表示するビューに移動し、対象のデータセットにカーソルを合わせ、そのURLを確認することで行えます。たとえば、データセットのURLがhttps://superset.example.org/explore/?dataset_type=table&dataset_id=42 の場合、そのIDは42です。

IDを取得したら、テーブルのようにクエリを実行できます。

SELECT * FROM {{ dataset(42) }} LIMIT 10

カラムに加えてメトリック定義も選択する場合は、追加のキーワード引数を渡す必要があります。

SELECT * FROM {{ dataset(42, include_metrics=True) }} LIMIT 10

メトリックは集計であるため、結果のSQL式は、メトリック以外のすべてのカラムでグループ化されます。代わりに、グループ化するカラムのサブセットを指定することもできます。

SELECT * FROM {{ dataset(42, include_metrics=True, columns=["ds", "category"]) }} LIMIT 10

メトリック

{{ metric('metric_key', dataset_id) }} マクロを使用すると、データセットからメトリックのSQL構文を取得できます。これはさまざまな目的に役立ちます。

  • チャートレベルでメトリックラベルを上書きする
  • 複数のメトリックを計算に組み合わせる
  • SQL Labでメトリック構文を取得する
  • データセット間でメトリックを再利用する

このマクロにより、コピーとペーストが不要になり、ユーザーはデータセット層でメトリック定義を一元化できます。

dataset_id パラメーターはオプションです。指定しない場合、Supersetはコンテキストから現在のデータセットを使用します（たとえば、このマクロをチャートビルダーで使用する場合、デフォルトでmacro_key はチャートを動かすデータセットで検索されます）。このパラメーターは、SQL Labで使用する場合、または別のデータセットからメトリックを取得する場合に使用できます。