日本語 English (US)

LOOKML TERMS AND CONCEPTS AWS USAWS TokyoIDCF

Avatar
taka
更新
フォローする

このページでは LookML のコンセプトについて説明していきます。以下のダイアグラムは LookML を構成する要素群を記述しています。ダイアグラムを見れば，LookML がデータベースとの関係を記述する Data Modeling の部分から，データ処理を行う部分，さらに可視化してダッシュボードを作る部分，その全てが LookML という統一言語で記述されていることがわかります。

Looker Terminology from Looker on Vimeo.

Relationships between LookML elements

DEFINITION: LOOKML PROJECT

LookML Project の定義を知ることはとても重要です。LookML ファイルは models, views, explores, dashboards の集合体です。

LookML ファイルは次の3つのファイルに分類されます：

  • model files（.model.lookml）
  • view files（.view.lookml）
  • dashboard files（.dashboard.lookml, ）

これらのファイルは github にてバージョン管理されています。あなたが自身の github アカウントを持っていれば，そのアカウントで LookML ファイルを管理することが可能です。

Relationship between LookML projects, files, database connections, and Git repositories

Accessing projects under the LookML button

WHERE DO LOOKML PROJECTS AND FILES COME FROM?

新しい Project を作成すると，Looker’s project generator が一連のベースファイル（テンプレート）を作成しますので，基本的にはスクラッチから記述せずに，こちらのベースファイルを編集する形で進めて行きます。

新しい Project ではまずデータベースとのコネクションについて記述するためのファイルが作成されます：

  • 複数の View ファイル：データベースの特定のテーブルからのデータ抽出を定義するファイル
  • 一つの Model ファイル：Model ファイルは全ての View に対して Explore を行うための情報を記述します。この情報の中には複数の View を Join するロジックが含まれます。

DEFINITION: MODEL

まず Model とは何かについて解説します。Model は以下の特徴を持ちます：

  • Model はビジネスユーザーが，データベースへ直感的な操作でアクセスできるためのカスタマイズポータルです。
  • 1つの LookML プロジェクトの中では，複数の Model は同じデータベースコネクションを持つことが可能です。例えば sales agents と company executives では同じデータベースに対しても，分析概念が全く異なりますので，2つの Model として識別します。

Explore ボタンを押すと登録された複数の Model と，それに属する View を見る事ができます。

Models listed under the Explore button

LookML では Model ファイルが各々のデータ接続に対しての explores の集合を記述します。以下は Model ファイルの一例です。詳細は LookML Reference を参照してください。

- scoping: true    # Always true. For backward compatibility.
- connection: <connection_name>
- persist_for: <timeframe>
- template: liquid | erb
- case_sensitive: true | false

- include: <filename pattern>    # for example "*.view.lookml"
...    # More include declarations

- explore: <explore_name>
  view: <view_name>
  joins:
    - join: <view_name>

    ...    # More join declarations

...    # More explore declarations

DEFINITION: VIEW

View では fields (dimensions or measures) のリストを記述します。field には2種類あって，データベースに存在するテーブルのカラムをピックアップしたものと，derived table と呼ばれるデータベースのテーブルに対して SQL で整形・集計した field があります。

また，ある view で他の view 同士の join を記述したりすることもあります。こうした view 間の関係は model ファイルの中で explore の定義の一部として記述されます。

View names listed as part of field names

以下は view の例です。View LookMLの詳細は LookML Reference を参照ください。

- view: <view_name>
  fields:
    - dimension: <field_name>
      <dimension_parameters>
    ...   # more dimension declarations
    - measure: <field_name>
      <measure_parameters>
    ...   # more measure declarations

  sets:
     <set definitions> 

DEFINITION: EXPLORE

Explore は，view に対してクエリを投げる事のできる機能です。"SELECT ... FROM view ..." に該当する様々なデータ操作を Look UI 上で実行できるのがこの Explore です。Explore では，複数の view に対して join を記述することができます。Joins で詳細を参照ください。

Explores listed under the Explore button

By convention explores are declared in the model file. The example below demonstrates the declaration for an以下の例では eコマースデータベースに対して， “Orders” explore のデモンストレーションです。2つの views： “orders” と “customers” に対する join が記述されています。

Example explore declaration

#------------------------------
# file: ecommercestore.model.lookml
#------------------------------
- connection: order_database
- include *.view.lookml           # include all the views.
- scoping: true                   # for backwards compatibility

- explore: orders
  joins:
    - join: customers
      sql_on: ${orders.customer_id} = ${customers.id}

joins については Joins を，詳細は LookML Reference を参照ください。

DEFINITION: DIMENSION AND MEASURE FIELDS

view における各々の fields は dimension か measure のいずれかの属性を持っています。

Looker では， dimension は "groupable field"（GROUP BY 句に設定される field），となり得るもの，またはクエリ結果をフィルタリングする field として定義されます。例えば "Product" view における dimension fields は以下のものが挙げられます：

  • product name
  • product model
  • product color
  • product price
  • product created date
  • product end-of-life date

Dimension になり得る field の型については dimension types を参照ください。

