打开 SQL 接口

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

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

开放式 SQL 接口如何显示 LookML 项目元素

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

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

• LookML 模型用于指定数据库连接和一个或多个探索。Open SQL 接口将模型作为数据库架构公开。
• 探索是一项功能，可将一个或多个视图以及这些视图之间的联接关系进行逻辑分组。Open SQL 接口将探索显示为数据库表。
• 视图定义了一组字段（包括维度和指标）。视图通常基于数据库中的表或派生表。视图可以包含基础数据库表中的列，以及最终用户可能需要的任何自定义维度或度量。Open SQL 接口将视图名称和字段名称的组合作为数据库列名称公开。例如，order_items 视图中的 id 维度通过开放式 SQL 接口显示为名为 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 接口 JDBC 驱动程序文件。

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

要求

如需使用 Open SQL 接口，您需要以下组件：

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

下载 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://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 扩展程序向 Looker 实例注册 JDBC OAuth 客户端，以便 Looker 实例可以识别 OAuth 请求。如需查看相关说明，请参阅注册 OAuth 客户端应用。
2. 使用 OAuth 登录 Looker 以请求访问令牌。如需查看示例，请参阅使用 OAuth 执行用户登录。
3. 在打开与 Open SQL Interface 的 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 接口 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. 在 Properties 对象中传递响应的 <access_token> 值作为令牌，以便在打开与 Open SQL Interface 的 JDBC 连接时传递 OAuth 凭据。

API 密钥

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

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

使用 Open SQL 接口运行查询

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

