Back to Details

lookml-liquid

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Instructions

使用说明

  1. Syntax: 
    • {{ value }}
      : Output syntax (inserts text).
    • {% if condition %}
      : Tag syntax (logic).
  2. Common Variables: 
    • value
      : Raw value from DB (best for comparisons).
    • rendered_value
      : Formatted value (best for display).
    • _filters['view.field']
      : User-selected filter values.
    • parameter_name._parameter_value
      : Selected parameter value.
  3. Best Practices:
    • SQL Injection: Always use 
      | sql_quote
      when inserting user input (like 
      _filters
      ) into SQL that generates string literals.
    • Booleans: If your dialect requires literal 
      TRUE
      /
      FALSE
      (like BigQuery), append 
      | sql_boolean
      to 
      _in_query
      or 
      _is_selected
      variables (e.g., 
      {{ view.field._in_query | sql_boolean }}
      ).
    • Dependency Awareness: Remember that 
      _in_query
      checks for usage in SELECT, Filters, and 
      required_fields
      . It is NOT limited to just the visible columns.
    • Performance: Avoid referencing 
      {{ field._value }}
      in 
      link
      parameters if the field isn't already in the query, as this forces Looker to add the field to the 
      GROUP BY
      clause, potentially fan-outing the result set. Use 
      row['view.field']
      instead if you only need the value from the browser result row.
  1. 语法: 
    • {{ value }}
      : 输出语法（插入文本）。
    • {% if condition %}
      : 标签语法（用于逻辑判断）。
  2. 常用变量: 
    • value
      : 数据库中的原始值（最适合用于比较）。
    • rendered_value
      : 格式化后的值（最适合用于展示）。
    • _filters['view.field']
      : 用户选择的筛选器值。
    • parameter_name._parameter_value
      : 选中的参数值。
  3. 最佳实践:
    • SQL注入防护: 当将用户输入（如
      _filters
      ）插入生成字符串字面量的SQL时，务必使用
      | sql_quote
      。
    • 布尔值处理: 如果你的SQL方言要求使用字面量
      TRUE
      /
      FALSE
      （如BigQuery），请在
      _in_query
      或
      _is_selected
      变量后追加
      | sql_boolean
      （例如：
      {{ view.field._in_query | sql_boolean }}
      ）。
    • 依赖关系认知: 请注意，
      _in_query
      会检查字段是否在**SELECT语句、筛选器及
      required_fields
      **中被使用，并不局限于可见列。
    • 性能优化: 如果字段未包含在查询中，请避免在
      link
      参数中引用
      {{ field._value }}
      ，因为这会强制Looker将该字段添加到
      GROUP BY
      子句中，可能导致结果集膨胀。如果仅需浏览器结果行中的值，请改用
      row['view.field']
      。

Advanced Variable Usage

高级变量用法

_in_query
vs 
_is_selected

_in_query
与 
_is_selected
对比

VariableDefinitionCritical Difference (Totals)
_in_query
Returns 
true
if the field is in the SELECT clause, Filters, or 
required_fields
.		Remains 
true
 during totals calculation if the field contributed to the query.
_is_selected
Returns 
true
if the field is in the SELECT clause or 
required_fields
.		Returns 
false
 during totals calculation (Row/Column/Grand Totals) for dimensions, because dimensions are removed from the query to calculate totals.
[!WARNING] If you use 
_is_selected
to conditionally render logic for a dimension, that logic will fail (return false) in the Totals row. Use 
_in_query
if you need the logic to persist in totals, or explicitly handle the 
false
state for totals if that is the desired behavior.
变量定义总计计算时的关键差异
_in_query
如果字段存在于SELECT子句、筛选器或
required_fields
中，则返回
true
。		如果字段为查询做出了贡献，在总计计算期间仍返回
true
。
_is_selected
如果字段存在于SELECT子句或
required_fields
中，则返回
true
。		对于维度，在总计计算（行/列/总计行）期间返回
false
，因为计算总计会从查询中移除维度。
[!WARNING] 如果你使用
_is_selected
为维度实现条件渲染逻辑，该逻辑在总计行中会失效（返回false）。如果需要逻辑在总计中保持生效，请使用
_in_query
；如果希望总计行呈现false状态，请显式处理该情况。

Liquid Variable Definitions

Liquid变量定义

The following table describes the Liquid variables that you can use with LookML and where they can be used.
Usage Key:
  • A: 
    action
  • DV: 
    default_value
    (dashboards)
  • DE: 
    description
  • F: 
    filters
    (dashboard elements)
  • H: 
    html
  • LA: Label parameters (
    label
    , 
    view_label
    , 
    group_label
    , 
    group_item_label
    )
  • LI: 
    link
  • S: SQL parameters (
    sql
    , 
    sql_on
    , 
    sql_table_name
    )
