打开 SQL 接口

借助 Looker LookML 语义建模层，数据分析师可以在 SQL 数据库中定义维度、聚合、计算和数据关系。LookML 模型提供代码可重用性和 Git 集成功能。结构合理的 LookML 模型可让用户自行进行数据探索和报告。

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

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

如需了解开放式 SQL 接口如何呈现 LookML 项目的元素，请务必了解如何构建 LookML 项目。

LookML 项目是一组文件，用于描述用于在 Looker 中执行 SQL 查询的对象、数据库连接和界面元素（如需了解详情，请参阅 LookML 术语和概念）。以下 LookML 项目概念与开放式 SQL 接口相关：

  • LookML 模型指定数据库连接以及一个或多个探索。modelOpen SQL 接口将模型显示为数据库架构。
  • 探索是一个或多个视图的逻辑分组，以及这些视图之间的联接关系。Open SQL 界面会将探索显示为数据库表。
  • 视图定义字段的集合（包括维度和测量）。视图通常基于数据库中的表或派生表。视图可以包含底层数据库表中的列，以及最终用户可能需要的任何自定义维度或测量值。Open SQL 接口会将视图名称和字段名称的组合显示为“数据库列名称”。例如，order_items 视图中的 id 维度由 Open SQL Interface 显示为名为 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`。（如需了解在引用数据库标识符时反引号的放置位置，请参阅对数据库标识符使用反引号。）

设置开放式 SQL 接口

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

  1. 验证是否满足要求。
  2. 在 Looker 实例上启用开放式 SQL 界面。
  3. 下载 Open SQL 接口 JDBC 驱动程序文件。

下面几部分介绍了这些步骤。

使用要求

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

在 Looker 实例上启用开放式 SQL 界面

通过执行以下步骤，在您的实例上启用 Open SQL 接口：

下载 Open SQL 接口 JDBC 驱动程序

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

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

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

例如：

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

JDBC 驱动程序类如下所示：

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

向开放式 SQL 接口进行身份验证

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

OAuth

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

  1. 使用 API Explorer 扩展程序向 Looker 实例注册 JDBC OAuth 客户端，以便 Looker 实例能够识别 OAuth 请求。有关说明，请参阅注册 OAuth 客户端应用。
  2. 使用 OAuth 登录 Looker，以请求访问令牌。有关示例，请参阅使用 OAuth 执行用户登录。
  3. 在打开与打开 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);

使用 API 密钥生成访问令牌

您可以按照以下步骤使用 Looker API 生成可传递给 Open SQL 接口 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 安全，可能仅在开放 SQL 接口预览版期间可用。如需了解如何为 Looker 实例创建 API 密钥，请参阅 API 密钥。

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

使用开放式 SQL 接口运行查询

使用开放式 SQL 界面运行查询时，请注意以下准则：

LookML 限制

向开放式 SQL 接口发送查询时，请注意以下 LookML 限制：

SQL 限制

向开放式 SQL 界面发送查询时，请注意以下 SQL 限制：

  • Open SQL 接口仅支持 SELECT 查询。开放式 SQL 接口不支持 UPDATE 和 DELETE 语句，也不支持任何其他数据定义语言 (DDL)、数据操纵语言 (DML) 或数据控制语言 (DCL) 语句。
  • 开放式 SQL 接口不支持 JOIN 运算符。
    • 您不能使用 JOIN 运算符向 Open SQL 界面发送查询来在同一探索或两个不同的探索中创建联接。
    • 如果要在数据库中两个表之间创建联接，可以在 LookML 模型中执行此操作，方法是在 LookML 项目的模型文件内的探索定义中创建与一个或多个视图的联接。
  • Open SQL 接口不支持 window 函数调用。
  • Open SQL 接口不支持子查询。
  • 开放式 SQL 界面不支持时区转换。LookML 模型中的日期时间将采用您在设置中定义的时区（用户时区、应用时区或数据库时区设置）中的 DATETIME 类型。
  • Open SQL 接口不支持 BigQuery 数据类型地理位置、JSON 和时间。

对数据库标识符使用反引号

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

  • schema：`<model_name>`
  • 表：`<explore_name>`

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

以下是使用以下元素的 SELECT 语句格式示例：

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

使用 AGGREGATE() 指定 LookML 测量

数据库表通常仅包含维度，即描述表中某一行的单个属性的数据。但是，LookML 项目可以定义维度和测量。测量是跨多个行的数据聚合，例如 SUM、AVG、MIN 或 MAX。（也支持其他类型的测量，请参阅测量类型页面，查看受支持的 LookML 测量类型的完整列表）。

使用开放式 SQL 接口，您必须指定查询中包含的任何 LookML 测量，只需将测量（包括反引号）封装在特殊函数 AGGREGATE() 中即可。例如，您可以使用此代码从 orders 视图指定 count 测量：

AGGREGATE(`orders.count`)

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

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

示例

以下是同时使用维度和测量的示例查询。此查询会从state视图中检索state和state维度，并从state视图中检索state测量。这两个视图都会合并到“ecommerce”模型中的“订单”探索中。对于订单数量超过 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;

使用 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 定义中定义的普通值。

如需查看有关在开放式 SQL 接口中使用参数和仅限过滤条件的字段的示例，请参阅以下部分。

参数示例

以采用 Open SQL 接口的 parameter 为例，如果 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 文档。

仅包含过滤条件的字段示例

您可以将 filter 字段用于开放式 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 文档。

访问数据库元数据

开放式 SQL 接口支持标准 JDBC DatabaseMetaData 接口的子集，该接口用于获取有关底层数据库的信息。您可以使用 DatabaseMetaData 接口的以下方法获取有关 LookML 模型的信息：

DatabaseMetadata.getSchemas

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

“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 (BOOLEAN)。
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 界面确定哪些查询来自开放式 SQL 界面：

  • 在查询管理页面中，来自 Open SQL 界面的查询的来源值为“Sql 接口”。用户值将显示运行查询的 Looker 用户的姓名。

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

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

有关开放式 SQL 接口的反馈

如果您对开放式 SQL 接口有任何疑问或需要功能请求，请与 looker-sql-interface@google.com 联系。