[新機能]MetricFlow統合後のdbt Semantic Layerが一般提供になったのでsemantic_modelsとmetricsを定義しdbt Cloud CLIから参照してみた

さがらです。

現地時間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_models1つごとに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からの参照は、早めに試したいと思いますｗ