打开 SQL 接口

借助 Looker LookML 语义建模层，数据分析师可以在 SQL 数据库中定义维度、汇总、计算和数据关系。LookML 模型支持代码重复使用和 Git 集成。结构良好的 LookML 模型可让用户自行执行自助数据探索和报告。

LookML 模型是从 Looker 请求的任何数据的基础，无论该请求来自 Looker 界面中的 Looker Explore 界面、公司门户或其他第三方应用中的嵌入式可视化，还是使用 Looker API 开发的自定义应用。开放式 SQL 接口可让任何支持 Java 数据库连接 (JDBC) 的第三方应用访问 LookML 模型。应用可以像连接到数据库一样连接到 LookML 模型，让用户能够利用数据分析师在 LookML 模型中完成的所有工作，同时使用他们最熟悉的任何工具。

Open SQL 接口如何显示 LookML 项目元素

若要了解 Open SQL 接口如何显示 LookML 项目的元素，请务必了解 LookML 项目的结构。

LookML 项目是一个文件集合，其中的文件负责说明用于在 Looker 中执行 SQL 查询的对象、数据库连接和界面元素（如需了解详情，请参阅 LookML 术语和概念）。以下 LookML 项目概念与 Open SQL 接口相关：

• LookML 模型可指定数据库连接以及一个或多个探索。Open SQL 接口会将模型显示为数据库架构。
• 探索是对一个或多个视图以及这些视图之间的联接关系的逻辑分组。Open SQL 接口会将“探索”显示为数据库表。
• 视图用于定义一组字段（包括维度和测量列）。视图通常基于数据库中的表或派生表。视图可以包含底层数据库表中的列，以及最终用户可能需要的任何自定义维度或测量。Open SQL 接口会将视图名称和字段名称的组合显示为数据库列名称。例如，Open SQL 接口会将 order_items 视图中的 id 维度显示为名为 order_items.id 的数据库列。

Looker 探索可以定义多个视图之间的联接关系。由于一个视图中可能存在与其他视图中字段同名的字段，因此 Open SQL 接口在引用列时会同时包含视图名称和字段名称。因此，在向 Open SQL 接口发送查询时，请使用以下格式引用列名称：

`<view_name>.<field_name>`

例如，如果有一个名为 order_items 的探索将名为 customer 的视图与名为 product 的视图联接起来，并且这两个视图都具有 id 维度，那么您可以分别将这两个 id 字段称为 `customer.id` 和 `product.id`。如需将完全限定名称与“探索”名称搭配使用，您可以将这两个字段称为 `order_items`.`customer.id` 和 `order_items`.`product.id`。（如需了解在引用数据库标识符时应将反引号放在何处，请参阅在数据库标识符周围使用反引号。）

设置 Open SQL 接口

如需使用 Open SQL 接口，请执行以下步骤：

1. 验证是否满足要求。
2. 下载 Open SQL Interface JDBC 驱动程序文件。

以下部分介绍了这些步骤。

要求

如需使用 Open SQL 接口，必须具备以下组件：

• 您要使用的第三方应用（例如 Tableau、ThoughtSpot 或自定义应用）必须能够连接到您的 Looker 实例。只要 Looker 实例的网络连接方式允许第三方应用访问该 Looker 实例，Open SQL 接口便可与客户托管的 Looker 实例搭配使用。
• 使用来自 Google BigQuery 连接的数据的 LookML 项目。（LookML 项目必须具有一个模型文件，用于在其 connection 参数中指定 Google BigQuery 连接）。
• 一个 Looker 用户角色，该角色对您要使用 Open SQL 接口访问的 LookML 模型具有 explore 权限。

下载 Open SQL Interface JDBC 驱动程序

Looker Open SQL Interface JDBC 驱动程序称为 avatica-<release_number>-looker.jar。从 GitHub 下载最新版本：https://github.com/looker-open-source/calcite-avatica/releases。

JDBC 驱动程序需要以下网址格式：

jdbc:looker:url=https://Looker instance URL

例如：

jdbc:looker:url=https://myInstance.cloud.looker.com

JDBC 驱动程序类为：

org.apache.calcite.avatica.remote.looker.LookerDriver

向 Open SQL 接口进行身份验证

Open SQL 接口支持三种身份验证方法：

