開啟 SQL 介面

資料分析師可透過 Looker LookML 語意模型層，在 SQL 資料庫中定義維度、匯總、計算和資料關係。LookML 模型可重複使用程式碼，並提供 Git 整合功能。結構良好的 LookML 模型可讓使用者自行探索資料及製作報表。

無論要求來自 Looker UI 中的 Looker 探索介面、公司入口網站或其他第三方應用程式中嵌入的視覺化內容，還是使用 Looker API 開發的自訂應用程式，Looker 要求的任何資料都以 LookML 模型為基礎。開放式 SQL 介面可讓支援 Java Database Connectivity (JDBC) 的任何第三方應用程式存取 LookML 模型。應用程式可以連線至 LookML 模型，就像連線至資料庫一樣，讓使用者能充分運用資料分析師在 LookML 模型中完成的所有工作，同時使用最得心應手的工具。

開放式 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`。(如要瞭解在參照資料庫 ID 時，應將反引號放在何處，請參閱「在資料庫 ID 周圍使用反引號」一文)。

設定 Open SQL 介面

如要使用 Open SQL 介面，請按照下列步驟操作：

1. 確認符合需求條件。
2. 下載 Open SQL 介面 JDBC 驅動程式檔案。

以下各節將說明這些步驟。

需求條件

如要使用 Open SQL 介面，必須具備下列元件：

• 您想使用的第三方應用程式 (例如 Tableau、ThoughtSpot 或自訂應用程式) 必須能夠連線至 Looker 執行個體。只要 Looker 執行個體已連上網路，第三方應用程式就能存取 Looker 執行個體，即可搭配客戶代管的 Looker 執行個體使用 Open SQL 介面。
• 使用 Google BigQuery 連線資料的 LookML 專案。(LookML 專案必須有模型檔案，並在 connection 參數中指定 Google BigQuery 連線)。
• Looker 使用者角色，其中包含您要透過「開啟 SQL 介面」存取的 LookML 模型上的 explore 權限。

下載 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 介面的 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 金鑰產生存取權杖

您不必使用標準 OAuth 流程產生存取權杖，只要按照下列步驟，使用 Looker API 產生存取權杖，即可傳送至 Open SQL Interface JDBC 驅動程式：

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. 將回應的 <access_token> 值做為 Properties 物件中的權杖，在開啟 JDBC 連線至 Open SQL Interface 時傳遞 OAuth 憑證。

API 金鑰

您也可以使用 API 金鑰進行驗證，取代使用者名稱和密碼。API 金鑰的安全性不如 OAuth，且可能僅在 Open SQL 介面的預先發布版中提供。如要瞭解如何為 Looker 執行個體建立 API 金鑰，請參閱「API 金鑰」。

將 Looker API 金鑰的「Client ID」部分做為使用者名稱。使用「Client Secret」(用戶端密鑰) 部分做為密碼。

使用 Open SQL 介面執行查詢

使用 Open SQL 介面執行查詢時，請注意下列規範：

• Open SQL 介面接受符合 GoogleSQL 語法的 SQL 查詢。
• Open SQL 介面需要在模型、探索和欄位 ID 周圍加上反引號 (`)。如需其他資訊和範例，請參閱「在資料庫 ID 周圍使用反引號」。
• 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 運算子。
  • 您無法使用 JOIN 運算子將查詢傳送至 Open SQL 介面，在同一個或兩個不同的「探索」中建立聯結。
  • 如要在資料庫中建立兩個資料表之間的聯結，您可以在 LookML 模型中，於 LookML 專案的模型檔案內，為探索定義中的一或多個檢視區塊建立聯結。
• Open SQL 介面不支援視窗函式呼叫。
• Open SQL 介面不支援子查詢。
• Open SQL 介面不支援時區轉換。LookML 模型中的日期時間會以設定中定義的時區 (使用者時區、應用程式時區或資料庫時區設定) 顯示 DATETIME 型別。
• 開放式 SQL 介面不支援 BigQuery 資料類型 geography、JSON 和 time。

在資料庫 ID 周圍使用反引號

將查詢傳送至 Open SQL 介面時，請在結構定義、資料表和資料欄 ID 前後加上反引號。以下說明如何使用反引號和 Looker 字詞指定資料庫元素：

