Hi,

I've exposed MetriQL behind an ssl proxy and I can connect via the python library, but via DBeaver I get an exception when trying to connect.

Error starting query at https://some-domain.com/v1/statement returned an invalid response: JsonResponse{statusCode=500, statusMessage=, headers={access-control-allow-credentials=[true], access-control-allow-headers=[content-type,token], access-control-allow-origin=[*], content-length=[129], date=[Sun, 28 Nov 2021 06:46:36 GMT], samesite=[None; Secure], server=[openresty/1.19.9.1], strict-transport-security=[max-age=63072000]}, hasValue=false} [Error: {"errors":[{"id":null,"links":null,"status":null,"code":null,"title":"An error occurred","detail":null,"meta":null}],"meta":null}]

The server side says.

SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
TYPE_CAT, TYPE_SCHEM, TYPE_NAME,   SELF_REFERENCING_COL_NAME, REF_GENERATION
FROM system.jdbc.tables
WHERE TABLE_NAME LIKE '%' ESCAPE '\'
ORDER BY TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME
javax.ws.rs.WebApplicationException: User must be set
io.trino.server.HttpRequestSessionContext.badRequest(HttpRequestSessionContext.java:503)
io.trino.server.HttpRequestSessionContext.assertRequest(HttpRequestSessionContext.java:452)
io.trino.server.HttpRequestSessionContext.buildSessionIdentity(HttpRequestSessionContext.java:223)
io.trino.server.HttpRequestSessionContext.<init>(HttpRequestSessionContext.java:120)
com.metriql.service.jdbc.StatementService.createSessionContext(StatementService.kt:95)
com.metriql.service.jdbc.StatementService.query$lambda-3(StatementService.kt:105)

I followed the instructions provided here, I also executed a "dbt compile" before running metriql in order to generate the manifest.json file. I'm running metriql thru docker, so my command is:

docker run -v c:\dbt:/mnt/dbt -v c:\users\frigod.dbt:/mnt/dbt_profile buremba/metriql:latest run --project-dir /mnt/dbt/metriql_test2 --profiles-dir /mnt/dbt_profile --manifest-json file:/mnt/dbt/metriql_test/target/manifest.json

I then tried connecting to the Trino engine using dbeaver. I can connect, but I cannot see any table (and opening the table list invalidates the connection).

I the tried running metriql generate to understand the issue:

docker run -v c:\dbt:/mnt/dbt -v c:\users\frigod.dbt:/mnt/dbt_profile buremba/metriql:latest generate --project-dir /mnt/dbt/metriql_test2 --profiles-dir /mnt/dbt_profile

and the result is:

Done creating 0 aggregate dbt models.

In order to use metriql in a dbt project, you need to be using YML files in dbt but not all the users are familiar with it. We should find a dbt-native way for SQL-heavy users to define them inside the SQL. One solution would be parsing column names such as metric__[MEASURE_NAME]__[OPTIONAL_AGGREGATION_TYPE] and creating measures automatically for these columns.

We need to create a macro so that the users can define them as follows:

{{config(materialized='view')}}

select c_nationkey, 
    count(*) as {{measure('total_users', aggregation='sum')}}, 
    min(c_acctbal) as {{measure('min_balance', aggregation='min')}}
from {{source('tpch', 'customer')}} 

The model above will be compiled as:

select c_nationkey, 
  count(*) as metric__total_users__count, 
  min(c_acctbal) as metric__min_balance__min
from snowflake_sample_data.tpch_sf1.customer group by 1

If there is no YML file that documents the model, we will get the table schema, create dimensions for each column, look for the columns that start with metric__, and create a measure for each of them. The dataset will look something like this:

target: source('tpch', "customer")
dimensions:
  c_nationkey:
        column: c_nationkey
  total_users:
        column: total_users
  min_balance:
        column: min_balance
measures:
  total_users_sum:
     aggregation: sum
     column: total_users
  min_balance_min:
     aggregation: min
     column: min_balance

I'm trying to run metriql on a macbook. My dbt project compiles and runs just fine. I reinstalled docker and pulled the latest metriql image.

My dbt setup is customised a little:

• profiles.yml is in the project root. This is configured as an env_variable
• I'm building to ${workspaceFolder}/.dbt/artifacts/. This is configured in dbt_project. Both work fine except for metriql.

(.venv) daniel@dbrtly-MBP dbt_shop % pwd
/Users/daniel/git/dbrtly/dbt_shop
(.venv) daniel@dbrtly-MBP dbt_shop % export DBT_PROJECT_DIR=${PWD}
export DBT_PROFILES_DIR=${PWD}
export METRIQL_PORT=5656

