SQLの組み込みとLookMLオブジェクトの参照

高度なLookMLを書くためには、既存のディメンション、メジャー、ビュー、派生テーブルなどを、どのファイルからでも参照できるようになる必要があります。また、基になるテーブルの列を参照すること、および、それらの値を操作するためにデータベースダイアレクトの関数呼び出しを使用することも必要になります。

置換演算子（$）

置換演算子 $ を使用すると、LookML コードの再利用性とモジュール性が向上します。これにより、他のビューや派生テーブル、SQL テーブル内の列、LookML のディメンションとメジャーを参照できます。これには2つの理由があります。まず、すでにかなり複雑なディメンションやメジャーを計算してきた方にとっては、その定義過程をやり直す必要がなくなります。また、あるディメンションやメジャーに何か変更を加えても、変更されたフィールドに依存している項目すべてにその変更が反映されます。

文字列置換演算子を使う方法はいくつかあります。

${TABLE}.column_name は、作業中のビューに接続されているテーブルの列を参照します。例:

dimension: customer_id {
  type: number
  sql: ${TABLE}.customer_id ;;
}

${field_name} は、作業中のビューの中にあるディメンションまたはメジャーを参照します。例:

measure: total_population {
  type: sum
  sql: ${population} ;;
}

${view_name.field_name} は、別のビューにあるディメンションまたはメジャーを参照します。例:

dimension: lifetime_orders {
  type: number
  sql: ${user_order_facts.lifetime_orders} ;;
}

${view_name.SQL_TABLE_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});;
}

${view_name.SQL_TABLE_NAME} は、データグループで使用される sql_trigger パラメータでは使用できません。

スコーピングとネーミング

Explore、ビュー、フィールド、セットに名前を付けることができます。これらのLooker識別子は引用符なしで記述されます。

LookML のフィールドとセットには、フルネームと短縮名があります。

フルネームは、<view>.<field-name | set-name> の形式です左側はスコープを示しています。つまり、このフィールドまたはセットを含むビューのことです。右側では特定のフィールドまたはセットの名前を指定します。
短縮名は <field-name | set-name> の形式をとり、区切りのピリオドはありません。Lookerは、フィールドやセットが使われているスコープを使用して略称をフルネームへと拡張します。

下記はさまざまな名前とスコープの形式を示した例です。これのフィールドのグループは現実的ではありませんが、さまざまなスコーピングの式を示すために示されています。

view: orders {                   # "orders" becomes the containing scope
  measure: count {               # short name, equivalent to orders.count
    type: count
  }
  dimension: customer_id {       # short name, equivalent to orders.customer_id
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  dimension: customer_address {  # short name, equivalent to orders.customer_address
    sql: ${customer.address} ;;  # full name, references a field defined in the "customer" view
  }
  set: drill_fields {            # short name, equivalent to orders.drill_fields
    fields: [
      count,                     # short name, equivalent to orders.count
      customer.id                # full name, references a field defined in the "customer" view
    ]
  }
}

dimension: customer_address 宣言では、SQL ブロック（customer）の基になるビューが、全体を囲むビュースコープ（orders）とは異なることに注意してください。これは、2 つの異なるビュー間でフィールドを比較する場合に役に立ちます。

ビュー（「ビュー A」と呼ぶ）が別のビュー（「ビュー B」と呼ぶ）で定義されているフィールドを参照する場合、以下の点に留意してください。

ビュー B ファイルは、include パラメータを使用してビュー A と同じモデルに含める必要があります。
ビューBは、1つ以上のExploreにおいてビューAに結合されている必要があります。結合について詳しくは、LookML での結合の使用のページをご覧ください。

SQL 言語

Looker は、MySQL、Postgres、Redshift、BigQuery など、多くのデータベースタイプに対応しています。それぞれのデータベースがサポートしている機能の種類は少しずつ異なり、関数名も異なります。これらは SQL 言語と呼ばれています。

LookMLはあらゆるSQLダイアレクトに対応するよう設計されています。また、LookMLがあるダイアレクトを別のダイアレクトよりも優先することはありません。ただし、特定の LookML パラメータに SQL コード式（SQL ブロックと呼ばれます）を含める必要があります。これらのパラメータを使う場合、LookerがSQL式を直接データベースに渡します。そのため、使っているデータベースに合ったSQLダイアレクトを使わなければなりません。例えば、SQL関数を使うのであれば、その関数はお使いのデータベースにサポートされているものである必要があります。

SQLブロック

LookMLパラメータの中には、Lookerがデータベースからデータを取得する方法を理解できるように、生のSQL式を要求するものもあります。

sql_ で始まる LookML パラメータには、なんらかの形式の SQL 式が想定されます。例: sql_always_where、sql_on、sql_table_nameSQL ブロックの最も一般的な LookML パラメータは sql です。これは、ディメンションとメジャーのフィールド定義で、ディメンションまたはメジャーを定義する SQL 式を指定するために使用されます。

SQLブロックにおいては、1つのフィールド名のみを使用する簡潔なものから、相関サブセレクトという複雑なものまで、幅広いコードを指定できます。コンテンツはかなり複雑なものにもでき、生のSQLにおいて表現する必要のあるカスタムクエリロジックのニーズにほぼすべて対応することができます。SQL ブロックで使用するコードは、データベースで使用されている SQL 言語と一致している必要があります。

ディメンションやメジャー用の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: ${order_items.cost} ;;   # Specify the field that you want to average
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: int
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

最後の 2 つのディメンションに示されているように、SQL ブロックは、基盤となるデータベースでサポートされている関数（この例では MySQL 関数 CONCAT と DATEDIFF）を使用できます。

相関サブセレクトが入ったSQLブロックの例

フィールドのSQLブロックには、相関サブセレクトを含め、あらゆるSQL文を置くことができます。次に例を示します。

view: customers {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }
  dimension: first_order_id {
    sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;;
         # correlated subselect to derive the value for "first_order_id"
  }
}

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