一方 measure は SQLの COUNT, SUM, AVG, MIN MAX といった集約関数の引数として使用される数値型の field です。また，ある measure から計算された field もまた maeasure となります。

例えば，“Sales” view における measure fields は以下のものが挙げられます：

  • total items sold (a count)
  • total sale price (a sum)
  • average sale price (an average)

Dimensions and measures are the building blocks for Looker queries

By convention fields are declared as part of the view they belong to, stored in a view file. いくつかの dimension and measure を定義した view の例を以下に示します。the substitution operator ($) はSQLのテーブル名，カラム名を記述する際に使用します。

Example declarations of dimensions and measures

- view: orders
  fields:
  - dimension: id
    primary_key: true
    type: number
    sql: ${TABLE}.id

  - dimension: customer_id
    sql: ${TABLE}.customer_id

  - dimension: amount
    type: number
    value_format: '0.00'
    sql: ${TABLE}.amount

  - dimension: created
    type: time
    sql: ${TABLE}.created_at

  - measure: count
    type: count        # creates sql COUNT(orders.id)
    sql: ${id}

  - measure: total_amount
    type: sum
    sql: ${amount}     # creates sql SUM(orders.amount)

LookML Reference にさらに詳細があります。

DEFINITION: SUBSTITUTION OPERATOR ($)

Substitution operator, $  の使い方は3種類あります。

  1. ${TABLE}.column_name は現在スコープにある view をテーブル名として代入します。
  2. ${field_name} はdimension， measure いずれかの field 名を指定します。
  3. ${view_name.field_name} は他の view の field を持ってくる際に指定します。

DEFINITION: JOINS

explore declaration の中で記述される joins declaration は, explore 内で join される view を指定します。ユーザーが複数の view から fields を作成する場合には，Looker 側で自動で join ロジックを作成してくれます。ほとんどのケースで， LookML は foreign-key を定義することで join を正しく定義します。

Example joins section in an explore declaration

#------------------------------
# file: ecommercestore.model.lookml
#------------------------------
- connection: order_database
- include *.view.lookml           # include all the views.
- scoping: true                   # for backwards compatibility

- explore: orders
  joins:
    - join: customers
      sql_on: ${orders.customer_id} = ${customers.id}

For more details check out Working with Joins in LookML.

DEFINITION: DRILL DOWN

In Looker, you can drill down on most fields by clicking on the value. Drilling works in both query results tables and dashboards. Drilling starts a new query that is restricted by the value drilled on.

Looker では，テーブル内の値をクリックすることでドリルダウンすることができます。ドリルダウンは Explore 時のクエリー結果テーブルおよび Dashboard に対して適用することができます。バックエンドでは，ドリルダウンはクリックした値に制限して新しいクエリを組み立て実行していることになります。

Dimension か Measure をクリックするかによってドリルダウンの振る舞いは変わってきます：

  • Dimension：その Dimension 値を制限句（WHERE ）にしてクエリを再実行します。例えば日付をクリックすれば，その日付に関する集計結果のみを表示することになります。
  • Count Measure：Count の集約関数を持つ Measure がクリックされると，それに紐付いた情報を表示します。ドリルダウンで表示する項目は，view の drill_fields で記述します。また，Count 以外の，Sum や Max, Min などの集計値に対してはドリルダウンは行えません。

DEFINITION: SETS

Looker では，set は field の集合として定義されます。ほとんどのケースで，set は結果テーブルやドリルダウン時に表示するための field 集合を定義する際に使われます。

The following example shows a set declaration in a

下の例では view： order_items に対し，order_items_stats_set 購入された商品の情報群を定義しています。

sets:
  order_items_stats_set:
    - id   # scope defaults to order_items view
    - orders.created_date  # scope is "orders" view
    - orders.id
    - users.name
    - users.history  # show all products this user has purchased
    - products.item_name
    - products.brand
    - products.category
    - total_sale_price

LookML Reference で詳細をご参照下さい。

DEFINITION: DERIVED TABLES AND FACTS TABLES

A derived table is table comprised of values from other tables, which is accessed as though it were a physical table with its own set of columns. A derived table is exposed as its own view using thederived_table parameter, and defines dimensions and measures in the same manner as conventional views. The view for a derived table can be queried and joined into other views, just like any other view.

Derived tables are created by using the derived_table parameter in a view declaration. For complete details, see Derived Tables Reference.

USING DERIVED TABLES FOR FACTS TABLES

In Looker a common use for derived tables is to present a facts table, which computes facts about an entity, based on values derived from other views. For example, it is a common need to analyze user traits based past orders or actions, and report, sort, and filter like any other facet of a user.

EXAMPLE: A DERIVED TABLE FOR USER ORDER FACTS

Consider an e-commerce data set with a users table containing customer data and an orderstable containing details about customer orders. A derived table can be used to create a user-orders facts table, containing user-centric facts such as lifetime total revenue for a user, which doesn’t physically exist in the underlying tables. More example columns are: number of lifetime orders, latest order date, has the user placed multiple orders. See the diagram below.

