SQL 範本

Jinja 範本

SQL Lab 和 Explore 支援查詢中的 Jinja 範本。若要啟用範本功能，需要在 superset_config.py 中啟用 ENABLE_TEMPLATE_PROCESSING 功能旗標。啟用範本功能後，可以在虛擬資料集以及 Explore 中篩選器和指標控制項的自訂 SQL 中嵌入 Python 程式碼。依預設，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,
}

可以使用 SQL Lab 使用者介面中的「參數」選單，指定 Jinja 範本的預設值。在 UI 中，您可以將一組參數指派為 JSON

{
  "my_table": "foo"
}

這些參數會透過使用 Jinja 範本語法在您的 SQL 中可用（範例：SELECT * FROM {{ my_table }}）。SQL Lab 範本參數會以 TEMPLATE PARAMETERS 形式儲存在資料集中。

有一個特殊的 _filters 參數可用於測試 Jinja 範本中使用的篩選器。

{
  "_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 範本處理器，其使用 RegEx 解析實作自己的巨集範本處理邏輯。它在 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') }}'

• 您在網域 www.example.com 上託管 Superset，並且您將以下 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：如果時間篩選器不存在或值為 No filter 時，要回退到的預設值
• 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 %}

假設我們正在創建一個表格圖表，其中以簡單的 COUNT(*) 作為指標，並在 dttm 欄位上使用 Last week 的時間篩選器，這將在 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 中使用，或者從另一個資料集中擷取指標時使用。