Skip to main content

Querying Metrics using Python

Transform's Metric Query Language is a powerful tool that allows you to query metrics cut by different dimensions. The MQLClient.query() function takes a number of arguments. At minimum, you need to provide it a metric and a dimension.

Usage#

See API Reference Doc

Query Examples#

Query Metrics and Dimensions#

Assume you want to get the revenue metric by a dimension of country:

mql.query(["revenue"], ["country"])

You can optionally include multiple dimensions. Let's say you want to include revenue by date and country:

mql.query(["revenue"], ["date", "country"])

Order and Limit#

Like a SQL function, you can limit the number of rows and also order by a particular field:

mql.query(["revenue"], ["date", "country"], limit=100, order=["date"]) # Ascendingmql.query(["revenue"], ["date", "country"], limit=100, order=["-date"]) # Descending

Time Constraints#

Use Time Constraints to filter the resultset by your primary time dimension: 

mql.query(["revenue"], ["date"], time_constraint="date BETWEEN 2020-10-01 and 2020-10-10")

Where constraints#

You can also use constraints with the where argument to filter by other dimensions:

mql.query(["revenue"], ["date", "country"], where="country = 'Spain'")

Time Granularity and Time over Time#

You can optionally specify the time granularity you want your data to be aggregated at by appending double underscore and the unit of granularity you want against your time dimension. Additionally, you can expand that to do time over time comparison at a given granularity. For example, you can compute a monthly granularity and calculate the month over month growth rate. The granularity options are: day, week, month, quarter year. Similarly, the time over time options are, week over week (wow), month over mom (mom), quarter over quarter (qoq), year over year (yoy). The syntax is as follows:

mql.query([metric_name{__wow/mom/qoq/yoy}], [time_dimension_name{__day|week|month|quarter|year}])

The valid pairs for granularity and time over time are as follows:

  • wow: day, week
  • mom: day, week, month
  • qoq: day, week, month, quarter
  • yoy: day, week, month, quarter, year

Note: Querying for more than 1 metric is not supported for Time over time.

Here is an example of aggregating revenue by week where the time dimension is called date.

mql.query(["revenue"], ["date__week"])

Here is an example of calculating a revenue month over month comparison with a monthly granularity: 

mql.query(["revenue__mom"], ["date__month"])

Cache Mode#

The interface allows you to control how it is reading and writing from Transform's cache. This is an optional parameter. By default, we will use 'rw' - which indicates we will both read and write from the cache. The options include:

'r' - When answering queries, use tables in fast cache, materializations, or in the dynamic cache that have the data.

'w' - Once a result has been computed, whether to write the result into the dynamic cache so it can be read later.

'rw' - Combination of read and write.

'i' - Don't read from any cached tables, or write to any of them.

mql.query(["revenue"], ["date"], cache_mode="r")