VariableDefinitionUsage
value
The raw value of the field.A, H, LI
rendered_value
The formatted value of the field.A, H, LI
filterable_value
The value formatted for URL filtering.A, H, LI
link
The default drill link URL.A, H, LI, S
linked_value
The value with default formatting and linking.A, H, LI
_filters['view.field']
User filters applied to the field.A, DE, H, LA, LI
{% date_start filter %}
Start date of a date filter.S
{% date_end filter %}
End date of a date filter.S
{% condition filter %} sql {% endcondition %}
Applies filter logic to SQL.S
{% parameter name %}
Value of a parameter.DE, LA, S
name._parameter_value
Injects parameter value (safe for logic).DE, H, LA, LI, S
_user_attributes['name']
User attribute value.A, DE, H, LA, LI, S, DV, F
_model._name
Model name.A, DE, H, LA, LI, S
_view._name
View name.A, DE, H, LA, LI, S
_explore._name
Explore name.A, DE, H, LA, LI, S
_field._name
Field name.A, DE, H, LA, LI, S
view._in_query
true
if any field from view is queried.		DE, LA, LI, S
view.field._in_query
true
if field is in query/filter.		DE, LA, LI, S
view.field._is_selected
true
if field is in SELECT.		DE, LA, LI, S
view.field._is_filtered
true
if field is filtered.		DE, LA, LI, S
下表描述了可在LookML中使用的Liquid变量及其适用场景。
使用场景缩写说明:
  • A: 
    action
  • DV: 
    default_value
    （仪表板）
  • DE: 
    description
  • F: 
    filters
    （仪表板元素）
  • H: 
    html
  • LA: 标签参数（
    label
    、
    view_label
    、
    group_label
    、
    group_item_label
    ）
  • LI: 
    link
  • S: SQL参数（
    sql
    、
    sql_on
    、
    sql_table_name
    ）
变量定义适用场景
value
字段的原始值。A, H, LI
rendered_value
字段的格式化后的值。A, H, LI
filterable_value
为URL筛选格式化后的值。A, H, LI
link
默认钻取链接URL。A, H, LI, S
linked_value
带有默认格式与链接的值。A, H, LI
_filters['view.field']
应用于该字段的用户筛选器。A, DE, H, LA, LI
{% date_start filter %}
日期筛选器的起始日期。S
{% date_end filter %}
日期筛选器的结束日期。S
{% condition filter %} sql {% endcondition %}
为SQL应用筛选逻辑。S
{% parameter name %}
参数的值。DE, LA, S
name._parameter_value
注入参数值（可安全用于逻辑判断）。DE, H, LA, LI, S
_user_attributes['name']
用户属性值。A, DE, H, LA, LI, S, DV, F
_model._name
模型名称。A, DE, H, LA, LI, S
_view._name
视图名称。A, DE, H, LA, LI, S
_explore._name
Explore名称。A, DE, H, LA, LI, S
_field._name
字段名称。A, DE, H, LA, LI, S
view._in_query
如果视图中的任意字段被查询，则返回
true
。		DE, LA, LI, S
view.field._in_query
如果字段存在于查询/筛选器中，则返回
true
。		DE, LA, LI, S
view.field._is_selected
如果字段存在于SELECT子句中，则返回
true
。		DE, LA, LI, S
view.field._is_filtered
如果字段被筛选，则返回
true
。		DE, LA, LI, S

Advanced Use Cases

高级使用场景

1. Aggregate Awareness (Dynamic Table Selection)

1. 聚合感知（动态表选择）

Use 
_in_query
to route queries to smaller, pre-aggregated tables when the user doesn't request granular details. This significantly improves query performance.
lookml
view: orders {
  sql_table_name:
    {% if orders.created_date._in_query or orders.created_hour._in_query %}
      orders_daily_summary  -- Fallback to daily partition if granular date used
    {% elsif orders.created_month._in_query %}
      orders_monthly_summary -- Use monthly summary for high-level queries
    {% else %}
      orders_all_transactions -- Default/Detail table
    {% endif %} ;;
}
当用户不需要粒度细节时，使用
_in_query
将查询路由到更小的预聚合表，这能显著提升查询性能。
lookml
view: orders {
  sql_table_name:
    {% if orders.created_date._in_query or orders.created_hour._in_query %}
      orders_daily_summary  -- Fallback to daily partition if granular date used
    {% elsif orders.created_month._in_query %}
      orders_monthly_summary -- Use monthly summary for high-level queries
    {% else %}
      orders_all_transactions -- Default/Detail table
    {% endif %} ;;
}

2. Dynamic Joins (
sql_on
)

2. 动态关联（
sql_on
）

