Querying Metrics using CLI
MetricFlow Query Language (MQL) is a powerful tool that allows you to query metrics and slice 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
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
Usage: mql query [OPTIONS]
Create a new MQL query, polls for completion and assembles a DataFrame from
the response.
Options:
-t, --timeout INTEGER Sets the timeout to wait for query
completion. Pass 0 to remove any timeout.
--detach BOOLEAN Returns the created query ID to allow for
asynchronous querying.
--dimensions TEXT Dimensions to group by: syntax is
--dimensions ds. Submit a list of metrics by
providing multiple --dimensions flags.
--metrics TEXT Metrics to query for: syntax is --metrics
bookings. Submit a list of metrics by
providing multiple --metrics flags.
[required]
--trim BOOLEAN Trim incomplete time periods at the start
and end of query results to avoid misleading
data.
--style [plain|standard|pretty]
Choose your print style. Default: standard
--end-time TEXT Optional iso8601 timestamp to constraint the
end time of the data (inclusive) (eg.,
'2020-01-04')
--start-time TEXT Optional iso8601 timestamp to constraint the
start time of the data (inclusive) (eg.,
'2020-01-04')
--time-comparison [dod|wow|mom|qoq|yoy]
--time-granularity [day|week|month|quarter|year]
--time-constraint TEXT Optional TimeConstraint on query (written as
a WHERE clause, ie: `ds < '2020-01-01')
--where TEXT SQL-like where statement provided as a
string. For example: --where "ds =
'2020-04-15'"
--limit TEXT Limit the number of rows out(Default: 100)
using an int or 'inf' for no limit. For
example: --limit 100 or --limit inf
--order TEXT Metrics or dimensions to order by ("-" in
front of a column means descending). For
example: --order -ds
--cache-mode [r|rw|w|i] Optional interface allowing you to control
how it is reading and writing from
Transform's cache.
[r] read from cached tables
[w] write to cached tables
[rw] read + write -- DEFAULT
[i] neither read nor write from cache
--force-commit When using a local model (--config-dir),
don't skip the commit process even if no
changes are detected.
--dbt-target TEXT The dbt target to use when using `--dbt-
target`. Defaults to the target specified in
dbt profile defintion
--dbt-profile TEXT The dbt profile to use when using `--dbt-
project`. Defaults to the profile specified
in dbt_profile.yml
--dbt-project If specified, treats config directory as dbt
project
--config-dir TEXT Path to directory containing Transform yaml
models to execute query against
--as-table TEXT To write a table in your data warehouse
--csv FILENAME Provide filepath for dataframe output to csv
--explain In the query output, show the query that was
executed against the data warehouse
--decimals INTEGER Choose the number of decimal places to round
for the numerical values
--web Open the MQL server logs on the web
--debug Enable showing query log in the terminal for
debugging purpose
--help Show this message and exit.
Query Examples
Query Metrics and Dimensions
Assume you want to get the transactions metric by a dimension of country:
mql query --metrics transactions --dimensions customer__billing_country --limit 5
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203214
✔ Success 🦄 - query completed after 7.10 seconds
| customer__billing_country | transactions |
|:----------------------------|---------------:|
| MX | 52396 |
| US | 249761 |
| DE | 62901 |
| GR | 46884 |
| CA | 34700 |
You can optionally include multiple dimensions. Let's say you want to include transactions by date (defined as metric_time or datestamp) and the customer's billing country:
mql query --metrics transactions --dimensions 'metric_time, customer__billing_country' --limit 5
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203215
✔ Success 🦄 - query completed after 11.65 seconds
| metric_time | customer__billing_country | transactions |
|:--------------------|:----------------------------|---------------:|
| 2022-12-04 00:00:00 | FR | 1651 |
| 2022-12-04 00:00:00 | MX | 365 |
| 2022-12-04 00:00:00 | DE | 445 |
| 2022-12-04 00:00:00 | IT | 188 |
| 2022-12-03 00:00:00 | IT | 164 |
Order and Limit
Like a SQL function, you can limit the number of rows and also order by a particular field:
mql query --metrics transactions --dimensions 'metric_time, customer__billing_country' --limit 5 --order metric_time
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203216
✔ Success 🦄 - query completed after 2.26 seconds
| metric_time | customer__billing_country | transactions |
|:--------------------|:----------------------------|---------------:|
| 2022-03-01 00:00:00 | MX | 4 |
| 2022-03-01 00:00:00 | US | 17 |
| 2022-03-01 00:00:00 | GR | 5 |
| 2022-03-01 00:00:00 | CA | 2 |
| 2022-03-01 00:00:00 | IT | 2 |
Time Constraints
Use Time Constraints to filter the result set by your primary time dimension:
mql query --metrics transactions --dimensions metric_time --time-constraint "metric_time BETWEEN '2022-01-01' and '2022-01-10'"
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203217
✔ Success 🦄 - query completed after 2.44 seconds
Successful MQL query returned an empty result set.
Note: Use single quotes around dates
Where constraints
You can also use constraints with the where argument to filter by specific dimensions. In this case, we're filtering by the country Italy (IT).
mql query --metrics transactions --dimensions 'metric_time, customer__billing_country' --where "customer__billing_country = 'IT'" --limit 5
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203218
✔ Success 🦄 - query completed after 3.53 seconds
| metric_time | customer__billing_country | transactions |
|:--------------------|:----------------------------|---------------:|
| 2022-12-04 00:00:00 | IT | 188 |
| 2022-12-03 00:00:00 | IT | 164 |
| 2022-12-01 00:00:00 | IT | 134 |
| 2022-11-28 00:00:00 | IT | 160 |
| 2022-11-27 00:00:00 | IT | 163 |
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 month (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 transactions by week where the time dimension is called date.
mql query --metrics transactions --dimensions metric_time__week --limit 5
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203219
✔ Success 🦄 - query completed after 4.19 seconds
| metric_time__week | transactions |
|:------------------------|---------------:|
| 2022-11-28T00:00:00.000 | 34069 |
| 2022-11-21T00:00:00.000 | 32902 |
| 2022-11-14T00:00:00.000 | 32183 |
| 2022-10-31T00:00:00.000 | 30165 |
| 2022-10-24T00:00:00.000 | 28856 |
Here is an example of calculating a revenue month over month comparison with a monthly granularity:
mql query --metrics transactions__mom --dimensions metric_time__month --limit 5
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203220
✔ Success 🦄 - query completed after 4.32 seconds
| metric_time__month | transactions__mom |
|:---------------------|--------------------:|
| 2022-04-01 00:00:00 | 0.25 |
| 2022-05-01 00:00:00 | 0.39 |
| 2022-06-01 00:00:00 | 0.23 |
| 2022-07-01 00:00:00 | 0.30 |
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 transactions --dimensions metric_time --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 transactions --dimensions metric_time --timeout 300 --limit 5
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203221
✔ Success 🦄 - query completed after 2.45 seconds
| metric_time | transactions |
|:--------------------|---------------:|
| 2022-12-04 00:00:00 | 5067 |
| 2022-12-02 00:00:00 | 5056 |
| 2022-12-01 00:00:00 | 4662 |
| 2022-11-30 00:00:00 | 4727 |
| 2022-11-27 00:00:00 | 4924 |
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 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 to read from later.
'rw' - Combination of read and write.
'i' - Don't read from any cached tables, or write to any of them.
mql query --metrics transactions --dimensions metric_time --cache-mode 'r' --limit 5
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203221
✔ Success 🦄 - query completed after 1.77 seconds
| metric_time | transactions |
|:--------------------|---------------:|
| 2022-12-04 00:00:00 | 5067 |
| 2022-12-02 00:00:00 | 5056 |
| 2022-12-01 00:00:00 | 4662 |
| 2022-11-30 00:00:00 | 4727 |
| 2022-11-27 00:00:00 | 4924 |
Debug
Use --debug to print out the detailed log of how the query is being executed
mql query --metrics transactions --dimensions metric_time --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 transactions --dimensions metric_time --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 transactions --dimensions metric_time --detach true
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203222
Explain
Use Explain to return the source SQL executed by Transform on your data warehouse. This will not execute the query, it will only return the SQL.
mql query --metrics transactions --dimensions metric_time --explain
/opt/hostedtoolcache/Python/3.9.16/x64/lib/python3.9/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning
warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')
✔ Query initialized: 203222
🔎 SQL executed for successful query (remove --explain to see data):
-- Aggregate Measures
-- Compute Metrics via Expressions
-- Order By [] Limit 100
SELECT
metric_time
, SUM(transactions) AS transactions
FROM (
-- Read Elements From Data Source 'transactions'
-- Metric Time Dimension 'ds'
-- Pass Only Elements:
-- ['transactions', 'metric_time']
SELECT
ds AS metric_time
, 1 AS transactions
FROM demo_dbt.transactions_base transactions_src_26
) subq_381
GROUP BY
metric_time
LIMIT 100