如果您有 SQL 方面的背景,可能对 Looker 如何生成 SQL 感到好奇。从根本上说,Looker 是一款工具,可以生成 SQL 查询,并通过数据库连接提交这些查询。Looker 会根据描述数据库中表与列之间关系的 LookML 项目来制定 SQL 查询。通过了解 Looker 如何生成查询,您可以更好地了解 LookML 代码如何转换为高效的 SQL 查询。
每个 LookML 参数都会通过更改查询的结构、内容或行为来控制 Looker 生成 SQL 的某个方面。本页介绍了 Looker 生成 SQL 的基本原理,但并未详细介绍所有 LookML 元素。如需了解 LookML 参数,不妨先参阅 LookML 快速参考文档页面。
查看查询
在已保存的数据分析或探索中,您可以使用数据面板中的 SQL 标签页查看 Looker 向数据库发送了哪些内容来获取数据。您还可以使用 SQL 标签页底部的 Open in SQL Runner 和 Explain in SQL Runner 链接,在 SQL Runner 中查看查询或查看数据库针对查询的解释计划。
如需详细了解 SQL Runner,请参阅 SQL Runner 基础知识文档页面。如需详细了解如何使用 SQL Runner 优化查询,请参阅如何使用 EXPLAIN 优化 SQL 社区帖子。
Looker 查询的规范形式
Looker 的 SQL 查询始终采用以下格式。
SELECT
<dimension>, <dimension>, ...
<measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>
LookML 项目定义 SQL 查询中引用的所有维度、测量值、探索和视图。过滤条件表达式由用户在 Looker 中指定,用于构建临时查询。也可以直接在 LookML 中声明过滤条件表达式,将其应用于所有查询。
Looker 查询的基本组成部分
所有 Looker 查询都由应用于 LookML 项目的这些基本参数表示,如上一个查询示例所示。
Looker 使用以下参数生成完整的 SQL 查询:
model
:要定位的 LookML 模型的名称,用于指定目标数据库explore
:要查询的探索的名称,用于填充 SQLFROM
子句- 字段:要包含在查询中的
dimension
和measure
参数,用于填充 SQLSELECT
子句 filter
:要应用于零个或多个字段的查询器过滤条件表达式,用于填充 SQLWHERE
和HAVING
子句- 排序顺序:排序依据的字段以及用于填充 SQL
ORDER BY
子句的排序顺序
这些参数正是用户在 Looker 探索页面上构建查询时指定的元素。这些元素会在使用 Looker 执行查询的所有模式中显示,例如在生成的 SQL 中、表示查询的网址中以及 Looker API 中。
LEFT JOIN
子句指定的视图如何处理?JOIN
子句会根据 LookML 模型的结构进行填充,该结构指定了视图如何与探索联接。构建 SQL 查询时,Looker 仅在需要时包含 JOIN
子句。用户在 Looker 中构建查询时,不需要指定表的联接方式,因为这类信息是在模型中编码的,而 Looker 为企业用户带来的最有力的好处之一就是这类信息。
示例查询和生成的 SQL
我们在 Looker 中构建一个查询,以演示如何根据上一个模式生成查询。假设某个电子商务商店的数据库中有两个表:orders 和 users,用于跟踪用户和订单。
orders |
---|
id INT |
created_at DATETIME |
users_id INT |
status VARCHAR(255) |
traffic_source VARCHAR(15) |
users |
---|
id INT |
email VARCHAR(255) |
first_name VARCHAR(255) |
last_name VARCHAR(255) |
created_at DATETIME |
zip INT |
country VARCHAR(255) |
state VARCHAR(255) |
city VARCHAR(255) |
age INT |
traffic_source VARCHAR(15) |
我们在 Looker 探索中查找按状态(USERS State)分组且按订单创建日期(ORDERS Created Date)过滤的订单数量(ORDERS Count)。
如需查看由 Looker 生成和执行的 SQL 查询,请点击数据面板中的 SQL 标签页。
SELECT COALESCE(users.state, ' ') AS "_g1",
users.state AS 'users.state',
COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id
WHERE
orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500
请注意与规范查询公式的相似之处。Looker SQL 展示了机器生成的代码的一些特征(例如 COALESCE(users.state,'') AS "_g1"
),但始终适合公式。
在 Looker 中尝试更多查询,以证明查询结构始终相同。
在 Looker 的 SQL Runner 中运行原始 SQL
Looker 提供了一项名为 SQL Runner 的功能,该功能可让您针对在 Looker 中设置的数据库连接运行任何 SQL。
由于 Looker 生成的每个查询都会生成完整且有效的 SQL 命令,因此您可以使用 SQL Runner 来调查或试用查询。
在 SQL Runner 中执行的原始 SQL 查询会生成相同的结果集。如果 SQL 含有任何错误,SQL Runner 将突出显示 SQL 命令中第一个错误的位置,并在错误消息中包含错误的位置。
检查展开后的网址中的查询组件
在 Looker 中运行查询后,您可以检查展开的网址,了解 Looker 查询的基本组成部分。首先,从“探索”的齿轮菜单中选择分享,以打开分享网址菜单。
展开后的网址会提供足够的信息来重新创建查询。例如,以下展开的网址示例提供了以下信息:
https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
模型 | e_thelook |
探索 | events |
要查询和显示的字段 | fields=users.state,users.count |
排序字段和顺序 | sorts=users.count+desc |
过滤字段和值 | f[users.created_year]=2020 |
Looker 如何构建联接
在上述示例查询中,请注意 orders
Explore 出现在主 FROM
子句中,联接的视图出现在 LEFT JOIN
子句中。Looker 联接可以通过多种不同的方式编写,如需了解详情,请参阅 在 LookML 中使用联接页面。
SQL 块用于指定自定义 SQL 子句
并非 Looker 查询的所有元素都是由机器生成的。在某些情况下,数据模型需要提供具体的详细信息,以便 Looker 访问底层表并计算派生值。在 LookML 中,SQL 代码块是数据建模者提供的 SQL 代码段,Looker 会使用这些代码段来合成完整的 SQL 表达式。
最常见的 SQL 块参数是 sql
,用于维度和测量定义。sql
参数用于指定用于引用底层列或执行汇总函数的 SQL 子句。一般来说,所有以 sql_
开头的 LookML 参数都需要某种形式的 SQL 表达式。例如:sql_always_where
、sql_on
和 sql_table_name
。如需详细了解每个参数,请参阅 LookML 参考文档。
维度和测量的 SQL 代码块示例
以下代码示例提供了维度和衡量指标的几个 SQL 代码块示例。LookML 替换运算符 ($) 会使这些 sql
声明看起来与 SQL 不同。不过,替换完成后,生成的字符串是纯 SQL,Looker 会将其注入到查询的 SELECT
子句中。
dimension: id {
primary_key: yes
sql: ${TABLE}.id ;; # Specify the primary key, id
}
measure: average_cost {
type: average
value_format: "0.00"
sql: ${cost} ;; # Specify the field that you want to average
# The field 'cost' is declared elsewhere
}
dimension: name {
sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
type: number
sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}
如本示例中的最后两个维度所示,SQL 块可以使用底层数据库支持的函数(在本例中,例如 MySQL 函数 CONCAT
和 DATEDIFF
)。您在 SQL 块中使用的代码必须与数据库使用的 SQL 方言匹配。
派生表的 SQL 块示例
派生表还使用 SQL 块来指定派生表的查询。以下是基于 SQL 的派生表示例:
view: user_order_facts {
derived_table: {
sql:
SELECT
user_id
, COUNT(*) as lifetime_orders
FROM orders
GROUP BY 1 ;;
}
# later, dimension declarations reference the derived column(s)…
dimension: lifetime_orders {
type: number
}
}
用于过滤探索的 SQL 代码块示例
借助 sql_always_where
和 sql_always_having
LookML 参数,您可以将 SQL 块注入 SQL WHERE 或 HAVING 子句,从而限制查询可用的数据。在此示例中,LookML 替换运算符 ${view_name.SQL_TABLE_NAME}
用于引用派生表:
explore: trips {
view_label: "Long Trips"
# This will ensure that we only see trips that are longer than average!
sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}