lookml-explore

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Instructions

说明

1. Core Standards

1. 核心标准

Naming Convention:
```
snake_case
```
for the Explore name.
Required Parameters:
- ```
description
```
  : 100% Coverage. Every Explore MUST have a description.
- ```
label
```
  : A user-friendly name for the Explore in the UI.
- ```
view_name
```
  : Defaults to explore name, but explicit definition is safer.
Joins:
- ```
relationship
```
  : Required (one_to_one, many_to_one).
- ```
sql_on
```
  : Required. Use
```
${left.id} = ${right.id}
```
  syntax.
- ```
type
```
  : defaults to
```
left_outer
```
  . Use
```
inner
```
  or
```
full_outer
```
  explicitly if needed.
Formatting:
- Do NOT use
```
from
```
  to rename views just for aesthetics. Use
```
view_label
```
  instead.
- Exception: Polymorphic joins, Self-joins, Rescoping extensions.

命名规范：Explore名称使用
```
snake_case
```
格式。
必填参数：
- ```
description
```
  ：100% 覆盖。每个Explore必须包含描述。
- ```
label
```
  ：在UI中显示的用户友好名称。
- ```
view_name
```
  ：默认与Explore名称一致，但显式定义更安全。
关联（Joins）：
- ```
relationship
```
  ：必填（one_to_one、many_to_one）。
- ```
sql_on
```
  ：必填。使用
```
${left.id} = ${right.id}
```
  语法。
- ```
type
```
  ：默认值为
```
left_outer
```
  。如有需要，显式使用
```
inner
```
  或
```
full_outer
```
  。
格式规范：
- 不要仅为了美观使用
```
from
```
  重命名视图，应使用
```
view_label
```
  替代。
- 例外情况：多态关联、自关联、重新作用域扩展。

2. Advanced Configuration

2. 高级配置

```
always_filter
```
: specific filters that users can change but cannot remove.
```
sql_always_where
```
: specific restrictions that users cannot change.
```
persist_with
```
: Link explore cache to datagroups (e.g.,
```
default_datagroup
```
).
```
fields
```
: Use inclusive lists to strictly control content when necessary (
```
ALL_FIELDS*
```
,
```
-view.field
```
).

```
always_filter
```
：用户可修改但无法移除的特定过滤器。
```
sql_always_where
```
：用户无法修改的特定限制条件。
```
persist_with
```
：将Explore缓存与数据组关联（例如：
```
default_datagroup
```
）。
```
fields
```
：必要时使用包含列表严格控制内容（
```
ALL_FIELDS*
```
,
```
-view.field
```
）。

3. Performance Optimization (Aggregate Tables)

3. 性能优化（聚合表）

Aggregate Tables (Aggregate Awareness) allow Looker to query smaller, pre-aggregated tables instead of the raw granular data, drastically improving query performance.

聚合表（聚合感知）允许Looker查询更小的预聚合表而非原始明细数据，大幅提升查询性能。

Anatomy of an Aggregate Table

聚合表结构

lookml

explore: orders {
  aggregate_table: rollup_name {
    query: {
      dimensions: [created_date, status]
      measures: [total_revenue, count]
      filters: [orders.created_date: "6 months"]
    }
    materialization: {
      datagroup_trigger: ecommerce_etl
      # partition_keys: ["created_date"] # BigQuery/Presto optimization
      # increment_key: "created_date"    # Incremental builds
      # increment_offset: 3              # Rebuild last 3 periods
    }
  }
}

lookml

explore: orders {
  aggregate_table: rollup_name {
    query: {
      dimensions: [created_date, status]
      measures: [total_revenue, count]
      filters: [orders.created_date: "6 months"]
    }
    materialization: {
      datagroup_trigger: ecommerce_etl
      # partition_keys: ["created_date"] # BigQuery/Presto optimization
      # increment_key: "created_date"    # Incremental builds
      # increment_offset: 3              # Rebuild last 3 periods
    }
  }
}

Key Parameters

关键参数

Query: Defines the "shape" of the rollup.
- Dimensions: Include all dimensions commonly used in dashboards (including filters).
- Measures: Include base measures (sum, count). Looker can derive averages from sum+count.
- Filters: Optional. Restricts the rollup to a subset of data (e.g., "last 6 months").
Materialization:
- datagroup_trigger: (Recommended) Rebuilds when the ETL job completes.
- sql_trigger_value: Rebuilds when a SQL query returns a new value.
- increment_key: (Advanced) Appends new data instead of full rebuilds. Best for massive tables.
- indexes / partition_keys / cluster_keys: Dialect-specific optimizations.
Best Practices:
- Timeframes: Include the finest grain needed (e.g.,
```
date
```
  ). Looker can roll up
```
date
```
  to
```
month
```
  or
```
year
```
  automatically.