docker run -it -p "${METRIQL_PORT}:5656" -v "${DBT_PROJECT_DIR}:/root/app" -v "${DBT_PROFILES_DIR}:/root/.dbt" -e METRIQL_RUN_HOST=0.0.0.0 -e DBT_PROJECT_DIR=/root/app buremba/metriql \
 run
Aug 02, 2021 7:25:13 AM http://ru.yandex.clickhouse.ClickHouseDriver <clinit>
INFO: Driver registered
Exception in thread "main" Unknown warehouse: bigquery

Also FWIW I'd really prefer to use docker-compose rather than docker cli.

I installed the Metriql CLI through docker: docker pull buremba/metriql:latest

Then, while in my dbt directory, ran the instructions on the installation page:

export DBT_PROJECT_DIR=${PWD}
export DBT_PROFILES_DIR=${HOME}/.dbt
export METRIQL_PORT=5656

docker run -it -p "${METRIQL_PORT}:5656" -v "${DBT_PROJECT_DIR}:/root/app" -v "${DBT_PROFILES_DIR}:/root/.dbt" -e METRIQL_RUN_HOST=0.0.0.0 -e DBT_PROJECT_DIR=/root/app buremba/metriql \
 serve

I then get the following error:

Nov 03, 2021 11:36:01 AM ru.yandex.clickhouse.ClickHouseDriver <clinit>
INFO: Driver registered
Exception in thread "main" java.lang.IllegalAccessError: class com.metriql.Commands$Serve tried to access private method 'java.lang.String com.metriql.Commands.getProfilesContent()' (com.metriql.Commands$Serve and com.metriql.Commands are in unnamed module of loader 'app')
	at com.metriql.Commands$Serve.access$getProfilesContent$s-537891160(Commands.kt:180)
	at com.metriql.Commands$Serve.run(Commands.kt:232)
	at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:198)
	at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:211)
	at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:18)
	at com.github.ajalt.clikt.core.CliktCommand.parse(CliktCommand.kt:395)
	at com.github.ajalt.clikt.core.CliktCommand.parse$default(CliktCommand.kt:392)
	at com.github.ajalt.clikt.core.CliktCommand.main(CliktCommand.kt:410)
	at com.github.ajalt.clikt.core.CliktCommand.main(CliktCommand.kt:435)
	at com.metriql.ServiceStarterKt.main(ServiceStarter.kt:25)

Any idea on how to resolve this?

We need to develop a Connector Plugin using the SDK: https://tableau.github.io/connector-plugin-sdk/docs/ https://github.com/tableau/connector-plugin-sdk/tree/master/samples/scenarios/vendor_attributes/postgres_vendor https://help.tableau.com/current/pro/desktop/en-us/examples_connector_sdk.htm

It should support basic auth and OAuth.

Hi,

I'm running with a profile that is using bigquery and a service account. But when I start the docker container it says "Cannot run program "gcloud"". Is gcloud necessary even if I use a service account?

Cheers

dbt is preparing a new feature called "metrics" for the upcoming 1.0 release:

https://next.docs.getdbt.com/docs/building-a-dbt-project/metrics https://github.com/dbt-labs/dbt-core/issues/4071

Are you planning to support this metric definition format in metriql?

I am trying to add aggregates to an existing dbt model (using the jaffle_shop dataset from dbt tutorial since it is simple and easy to set up). However, the generate command is throwing me an exception even for simple aggregation and dimension properties in my model. My model code I modified in /models to add in these meta properties is:

models:

- name: stg_customers
  description: Staged customer data from our jaffle shop app.
  meta:
    metriql:
      measures:
        total_rows:
          aggregation: count
      dimensions:
        full_name:
          sql: CONCAT({TABLE}.first_name, {TABLE}.last_name)
          type: string
  columns:
    - name: customer_id
      description: The primary key for customers.
      tests:
        - unique
        - not_null
    - name: first_name
      description: First name of customer.
    - name: last_name
      description: Last name of customer.
      meta:
        metriql.dimension:
          type: string

and the message I get when trying to generate (in debug mode) is:

