開啟 SQL 介面

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

無論是透過 Looker 使用者介面中的 Looker Explore 介面、公司入口網站或其他第三方應用程式中的嵌入式視覺化內容，或是使用 Looker API 開發的自訂應用程式，LookML 模型都是向 Looker 要求任何資料的基礎。開放式 SQL 介面可讓任何支援 Java Database Connectivity (JDBC) 的第三方應用程式存取 LookML 模型。應用程式可以將 LookML 模型視為資料庫，以便使用者在使用自己最習慣的工具時，充分利用資料分析師在 LookML 模型中完成的所有工作。

Open SQL 介面如何顯示 LookML 專案元素

如要瞭解 Open SQL 介面如何顯示 LookML 專案的元素，請務必先瞭解 LookML 專案的結構。

LookML 專案是一系列檔案，用來說明在 Looker 中執行 SQL 查詢時所用的物件、資料庫連線和使用者介面元素 (詳情請參閱 LookML 術語和概念)。下列 LookML 專案概念與開放式 SQL 介面相關：

• LookML 模型會指定資料庫連線和一或多個探索。Open SQL 介面會將模型顯示為資料庫結構定義。
• 「探索」是指一或多個檢視畫面的邏輯群組，以及這些檢視畫面之間的彙整關係。Open SQL 介面會將 Explore 顯示為資料庫資料表。
• 檢視畫面會定義一組欄位 (包括維度和測量指標)。檢視表通常會根據資料庫中的資料表或衍生資料表建立。檢視畫面可包含基礎資料庫資料表的資料欄，以及可能需要的任何自訂維度或評估值。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 Interface JDBC 驅動程式檔案。

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

需求條件

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

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

下載 Open SQL Interface 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 擴充功能，將 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);

使用 API 金鑰產生存取權杖

您可以使用 Looker API 產生存取權杖，而非使用標準 OAuth 流程，這樣產生的存取權杖可傳送至 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 物件中的權杖，以便在開啟 Open SQL 介面的 JDBC 連線時傳遞 OAuth 憑證。

API 金鑰

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

請使用 Looker API 金鑰的「Client ID」部分做為使用者名稱。請使用「Client Secret」部分做為密碼。

使用 Open SQL 介面執行查詢

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

• Open SQL 介面可接受遵循 GoogleSQL 語法的 SQL 查詢。
• 在開放式 SQL 介面中，模型、探索和欄位 ID 必須以反斜線 (`) 包圍。如需詳細資訊和範例，請參閱「使用反引號括住資料庫 ID」一文。
• Open SQL 介面支援大部分的 BigQuery 運算子。
• 使用開放式 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 類型。
• Open SQL 介面不支援 BigQuery 資料類型 geography、JSON 和 time。

在資料庫 ID 前後使用反引號

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

• 結構定義：`<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 介面時，您必須在特殊函式 AGGREGATE() 中包裝指標 (包括反斜線)，指定查詢中包含的任何 LookML 指標。例如，您可以使用這項功能，指定來自「訂單」檢視畫面的「計數」評估資料：

AGGREGATE(`orders.count`)

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

如果不確定某個欄位是否為 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 介面中使用參數和篩選器限定欄位。

參數範例

以下範例說明如何在 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;

Open SQL Interface 會將此參數值傳遞至 Looker 中的查詢，而 Looker 會將 medium_customers 值套用至 Explore 中任何已設定為使用 segment 參數的欄位。如要進一步瞭解 Looker 中的參數運作方式，請參閱 parameter 說明文件。

篩選器限定欄位範例

您可以將 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，但不能同時支援兩者。

如果您已使用 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

範例

以下是同時使用維度和指標的查詢範例。這項查詢會從「客戶」檢視畫面擷取「州」和「城市」維度，以及從「訂單」檢視畫面擷取「總金額」指標。這兩個檢視畫面都會加入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

DatabaseMetadata.getSchemas

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

DatabaseMetadata.getTables

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

DatabaseMetadata.getColumns

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

在 Looker UI 中找出 Open SQL Interface 查詢

Looker 管理員可以使用 Looker UI，找出哪些查詢來自 Open SQL 介面：

• 在「查詢」管理員頁面中，Open SQL 介面查詢的「來源」值為「SQL 介面」。User 值會顯示執行查詢的 Looker 使用者名稱。您可以按一下查詢的「Details」按鈕，查看該查詢的其他資訊。在「Details」對話方塊中，您可以點選「SQL Interface query」，查看從 Open SQL 介面傳送至 Looker 的 SQL 查詢。

• 在「系統活動記錄探索」中，來自開放式 SQL 介面的查詢，其「來源」值為「sql_interface」。「使用者電子郵件」值會顯示執行查詢的 Looker 使用者電子郵件地址。您可以直接前往「歷史記錄」探索，並篩選出「sql_interface」：在這個網址開頭插入 Looker 例項位址：

  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/