Jinja templates in Superset (SQL Templating)

Use cases:

• The filters only applied on the same column name in the table.
• We can not modify the logic of the filter. Only using existing feature.
• Custom query to improve performance, adding logic, control the query (reuse query, variables,...) => Using jinja template to write a custom query

Available context

• columns: columns which to group by in the query
• filter: filters applied in the query
• from_dttm: start datetime value from the selected time range (None if undefined) (deprecated beginning in version 5.0, use get_time_filter instead)
• to_dttm: end datetime value from the selected time range (None if undefined). (deprecated beginning in version 5.0, use get_time_filter instead)
• groupby: columns which to group by in the query (deprecated)
• metrics: aggregate expressions in the query
• row_limit: row limit of the query
• row_offset: row offset of the query
• table_columns: columns available in the dataset
• time_column: temporal column of the query (None if undefined)
• time_grain: selected time grain (None if undefined)

Jinja templates in DMP

• The filter value always returns an array, even when only a single option is selected. For example, selecting one item still results in an array with a single element.

Example:

    Filter SQL:

    select null "kyBaoCao",
    null "maLoaiDat",
    null "tenLoaiDat"
    from dual 
    
    Chart SQL:

    select h."maLoaiDat",
    h."tenLoaiDat",
    cha."maLoaiDat" "maCha",
    cha."tenLoaiDat" "tenCha",
    goc."maLoaiDatGoc" "maGoc",
    goc."tenLoaiDatGoc" "tenGoc",
    h."thoiGianBatDau",
    h."thoiGianKetThuc"
    from tkkk."HieuLucLoaiDat" h
    left join tkkk."HieuLucLoaiDat" cha on h."loaiDatChaID" = cha."ID"
    left join tkkk."DM_LoaiDat" goc on h."loaiDatGocID" = goc."ID"
    where { % if filter_values("kyBaoCao") [0] % } h."thoiGianBatDau" <= to_date(
        '31/12/{{ filter_values("kyBaoCao")[0] | int }}',
        'DD-MM-YYYY'
    )
    and (
        h."thoiGianKetThuc" is null
        or to_date(
        '31/12/{{ filter_values("kyBaoCao")[0] | int }}',
        'DD-MM-YYYY'
        ) <= h."thoiGianKetThuc"
    )
    and { % endif % } { % if filter_values("maLoaiDat") [0] % } h."maLoaiDat" like '%' || 'filter_values("maLoaiDat")[0]' || '%'
    and { % endif % } { % if filter_values("tenLoaiDat") [0] % } h."tenLoaiDat" like '%' || 'filter_values("tenLoaiDat")[0]' || '%'
    and { % endif % } 1 = 1

For further reading:

• https://superset.apache.org/docs/configuration/sql-templating/