Exception in thread "main" com.hubspot.jinjava.interpret.FatalTemplateErrorsException: Could not resolve function 'get_where_subquery' at com.hubspot.jinjava.Jinjava.render(Jinjava.java:191) at com.metriql.dbt.DbtJinjaRenderer.renderReference(DbtJinjaRenderer.kt:63) at com.metriql.dbt.DbtManifest$Node$TestMetadata$DbtModelColumnTest$Relationships.getSourceModelName(DbtManifest.kt:79) at com.metriql.dbt.DbtManifest$Node$TestMetadata.applyTestToModel(DbtManifest.kt:123) at com.metriql.dbt.DbtManifest$Node.toModel(DbtManifest.kt:193) at com.metriql.dbt.DbtManifestParser.parse(DbtManifestParser.kt:45) at com.metriql.Commands.parseRecipe(Commands.kt:181) at com.metriql.Commands.parseRecipe$default(Commands.kt:122) at com.metriql.Commands$Generate.run(Commands.kt:222) at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:180) at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:189) at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:17) at com.github.ajalt.clikt.core.CliktCommand.parse(CliktCommand.kt:396) at com.github.ajalt.clikt.core.CliktCommand.parse$default(CliktCommand.kt:393) at com.github.ajalt.clikt.core.CliktCommand.main(CliktCommand.kt:411) at com.github.ajalt.clikt.core.CliktCommand.main(CliktCommand.kt:436) at com.metriql.ServiceStarterKt.main(ServiceStarter.kt:25)

I am under the impression that I need to generate the dbt models using metriql first so that the models/metriql directory is created and then run the serve command based off of the documentation.

We will create LookML files via CLI.

:+1: Upvote if you plan to use this integration. :writing_hand: If you have any ideas, questions or use cases you'd like to share about this issue, feel free to use comments.

We're running Metriql within a docker container using this command: sudo docker run -it -p "5656:5656" -v "${DBT_PROJECT_DIR}:/root/app" -v "${DBT_PROFILES_DIR}:/root/.dbt" -e METRIQL_RUN_HOST=0.0.0.0 -e DBT_PROJECT_DIR=/root/app buremba/metriql serve --pass-credentials-to-datasource Exception in thread "main" java.lang.NullPointerException at com.metriql.deployment.SingleTenantDeployment.getDataSource(SingleTenantDeployment.kt:75) at com.metriql.deployment.SingleTenantDeployment$modelService$1.invoke(SingleTenantDeployment.kt:47) at com.metriql.deployment.SingleTenantDeployment$modelService$1.invoke(SingleTenantDeployment.kt:46) at com.metriql.service.model.UpdatableModelService.(UpdatableModelService.kt:8) at com.metriql.deployment.SingleTenantDeployment.(SingleTenantDeployment.kt:46) at com.metriql.Commands$Serve.run(Commands.kt:233) at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:198) at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:211) at com.github.ajalt.clikt.parsers.Parser.parse(Parser.kt:18) at com.github.ajalt.clikt.core.CliktCommand.parse(CliktCommand.kt:395) at com.github.ajalt.clikt.core.CliktCommand.parse$default(CliktCommand.kt:392) at com.github.ajalt.clikt.core.CliktCommand.main(CliktCommand.kt:410) at com.github.ajalt.clikt.core.CliktCommand.main(CliktCommand.kt:435) at com.metriql.ServiceStarterKt.main(ServiceStarter.kt:10) Note that it's getting a NullPointerException. How can we pass credentials to the datasource? Thanks!

Hi. In testing the base docker image with Superset (local docker install) it fails with a "The resource does not exist or you don't have the permission" on the top right and shows a 404 code. I am able to connect to the SS image fine outside of MetriQL. It would be great to leverage MetriQL as a Tool agnostic metrics layer.

To provide some more info. I tested a DBeaver connection (via Trino) and it can connect but when trying to view tables (which is the basic Jaffle Shop schema in Postgres) I also get this error: java.lang.IllegalArgumentException: argument "content" is null

I am unsure if my configuration is incorrect or if the SS connector is still in early stages. Thank you.

Hi teams

have some example about pivots?

I use pivot like below

      metriql:
        measures:
          count:
            aggregation: count
        dimensions:
          sex:
            column: sex
            pivot: true
          email:
            column: email
            pivot: true

but it can't work,when request rest api

curl --location --request POST '127.0.0.1:5656/api/v0/query' \
--header 'Content-Type: application/json' \
--data-raw '{
    "type": "segmentation",
    "report": {
        "dataset": "model_db_user",
        "dimensions": [
            {
                "name": "email"
            },
            {
                "name": "sex"
            }
        ],
        "measures": [
            "count"
        ],
        "limit": 10,
        "reportOptions": {}
    }
}'

Hi, we would like to use metriql with neo4j through dbt. We are not aware of any community work on dbt/neo4j/metriql, maybe there is one? We have been exploring options and have found a sql driver framework that we could probably get to work with our neo4j queries. Does anyone have any information that could help us utilize our neo4j data in metriql?

I have been following the example here https://metriql.com/tutorial/for-starters

When I run

