借助联接,您可以关联不同的视图,以便同时探索多个视图中的数据,并了解数据的不同部分之间的相互关系。
例如,您的数据库可能包含表 order_items
、orders
和 users
。您可以使用联接同时探索所有表中的数据。本页介绍了 LookML 中的联接,包括特定联接参数和联接模式。
联接始于“探索”
联接在模型文件中定义,用于建立探索和视图之间的关系。联接会将一个或多个数据视图与单个探索相关联,无论是直接关联还是通过其他合并数据视图来发挥作用。
假设有两个数据库表:order_items
和 orders
。为这两个表生成视图后,请在模型文件的 explore
参数下声明一个或多个视图:
explore: order_items { ... }
当您从 order_items
探索运行查询时,order_items
会显示在生成的 SQL 的 FROM
子句中:
SELECT ...
FROM order_items
您可以将更多信息加入 order_items
的“探索”专区。例如,您可以使用以下示例 LookML 将 orders
视图联接到 order_items
探索:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
}
之前显示的 LookML 可以完成两件事。首先,您可以在探索字段选择器中查看 orders
和 order_items
中的字段:
其次,LookML 介绍了如何将 orders
和 order_items
联接在一起。该 LookML 会转换为以下 SQL:
SELECT ...
FROM order_items
LEFT JOIN orders
ON order_items.order_id = orders.id
以下各部分更详细地介绍了这些 LookML 参数。
联接参数
有四个主要参数用于联接:join
、type
、relationship
和 sql_on
。
第 1 步:开始探索
首先,创建 order_items
Explore:
explore: order_items { ... }
第 2 步:join
如需联接表,必须先在视图中声明表。在此示例中,假设 orders
是模型中的现有视图。
然后,使用 join
形参声明您要将 orders
视图联接到 order_items
探索:
explore: order_items {
join: orders { ... }
}
第 3 步:type
考虑要执行哪种类型的联接。Looker 支持 LEFT JOIN
、INNER JOIN
、FULL OUTER JOIN
和 CROSS JOIN
。这些变量对应于 left_outer
、inner
、full_outer
和 cross
的 type
参数值。
explore: order_items {
join: orders {
type: left_outer
}
}
type
的默认值为 left_outer
。
第 4 步:relationship
定义 order_items
探索和 orders
视图之间的联接关系。正确声明联接关系对于 Looker 计算准确的衡量指标至关重要。关系是从 order_items
的“Explore”到 orders
视图定义的。可能的选项包括 one_to_one
、many_to_one
、one_to_many
和 many_to_many
。
在此示例中,一个订单可以有多个订单商品。从 order_items
探索与 orders
视图的关系是 many_to_one
:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
}
}
如果您没有在联接中添加 relationship
参数,则 Looker 默认为 many_to_one
。
如需了解如何为联接正确定义 relationship
参数的其他提示,请参阅正确使用 relationship
参数。
第 5 步:sql_on
声明如何将 order_items
表和 orders
表与 sql_on
参数或 foreign_key
参数联接起来。
sql_on
参数等效于为查询生成的 SQL 中的 ON
子句。借助此参数,您可以声明应匹配哪些字段以执行联接:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
}
您还可以编写更复杂的联接。例如,您可能只想联接 id
大于 1000 的订单:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} AND ${orders.id} > 1000 ;;
}
}
如需详细了解这些示例中的 ${ ... }
语法,请参阅替换运算符文档。
第 6 步:测试
通过转到 Order Items 探索,测试此联接是否按预期运行。您应该会看到来自 order_items
和 orders
的字段。
如需详细了解如何在“探索”中测试 LookML 更改,请参阅测试“探索”中的字段。
正在通过其他视图加入会议
您可以通过另一个视图将一个视图联接到“探索”。在联接参数示例中,您通过 order_id
字段将 orders
连接到 order_items
。此外,我们还可能需要将名为 users
的视图中的数据联接到 order_items
探索,即使这两个视图并不共用同一字段。为此,您可以通过 orders
视图进行联接。
使用 sql_on
参数或 foreign_key
参数将 users
视图与 orders
视图(而不是 order_items
探索)联接起来。为此,您可以将 orders
中的字段正确限定为 orders.user_id
。
下面是一个使用 sql_on
参数的示例:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
join: users {
type: left_outer
relationship: many_to_one
sql_on: ${orders.user_id} = ${users.id} ;;
}
}
多次加入一个视图
users
数据视图包含买方和卖方的数据。如需将此视图中的数据联接到 order_items
,但要分别针对买方和卖方执行联接,您可以使用 from
参数,将 users
联接两次,并使用不同的名称。
借助 from
参数,您可以指定要在联接中使用的视图,并为联接指定一个唯一名称。例如:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
join: buyers {
from: users
type: left_outer
relationship: many_to_one
sql_on: ${orders.buyer_id} = ${buyers.id} ;;
}
join: sellers {
from: users
type: left_outer
relationship: many_to_one
sql_on: ${orders.seller_id} = ${sellers.id} ;;
}
}
在这种情况下,只有买方数据会作为 buyers
联接,而只有卖方数据会作为 sellers
联接。
注意:现在,必须在联接中通过别名名称 buyers
和 sellers
引用 users
视图。
限制联接中的字段
借助 fields
参数,您可以指定从联接导入到探索中的字段。默认情况下,视图中的所有字段会在联接时被引入。不过,您可能只想导入部分字段。
例如,当 orders
与 order_items
联接时,您可能只希望通过联接加入 shipping
和 tax
字段:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
fields: [shipping, tax]
}
}
您还可以引用一组字段,例如 [set_a*]
。每个集合都是在视图中使用 set
参数定义的。假设您在 orders
视图中定义了以下集合:
set: orders_set {
fields: [created_date, shipping, tax]
}
当您将 orders
加入 order_items
时,您可以选择仅显示以下三个字段:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
fields: [orders_set*]
}
}
对称汇总
Looker 使用一种名为“对称聚合”的功能正确计算聚合(例如总和和平均值),即使联接导致扇出也是如此。了解对称聚合中更详细地介绍了对称聚合。对称聚合所解决的扇出问题在 SQL 扇出问题社区帖子中进行了解释。
必须提供主键
如需通过联接获取测量结果(汇总),您必须在涉及联接的所有视图中定义主键。
为此,请将 primary_key
参数添加到每个视图的主键字段定义中:
dimension: id {
type: number
primary_key: yes
}
支持的 SQL 语言
为了让 Looker 在 Looker 项目中支持对称聚合,您的数据库方言也必须支持对称聚合。下表显示了哪些方言在最新版本的 Looker 中支持对称聚合:
方言 | 是否支持? |
---|---|
Actian Avalanche | 是 |
Amazon Athena | 是 |
Amazon Aurora MySQL | 是 |
Amazon Redshift | 是 |
Apache Druid | 否 |
Apache Druid 0.13+ | 否 |
Apache Druid 0.18 及更高版本 | 否 |
Apache Hive 2.3 及更高版本 | 否 |
Apache Hive 3.1.2 及更高版本 | 否 |
Apache Spark 3 及更高版本 | 是 |
ClickHouse | 否 |
Cloudera Impala 3.1 及以上版本 | 是 |
搭配原生驱动程序的 Cloudera Impala 3.1 及更高版本 | 是 |
带有原生驱动程序的 Cloudera Impala | 否 |
DataVirtuality | 是 |
Databricks | 是 |
Denodo 7 | 是 |
迪诺多 8 号星 | 是 |
Dremio | 否 |
Dremio 11+ | 是 |
Exasol | 是 |
火箭 | 是 |
Google BigQuery 旧版 SQL | 是 |
Google BigQuery 标准 SQL | 是 |
Google Cloud PostgreSQL | 是 |
Google Cloud SQL | 是 |
Google Spanner | 是 |
Greenplum | 是 |
HyperSQL | 否 |
IBM Netezza | 是 |
MariaDB | 是 |
Microsoft Azure PostgreSQL | 是 |
Microsoft Azure SQL 数据库 | 是 |
Microsoft Azure Synapse 分析 | 是 |
Microsoft SQL Server 2008 及更高版本 | 是 |
Microsoft SQL Server 2012 及更高版本 | 是 |
Microsoft SQL Server 2016 | 是 |
Microsoft SQL Server 2017 及更高版本 | 是 |
MongoBI | 否 |
MySQL | 是 |
MySQL 8.0.12 及更高版本 | 是 |
Oracle | 是 |
Oracle ADWC | 是 |
PostgreSQL 9.5 及更高版本 | 是 |
PostgreSQL 9.5 之前的版本 | 是 |
PrestoDB | 是 |
PrestoSQL | 是 |
SAP HANA 2 及更高版本 | 是 |
SingleStore | 是 |
单一商店 7+ | 是 |
Snowflake | 是 |
TeraData | 是 |
Trino | 是 |
矢量 | 是 |
Vertica | 是 |
如果您的方言不支持对称聚合,请在 Looker 中执行联接时小心,因为某些类型的联接可能会导致汇总不准确(例如求和与平均值)。SQL 扇出问题社区帖子中详细介绍了此问题及其解决方法。
详细了解联接
如需详细了解 LookML 中的联接参数,请参阅联接参考文档。