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

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} は、datagroup で使用される 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文を置くことができます。下に例を1つ挙げます。

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ブロックを使って、テーブルを派生させるクエリを指定します。下に例を1つ挙げます。

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 のメジャーでは使用できません。

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 パラメータドキュメントをご覧ください。