Because the primary key for the fact table is user_id, the view can be joined one-to-one with theusers explore, enabling rich query possibilities. An example is shown below:

DEFINITION: PERSISTENT DERIVED TABLES

There are often cases where the time needed to perform the computation that results in a derived table is significant. Looker can cache (or “persist”) the data in a derived table to avoid running an expensive derived-table computation more often than necessary. Persistent derived tables are simply derived tables that specify persist_for parameter, which specifies the refresh rate for the persisted data.

Persistent derived tables use a scratch table in the database to save results, which requires additional database configuration depending on the type of database. See Persistent Derived Tablesfor full details.

DEFINITION: DATABASE CONNECTION

Looker issues queries against a database, specified in the LookML model file. A Looker connectionspecifies a server hosting the database, and parameters defining how Looker should connect to the database. Database setup is typically done once (or infrequently) by the system administrator, and data modelers simply pick from the available connections.

SCOPING AND NAMING

LookML fields and sets have full names and short names. Full names are of the form <view>.<field-name | set-name>. The left side indicates the scope, which is the view that contains the field or set. The right side specifies the particular field or set name.

Short names simply take the form <field-name | set-name>, with no separating period. Looker expands short names into full names by using the scope in which they are used.

Below is an example showing many forms of names and scope. This is an unrealistic group of fields, but is shown to demonstrate a variety possible scoping expressions.

    - view: orders                  # 'orders' becomes the containing scope
      fields:
      - dimension: amount           # short name, equivalent to orders.amount
        type: number

      - measure: count              # short name, equivalent to orders.count
        type: count

      - dimension: customer_id     # long name, fully scoped as orders.customer_id
        type: number
        sql: ${TABLE}.customer_id

      - dimension: customer_address # a field defined in the 'customer' view and scoped as 'orders.customer_address'
        sql: ${customer.address}

      sets:
        drill_fields: [amount, customer.id]  # short name, equivalent to orders.drill_fields
                                             # List items equivalent to 'orders.amount' and 'customer.id'

In the dimension: customers.id declaration above, note that we are assigning a scope (customers) to a field name which is different from the default enclosing scope (orders). In practice, this pattern is rare, but it is legal. It can be used to create two separate views from one underlying table.

In the dimension: address declaration above, note that the underlying view for the SQL block (customer) is different than the enclosing view scope (orders). This is also a rare practice, but is legal syntax. 

DEFINITION: SQL DIALECT

Looker supports all the main database types, such as MySQL, Oracle, MS SQL, Redshift, and so on. Historically, each database supports a slightly different feature set with differing function names, referred to as the SQL dialect.

LookML is designed to work with all SQL dialects, and LookML does not prefer one dialect over the other. However, for LookML parameters that take SQL code expressions (known as SQL blocks), you must use the SQL dialect that matches your database.

DEFINITION: SQL BLOCK

Not all elements of a Looker query are machine-generated. At some point the data model needs to provide specific detail for Looker’s SQL generator to access the underlying tables and compute derived values. In LookML SQL blocks are snippets of SQL code provided by the data modeler, which Looker uses to synthesize complete SQL expressions.

LookML parameters starting with sql_ expect a SQL expression of some form. Examples are:sql_always_where, sql_on, sql_table_name, and sql_case. The most common SQL block parameter is sql, used in dimension and measure field definitions. The sql parameter specifies a SQL clause to reference an underlying column or to perform an aggregate function.

The code you specify in a SQL block can be as simple as a single field name or as complex as a correlated subselect. The content can be quite complex, accommodating almost any need you might have to express custom query logic in raw SQL. Note that the code you use in SQL blocks must match the SQL dialect used by the database.

EXAMPLE SQL BLOCKS FOR DIMENSIONS AND MEASURES

Below are examples of SQL blocks for dimensions and measures. The LookML substitution operator ($) can make these sql declarations appear deceptively unlike SQL. However, after substitution has occurred, the resulting string is pure SQL, which Looker injects into the SELECT clause of the query.

- dimension: id
  primary_key: true
  sql: ${TABLE}.id    # Specify the primary key, id.

- measure: average_cost
  type: average
  value_format: '0.00'
  sql: ${order_items.cost}   # Specify the field to run the AVG function on.

- dimension: name
  sql: CONCAT(${first_name}, ' ', ${last_name})

- dimension: days_in_inventory
  type: int
  sql: DATEDIFF(${sold_date}, ${created_date})

As shown in the last 2 examples above, SQL blocks can use functions supported by the underlying database (in this case, MySQL functions CONCAT and DATEDIFF).

EXAMPLE SQL BLOCK WITH A CORRELATED SUBSELECT

In the sql: block you can place any SQL statement, including a correlated subselect. An example is below:

    - view: customers
      fields:
      ...
      - dimension: id
        primary_key: true
        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'
      ...

EXAMPLE SQL BLOCK FOR DERIVED TABLES

Derived tables use the SQL block to specify the query that derives the table. An example is below:

- 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)...
  fields:
    - dimension: lifetime_orders
      type: number

関連記事

コメント

0件のコメント

ログインしてコメントを残してください。