高度な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_name
SQL ブロックの最も一般的な 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
の 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
パラメータのドキュメント ページをご覧ください。