派生表带来了无限可能的高级分析可能性,但对于方法而言、实施和排查问题却并非易事。本实战宝典包含 Looker 中派生表的最常见使用场景。
本页面包含以下示例:
派生表资源
这些实战宝典假定您对 LookML 和派生表有初步了解。您应该能够熟练创建视图和修改模型文件。如果您想回顾任何这些主题,请参阅以下资源:
在每天凌晨 3 点创建表
本示例中的数据在每天凌晨 2 点进入。无论是在凌晨 3 点还是晚上 9 点,针对此数据运行查询的结果都是一样的。因此,每天构建一次表格并让用户从缓存中提取结果是明智之举。
在模型文件中添加数据集组后,您便可以在多个表格和探索中重复使用该数据集组。此数据组包含一个 sql_trigger_value
参数,该参数会告知该数据组何时触发并重建派生表。
如需查看触发器表达式的更多示例,请参阅 sql_trigger_value
文档。
## in the model file
datagroup: standard_data_load {
sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
max_cache_age: "24 hours"
}
explore: orders {
…
将 datagroup_trigger
参数添加到视图文件中的 derived_table
定义,并指定要使用的 datagroup 的名称。在此示例中,datagroup 为 standard_data_load
。
view: orders {
derived_table: {
indexes: ["id"]
datagroup_trigger: standard_data_load
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
…
}
将新数据附加到大型表
增量 PDT 是 Looker 通过将新数据附加到表中(而不是重新构建整个表)构建的永久性派生表。
下一个示例基于 orders
表示例,展示了表的增量构建方式。每天都会有新的订单数据传入,您可以添加 increment_key
参数和 increment_offset
参数,将这些数据附加到现有表中。
view: orders {
derived_table: {
indexes: ["id"]
increment_key: "created_at"
increment_offset: 3
datagroup_trigger: standard_data_load
distribution_style: all
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;; }
…
}
increment_key
值设为 created_at
,这是在本示例中应查询新数据并将其附加到 PDT 的时间增量。
将 increment_offset
值设置为 3
,可指定重新构建以考虑延迟数据的先前时间段的数量(以递增键的粒度)。
使用 SQL 窗口函数
某些数据库方言支持窗口函数,尤其是用于创建序列号、主键、运行总计和累计总计以及其他实用的多行计算。执行主查询后,系统会在单独的传递中执行任何 derived_column
声明。
如果您的数据库方言支持窗口函数,则可以在原生派生表中使用它们。创建一个包含窗口函数的 sql
参数,并使用该参数创建 derived_column
参数。引用值时,应该按照原生派生表中定义的列名。
以下示例展示了如何创建一个包含 user_id
、order_id
和 created_time
列的原生派生表。然后,您可以将派生列与 SQL ROW_NUMBER()
窗口函数结合使用,计算出包含客户订单序列号的列。
view: user_order_sequences {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: order_id {
field: order_items.order_id
}
column: created_time {
field: order_items.created_time
}
derived_column: user_sequence {
sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
}
}
}
dimension: order_id {
hidden: yes
}
dimension: user_sequence {
type: number
}
}
为计算值创建派生列
您可以添加 derived_column
参数,以指定 explore_source
参数的“探索”部分中不存在的列。每个 derived_column
形参都有一个 sql
形参,用于指定如何构造值。
sql
计算可使用您通过 column
参数指定的任何列。派生列不能包含聚合函数,但可以包含可对表中的单个行执行的计算。
此示例会创建一个 average_customer_order
列,该列是根据原生派生表中的 lifetime_customer_value
和 lifetime_number_of_orders
列计算得出的。
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: users.id
}
column: lifetime_number_of_orders {
field: order_items.count
}
column: lifetime_customer_value {
field: order_items.total_profit
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
优化策略
由于 PDT 存储在数据库中,因此您应根据方言支持的以下策略优化 PDT:
例如,如需添加持久性,您可以将 PDT 设置为在数据组 orders_datagroup
触发时重建,然后可以在 customer_id
和 first_order
上添加索引,如下所示:
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
如果您未添加索引(或您方言的等效项),Looker 会向您发出警告,提醒您添加索引以提高查询性能。
使用 PDT 测试优化
您可以使用 PDT 测试不同的索引、分布和其他优化选项,而无需获得 DBA 或 ETL 开发者的大量支持。
假设您有一个表,但想要测试不同的索引。该视图的初始 LookML 可能如下所示:
view: customer {
sql_table_name: warehouse.customer ;;
}
如需测试优化策略,您可以使用 indexes
参数向 LookML 添加索引,如下所示:
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
查询视图一次以生成 PDT。然后,运行测试查询并比较结果。如果结果理想,您可以要求 DBA 或 ETL 团队将索引添加到原始表中。
UNION
两个表格
如果您的 SQL 方言支持,您可以在这两个派生表中执行 SQL UNION
或 UNION ALL
运算符。UNION
和 UNION ALL
运算符可合并两个查询的结果集。
以下示例展示了基于 SQL 的派生表与 UNION
的搭配使用方式:
view: first_and_second_quarter_sales {
derived_table: {
sql:
SELECT * AS sales_records
FROM sales_records_first_quarter
UNION
SELECT * AS sales_records
FROM sales_records_second_quarter ;;
}
}
sql
参数中的 UNION
语句会生成一个派生表,该表合并了两个查询的结果。
UNION
和 UNION ALL
之间的区别在于,UNION ALL
不会移除重复的行。在使用 UNION
与 UNION ALL
时,需要注意一些性能注意事项,因为数据库服务器必须执行额外的工作才能移除重复行。
对和求和(测量量度)
根据 SQL 以及 Looker 中的一般规则,您不能按聚合函数(在 Looker 中表示为测量值)的结果对查询进行分组。您只能按未汇总的字段(在 Looker 中表示为维度)进行分组。
如需按汇总项进行分组(例如求和的总和),您需要对指标进行“维度化”。实现此目的的一种方法是使用派生表,它实际上会创建汇总的子查询。
从探索开始,Looker 可以为您的所有或大部分派生表生成 LookML。只需创建一个“探索”,然后选择要包含在派生表中的所有字段即可。然后,如需生成原生(或基于 SQL)派生表 LookML,请按以下步骤操作:
点击“探索”的齿轮菜单,然后选择 Get LookML(获取 LookML)。
如需查看用于为探索创建原生派生表的 LookML,请点击派生表标签页。
复制 LookML。
现在,您已复制生成的 LookML,请按照以下步骤将其粘贴到视图文件中:
在 Looker IDE 中,点击项目文件列表顶部的 +,然后选择创建视图。或者,如需在文件夹中创建文件,请点击文件夹的菜单,然后选择创建视图。
为视图名称设置有意义的名称。
(可选)更改列名称、指定派生列和添加过滤条件。
具备汇总感知能力的汇总表
在 Looker 中,您可能会经常遇到非常大型的数据集或表,为了提高性能,这些数据集或表需要汇总表或汇总。
借助 Looker 的汇总感知功能,您可以预构建具有不同粒度、维度和汇总级别的汇总表;还可以告知 Looker 如何在现有探索中使用这些表。然后,查询将在 Looker 认为合适的地方使用这些汇总表,无需任何用户输入。这将缩减查询大小、减少等待时间并提升用户体验。
以下是 Looker 模型中的一个非常简单的实现,旨在展示汇总感知功能的轻量级程度。假设数据库中有一个假设的航班表,其中每条记录对应于通过 FAA 记录的每趟航班,那么您可以在 Looker 中使用自己的视图和探索对此表进行建模。以下是您可以为探索定义的汇总表的 LookML:
explore: flights {
aggregate_table: flights_by_week_and_carrier {
query: {
dimensions: [carrier, depart_week]
measures: [cancelled_count, count]
}
materialization: {
sql_trigger_value: SELECT CURRENT-DATE;;
}
}
}
借助此汇总表,用户可以查询 flights
“探索”,而 Looker 会自动使用汇总表来回答查询。如需详细了解汇总感知,请参阅“汇总感知”教程。