派生テーブルはSQLブロックを使って、テーブルを派生させるクエリを指定します。次に例を示します。

view: user_order_facts {
  derived_table: {
    sql:            # Get the number of orders for each user
      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
  }
}

LookMLフィールドタイプの参照

別のフィールド内の既存の LookML フィールドを参照する場合は、二重コロン（::）の後に目的の型を続けて使用することで、参照するフィールドを特定のデータ型として扱うように Looker に指示できます。たとえば、別のフィールドで orders.created_date ディメンションを参照する場合は、構文 ${orders.created_date::date} を使用して、Looker で生成される SQL で、created_date フィールドとして文字列としてキャストされるのではなく、SQL の日付フィールドとして扱われるようにできます。

参照で使用できるデータタイプは、参照している元のフィールドのデータタイプに依存します。たとえば、文字列フィールドを参照する場合、指定できるデータ型は ::string だけです。フィールドの各タイプに使用できる、許可されたフィールドタイプ参照の一覧は以下のとおりです。

文字列フィールドへの参照では、::string を使用できます。
数値フィールドの参照では、::string と ::number を使用できます。
日付フィールドまたは時間フィールドへの参照では、::string、::date、::datetime を使用できます。
::string と ::date を使用する参照はクエリのタイムゾーンでデータを返し、::datetime を使用する参照はデータベースのタイムゾーンでデータを返します。
yesno フィールドへの参照では、::string、::number、::boolean を使用できます。
ブールデータ型をサポートしていないデータベース言語では、::boolean 型を使用したフィールド参照を使用できません。
ロケーションフィールドへの参照では、::latitude と ::longitude を使用できます。

データフィールドのあるLookMLフィールドタイプ参照の使用

たとえば、enrollment_month ディメンションと graduation_month ディメンションがあり、どちらも type: time のディメンショングループ内に作成されたとします。この例では、enrollment_month ディメンションは、次のディメンショングループ type: time によって生成されます。


dimension_group: enrollment {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.enrollment_date ;;
}

同様に、graduation_month ディメンションは、次の type: time ディメンショングループによって作成されます。


dimension_group: graduation {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.graduation_date ;;
}

enrollment_month ディメンションと graduation_month のディメンションを使用して、type: duration のディメンショングループを作成することで、生徒の登録から卒業までに経過した月数や年数を計算できます。ただし、一部の日付フィールドは Looker によって生成される SQL の文字列としてキャストされるため、enrollment_month ディメンションと graduation_month ディメンションを sql_start と sql_end の値として設定するとエラーが発生する可能性があります。

このような時間フィールドが文字列としてキャストされることによるエラーを避けるために、オプションの　1 つとして、type: duration のディメンショングループを作成し、sql_start　と　sql_endパラメータにおけるenrollmentおよびgraduationディメンショングループのraw　時間フレームを反映します。


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_raw} ;;
  sql_end: ${graduation_raw} ;;
}

Explore UI では、登録月数と登録年数という個別のディメンションを持つ、登録期間という名前のディメンショングループが生成されます。

type: duration のディメンショングループで raw 期間を使用する代わりに、sql_start と sql_end パラメータで参照されるフィールドに ::date または ::datetime 参照タイプを指定するのが簡単です。


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_month::date} ;;
  sql_end: ${graduation_month::date} ;;
}

この例の LookML でも「登録期間」ディメンショングループが作成されますが、::date 参照を使用すると、enrollment_month ディメンションと graduation_month ディメンションを raw を使用しないか、SQL を使用した文字列としてキャストして使用できます。

LookML フィールドタイプ参照を使用して type: duration のカスタムディメンショングループを作成する方法のその他の例については、dimension_group パラメータのドキュメントページをご覧ください。

この構文は、Looker 6.8 以降では参照できない type: list の measure では使用できません。

LookML定数

constant パラメータを使用すると、LookML プロジェクト全体で使用できる定数を指定できます。LookML定数で一度値を定義しておけば、ストリング型の値が取れる場所ならプロジェクトのどこからでもそれを参照することができます。そのようにして、LookMLコード内の反復を少なくすることができます。

定数は、プロジェクトのマニフェストファイル内で宣言する必要があり、定数の値は文字列でなければなりません。たとえば、次のように値 "Okayama" で定数 city を定義できます。

constant: city {
  value: "Okayama"
}

city 定数は、@{city} 構文を使用してプロジェクト全体で参照できます。たとえば、users Explore の label パラメータで city 定数を使用できます。


explore: users {
  label: "@{city} Users"
}

その後、Looker ではデフォルトの [Users] ではなく [Explore] メニューと Explore のタイトルの両方に「Okayama Users」が表示されます。

LookML 定数を使用して再利用可能なコードを記述する方法の詳細と例については、constant パラメータのドキュメントページをご覧ください。