• 結構定義：`<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 介面時，您必須將查詢中包含的任何 LookML 測量值 (包括反引號) 包在特殊函式 AGGREGATE() 中，藉此指定測量值。舉例來說，您可以使用這項功能，指定「訂單」檢視畫面中的「數量」指標：

AGGREGATE(`orders.count`)

無論測量指標位於 SELECT 子句、HAVING 子句或 ORDER BY 子句中，都必須以 AGGREGATE() 函式包裝 LookML 測量指標。

注意： 您無法在 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 介面，請參閱下列章節的範例。

參數範例

舉例來說，如果 customers 檢視區塊在 Looker 中定義了參數，如下所示：parameter

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 介面會傳回錯誤。

篩選器限定欄位範例

您可以在 Open SQL 介面中使用 filter 欄位。舉例來說，如果 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 探索，則必須在 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 子句將指標篩選器的值傳遞至 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

範例

以下是同時使用維度和指標的查詢範例。這項查詢會從 customers 檢視區塊擷取 state 和 city 維度，並從 orders 檢視區塊擷取 total amount 指標。這兩個檢視畫面都會併入 ecommerce 模型中的「orders」探索。如果城市訂單數超過 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

Open SQL 介面只會傳回您有權存取的模型、探索和欄位結果。

DatabaseMetadata.getSchemas

下表說明 LookML 模型與 DatabaseMetadata.getSchemas 介面方法回應中的標準資料庫結構之間的關係。

getSchemas 回應欄	說明
TABLE_SCHEM	LookML 模型名稱
TABLE_CATALOG	(空值)

DatabaseMetadata.getTables

下表說明 LookML 模型與 DatabaseMetaData.getTables 介面方法回應中的資料庫結構之間的關係。回應包含標準 JDBC 中繼資料，以及 Looker 專屬中繼資料：

getTables 回應欄	說明
JDBC 標準中繼資料
TABLE_CAT	(空值)
TABLE_SCHEM	LookML 模型名稱
TABLE_NAME	LookML 探索名稱
TABLE_TYPE	一律傳回 TABLE_TYPE 值
REMARKS	(空值)
TYPE_CAT	(空值)
TYPE_SCHEM	(空值)
TYPE_NAME	代表表格類型的字串。可能的類型為 TABLE、VIEW、SYSTEM TABLE、GLOBAL TEMPORARY、LOCAL TEMPORARY、ALIAS、SYNONYM。
SELF_REFERENCING_COL_NAME	(空值)
REF_GENERATION	(空值)
Looker 專屬中繼資料
DESCRIPTION	探索說明
LABEL	探索標籤
TAGS	探索標記
CONDITIONALLY_FILTER_UNLESS	「探索」的 conditionally_filter 參數 unless 子參數中的欄位清單。如果 unless 子參數中未指定任何欄位，或未針對「探索」定義 conditionally_filter 參數，這個值會是空值。

DatabaseMetadata.getColumns

下表說明 LookML 模型與 DatabaseMetaData.getColumns 介面方法回應中的資料庫結構之間的關係。回應包含標準 JDBC 中繼資料，以及 Looker 專屬中繼資料：

getColumns 回應欄	說明
JDBC 標準中繼資料
TABLE_CAT	(空值)
TABLE_SCHEM	LookML 模型名稱
TABLE_NAME	LookML 探索名稱
COLUMN_NAME	LookML 欄位名稱，格式為 `<view_name>.<field_name>`。例如：`orders.amount`。
DATA_TYPE	資料欄的 java.sql.Types 程式碼。舉例來說，Looker yesno 欄位是 SQL 型別代碼 16 (BOOLEAN)。
TYPE_NAME	代表資料欄資料類型的字串。如果是使用者定義型別 (UDT)，型別名稱會是完整名稱。
COLUMN_SIZE	整數，代表資料欄中可儲存的字元或位元組數上限。
BUFFER_LENGTH	(空值)
DECIMAL_DIGITS	代表資料比例的整數：適用資料類型的小數點右側位數，或小數位數。如果 DECIMAL_DIGITS 不適用於資料類型，系統會傳回空值。
NUM_PREC_RADIX	代表資料的基底或基數 (通常為 10 或 2) 的整數。
NULLABLE	指出是否允許空值的整數： 0：columnNoNulls - 可能不允許 NULL 值 1：columnNullable - 絕對允許 NULL 值 2：columnNullableUnknown - nullability unknown
REMARKS	(空值)
COLUMN_DEF	(空值)
SQL_DATA_TYPE	(空值)
SQL_DATETIME_SUB	(空值)
CHAR_OCTET_LENGTH	如果是字元資料類型，則為代表資料欄位元組數上限的整數。
ORDINAL_POSITION	欄位在「探索」中的序數 (以 1 為基底)，維度和指標會依檢視名稱和欄位名稱的字母順序混合排序
IS_NULLABLE	一律傳回 YES 值
SCOPE_CATALOG	(空值)
SCOPE_SCHEMA	(空值)
SCOPE_TABLE	(空值)
SOURCE_DATA_TYPE	(空值)
IS_AUTOINCREMENT	(空值)
IS_GENERATEDCOLUMN	YES 代表指標，NO 代表維度
Looker 專屬中繼資料
DIMENSION_GROUP	如果欄位屬於維度群組，則為維度群組的名稱。如果該欄位不屬於維度群組，這個值會是空值。
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 參數，這個值會是空值。
CONDITIONALLY_FILTER	欄位中設定的 conditionally_filter 參數預設值。如果該欄位不屬於 conditionally_filter 參數，這個值會是空值。

在 Looker UI 中找出 Open SQL 介面查詢

Looker 管理員可以使用 Looker 使用者介面，找出源自 Open SQL 介面的查詢：

• 在「查詢」管理頁面中，Open 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/