Looker 產生 SQL 的方式

如果您是從 SQL 背景轉到 Looker，可能會好奇 Looker 如何產生 SQL。從根本上來說，Looker 是一種工具，可產生 SQL 查詢，並針對資料庫連線提交查詢。Looker 會根據 LookML 專案 (說明資料庫中資料表和欄之間的關係) 編寫 SQL 查詢。瞭解 Looker 的查詢產生方式，有助您進一步瞭解 LookML 程式碼如何轉譯成高效率的 SQL 查詢。

每個 LookML 參數都會控制 Looker 產生 SQL 的方式，藉此變更查詢的結構、內容或行為。本頁面說明 Looker 產生 SQL 的運作原理，但未詳細說明所有 LookML 元素。如要瞭解 LookML 參數，請參閱 LookML 快速參考資料說明文件。

查看查詢

在已儲存的Look 或探索中，您可以使用「資料」面板中的「SQL」分頁，查看 Looker 傳送至資料庫的內容，以便取得資料。您也可以使用「SQL」分頁底部的「在 SQL Runner 中開啟」和「在 SQL Runner 中說明」連結，在 SQL Runner 中查看查詢，或查看資料庫的查詢說明計畫。

如要進一步瞭解 SQL Runner，請參閱「SQL Runner 基本概念」說明文件頁面。如要進一步瞭解如何使用 SQL Runner 最佳化查詢，請參閱「如何使用 EXPLAIN 最佳化 SQL」社群文章。

Looker 查詢的標準格式

Looker 的 SQL 查詢一律採用以下格式。

SELECT
   <dimension>, <dimension>, ...
   <measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>

LookML 專案會定義 SQL 查詢中參照的所有維度、指標、探索和檢視表。使用者可在 Looker 中指定篩選運算式，以便建立臨時查詢。您也可以直接在 LookML 中宣告篩選運算式，套用至所有查詢。

Looker 查詢的基本元件

所有 Looker 查詢都會以這些基本參數表示，並套用至 LookML 專案，如上一個查詢範例所示。

Looker 會使用下列參數產生完整的 SQL 查詢：

  • model：要指定的 LookML 模型名稱，可用來指定目標資料庫
  • explore：探索查詢的名稱，用於填入 SQL FROM 子句
  • 欄位：要納入查詢中的 dimension 和 measure 參數，用於填入 SQL SELECT 子句
  • filter：Looker 篩選運算式，可套用至零個或多個欄位，用於填入 SQL WHERE 和 HAVING 子句
  • 排序順序：要依據的欄位和排序順序，用於填入 SQL ORDER BY 子句

這些參數正是使用者在 Looker 探索頁面上建構查詢時指定的元素。這些相同的元素會在使用 Looker 執行查詢的所有模式中顯示，例如產生的 SQL、代表查詢的網址，以及 Looker API。

那麼，由 LEFT JOIN 子句指定的檢視畫面呢？JOIN 子句會根據 LookML 模型的結構填入資料，該結構會指定檢視畫面如何與 Explore 結合。建構 SQL 查詢時，Looker 只會在必要時加入 JOIN 子句。使用者在 Looker 中建構查詢時，不必指定資料表的彙整方式，因為這項資訊已在模型中編碼，這是 Looker 對企業使用者最強大的優點之一。

查詢範例和產生的 SQL

我們在 Looker 中建構查詢，說明如何根據先前的模式產生查詢。舉例來說，假設電子商店的資料庫有兩個資料表：「訂單」和「使用者」，用於追蹤使用者和訂單。

orders
id INT
created_at DATETIME
users_id INT
status VARCHAR(255)
traffic_source VARCHAR(15)
users
id INT
email VARCHAR(255)
first_name VARCHAR(255)
last_name VARCHAR(255)
created_at DATETIME
zip INT
country VARCHAR(255)
state VARCHAR(255)
city VARCHAR(255)
age INT
traffic_source VARCHAR(15)

我們將在 Looker 探索中，依狀態 (USERS State) 分組，並依訂單建立日期 (ORDERS Created Date) 篩選，找出訂單數量 (ORDERS Count)。

「探索」資料表會根據使用者狀態，顯示過去 30 天內下單的訂單數量。

