Getting Started

After you define important metrics in MetricFlow, you can easily query metrics and test configurations using our command-line interface that executes MQL requests (MetricFlow Query Language).

Installation

Run pip install transform in your terminal (pip3 if you're on Python 3).
Create an API key in the Transform user interface or use an existing API key that you've saved (under Settings).
Run mql setup -k <your api key>
Upon success, you should receive a message indicating that you're ready to query metrics from your organization!

CLI Usage

Run help to see all commands.

mql --help

    /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 [OPTIONS] COMMAND [ARGS]...
    
    Options:
      -v, --verbose
      -y, --skip-confirm
      --debug-log-file
      --help              Show this message and exit.
    
    Commands:
      commit-configs              Commit yaml configs found in specified...
      contact                     Instructions for how to contact Transform...
      data-warehouse-validations  Run data warehouse validations for a model,...
      drop-cache                  Drop the MQL cache.
      drop-materialization        ***NEW*** Create a new MQL drop...
      get-dimension-values        List all dimension values that are...
      health-report               Completes a health check on MQL servers.
      identify                    Identify the currently authenticated user.
      install-completion          Install command completion for the MQL CLI
      invalidate-caches           Invalidates caches
      latest-mql-image            Outputs the latest MQL server image details
      list-dimensions             List all unique dimensions for the...
      list-materializations       List the materializations for the...
      list-metrics                List the metrics for the Organization with...
      list-queries                Retrieve queries from mql server
      list-servers                Lists available MQL servers.
      materialize                 Create a new MQL materialization query,...
      pin-model                   Pin a model id from configs that are...
      ping                        Perform basic HTTP health check against...
      query                       Create a new MQL query, polls for...
      setup                       Guides user through CLI setup.
      stream-query-logs           Retrieve queries from mql server
      unpin-model                 Unpin a model id
      validate-configs            Validate yaml configs found in specified...
      version                     Print the current version of the MQL CLI.

Note: See mql query for reference on how to query metrics and mql materialize for reference on how to materialize metrics.

Examples - Informational Commands

Identify

Use MQL identify to confirm what organization you're querying. This is useful if you have multiple Transform organizations.

mql identify

You are authenticated as Roy Kent within the AFC Richmond organization using model at transform/master:b3d5fcf682418cba3c8c12c22
Your MQL server is https://afcrichmond-mql.prod.transformdata.io/

List Metrics

List Metrics will provide all metrics in your Transform organization as well as a set of corresponding dimensions. Use the show-all-dims argument to get a full list of dimensions

mql list-metrics --show-all-dims

    /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')
    ✔ 🌱 We've found 23 metrics for the new-sales-demo organization.
    The list below shows metrics in the format of "metric_name: list of available dimensions"
    • active_products: category, ds, is_active, is_hosted, name, product__category, product__ds, product__is_active, product__is_hosted, product__name
    • customers: billing_country, customer__billing_country, customer__ds, customer__segment, customer__status, customer__type, ds, segment, status, type
    • framework_gross_sales: customer__billing_country, customer__ds, customer__segment, customer__status, customer__type, ds, product__category, product__ds, product__is_active, product__is_hosted, product__name, sales_geo, sales_person__ds, sales_person__name, sales_person__region, sales_person__tier, sales_person__window_end, sales_person__window_start, transaction_id__ds, transaction_id__sales_geo
    • gross_sales: customer__billing_country, customer__ds, customer__segment, customer__status, customer__type, ds, product__category, product__ds, product__is_active, product__is_hosted, product__name, sales_geo, sales_person__ds, sales_person__name, sales_person__region, sales_person__tier, sales_person__window_end, sales_person__window_start, transaction_id__ds, transaction_id__sales_geo
    • gross_sales_last28: customer__billing_country, customer__ds, customer__segment, customer__status, customer__type, ds, product__category, product__ds, product__is_active, product__is_hosted, product__name, sales_geo, sales_person__ds, sales_person__name, sales_person__region, sales_person__tier, sales_person__window_end, sales_person__window_start, transaction_id__ds, transaction_id__sales_geo
    • gross_sales_last365: customer__billing_country, customer__ds, customer__segment, customer__status, customer__type, ds, product__category, product__ds, product__is_active, product__is_hosted, product__name, sales_geo, sales_person__ds, sales_person__name, sales_person__region, sales_person__tier, sales_person__window_end, sales_person__window_start, transaction_id__ds, transaction_id__sales_geo
    ...

List Servers

List servers will list all MQL servers for your Transform Data Organization.

mql list-servers

🖨  We've found 1 MQL servers for the AFC Richmond organization. ⭐️ - Primary.
• ⭐️ primary: https://afcrichmond.mql.prod.transformdata.io/

List Queries

This command will provide JSON output of queries that have recently run on the MQL server.

mql list-queries

List Materializations

This command will show all materializations defined in your organization

mql list-materializations

    /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')
    ✔ 🌱 We've found 2 materializations for the new-sales-demo organization.
    The list below shows materializations in the format of "materialization: list of available metrics, then dimensions"
    • sales_metrics_tableau:
    Metrics: transactions, transactions_per_customer
    Dimensions: customer__billing_country, ds__week
    destination table: sales_metrics_tableau
    • transaction_metrics:
    Metrics: gross_sales, meetings
    Dimensions: metric_time
    destination table: transaction_metrics

Version

Show the current version of the MQL CLI.

mql version

    /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')
    1.4.3

Examples - Configuration Development

Pin and Unpin

Pin a model id from configs that are already committed to the MQL Server to use for querying

mql pin-model --model-id 1000

Unpin a model will unpin the currently pinned model

mql unpin-model

Validate Configs

mql validate-configs /a/path/to/configs

If successful, you should receive the following output with additional information on your model:

🎉 Successfully validated configs for commit

Commit Configs

Commit Configurations to your Transform model. Optionally pin the model for local querying

mql commit-configs --config-dir /a/path/to/configs --pin true

✔ 🦄 Successfully committed configs id 1000 on branch led_tasso with commit 8374837843

Examples - Debugging

Health Report

MQL health report returns the health of the MQL server that you are querying. This will provide any errors or issues with your setup.

mql health-report

    /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')
    ✔ Successfully built health report!
    🏥 Health Report for 2 MQL Servers at new-sales-demo
      • ✅ snowflakedbclient - Select 1: No Errors
      • ✅ snowflakedbclient - CREATE CACHE SCHEMA: No Errors
      • ✅ snowflakedbclient - List Tables in Cache: No Errors
      • ✅ snowflakedbclient - Prerequisite - Drop Test Tables: No Errors
      • ✅ snowflakedbclient - Create Table in Cache: No Errors
      • ✅ snowflakedbclient - Rename Table in Cache: No Errors
      • ✅ snowflakedbclient - Drop Table in Cache: No Errors
      • ✅ snowflakedbclient - Connection State: No Errors
      • ✅ mysqldbclient - Select 1: No Errors
      • ✅ mysqldbclient - CREATE CACHE SCHEMA: No Errors
      • ✅ mysqldbclient - List Tables in Cache: No Errors
      • ✅ mysqldbclient - Prerequisite - Drop Test Tables: No Errors
      • ✅ mysqldbclient - Create Table in Cache: No Errors
      • ✅ mysqldbclient - Rename Table in Cache: No Errors
      • ✅ mysqldbclient - Drop Table in Cache: No Errors

Ping

Ping the MQL server to make sure it's alive

mql ping

    /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')
    🏓 Received HTTP 200 code from MQL in 0.4921 seconds. 
     Healthily running commit: 3561c7f48fae8e7f38c5edfb8b4d9edf178c0bf2

Stream Query Logs

You may be prompted to run Stream Query Logs after an error. Stream Query logs takes an ID argument.

mql stream-query-logs --query-id 100000

A log containing information on execution, errors, and other information from start to finish of the query. An example snippet is provided below.

[INFO][2021-09-23 16:50:41,626]: query: [SELECT annotations_created AS annotations_created , groupby_unalias_8d21d772.ds ...]
[INFO][2021-09-23 16:50:42,884]: query execution done
[INFO][2021-09-23 16:50:42,885]: fetching data done
[INFO][2021-09-23 16:50:42,888]: query: [ROLLBACK]
[INFO][2021-09-23 16:50:42,938]: query execution done
[INFO][2021-09-23 16:50:42,939]: Query took 1.31 seconds. Result contains 81 row(s)
[INFO][2021-09-23 16:50:42,947]: Query ID: 1632375498179_0000016 - Successful.

Drop Cache

Drop Cache should only be run if there is a corruption issue.

mql drop-cache --f

Installation​

CLI Usage​

Examples - Informational Commands​

Identify​

List Metrics​

List Servers​

List Queries​

List Materializations​

Version​

Examples - Configuration Development​

Pin and Unpin​

Validate Configs​

Commit Configs​

Examples - Debugging​

Health Report​

Ping​

Stream Query Logs​

Drop Cache​