通过联接,您可以关联不同的数据视图,以便同时探索多个数据视图中的数据,并了解数据的不同部分之间的关系。
例如,您的数据库可能包含表 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 参数。如需详细了解如何将此 LookML 转换为 SQL,请参阅 join
参数参考页面。
Chat 团队提示:用户最常询问的验证错误“未知或无法访问的字段”,该错误可能是由于未成功加入而导致的。如需了解详情,请参阅有关此错误的最佳实践页面。
联接参数
联接所用的四个主要参数为:join
、type
、relationship
和 sql_on
。
第 1 步:启动探索
首先,创建 order_items
探索:
explore: order_items { ... }
第 2 步:join
如需联接表,必须先在视图中声明该表。在此示例中,orders
是模型中的现有视图。
然后,使用 join
形参声明您想要将 orders
视图联接到 order_items
:
explore: order_items {
join: orders { ... }
}
第 3 步:type
考虑要执行哪个 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
之间的联接 relationship
。正确声明联接的 relationship
对于 Looker 计算准确的测量值非常重要。关系定义从 order_items
探索到 orders
视图。可能的选项包括 one_to_one
、many_to_one
、one_to_many
和 many_to_many
。
在本例中,单个 order
可以有多个 order_items
。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
使用 sql_on
或 foreign_key
参数声明如何将这两个表联接在一起。我们通常建议使用 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
合并到一起。
注意:users
视图现在必须在联接中通过其别名 buyers
和 sellers
来引用。
限制联接中的字段
借助 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
参数在 View 中定义。假设您在 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
}
为了正确处理联接的测量,Looker 需要您指定一个主键,其中值是完全唯一的非 NULL 值。如果您的数据不包含主键,请考虑串联多个字段是否会导致主键具有完全唯一的非 NULL 值。如果您的主键不是唯一的或包含 NULL 值,并且您的查询包含揭示这些问题的数据,则 Looker 会返回错误,如错误:非唯一值/主键(或 sql_distinct_key)、计算求和时的值溢出或冲突一文中所述的错误。
支持的 SQL 语言
为了让 Looker 支持 Looker 项目中的对称聚合,您的数据库方言也必须支持这类聚合。下表显示了哪些方言支持最新版 Looker 中的对称聚合:
方言 | 是否支持? |
---|---|
艾克蒂安雪崩 | 是 |
Amazon Athena | 是 |
Amazon Aurora MySQL | 是 |
Amazon Redshift | 是 |
阿帕奇·德鲁伊 | 否 |
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 | 是 |
Denodo 8 星 | 是 |
德雷米奥 | 否 |
Dremio 11 及更高版本 | 是 |
Exasol | 是 |
火箭 | 是 |
Google BigQuery 旧版 SQL | 是 |
Google BigQuery 标准 SQL | 是 |
Google Cloud PostgreSQL | 是 |
Google Cloud SQL | 是 |
Google Spanner | 是 |
绿紫红 | 是 |
HyperSQL | 否 |
IBM Netezza | 是 |
MariaDB | 是 |
Microsoft Azure PostgreSQL | 是 |
Microsoft Azure SQL 数据库 | 是 |
Microsoft Azure Synapse Analytics | 是 |
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 | 是 |
SAP HANA 2 及更高版本 | 是 |
SingleStore | 是 |
SingleStore 7 或更高版本 | 是 |
Snowflake | 是 |
Teradata | 是 |
Trino | 是 |
矢量 | 是 |
Vertica | 是 |
如果您的方言不支持对称聚合,在 Looker 中执行联接时要小心,因为某些类型的联接可能会导致汇总不准确(例如求和和平均值)。SQL 扇出问题社区帖子对此问题及其解决方法进行了详细说明。
详细了解联接
如需详细了解 LookML 中的联接参数,请参阅联接参考文档。