如要查看 Looker 產生及執行的 SQL 查詢，請按一下「資料」面板中的「SQL」分頁。

SELECT COALESCE(users.state, ' ') AS "_g1",
   users.state AS 'users.state',
   COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id

WHERE
  orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500

請注意，這與標準查詢公式相似。Looker SQL 會顯示機器產生的程式碼 (例如 COALESCE(users.state,'') AS "_g1") 的一些特徵，但一律會符合公式。

在 Looker 中嘗試更多查詢，證明查詢結構一律相同。

在 Looker 的 SQL Runner 中執行原始 SQL

Looker 提供 SQL Runner 功能，可讓您針對在 Looker 中設定的資料庫連線，執行任何 SQL 查詢。

由於 Looker 產生的每個查詢都會產生完整且可用的 SQL 指令，因此您可以使用 SQL Runner 來調查或測試查詢。

在 SQL Runner 中執行的原始 SQL 查詢會產生相同的結果集。如果 SQL 含有任何錯誤，SQL Runner 會在 SQL 指令中醒目顯示第一個錯誤的位置，並在錯誤訊息中加入錯誤的位置。

檢查展開的網址中的查詢元素

在 Looker 中執行查詢後，您可以檢查展開的網址，查看 Looker 查詢的基本元件。首先，請從「探索」的齒輪選單中選取「分享」，開啟「分享網址」選單。

擴充的網址會提供足夠的資訊，讓您重建查詢。舉例來說，這個已展開的網址提供以下資訊：

https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
模型 e_thelook
探索 events
要查詢及顯示的欄位 fields=users.state,users.count
排序欄位和順序 sorts=users.count+desc
篩選器欄位和值 f[users.created_year]=2020

Looker 如何建構 JOIN

在上述範例查詢中，請注意 orders Explore 會顯示在主要 FROM 子句中，而彙整的檢視畫面會顯示在 LEFT JOIN 子句中。Looker 彙整可透過多種方式編寫，詳情請參閱「在 LookML 中使用彙整」頁面。

SQL 區塊指定自訂 SQL 子句

Looker 查詢的元素並非全由機器產生。在某個時間點，資料模型需要提供特定詳細資料，讓 Looker 存取基礎資料表並計算衍生值。在 LookML 中，SQL 區塊是資料模型設計工具提供的 SQL 程式碼片段，Looker 會使用這些片段合成完整的 SQL 運算式。

最常見的 SQL 區塊參數是 sql，用於維度和計量定義。sql 參數會指定 SQL 子句，用於參照底層資料欄或執行匯總函式。一般來說，所有以 sql_ 開頭的 LookML 參數都會預期某種形式的 SQL 運算式。例如：sql_always_where、sql_on 和 sql_table_name。如要進一步瞭解各個參數，請參閱 LookML 參考資料。

維度和測量指標的 SQL 區塊範例

以下程式碼範例提供幾個維度和資料表欄的 SQL 區塊範例。LookML 替換運算子 ($) 會讓這些 sql 宣告看起來不像 SQL。不過，在替換完成後，產生的字串會是純 SQL，Looker 會將這項資訊插入查詢的 SELECT 子句中。

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;  # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${cost} ;;      # Specify the field that you want to average
                       # The field 'cost' is declared elsewhere
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: number
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

如本範例的最後兩個維度所示，SQL 區塊可使用底層資料庫支援的函式 (例如本例中的 MySQL 函式 CONCAT 和 DATEDIFF)。您在 SQL 區塊中使用的程式碼必須與資料庫使用的 SQL 方言相符。

衍生資料表的 SQL 區塊範例

衍生資料表也會使用 SQL 區塊來指定衍生資料表的查詢。以下是 SQL 衍生資料表的範例：

view: user_order_facts {
  derived_table: {
    sql:
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }

  # later, dimension declarations reference the derived column(s)…
  dimension: lifetime_orders {
    type: number
  }
}

用於篩選探索的 SQL 區塊範例

sql_always_where 和 sql_always_having LookML 參數可讓您在 SQL WHERE 或 HAVING 子句中插入 SQL 區塊，藉此限制查詢可用的資料。在本例中，LookML 替換運算子 ${view_name.SQL_TABLE_NAME} 用於參照衍生資料表：

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}