Skip to main content

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(Metrics Query Language) SQL JDBC driver, you can now express an API request inside of 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 Transform's metric Framework. 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 of 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.

metricdimensionsmeasures
listingsis_treehouselistings
bookingslisting__is_treehouse, country, ds, is_instantbookings
booking_valuelisting__is_treehouse, country, ds, is_instantbooking_value
instant_bookingslisting__is_treehouse, country, ds, is_instantinstant_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 (ds is the primary time dimension).

SELECT  AVG(booking_value) AS avg_booking_valueFROM MQL_QUERY(<    booking_value   BY       ds WHERE       ds >= '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     ds,    listing__is_treehouse  WHERE    ds = '2020-01-02'  LIMIT 10>);

Example 4: Using Time granularity

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

SELECT *FROM MQL_QUERY(<    bookings   BY     ds__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     ds__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     ds__week>);