Looker 22.16 のドキュメントを表示しています。最新のドキュメントはこちらをご覧ください。

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 リファレンスドキュメントページをご覧ください。

クエリの表示

保存済みの Look または Explore では、[Data] セクションの [SQL] タブを使用して、Looker がデータベースに送信してデータを取得するデータを確認できます。また、下部のリンクから 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: ゼロ個以上のフィールドに適用するための Looker フィルタ式。SQL の WHERE 句と HAVING 句に入力します。
並べ替え順序: 並べ替えの基準となるフィールドと、SQL の ORDER BY 句に自動入力する並べ替え順序

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

LEFT JOIN 句で指定されたビューの場合はどうなるでしょうか。JOIN 句は、Look を Explore に結合する方法を指定する LookML モデルの構造に基づいてデータが入力されます。Looker では、SQL クエリを構築する際に、必要な場合にのみ JOIN 句を使用できます。ユーザーが Looker でクエリを作成する際、この情報はモデルにエンコードされるため、テーブルの結合方法を指定する必要はありません。これは、ビジネスユーザーにとって最も強力な利点の一つです。

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

Looker でクエリを作成し、上のパターンに従ってクエリがどのように生成されるかを見てみましょう。ユーザーと注文を追跡するためのテーブルがある e コマースストアについて考えてみましょう。フィールドとテーブルの関係を以下に示します。

注文数（ORDERS Count）を、状態（USERS State）でグループ化して、注文の作成日（ORDERS Created Date）でフィルタしてみましょう。

以下は、Looker Explore ページのクエリ結果です。

[SQL] タブをクリックすると、Looker で生成、実行された SQL が表示されます。

上記の正規数式との類似点に注目してください。Looker の SQL は機械生成コード（COALESCE(users.state,'') AS "_g1" など）のなんらかの特性を示しますが、常に式に適合します。

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>,...
ORDER BY <dimension> | <measure>
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
LIMIT <limit>

Looker でさらに多くのクエリを試し、クエリ構造が常に同じであることを検証します。

Looker の SQL Runner で未加工 SQL を実行する

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

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

SQL Runner で実行される生の SQL クエリは、同じ結果セットを生成する

SQL にエラーがある場合、SQL Runner は SQL コマンド内の最初のエラーの場所をハイライト表示し、エラーメッセージにエラーの位置を含めます。

URL でのクエリコンポーネントの調査

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

Look から開始している場合は、Look の [Explore From Here] リンクをクリックして、Explore でクエリを開きます。

[URL の共有] ウィンドウが開き、拡張 URL が表示されます。

たとえば、上記のクエリは次のような拡張共有 URL を生成します。

https://docsexamples.dev.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500

URL は、クエリを再作成するのに十分な情報を提供します。

モデル	`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 クエリのすべての要素が機械生成されるわけではありません。ある時点でデータモデルで、基盤となるテーブルにアクセスし、派生値を計算するための具体的な詳細情報を提供する必要があります。LookML の SQL ブロックは、データモデラーが提供している SQL コードのスニペットであり、Looker はそれを使って完全な SQL 式を合成します。

最も一般的な SQL ブロックパラメータは、ディメンションとメジャーの定義で使用されます。sqlsql パラメータは、基礎となる列を参照する、または集計関数を実行する 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 ブロックの例

LookML パラメータの sql_always_where と sql_always_having を使用すると、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});;
}