curl -X POST -H "Content-Type: application/json" -d '{"type": "segmentation", "report": { "dataset": "source_metriql_tpch_customer", "dimensions": ["c_name"], "measures": ["total_rows"]}}'  http://localhost:5656/api/v0/query

I get the following response

{
    "id": "4665b41c-b3c9-4887-bbbe-ffc69bee8e81",
    "startedAt": "2022-08-18T13:46:08.404927Z",
    "duration": 2.968608000,
    "user": "(unknown user)",
    "source": null,
    "status": "failed",
    "update": {
        "state": "FINISHED",
        "info": {
            "reportType": "segmentation",
            "query": {
                "modelName": "source_metriql_tpch_customer",
                "dimensions": [
                    {
                        "name": "c_name",
                        "modelName": "source_metriql_tpch_customer",
                        "relationName": null,
                        "postOperation": null,
                        "pivot": null
                    }
                ],
                "measures": [
                    {
                        "modelName": "source_metriql_tpch_customer",
                        "name": "total_rows",
                        "relationName": null
                    }
                ],
                "filters": null,
                "reportOptions": null,
                "defaultDateRange": null,
                "limit": null,
                "orders": null
            },
            "compiledQuery": "SELECT \n    t1.c_name AS c_name,\n    count(1) AS total_rows\nFROM snowflake_sample_data.tpch_sf1.customer AS t1 AS t1\n\n    GROUP BY\n    1 \n"
        },
        "nodes": 1,
        "percentage": null,
        "elapsedTimeMillis": null,
        "totalBytes": null,
        "processedBytes": null
    },
    "result": {
        "metadata": null,
        "result": null,
        "error": {
            "message": "SQL compilation error:\nsyntax error line 4 at position 51 unexpected 'AS'.",
            "sqlState": "42000",
            "errorCode": 1003,
            "errorLine": null,
            "charPositionInLine": null
        },
        "properties": {
            "limit": 1000,
            "query": "SELECT \n    t1.c_name AS c_name,\n    count(1) AS total_rows\nFROM snowflake_sample_data.tpch_sf1.customer AS t1 AS t1\n\n    GROUP BY\n    1 \n"
        },
        "responseHeaders": null
    }
}

which has two AS t1, hence it show a compliation error.

My schema.yml looks like this

version: 2
sources:
  - name: tpch
    database: snowflake_sample_data
    schema: tpch_sf1
    tables:
      - name: customer
        meta:
          metriql:
            measures:
              total_rows:
                aggregation: count
        columns:
          - name: c_name
            meta:
              metriql.dimension:
                type: string

We have problems with Read timed. We use Metriql to bundle dbt, ClickHouse, Metriql, Google Data Studio technologies.

We have set our configurations after the update (https://github.com/metriql/metriql/commit/1e6c71708a11bc3cc4957c1a1afea383421bcba9) в

'connection_parameters': {
                        'connect_timeout': 40 * 1000,
                        'socket_timeout': 5 * 60 * 1000,
                    },

However, it didn't help. We use a simple query for testing:

select sleepEachRow(3) from numbers(25)

Which returns the following query from the database (used by DBeaver with increased socket_timeout settings):

However, attempting to evaluate this query using metriql returns an error:

curl --location --request POST 'https://metriql.****.io/api/v0/query' \
--header 'Authorization: Basic ***REPLASE***=' \
--header 'Content-Type: application/json' \
--data-raw '{
    "type": "sql",
    "report": {
        "query": "select sleepEachRow(3) from numbers(25)"
    }
}'

We get the following response:

{
    "id": "****",
    "startedAt": "2022-06-20T08:27:19.707825Z",
    "duration": 30.036700000,
    "user": "****",
    "source": null,
    "status": "failed",
    "update": {
        "state": "FINISHED",
        "info": {
            "reportType": "sql",
            "query": {
                "query": "select sleepEachRow(3) from numbers(25)",
                "queryOptions": null,
                "variables": null,
                "reportOptions": null
            },
            "compiledQuery": "select sleepEachRow(3) from numbers(25)"
        },
        "nodes": 1,
        "percentage": null,
        "elapsedTimeMillis": null,
        "totalBytes": null,
        "processedBytes": null
    },
    "result": {
        "metadata": null,
        "result": null,
        "error": {
            "message": "Read timed out, server ClickHouseNode(addr=***:8123, db=****)@846585128, Cause: Read timed out",
            "sqlState": "07000",
            "errorCode": 159,
            "errorLine": null,
            "charPositionInLine": null
        },
        "properties": {
            "limit": 1000,
            "query": "select sleepEachRow(3) from numbers(25)"
        },
        "responseHeaders": null
    }
}