Use 
_in_query
in joins to avoid joining heavy tables unless they are actually required by the user's selection.
lookml
explore: order_items {
  join: users {
    type: left_outer
    sql_on: ${order_items.user_id} = ${users.id} ;;
    relationship: many_to_one
  }

  join: user_facts {
    type: left_outer
    sql_on: ${users.id} = ${user_facts.user_id} ;;
    relationship: one_to_one
    # Only join user_facts if a field from it is actually selected/filtered
    sql_where: {% if user_facts._in_query %} 1=1 {% else %} 1=0 {% endif %} ;;
  }
}
Note: The 
sql_where
trick is one way to force a join drop in some dialects, but standard 
sql_on
logic with 
{% if %}
is cleaner if supported.
在关联中使用
_in_query
，避免关联大表，除非用户的选择确实需要该表。
lookml
explore: order_items {
  join: users {
    type: left_outer
    sql_on: ${order_items.user_id} = ${users.id} ;;
    relationship: many_to_one
  }

  join: user_facts {
    type: left_outer
    sql_on: ${users.id} = ${user_facts.user_id} ;;
    relationship: one_to_one
    # Only join user_facts if a field from it is actually selected/filtered
    sql_where: {% if user_facts._in_query %} 1=1 {% else %} 1=0 {% endif %} ;;
  }
}
注意：在部分SQL方言中，
sql_where
技巧是强制取消关联的一种方式，但如果支持的话，使用带
{% if %}
的标准
sql_on
逻辑会更简洁。

3. Column-Specific Logic (Dynamic Denominator)

3. 列专属逻辑（动态分母）

Change a calculation based on what other fields are present in the query.
lookml
measure: dynamic_rate {
  type: number
  sql:
    {% if users.traffic_source._is_selected %}
      ${total_revenue} / NULLIF(${traffic_source_count}, 0)
    {% else %}
      ${total_revenue} / NULLIF(${total_users}, 0)
    {% endif %} ;;
}
根据查询中包含的其他字段更改计算方式。
lookml
measure: dynamic_rate {
  type: number
  sql:
    {% if users.traffic_source._is_selected %}
      ${total_revenue} / NULLIF(${traffic_source_count}, 0)
    {% else %}
      ${total_revenue} / NULLIF(${total_users}, 0)
    {% endif %} ;;
}

Examples

示例

Dynamic HTML (Conditional Formatting)

动态HTML（条件格式化）

lookml
dimension: status {
  html:
    {% if value == 'complete' %}
      <span style="color: green">{{ rendered_value }}</span>
    {% else %}
      <span style="color: red">{{ rendered_value }}</span>
    {% endif %} ;;
}
lookml
dimension: status {
  html:
    {% if value == 'complete' %}
      <span style="color: green">{{ rendered_value }}</span>
    {% else %}
      <span style="color: red">{{ rendered_value }}</span>
    {% endif %} ;;
}

Templated Filters (Derived Table)

模板化筛选器（派生表）

lookml
view: customer_facts {
  derived_table: {
    sql:
      SELECT customer_id, SUM(amount)
      FROM orders
      WHERE {% condition order_date %} created_at {% endcondition %}
      GROUP BY 1 ;;
  }
}
lookml
view: customer_facts {
  derived_table: {
    sql:
      SELECT customer_id, SUM(amount)
      FROM orders
      WHERE {% condition order_date %} created_at {% endcondition %}
      GROUP BY 1 ;;
  }
}

Complex Logic (Loops & Split)

复杂逻辑（循环与拆分）

LookML Liquid can handle string manipulation and loops, which is useful for parsing complex filter parameters or unnesting values. This is used infrequently, but very handy in complex modeling tasks.
lookml
view: brand_category_item {
  parameter: filter { type: unquoted }
}

explore: complex_filter_parsing {
  # Example: Parsing a string like "Brand1..Category1__Brand2..Category2"
  # This logic splits the string by '__' then '..' to generate OR conditions
  sql_where:
    {% assign items = brand_category_item.filter._parameter_value | split: '__' %}
    {% for item in items %}
      {% assign parts = item | split: '..' %}
      {% if forloop.first %} ( {% else %} OR ( {% endif %}
        ${products.brand} = '{{ parts[0] }}' AND ${products.category} = '{{ parts[1] }}'
      )
    {% endfor %}
    {% if items.size == 0 %} 1=1 {% endif %}
  ;;
}
LookML Liquid支持字符串处理与循环，这在解析复杂筛选参数或展开值时非常有用。虽然使用频率不高，但在复杂建模任务中十分实用。
lookml
view: brand_category_item {
  parameter: filter { type: unquoted }
}

explore: complex_filter_parsing {
  # Example: Parsing a string like "Brand1..Category1__Brand2..Category2"
  # This logic splits the string by '__' then '..' to generate OR conditions
  sql_where:
    {% assign items = brand_category_item.filter._parameter_value | split: '__' %}
    {% for item in items %}
      {% assign parts = item | split: '..' %}
      {% if forloop.first %} ( {% else %} OR ( {% endif %}
        ${products.brand} = '{{ parts[0] }}' AND ${products.category} = '{{ parts[1] }}'
      )
    {% endfor %}
    {% if items.size == 0 %} 1=1 {% endif %}
  ;;
}