• OAuth
• 使用 API 密钥生成访问令牌
• API 密钥

OAuth

支持 OAuth 的 JDBC 客户端可以配置为使用 Looker 实例的 OAuth 服务器。请按照以下步骤配置 OAuth 身份验证：

1. 使用 API Explorer 扩展程序将 JDBC OAuth 客户端注册到 Looker 实例，以便 Looker 实例能够识别 OAuth 请求。如需查看相关说明，请参阅注册 OAuth 客户端应用。
2. 使用 OAuth 登录 Looker 以请求访问令牌。如需查看示例，请参阅使用 OAuth 执行用户登录。
3. 在打开与 Open SQL 接口的 JDBC 连接时，使用 Properties 对象传递 OAuth 凭据。

以下是使用 DriverManager#getConnection(<String>, <Properties>`) 的示例：

String access_token = getAccessToken() //uses the Looker OAuth flow to get a token
String URL = "jdbc:looker:url=https://myInstance.cloud.looker.com"
Properties info = new Properties( );
info.put("token", access_token);
Connection conn = DriverManager.getConnection(URL, info);

注意： 若要使用此方法，您必须在 Properties 对象中使用键 token，并将从 Looker 检索到的访问代码指定为 token 的值。

使用 API 密钥生成访问令牌

您可以按照以下步骤使用 Looker API 生成可传递给 Open SQL Interface JDBC 驱动程序的访问令牌，而无需使用标准 OAuth 流程生成访问令牌：

1. 按照管理设置 - 用户页面上的说明为您的 Looker 用户生成 API 密钥。

2. 为您的 Looker 实例使用 login API 端点。响应包含格式为 Authorization: token <access_token> 的访问令牌。以下是可用于发出此请求的 curl 命令示例：

     curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\
   

3. 在打开与 Open SQL 接口的 JDBC 连接时，将响应的 <access_token> 值作为 Properties 对象中的令牌传递，以传递 OAuth 凭据。

API 密钥

您还可以使用 API 密钥进行身份验证，而无需使用用户名和密码。API 密钥的安全性不及 OAuth，并且可能仅在 Open SQL 接口的预览版期间可用。如需了解如何为 Looker 实例创建 API 密钥，请参阅 API 密钥。

将 Looker API 密钥的客户端 ID 部分用作用户名。使用客户端密钥部分作为密码。

使用 Open SQL 接口运行查询

使用 Open SQL 接口运行查询时，请注意以下准则：

