lookml-liquid

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Instructions

使用说明

Syntax:
- ```
{{ value }}
```
  : Output syntax (inserts text).
- ```
{% if condition %}
```
  : Tag syntax (logic).
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.
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.

语法:
- ```
{{ value }}
```
  : 输出语法（插入文本）。
- ```
{% if condition %}
```
  : 标签语法（用于逻辑判断）。
常用变量:
- ```
value
```
  : 数据库中的原始值（最适合用于比较）。
- ```
rendered_value
```
  : 格式化后的值（最适合用于展示）。
- ```
_filters['view.field']
```
  : 用户选择的筛选器值。
- ```
parameter_name._parameter_value
```
  : 选中的参数值。
最佳实践:
- 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

_is_selected

_in_query

与

_is_selected

对比

Variable	Definition	Critical 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
```
)

Variable	Definition	Usage
`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 %}
  ;;
}

lookml-liquid

Original

Translation

Instructions

使用说明

Advanced Variable Usage

高级变量用法

`_in_query`
vs
`_is_selected`

`_in_query`
与
`_is_selected`
对比

Liquid Variable Definitions

Liquid变量定义

Advanced Use Cases

高级使用场景

1. Aggregate Awareness (Dynamic Table Selection)

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

2. Dynamic Joins (
`sql_on`
)

2. 动态关联（
`sql_on`
）

3. Column-Specific Logic (Dynamic Denominator)

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

Examples

示例

Dynamic HTML (Conditional Formatting)

动态HTML（条件格式化）

Templated Filters (Derived Table)

模板化筛选器（派生表）

Complex Logic (Loops & Split)

复杂逻辑（循环与拆分）

lookml-liquid

Original

Translation

Instructions

使用说明

Advanced Variable Usage

高级变量用法

_in_query vs _is_selected

_in_query 与 _is_selected 对比

Liquid Variable Definitions

Liquid变量定义

Advanced Use Cases

高级使用场景

1. Aggregate Awareness (Dynamic Table Selection)

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

2. Dynamic Joins (sql_on)

2. 动态关联（sql_on）

3. Column-Specific Logic (Dynamic Denominator)

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

Examples

示例

Dynamic HTML (Conditional Formatting)

动态HTML（条件格式化）

Templated Filters (Derived Table)

模板化筛选器（派生表）

Complex Logic (Loops & Split)

复杂逻辑（循环与拆分）

`_in_query`
vs
`_is_selected`

`_in_query`
与
`_is_selected`
对比

2. Dynamic Joins (
`sql_on`
)

2. 动态关联（
`sql_on`
）