Overview

Introduction

Many users want to consume their clean and accurate metrics from Transform into other tools for further analysis or distribution. This might be a BI tool, a customer relationship management (CRM) tool, or a compliance tool. With Transform's MQL(MetricFlow Query Language) SQL JDBC driver, you can now express an API request inside a SQL expression, which allows users to consume their metrics from Transform into various other tools that support this interface. This functionality further allows Transform to be the generic interface with cleaned metrics in your data stack.

JDBC is a very common interface supported by a plethora of tools. It is an API that defines how a client may access a database, and it is industry-standard for database access.

Transform uses a Calcite Avatica JDBC driver, which allows you to seamlessly query the metrics that you've defined in MetricFlow. This interface is also able to access your data warehouse, so you can enrich the data you've created in Transform with other data sources outside your metrics and run any supported SQL that your data warehouse supports alongside these requests.

Accessing your Metrics

Once set up with Transform's JDBC SQL interface, you can access your metrics. Start with running SELECT * FROM MQL_LIST_METRICS(); to view all of your metrics and corresponding dimensions in Transform.

Querying your Metrics

The usage for our MQL query expression is as follows. The delimiter for expressing MQL request in SQL is <> (MQL_QUERY(<>)):

SELECT ... 


FROM MQL_QUERY(<

    {EXPLAIN}
    {EXPLAIN SOURCE}

        [metric,...]]

    [BY]

        [dimension,..],
            {time_dimension{__day|week|month|quarter|year}}
    
    {WHERE expr}

    {ORDER BY {col_name | expr } [ASC | DESC], ...]}

    {LIMIT [rowcount]}

        {CACHE MODE 'READ|WRITE|READWRITE|NONE'}

>)

Remarks

• At least one metric and at least one dimension is required
• EXPLAIN provides the query executed from Transform's cache. For the source query used to build the metric from the data warehouse, use EXPLAIN SOURCE
• To aggregate your metric by different time granularities, append the double underscore and time granularity option to the time dimension that you are choosing
• CACHE MODE allows you to control whether Transform is going to read from the caches, write to the caches, or do neither. By default, we will read and write from the cache.

Examples

Example 1: Listing all your metrics

SELECT * FROM MQL_LIST_METRICS();

The output will return the metrics you've defined along with corresponding dimensions, and measures.

metric	dimensions	measures
listings	is_treehouse	listings
bookings	listing__is_treehouse, country, metric_time, is_instant	bookings
booking_value	listing__is_treehouse, country, metric_time, is_instant	booking_value
instant_bookings	listing__is_treehouse, country, metric_time, is_instant	instant_bookings

Example 2: Querying metrics by certain dimensions

Consider the above metric, dimension, and measures list from a popular travel booking service. It contains a dimensional data source for listings (listing) as well as a fact data source (bookings) and values associated with them.

If we want to examine a metric called bookings queried by the dimension country, the API request would be the following:

SELECT * 
FROM 
MQL_QUERY(<
    bookings 
  BY 
    user__country>);

Example 3: Querying metrics with constraints

Perhaps you want to average the booking_value metric over time after the January 1, 2020 (metric_time is the primary time dimension).

SELECT
  AVG(booking_value) AS avg_booking_value
FROM MQL_QUERY(<
    booking_value 
  BY 
      metric_time
 WHERE 
      metric_time >= '2020-01-01'
>)
GROUP BY 1;

You can use constraints in the MQL statement to grab only certain values. Consider this query that grabs 10 treehouse bookings on January 2nd, 2020:

SELECT *
FROM MQL_QUERY(<
    bookings 
  BY 
    metric_time,
    listing__is_treehouse
  WHERE
    metric_time = '2020-01-02'
  LIMIT 10
>);

Example 4: Using Time granularity

This aggregates the time dimension - in this case - the primary time dimension metric_time by weekly granularity.

SELECT *
FROM MQL_QUERY(<
    bookings 
  BY 
    metric_time__week
>);

Example 4: Using EXPLAIN and EXPLAIN SOURCE

EXPLAIN will provide the query as generated from Transform's cache. EXPLAIN will not execute the query.

SELECT *
FROM MQL_QUERY(<EXPLAIN
    bookings 
  BY 
    metric_time__week
>);

EXPLAIN SOURCE will provide the query that was executed in the past to build the data directly from the data warehouse. Similarly, EXPLAIN SOURCE will not execute the query.

SELECT *
FROM MQL_QUERY(<EXPLAIN SOURCE
    bookings 
  BY 
    metric_time__week
>);