Looker 如何生成 SQL

如果您有 SQL 背景，那么您可能很好奇 Looker 如何生成 SQL。从根本上讲，Looker 是一款工具，能够生成 SQL 查询并针对数据库连接提交这些查询。Looker 会根据 LookML 项目制定 SQL 查询，该项目描述了数据库中表和列之间的关系。通过了解 Looker 如何生成查询，您将更好地了解 LookML 代码如何转换为高效的 SQL 查询。

每个 LookML 参数都会通过更改查询的结构、内容或行为来控制 Looker 生成 SQL 的某些方面。本页介绍了 Looker 生成 SQL 的原理，但未详细介绍所有 LookML 元素。如需了解 LookML 参数，LookML 快速参考文档页面是一个不错的起点。

查看查询

在已保存的 Look 或 探索中，您可以使用 数据 面板中的 SQL 标签页查看 Looker 发送到数据库以获取数据的内容。您还可以使用 SQL 标签页底部的在 SQL Runner 中打开 和在 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：要查询的探索的名称，用于填充 SQL FROM 子句
• 字段：要包含在查询中的 dimension 和 measure 参数，用于填充 SQL SELECT 子句
• filter：要应用于零个或多个字段的 Looker 过滤条件表达式，用于填充 SQL WHERE 和 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 探索中查找按州分组的订单数（ORDERS Count），并按订单的创建日期（ORDERS Created Date）过滤。

如需查看 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 将在连接时发出 SET TIMEZONE，这可能会影响结果。

检查展开后的网址中的查询组件

在 Looker 中运行查询后，您可以检查展开后的网址，以查看 Looker 查询的基本组件。首先，从探索的齿轮菜单中选择分享 ，以打开分享网址 菜单。

注意： 如果您从 Look 开始，请点击 Look 的从此处探索 链接，以在探索中打开查询。

展开后的网址提供了足够的信息来重新创建查询。例如，此展开后的网址示例提供了以下信息：

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 探索显示在主 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});;
}