Skip to main content

Querying Metrics using CLI

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

Query Usage#

Run for usage commands:

mql query --help

Query Examples#

Query Metrics and Dimensions#

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

mql query --metrics revenue --dimensions country

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

mql query --metrics revenue --dimensions '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 --metrics revenue --dimensions 'date, country' --limit 100 --order date

Time Constraints#

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

mql query --metrics revenue --dimensions date --time-constraint "date BETWEEN '2020-10-01' and '2020-10-10'"

Note: Use single quotes around dates

Where constraints#

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

mql query --metrics revenue --dimensions '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 --metrics metric_name{__wow/mom/qoq/yoy} --dimensions 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

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

mql query --metrics revenue --dimensions date__week

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

mql query --metrics revenue__mom --dimensions date__month

Point to a directory of YAML files#

You can optionally point to a directory with YAML files to execute your query against. This option may be useful if you are testing local changes to your model

mql query  --metrics revenue --dimensions date --config-dir /configs/transform.io

Export CSV#

You can optionally export results out to a csv format by using csv and a filename. This will export to a file called revenue.csv in directory you are in when you executed it.

mql query --metrics revenue --dimensions date --csv revenue.csv

Timeout#

You can optionally set a timeout parameter. By default, the CLI defaults to a timeout of 180 seconds. 

mql query --metrics revenue --dimensions date --timeout 300

Cache Mode#

The CLI 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 --metrics revenue --dimensions date --cache-mode 'r'

Debug#

Use --debug to print out the detailed log of how the query is being executed 

mql query --metrics revenue --dimensions date --debug

Web#

Using --web will open the MQL server logs in the Transform Metrics Catalog which provides a more user-friendly viewing experience

mql query --metrics revenue --dimensions date --web

Detach Mode#

Detach allows you to initialize the query and return a query id without executing the query and returning results. 

mql query --metrics revenue --dimensions date --detach true

Explain#

Use Explain to return the SQL executed by Transform. This will not execute the query, it will only run the SQL.

mql query --metrics revenue --dimensions date --explain