このページでは 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種類あります。
${TABLE}.column_name
は現在スコープにある view をテーブル名として代入します。${field_name}
はdimension, measure いずれかの field 名を指定します。${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 orders
table 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件のコメント
ログインしてコメントを残してください。