• Open SQL 接口接受遵循 GoogleSQL 语法的 SQL 查询。
• 若要使用开放式 SQL 接口，需要在模型、探索和字段标识符周围添加反引号 (`)。如需了解详情和示例，请参阅在数据库标识符周围使用反引号。
• Open SQL 接口支持大多数 BigQuery 运算符。
• 使用 Open SQL 接口时，您必须将查询中包含的所有 LookML 测量值封装在特殊函数 AGGREGATE() 中（包括反引号）。请参阅使用 AGGREGATE() 指定 LookML 测量部分。

SQL 限制

向 Open SQL 接口发送查询时，请注意以下 SQL 限制：

• Open SQL 接口仅支持 SELECT 查询。Open SQL 接口不支持 UPDATE 和 DELETE 语句，也不支持任何其他数据定义语言 (DDL)、数据操纵语言 (DML) 或数据控制语言 (DCL) 语句。
• Open SQL 接口不支持 JOIN 运算符。
  • 您无法向 Open SQL 接口发送包含 JOIN 运算符的查询，以便在同一探索中或跨两个不同的探索中创建联接。
  • 如果您想在数据库中的两个表之间创建联接，可以在 LookML 项目的模型文件中的探索定义中，针对一个或多个视图创建联接，以便在 LookML 模型中执行此操作。
• Open SQL 接口不支持窗口函数调用。
• Open SQL 接口不支持子查询。
• Open SQL 接口不支持时区转换。LookML 模型中的日期时间将采用 DATETIME 类型，并采用您在设置中定义的时区（用户时区、应用时区或数据库时区设置）。
• Open SQL 接口不支持 BigQuery 数据类型 geography、JSON 和 time。

在数据库标识符周围使用英文反引号

向 Open SQL 接口发送查询时，请在架构、表和列标识符周围使用反引号。下面介绍了如何使用反引号和 Looker 字词指定数据库元素：

• schema：`<model_name>`
• table: `<explore_name>`

• 列：`<view_name>.<field_name>`

  注意 ：<view_name> 和 <field_name> 元素在单个反引号内组合。

下面是一个使用这些元素的 SELECT 语句格式示例：

SELECT `view.field`
  FROM `model`.`explore`
  LIMIT 10;

使用 AGGREGATE() 指定 LookML 指标

数据库表通常仅包含维度，即用于描述表中某一行的单个属性的数据。不过，LookML 项目可以同时定义维度和测量字段。测量是多行数据的汇总，例如 SUM、AVG、MIN 或 MAX。（系统也支持其他类型的测量，如需查看受支持的 LookML 测量类型的完整列表，请参阅测量类型页面。）

使用 Open SQL 接口时，您必须将查询中包含的所有 LookML 测量值封装在特殊函数 AGGREGATE() 中（包括反引号）。例如，您可以使用此方法在订单视图中指定计数测量参数：

AGGREGATE(`orders.count`)

无论 LookML 测量值位于 SELECT 子句、HAVING 子句还是 ORDER BY 子句中，您都必须将其封装在 AGGREGATE() 函数中。

注意 ：您无法在 GROUP BY 子句中使用 LookML 测量参数。

如果您不确定某个字段是否为 LookML 测量，可以使用 DatabaseMetaData.getColumns 方法访问 LookML 项目的元数据。对于任何 LookML 测量，IS_GENERATEDCOLUMN 列将显示 YES；对于 LookML 维度，IS_GENERATEDCOLUMN 列将显示 NO。如需了解详情，请参阅访问数据库元数据部分。

使用 JSON_OBJECT 指定仅限过滤条件的字段和参数

开放式 SQL 接口支持参数和仅限过滤条件的字段。

使用 Open SQL 接口运行查询时，您可以通过添加采用以下格式的 JSON_OBJECT 构造函数调用，将参数和仅限过滤的字段应用于查询：

JSON_OBJECT(
    '<view>.<parameter name>', '<parameter value>',
    '<view>.<filter name>', '<Looker filter expression>'
)

JSON 对象可以包含零个或多个过滤条件键值对，以及零个或多个参数键值对。

• JSON_OBJECT 构造函数中的键必须是仅限过滤条件的字段或参数的名称。
• 对于仅限过滤条件的字段，每个键的值都必须是 Looker 字符串过滤条件表达式。
• 对于参数，每个键的值必须是 parameter 定义中定义的纯值。

请参阅以下部分，了解如何将参数和仅限过滤条件的字段与 Open SQL 接口搭配使用。

参数示例

下面的示例展示了如何将 parameter 与 Open SQL 接口搭配使用。假设 customers 视图在 Looker 中定义了以下参数：

parameter: segment {
  type: string
  allowed_value: {
    label: "Small (less than 500)"
    value: "small_customers"
  }
  allowed_value: {
    label: "Larger (greater than 10,000)"
    value: "large_customers"
  }
  allowed_value: {
    label: "Medium customers (Between 500 and 10,000)"
    value: "medium_customers"
  }
}

您可以将此查询发送到 Open SQL 接口，以将 medium_customers 的 segment 参数值应用于查询：

SELECT `customers.segment_size`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'customers.segment', 'medium_customers'
))
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

打开 SQL 接口将此参数值传递给 Looker 中的查询，Looker 将 medium_customers 值应用于“探索”中配置为使用 segment 参数的任何字段。如需了解参数在 Looker 中的运作方式，请参阅 parameter 文档。

注意： 如果您未在 JSON_OBJECT 构造函数中添加参数值，Open SQL 接口将返回错误。

仅限过滤条件的字段示例

您可以将 filter 字段与 Open SQL 接口搭配使用。例如，如果 products 视图在 Looker 中定义了维度和仅限过滤条件的字段，如下所示：

filter: brand_select {
  type: string
  }

dimension: brand_comparitor {
  sql:
    CASE
      WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
      THEN ${products.brand_name}
      ELSE "All Other Brands"
    END ;;
    }

您可以通过发送类似以下的查询，将 brand_select 过滤条件与 Open SQL 接口搭配使用：

SELECT `products.brand_comparator`, `products.number_of_brands`,
  AGGREGATE(`products.total_revenue`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'products.brand_select', '%Santa Cruz%'
))
GROUP BY `products.brand_comparator`
ORDER BY 3 DESC LIMIT 5;

打开 SQL 接口后，系统会将 Looker 字符串过滤条件表达式 %Santa Cruz% 应用于 Looker 中的查询。如需了解仅限过滤条件的字段在 Looker 中的运作方式，请参阅 filter 文档。

在 WHERE 或 HAVING 子句中提供 always_filter 或 conditionally_filter 值

Open SQL 接口可以支持包含 always_filter 或 conditionally_filter 的探索，但不能同时支持这两者。

如果您使用 always_filter 或 conditionally_filter 定义了 LookML Explore，则需要将 SQL 查询中过滤条件字段的值传递给 Open SQL 接口：

• 如果过滤条件定义指定了一个或多个维度，则您必须在 SQL 查询中为每个过滤维度添加 WHERE 子句。
• 如果过滤条件定义指定了一个或多个测量，则您必须在 SQL 查询中为每个过滤条件测量添加 HAVING 子句。

例如，假设您在 faa 模型中定义了一个 LookML 探索 flights，其中包含一个 always_filter 参数，用于指定 country 和 aircraft_category 维度以及 count 测量，如下所示：

explore: flights {
  view_name: flights
  always_filter: {
    filters: [country : "Peru" , aircraft_category : "Airplane", count : ">1"]
  }
}

在对 Open SQL 接口的查询中，您必须使用 WHERE 子句传递过滤维度的值，并使用 HAVING 子句将衡量过滤条件的值传递给 LookML 模型，例如：

SELECT
    `flights.make`
FROM
    `faa`.`flights`
      WHERE `flights.country` = 'Ecuador' AND `flights.aircraft_category` = 'Airplane'
      GROUP BY
          1
      HAVING `flights.count` > 2) 
LIMIT 5

如果您未为 always_filter 参数中指定的每个维度和测量参数传递过滤条件值，则查询将返回错误。conditionally_filter 参数中指定的维度和测量标准也是如此，不过您可以使用 unless 子参数定义 conditionally_filter 参数，如下所示：

explore: flights {
  view_name: flights
  conditionally_filter: {
    filters: [country : "Peru" , aircraft_category : "Airplane"]
    unless: [count]
  }
}

在这种情况下，您必须为 conditionally_filter 的 filters 子参数中指定的每个维度和测量参数传递过滤条件值，除非您改为在 unless 子参数中针对字段指定过滤条件。（如需详细了解如何使用 unless 子参数，请参阅 conditionally_filter 文档页面。）

例如，以下任一对 Open SQL 接口的查询都是可接受的。第一个查询为 filters 子参数中指定的字段提供过滤条件值，第二个查询为 unless 子参数中指定的字段提供过滤条件值：

SELECT
    `flights.make`
FROM
    `faa`.`flights`
      WHERE `flights.country` = 'Ecuador' AND `flights.aircraft_category` = 'Airplane'
      
LIMIT 5

SELECT
    `flights.make`
FROM
    `faa`.`flights`
      GROUP BY
          1
      HAVING `flights.count` > 2

示例

下面是一个同时使用维度和指标的查询示例。此查询会从“客户”视图检索“州”和“城市”维度，并从“订单”视图检索“总金额”衡量标准。这两个视图都会联接到电子商务模型中的订单探索。对于订单数量超过 10 个的城市，此查询响应会按订单金额显示前 5 个城市：

SELECT `customers.state`, `customers.city`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

访问数据库元数据

Open SQL 接口支持标准 JDBC DatabaseMetaData 接口的一部分，该接口用于获取有关底层数据库的信息。您可以使用 DatabaseMetaData 接口的以下方法获取 LookML 模型的相关信息：

• DatabaseMetadata.getSchemas
• DatabaseMetaData.getTables

• DatabaseMetaData.getColumns

  注意： 打开的 SQL 接口仅会针对用户有权访问的模型、探索和字段返回结果。

DatabaseMetadata.getSchemas

下表介绍了 LookML 模型如何与 DatabaseMetadata.getSchemas 接口方法响应中的标准数据库结构相关联。

getSchemas 响应列	说明
TABLE_SCHEM	LookML 模型名称
TABLE_CATALOG	(null)

DatabaseMetadata.getTables

下表介绍了 LookML 模型如何与 DatabaseMetaData.getTables 接口方法响应中的数据库结构相关联。响应包含标准 JDBC 元数据以及 Looker 专用元数据：

getTables 响应列	说明
JDBC 标准元数据
TABLE_CAT	(null)
TABLE_SCHEM	LookML 模型名称
TABLE_NAME	LookML 探索的名称
TABLE_TYPE	始终返回值 TABLE_TYPE
特定于 Looker 的元数据
DESCRIPTION	探索说明
LABEL	“探索”标签
TAGS	探索标签

DatabaseMetadata.getColumns

下表介绍了 LookML 模型如何与 DatabaseMetaData.getColumns 接口方法响应中的数据库结构相关联。响应包含标准 JDBC 元数据以及 Looker 专用元数据：

getColumns 响应列	说明
JDBC 标准元数据
TABLE_CAT	(null)
TABLE_SCHEM	LookML 模型名称
TABLE_NAME	LookML 探索名称
COLUMN_NAME	采用 `<view_name>.<field_name>` 格式的 LookML 字段名称。例如 `orders.amount`。
DATA_TYPE	列的 java.sql.Types 代码。例如，Looker yesno 字段的 SQL 类型代码为 16（布尔值）。
ORDINAL_POSITION	“探索”中的字段的从 1 开始的序数（维度和测量参数按视图名称、字段名称的字母顺序混合在一起）
IS_NULLABLE	始终返回值 YES
IS_GENERATEDCOLUMN	YES 适用于测量值，NO 适用于维度
特定于 Looker 的元数据
DIMENSION_GROUP	如果该字段属于某个维度组，则为维度组的名称。如果该字段不属于任何维度组，则此值为 null。
DRILL_FIELDS	为维度或指标设置的展开字段列表（如果有）
FIELD_ALIAS	字段的别名（如果有）
FIELD_CATEGORY	字段是 dimension 还是 measure
FIELD_DESCRIPTION	字段 description
FIELD_GROUP_VARIANT	如果字段显示在字段组标签下，FIELD_GROUP_VARIANT 将指定显示在组标签下方的字段的简短名称。
FIELD_LABEL	字段 label
FIELD_NAME	维度或测量字段的名称
HIDDEN	字段是从“探索”中的字段选择器 (TRUE) 中隐藏的，还是在“探索”中的字段选择器 (FALSE) 中显示的。
LOOKER_TYPE	维度或测量的 LookML 字段类型
REQUIRES_REFRESH_ON_SORT	是否必须刷新 SQL 查询才能重新排序字段的值 (TRUE)，还是无需刷新 SQL 查询即可重新排序字段的值 (FALSE)。
SORTABLE	字段是否可排序 (TRUE) 或不可排序 (FALSE)
TAGS	字段标记
USE_STRICT_VALUE_FORMAT	字段是否使用严格的值格式 (TRUE) 或不使用 (FALSE)
VALUE_FORMAT	字段的值格式字符串
VIEW_LABEL	字段的查看标签
VIEW_NAME	在 LookML 项目中定义该字段的视图的名称

在 Looker 界面中识别打开的 SQL 接口查询

Looker 管理员可以使用 Looker 界面来确定哪些查询来自 Open SQL 接口：

• 在查询管理页面中，来自开放式 SQL 接口的查询的来源值为“SQL 接口”。用户值将显示运行查询的 Looker 用户的名称。您可以点击查询的详细信息按钮，查看与该查询相关的更多信息。在详细信息对话框中，您可以点击 SQL 接口查询，查看从开放式 SQL 接口发送到 Looker 的 SQL 查询。

• 在系统活动记录探索中，来自开放式 SQL 接口的查询的来源值为“sql_interface”。用户电子邮件地址值将显示运行查询的 Looker 用户的电子邮件地址。您可以将 Looker 实例地址插入到以下网址开头，直接前往按“sql_interface”过滤的历史记录探索页面：

  https://Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=%22sql_interface%22
  

第三方依赖项的代码库

以下链接提供对 Looker JDBC 驱动程序使用的第三方依赖项的 Google 托管代码库的访问权限：

https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/