Looker のドキュメントを表示していることに注意してください。Looker Studio のドキュメントについては、https://support.google.com/looker-studio をご覧ください。

How Looker generates SQL（LookerのSQL生成の仕組み）

SQL のバックグラウンドから Looker を利用している場合、Looker で SQL がどのように生成されるのかに興味をお持ちではないでしょうか。基本的に、Looker は SQL クエリを生成し、データベース接続に対して送信するためのツールです。Looker は、データベースのテーブルと列の関係を記述する LookML プロジェクトに基づいて SQL クエリを作成します。Looker によるクエリ生成方法を理解することで、LookML コードが効率的な SQL クエリにどのように変換されるかを理解できます。

各 LookML パラメータは、クエリの構造、コンテンツ、動作を変更することで、Looker が SQL を生成する方法の一部の側面を制御します。このページでは、Looker による SQL 生成の原則について説明しますが、すべての LookML 要素については詳しく説明しません。LookML クイックリファレンスのドキュメントページでは、最初に LookML パラメータに関する情報を確認してください。

クエリの表示

保存済み Look または Explore では、[データ] パネルの [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 プロジェクトでは、上記の式で参照されるすべてのディメンション、メジャー、Explore、ビューを定義します。フィルタ式はユーザーが Looker でアドホッククエリを形成するために指定します。フィルタ式を LookML で直接宣言して、すべてのクエリに適用することもできます。

Looker クエリの基本コンポーネント

すべての Looker クエリは、上記の式に示されているように、LookML プロジェクトに適用されるこれらの基本的なパラメータで表されます。

Looker では、次のパラメータを使用して完全な SQL クエリを生成します。

model: ターゲットにする LookML モデルの名前。ターゲットデータベースを指定します。
explore: クエリを実行する Explore の名前。SQL FROM 句が入力されます。
フィールド: クエリに含める dimension パラメータと measure パラメータ（SQL SELECT 句に入力）
filter: SQL の WHERE 句と HAVING 句に入力される、0 個以上のフィールドに適用する Looker フィルタ式
並べ替え順序: 並べ替えるフィールドと、SQL ORDER BY 句に格納する並べ替え順序

これらのパラメータはユーザーが Looker の [Explore] ページでクエリを作成する際に指定する要素です。この同じ要素は、生成された SQL、クエリを表す URL、Looker API など、Looker でクエリを実行するすべてのモードで表示されます。

LEFT JOIN 句で指定されたビューについてはどうでしょうか。 JOIN 句は、LookML モデルの構造に基づいて入力されます。この構造では、ビューを Explore に結合する方法を指定します。SQL クエリを作成する場合、Looker では必要な場合にのみ JOIN 句を指定します。Looker でクエリを作成するとき、テーブルで結合する方法を指定する必要はありません。これは、モデルの Looker の最も大きなメリットの 1 つである、モデルのエンコードです。

クエリの例と生成される SQL

Looker でクエリを作成して、前のパターンに従ってクエリがどのように生成されるかを示します。e コマースストアに、orders と users の 2 つのテーブルがあり、ユーザーと注文を追跡するデータベースがあるとします。

`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 Explorer で、状態（USERS State）別にグループ化され、注文の作成日（ORDERS Created Date）でフィルタされた注文数（[ORDERS Count]）を検索します。

Explore データテーブルには、過去 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 で raw SQL を実行する

Looker には SQL Runner という機能が含まれています。Looker で設定したデータベース接続に対して任意の SQL を実行できます。

Looker によって生成されたすべてのクエリは完全で機能する SQL コマンドになるため、SQL Runner を使用してクエリを調査するか、操作できます。

SQL Runner で実行される未加工の SQL クエリは、同じ結果セットを生成します。SQL にエラーがある場合、SQL Runner は SQL コマンドの最初のエラーの場所をハイライト表示し、エラーメッセージにエラーの位置を含めます。

拡張 URL 内のクエリコンポーネントの調査

Looker でクエリを実行した後、拡張 URL を調べて、Looker クエリの基本コンポーネントを確認できます。まず、Explore の歯車メニューから [共有] を選択して、[URL を共有] メニューを開きます。

拡張 URL には、クエリを再作成するのに十分な情報が含まれています。たとえば、この拡張 URL の例では次の情報が提供されます。

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

model	`e_thelook`
explore	`events`
クエリおよび表示するフィールド	`fields=users.state,users.count`
並べ替えフィールドと並べ替え	`sorts=users.count+desc`
フィールドと値のフィルタリング	`f[users.created_year]=2020`

Looker による JOIN の構造

上記のクエリ SQL では、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}) ;;
}

上記の最後の 2 つのディメンションに示すように、SQL ブロックでは、基盤となるデータベースでサポートされている関数（この場合は MySQL 関数 CONCAT と DATEDIFF）を使用できます。SQL ブロックで使用するコードは、データベースで使用されている SQL 言語と一致している必要があります。

派生テーブルのためのSQLブロックの例

派生テーブルでは SQL ブロックを使用して、テーブルを派生するクエリも指定します。下に例を1つ挙げます。

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
  }
}

Explore をフィルタリングする 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});;
}