- Exact Match: The user's query must be a strict subset of the aggregate table's fields to satisfy the awareness logic.
- Filter Awareness: If a user filters on a field not in the aggregate table, Looker cannot use it (unless it's an "exact match" special case). Add common filter fields to the
```
dimensions
```
  list.

Query：定义汇总表的“形态”。
- Dimensions：包含仪表板中常用的所有维度（包括过滤器）。
- Measures：包含基础度量（sum、count）。Looker可通过sum+count推导平均值。
- Filters：可选。将汇总表限制为数据的子集（例如：“最近6个月”）。
Materialization：
- datagroup_trigger：（推荐）ETL作业完成时重建。
- sql_trigger_value：当SQL查询返回新值时重建。
- increment_key：（高级）追加新数据而非全量重建。适用于超大规模表。
- indexes / partition_keys / cluster_keys：特定方言的优化项。
最佳实践：
- 时间粒度：包含所需的最细粒度（例如：
```
date
```
  ）。Looker可自动将
```
date
```
  汇总为
```
month
```
  或
```
year
```
  。
- 精确匹配：用户的查询必须是聚合表字段的严格子集才能满足感知逻辑。
- 过滤器感知：如果用户过滤的字段不在聚合表中，Looker无法使用该聚合表（除非是“精确匹配”特殊情况）。将常用过滤字段添加到
```
dimensions
```
  列表中。

4. Extending Explores

4. 扩展Explore

Extends: Use
```
extends: [base_explore]
```
to inherit joins, fields, and descriptions from another explore.
- Use Case: Create a "Base" explore with common joins, then "Extended" explores for specific analysis (e.g.,
```
orders
```
  ->
```
marketing_orders
```
  ).

Extends：使用
```
extends: [base_explore]
```
继承另一个Explore的关联、字段和描述。
- 使用场景：创建包含通用关联的“基础”Explore，然后为特定分析创建“扩展”Explore（例如：
```
orders
```
  ->
```
marketing_orders
```
  ）。

Examples

示例

Basic Explore

基础Explore

lookml

explore: orders {
  label: "Orders"
  description: "Analyze order data, including user and product details."
  view_name: orders
  
  join: users {
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

lookml

explore: orders {
  label: "Orders"
  description: "Analyze order data, including user and product details."
  view_name: orders
  
  join: users {
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

Explore with Filters & Caching

带过滤器与缓存的Explore

lookml

explore: events {
  label: "Web Events"
  description: "User interaction events."
  persist_with: default_datagroup

  # Users can change this filter, but it defaults to '7 days'
  always_filter: {
    filters: [events.created_date: "7 days"]
  }

  # Users CANNOT change this filter.
  sql_always_where: ${events.is_test_data} = false ;;
  
  join: sessions {
    relationship: many_to_one
    sql_on: ${events.session_id} = ${sessions.session_id} ;;
  }
}

lookml

explore: events {
  label: "Web Events"
  description: "User interaction events."
  persist_with: default_datagroup

  # Users can change this filter, but it defaults to '7 days'
  always_filter: {
    filters: [events.created_date: "7 days"]
  }

  # Users CANNOT change this filter.
  sql_always_where: ${events.is_test_data} = false ;;
  
  join: sessions {
    relationship: many_to_one
    sql_on: ${events.session_id} = ${sessions.session_id} ;;
  }
}

Aggregate Table (Advanced)

聚合表（高级）

lookml

explore: orders {
  aggregate_table: monthly_sales_summary {
    query: {
      dimensions: [created_month, status, products.category]
      measures: [total_revenue, count]
      filters: [orders.created_date: "2 years"]
    }
    materialization: {
      datagroup_trigger: ecommerce_etl
      partition_keys: ["created_month"]
      increment_key: "created_month"
      increment_offset: 1 # Rebuild current and previous month
    }
  }
}

lookml

explore: orders {
  aggregate_table: monthly_sales_summary {
    query: {
      dimensions: [created_month, status, products.category]
      measures: [total_revenue, count]
      filters: [orders.created_date: "2 years"]
    }
    materialization: {
      datagroup_trigger: ecommerce_etl
      partition_keys: ["created_month"]
      increment_key: "created_month"
      increment_offset: 1 # Rebuild current and previous month
    }
  }
}

Extended Explore

扩展Explore

lookml

explore: orders_extended {
  extends: [orders]
  label: "Orders (Marketing View)"
  view_name: orders
  
  # Add new joins specific to this view
  join: marketing_channels {
    sql_on: ${orders.channel_id} = ${marketing_channels.id} ;;
    relationship: many_to_one
  }
}

undefined

lookml

explore: orders_extended {
  extends: [orders]
  label: "Orders (Marketing View)"
  view_name: orders
  
  # Add new joins specific to this view
  join: marketing_channels {
    sql_on: ${orders.channel_id} = ${marketing_channels.id} ;;
    relationship: many_to_one
  }
}

Reference Skills

参考技能

For more complex scenarios, refer to these specialized skills:

Advanced Explore Configuration: UNNESTing, lateral flattens, and row-level security.
Joins Deep Dive: Detailed join types, relationships, and aliasing.

对于更复杂的场景，请参考以下专业技能：

高级Explore配置：UNNEST操作、横向展平、行级安全。
关联深入解析：详细的关联类型、关系和别名。