さがらです。
現地時間10月16日~10月19日で、dbt Coalesceが開催されました。
基調講演で発表された新機能については、下記のブログが参考になります。
この基調講演で、MetricFlow統合後のdbt Semantic Layerが一般提供になったと発表がありました!
連携できるパートナー製品も併せて発表され、なんとTableauも含まれています。
ということで早速試してみたので、本記事でその内容をまとめてみます。
試す内容
下記の公式Docに沿ってMetricFlowのsemantic_models
やmetrics
を定義し、dbt Cloud CLIからコマンドを実行して確認してみます。
下記のリポジトリですでにsemantic_models
やmetrics
が定義されているため、このリポジトリをforkしてどのようにsemantic_models
やmetrics
が定義されているのかを見ていきたいと思います。
検証環境
- dbt Cloud:Enterpriseエディション
- 各Environmentのdbtのバージョン:1.6
- dbt Cloud CLI:0.34.2
「semantic_models」を定義
まず、semantic_models
を定義していく必要があります。
semantic_models
は、大きく以下の3つの概念から構成されています。
entities
:対象のsemantic_models
におけるprimary keyや、他のsemantic_models
とJOINするためのforeign keyを定義dimensions
:集計を行う際の粒度となるカラムを定義。SQLでいうGROUP BY
句で指定されるカラムmeasures
:集計を行うカラムとその集計方法を定義。SQLでいうSUM
などの集計関数を使った定義
semantic_models
の定義方法の詳細については、下記の公式Docも併せてご覧ください。
forkしたリポジトリのmodels/marts/orders.yml
の45行目を見ると、semantic_models
の定義がされています。model: ref('orders')
という形で、semantic_models
1つごとにdbtで定義したModelへref
関数で参照を行う必要があります。
semantic_models:
- name: orders
defaults:
agg_time_dimension: ordered_at
description: |
Order fact table. This table is at the order grain with one row per order.
model: ref('orders')
次に、forkしたリポジトリのmodels/marts/orders.yml
の52行目からはsemantic_models
の中でentities
を定義しています。
type: primary
で参照しているModelのPrimary Keyを設定し、type: foreign
で参照しているModelのForeign Keyを設定します。
entities:
- name: order_id
type: primary
- name: location
type: foreign
expr: location_id
- name: customer
type: foreign
expr: customer_id
次に、forkしたリポジトリのmodels/marts/orders.yml
の61行目からはsemantic_models
の中でdimensions
を定義しています。
dimensions
では、集計指標を分類したいカラムを参照しているModelから引用して定義します。時間ベースのカラムや、カテゴリを表すカラムを設定することが多くなると思います。
dimensions:
- name: ordered_at
expr: ordered_at
type: time
type_params:
time_granularity: day
- name: order_total_dim
type: categorical
expr: order_total
- name: is_food_order
type: categorical
- name: is_drink_order
type: categorical
次に、forkしたリポジトリのmodels/marts/orders.yml
の74行目からはsemantic_models
の中でmeasures
を定義しています。
measures
では参照先のModelのカラムを用いた、集計方法や計算方法を定義します。
例えばorder_total
は、参照しているModelにorder_total
というカラムがあり、このカラムをsum
する、ということを意味しています。
別の例としてlocations_with_orders
について、集計方法はagg: count_distinct
、count_distinctの対象カラムはexpr: customer_id
と定義することで、参照しているModelにlocation_id
というカラムがあるので、このカラムに対するcount_disinctを集計する、ということを意味しています。
measures:
- name: order_total
description: The total amount for each order including taxes.
agg: sum
- name: order_count
expr: 1
agg: sum
- name: tax_paid
description: The total tax paid on each order.
agg: sum
- name: customers_with_orders
description: Distinct count of customers placing orders
agg: count_distinct
expr: customer_id
- name: locations_with_orders
description: Distinct count of locations with order
expr: location_id
agg: count_distinct
- name: order_cost
description: The cost for each order item. Cost is calculated as a sum of the supply cost for each order item.
agg: sum
「metrics」を定義
semantic_models
を定義したら、今度はmetrics
を定義する必要があります。
metrics
は、semantic_models
で定義されたmeasure
やdimensions
を用いて、実際にBIツールやデータアプリケーション上で使われる集計指標を定義するものです。
metrics
の定義方法の詳細については、下記の公式Docも併せてご覧ください。
forkしたリポジトリのmodels/marts/orders.yml
の96行目からmetrics
を定義しています。
例えばlarge_order
では、事前に定義したmeasures
であるorder_count
を元に集計しますが、order_total_dim
カラムの値が20以上のレコードだけで集計を行った結果を返す指標を定義しています。
metrics:
- name: order_total
description: Sum of total order amonunt. Includes tax + revenue.
type: simple
label: Order Total
type_params:
measure: order_total
- name: large_order
description: "Count of orders with order total over 20."
type: simple
label: "Large Orders"
type_params:
measure: order_count
filter: |
{{ Dimension('order_id__order_total_dim') }} >= 20
- name: orders
description: Count of orders.
label: Orders
type: simple
type_params:
measure: order_count
- name: food_orders
description: Count of orders that contain food order items
label: Food Orders
type: simple
type_params:
measure: order_count
filter: |
{{ Dimension('order_id__is_food_order') }} = true
また、forkしたリポジトリのmodels/marts/order_items.yml
の46行目からは、他のtypeを用いたmetrics
が定義されています。こちらもぜひ参考にしてみてください。
- 割合を求める
type: ratio
- 他の定義済の
metrics
を引用したmetrics
を引用したい場合に使うtype: derived
- Window関数のように累積値を求める
type: cumulative
dbt Cloud CLIからMetricsを参照してみた
続いて、dbt Cloud CLIを用いて定義したMetricsを参照してみます。※公式Docにもある通り、2023年10月27日時点ではdbt CloudのIDE上からMetricsを参照できず、dbt Cloud CLIかdbt Coreを使用してMetricsを参照する必要があります。
dbt Cloud CLIの使用方法については下記のブログにも書いておりますので、ぜひ参考にしてください。
対象のリポジトリをCloneし、一度dbt seed
とdbt build
を実行します。
その後、MetricFlowを用いたMetricsの参照がちゃんと行えるのかを確認するため、dbt sl --help
コマンドを実行します。
下記のように表示されたら、準備完了です!
$ ./dbt sl --help
Query metrics or metadata against your semantic layer.
Usage:
dbt sl [command]
Available Commands:
list List semantic layer model metadata from your project.
query Make a query against your semantic layer.
Flags:
-h, --help help for sl
Use "dbt sl [command] --help" for more information about a command.
まずは一番シンプルな参照方法として、定義したMetricsであるorder_total
の集計結果をリクエストしてみます。dbt sl query --metrics order_total
と入力して実行すると、下記のような結果が得られます。
$ ./dbt sl query --metrics order_total
+-------------+
| ORDER_TOTAL |
+-------------+
| 254810.4 |
+-------------+
ちなみに、どのようなクエリが実行されているかを知るには、--compile
をつけると実行されるクエリを返してくれます。
$ ./dbt sl query --metrics order_total --compile
SELECT
SUM(order_total) AS order_total
FROM SAGARA_DBT_SEMANTIC_LAYER_TEST.dbt_ssagara.orders orders_src_161
LIMIT 100
SQL compiled
定義したdimensions
で粒度を細かく集計結果を得たい場合には、--group-by
を入れてあげればOKです。,
で区切ることで、複数のdimensions
であっても対応可能です。
$ ./dbt sl query --metrics order_total --group-by order_id__ordered_at__month
+-----------------------------+-------------+
| ORDER_ID__ORDERED_AT__MONTH | ORDER_TOTAL |
+-----------------------------+-------------+
| 2016-09-01T00:00:00.000Z | 18053.28 |
| 2016-10-01T00:00:00.000Z | 21923.95 |
| 2016-11-01T00:00:00.000Z | 27916.82 |
| 2017-01-01T00:00:00.000Z | 45017.95 |
| 2017-03-01T00:00:00.000Z | 62862.68 |
| 2016-12-01T00:00:00.000Z | 35677.6 |
| 2017-02-01T00:00:00.000Z | 43358.12 |
+-----------------------------+-------------+
$ ./dbt sl query --metrics order_total --group-by order_id__ordered_at__month
,location__location_name
+-----------------------------+-------------------------+-------------+
| ORDER_ID__ORDERED_AT__MONTH | LOCATION__LOCATION_NAME | ORDER_TOTAL |
+-----------------------------+-------------------------+-------------+
| 2016-09-01T00:00:00.000Z | Philadelphia | 18053.28 |
| 2016-10-01T00:00:00.000Z | Philadelphia | 21923.95 |
| 2016-11-01T00:00:00.000Z | Philadelphia | 27916.82 |
| 2016-12-01T00:00:00.000Z | Philadelphia | 35677.6 |
| 2017-01-01T00:00:00.000Z | Philadelphia | 45017.95 |
| 2017-03-01T00:00:00.000Z | Philadelphia | 43440.77 |
| 2017-03-01T00:00:00.000Z | Brooklyn | 19421.91 |
| 2017-02-01T00:00:00.000Z | Philadelphia | 43358.12 |
+-----------------------------+-------------------------+-------------+
ちなみに、--group-by
で入力したdimensions
が間違っていると、--group-by
で使えるdimensions
の一覧を返してくれます。
$ ./dbt sl query --metrics order_total --group-by ordered_at
Polling for queried result set
Encountered an error: error querying against the semantic layer: Unable to resolve the time dimension spec for PartialTimeDimensionSpec(element_name='ordered_at', entity_links=(), date_part=None). Valid group by elements are:
['order_id__order_total_dim',
'order_id__is_food_order',
'order_id__is_drink_order',
'location__location_name',
'customer__customer_name',
'customer__customer_type',
'order_id__ordered_at__day',
'order_id__ordered_at__week',
'order_id__ordered_at__month',
'order_id__ordered_at__quarter',
'order_id__ordered_at__year',
'metric_time__day',
'metric_time__week',
'metric_time__month',
'metric_time__quarter',
'metric_time__year',
'location__opened_at__day',
'location__opened_at__week',
'location__opened_at__month',
'location__opened_at__quarter',
'location__opened_at__year',
'customer__first_ordered_at__day',
'customer__first_ordered_at__week',
'customer__first_ordered_at__month',
'customer__first_ordered_at__quarter',
'customer__first_ordered_at__year',
'customer__last_ordered_at__day',
'customer__last_ordered_at__week',
'customer__last_ordered_at__month',
'customer__last_ordered_at__quarter',
'customer__last_ordered_at__year',
'order_id',
'order_id__order_id',
'location',
'order_id__location',
'customer',
'order_id__customer']
最後に
MetricFlow統合後のdbt Semantic Layerが一般提供になったので、サンプルリポジトリをforkしてsemantic_models
とmetrics
の定義方法を確認し、dbt Cloud CLIから定義したmetrics
を参照してみました。
ただ、今日試したCLIから参照するケースは正直テスト的な用途でしかないと思いますので、Tableauから参照してみたり、JDBCやGraphQLから参照してこそ、Semantic Layerとしての本領が見えてくると思います。特にTableauからの参照は、早めに試したいと思いますw