このページは Cloud Translation API によって翻訳されました。

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

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

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

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

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

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

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

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

Looker でクエリを作成して、前のパターンに従ってクエリが生成される仕組みを見てみましょう。ユーザーと注文を追跡するために、orders と users の 2 つのテーブルを含むデータベースがある e コマースストアを考えてみましょう。

`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 で未加工の 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

モデル	`e_thelook`
Explore	`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}) ;;
}

この例のディメンションの最後の 2 つに示されているように、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
  }
}

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