Introducing Transform’s JDBC driver: Consume your metrics in downstream tools

We know that you want to see consistent, accurate metrics wherever you consume data—in BI tools, customer relationship management (CRM) tools, compliance tools...the list goes on and on.

We feel the same way—that’s why we’ve built Transform’s JDBC driver. This driver gives you the power to express an API request inside of a SQL expression. In other words, you can use Transform as your hub for metrics and then consume all of these metrics in all your other tools that support this interface.

After you define your important metrics in MetricFlow, Transform’s Metrics Framework, we offer a wide variety of interfaces to access this information, which all rely on the core Metrics Query Language API (MQL). MQL is the single source-of-truth that allows you to access consistent query results in all your favorite tools.

What is a JDBC driver? And how does it work?

JDBC ( Java™ database connectivity)  is a standard API that allows programs to access a database. It is a very common interface that is supported by a plethora of tools.

Transform allows you to seamlessly query the metrics that you've defined in the MetricFlow framework using a JDBC interface. This connection can also 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 from Transform

Once set up with Transform's JDBC SQL interface, you can access your metrics. Start with running the following to view all of your metrics and corresponding dimensions in Transform.

SELECT * FROM MQL_LIST_METRICS(); 

Querying your metrics using JDBC

Transform's JDBC interface supports a variety of SQL inputs, including at the most basic level to list your metrics, query your metrics, slice and dice by different time granularities and troubleshoot your SQL if required. We’ve included the usage syntax for our MQL SQL interface below:

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'}
>)

Five examples of how to use the syntax

Example 1: Listing all your metrics

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

Consider the following 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.

Example 2: Querying metrics by certain dimensions

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 January 1, 2020 (where ds is the time dimension), you can do so using a constraint and the average function.

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

Example 4: Using time granularity

This aggregates the time dimension. In this case, we slice the primary time dimension ds by weekly granularity.

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

Example 5: Using EXPLAIN and EXPLAIN SOURCE

EXPLAIN and EXPLAIN SOURCE will provide the query as generated from Transform's cache.

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. Neither option will execute the query.

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

Conclusion: What JDBC means for you

Every team in your organization has their own set of tools. Your metrics should be consistent across all of them. With this technology, we’re one step closer to helping you centralize your metrics across all the tools in your data stack. Check out our most recent integration with Mode.