• Open SQL 接口接受符合 GoogleSQL 语法的 SQL 查询。
• Open SQL 接口要求在模型、Explore 和字段标识符周围添加反引号 (`)。如需了解更多信息和示例，请参阅在数据库标识符周围使用反引号。
• Open SQL 接口支持大多数 BigQuery 运算符。
• 使用 Open SQL 接口时，您必须使用特殊函数 AGGREGATE() 将查询中包含的任何 LookML 度量（包括反引号）括起来，以指定这些度量。请参阅使用 AGGREGATE() 指定 LookML 度量部分。

SQL 限制

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

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

在数据库标识符前后使用英文反引号

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

• schema：`<model_name>`
• 表格：`<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 接口时，您必须使用特殊函数 AGGREGATE() 将查询中包含的任何 LookML 度量（包括反引号）括起来，以指定这些度量。例如，使用此参数可指定来自 orders 视图的 count 指标：

AGGREGATE(`orders.count`)

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

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

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

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

Open 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 接口，以将 segment 参数值 medium_customers 应用于查询：

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;

Open 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 的 Explore，但不能同时支持这两种 Explore。

如果您已使用 always_filter 或 conditionally_filter 定义了 LookML 探索，则需要在 SQL 查询中为过滤字段传递值，以使用开放式 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 子句来传递衡量指标过滤条件值，例如：

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

示例

以下是同时使用维度和度量的查询示例。此查询从 customers 视图中检索 state 和 city 维度，并从 orders 视图中检索 total amount 指标。这两个视图都已加入到 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;

访问数据库元数据

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
REMARKS	(null)
TYPE_CAT	(null)
TYPE_SCHEM	(null)
TYPE_NAME	表示表格类型的字符串。可能的类型有 TABLE、VIEW、SYSTEM TABLE、GLOBAL TEMPORARY、LOCAL TEMPORARY、ALIAS、SYNONYM。
SELF_REFERENCING_COL_NAME	(null)
REF_GENERATION	(null)
特定于 Looker 的元数据
DESCRIPTION	探索说明
LABEL	探索标签
TAGS	探索标签
CONDITIONALLY_FILTER_UNLESS	探索的 conditionally_filter 参数的 unless 子参数中的字段列表。如果 unless 子参数中未指定任何字段，或者如果未为探索定义 conditionally_filter 参数，则此值为 null。

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)。
TYPE_NAME	表示列数据类型的字符串。对于用户定义的类型 (UDT)，类型名称是完全限定的。
COLUMN_SIZE	整数，表示列中可存储的最大字符数或字节数。
BUFFER_LENGTH	(null)
DECIMAL_DIGITS	表示数据比例的整数：对于适用的数据类型，指小数点右边的位数；对于其他数据类型，指小数位数。对于不适用 DECIMAL_DIGITS 的数据类型，系统会返回 Null。
NUM_PREC_RADIX	表示数据的基数（通常为 10 或 2）的整数。
NULLABLE	一个整数，用于指示是否允许 null 值： 0：columnNoNulls - 可能不允许 NULL 值 1：columnNullable - 明确允许 NULL 值 2：columnNullableUnknown - 可为 null 性未知
REMARKS	(null)
COLUMN_DEF	(null)
SQL_DATA_TYPE	(null)
SQL_DATETIME_SUB	(null)
CHAR_OCTET_LENGTH	对于字符数据类型，一个整数，表示列中的最大字节数。
ORDINAL_POSITION	探索中字段的从 1 开始的序号（按视图名称和字段名称的字母顺序混合排列维度和指标）
IS_NULLABLE	始终返回值 YES
SCOPE_CATALOG	(null)
SCOPE_SCHEMA	(null)
SCOPE_TABLE	(null)
SOURCE_DATA_TYPE	(null)
IS_AUTOINCREMENT	(null)
IS_GENERATEDCOLUMN	YES 适用于衡量指标，NO 适用于维度
Looker 特有的元数据
DIMENSION_GROUP	如果相应字段属于某个维度组，则为该维度组的名称。如果相应字段不属于任何维度组，则此值为 null。
DRILL_FIELDS	为维度或指标设置的下钻字段的列表（如有）
FIELD_ALIAS	相应字段的别名（如有）
FIELD_CATEGORY	相应字段是 dimension 还是 measure
FIELD_DESCRIPTION	字段说明
FIELD_GROUP_VARIANT	如果该字段显示在字段组标签下，则 FIELD_GROUP_VARIANT 将指定显示在组标签下的字段的较短名称。
FIELD_LABEL	字段 label
FIELD_NAME	维度或指标的名称
LOOKER_TYPE	维度或度量的 LookML 字段类型
REQUIRES_REFRESH_ON_SORT	是否必须刷新 SQL 查询才能重新排序字段的值 (TRUE)，或者是否可以在不刷新 SQL 查询的情况下重新排序字段的值 (FALSE)。
SORTABLE	字段是否可排序（TRUE）或不可排序（FALSE）
TAGS	字段 tags
USE_STRICT_VALUE_FORMAT	相应字段是否使用严格的值格式 (TRUE)FALSE
VALUE_FORMAT	相应字段的值格式字符串
VIEW_LABEL	相应字段的查看标签
VIEW_NAME	LookML 项目中定义相应字段的视图的名称
HIDDEN	相应字段在探索中的字段选择器中是隐藏 (TRUE) 还是可见 (FALSE)。
ALWAYS_FILTER	在字段上设置的 always_filter 参数的默认值。如果该字段不是 always_filter 参数的一部分，则此值为 null。
CONDITIONALLY_FILTER	在字段上设置的 conditionally_filter 参数的默认值。如果相应字段不是 conditionally_filter 参数的一部分，则此值为 null。

在 Looker 界面中识别 Open SQL 接口查询

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

• 在查询管理页面中，来自开放式 SQL 接口的查询的来源值为“SQL 接口”。用户值将显示运行查询的 Looker 用户的名称。您可以点击查询的详细信息按钮，以显示有关该查询的更多信息。在详细信息对话框中，您可以点击 SQL 接口查询，查看从 Open 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
  

第三方依赖项的代码库

以下链接可用于访问 Google 托管的 Looker JDBC 驱动程序所使用的第三方依赖项的代码库：

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