SQL metrics

SQL metrics measure whatever you model with valid SQL, but must include a SELECT statement that meets basic metric query requirements (details below). To create one, begin at Create a metric, and choose SQL at Step 1 (Metric Info).

Step 2 (Configure Metric)

After you finish Step 1 (Metric Info), Step 2 (Configure Metric) appears in the main form:

1500

❗️

Full Table scope SQL

If you set your SQL metric's Query Scope to Full Table, the query requirements change.

  • The SELECT clause doesn't need a time-based column.
  • The WHERE clause doesn't need start and end tags.
  • The delay and time zone settings change to reflect the absence of a timestamp.
  • After you validate your SQL, you will only have one column to specify: the value column (the drop-down is labeled "Column").
  1. Under Place metric in Explorer confirm or specify the data asset you want to use for listing this metric in the Explorer tree.

  2. Enter your SQL in the Custom SQL box. To produce a metric, your SQL clauses must have certain elements:

    • The SELECT clause must have at least two output columns: (1) a time period equal to the desired aggregation interval, extracted from a time-based column; and (2) an aggregated value column. These become (1) the y-axis and (2) the x-axis of your metric's chart. Any additional output columns must not be aggregated; these can be used for slices.
    • The WHERE clause must specify a date range using the time-based column and two tags: one producing the start of the range and another for the end of the range. These tags reflect the kind of time-based column you have: (1) use {start_ts} and {end_ts} for a timestamp column, (2) use {start_date} and {end_date} for a date column, and (3) use {start_datetime} and {end_datetime} for a datetime column.
    • Don't forget to GROUP BY the aggregation interval column.
    • See below for SQL examples for each supported datasource.
  3. When your SQL is ready, select Validate Query. If the query validates, you'll be able to select required setting values. Note that Seasonality and Slices are optional but recommended to improve query performance and precision.

❗️

No hourly aggregation with Date-type timestamps

You cannot aggregate data on an hourly basis if your metric uses a date column as the timestamp. After your SQL validates, make sure that you don't set Aggregation Interval to Hourly if your SQL uses a date column as the timestamp.

  1. If desired, add a failing records query.
  2. Add any tags, then in the top right corner select Next to proceed to Step 3 (Preview).

SQL examples

SELECT

timestamp(date) as date,

count(*) AS count

FROM lightup_demo.dqfreshness

WHERE timestamp >= {start_ts}

AND timestamp < {end_ts}

GROUP BY date
SELECT

timestamp(date) as date,

count(*) AS count

FROM lightup_demo.dqfreshness

WHERE timestamp >= {start_ts}

AND timestamp < {end_ts}

GROUP BY date;
SELECT

DATE_TRUNC('day', "timestamp" AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' AS "day",

COUNT(*) AS "value"

FROM "lightup_demo"."dqfreshness"

WHERE "timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY "day"
SELECT
    date_trunc('day', "DATE") AS "aggregated_time",
    CAST(COUNT(*) AS INT) AS "value"
FROM
    "test"."WHO_SITUATION_REPORTS"
WHERE
    "DATE" >= {start_ts}
    AND "DATE" < {end_ts}
GROUP BY
            "aggregated_time"
;
SELECT

DATETRUNC(hh, "timestamp") AS "hour",

COUNT(*) as "value"

FROM

"lightup_demo"."dqfreshness"

WHERE "timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY

"hour"
SELECT

TRUNC("timestamp", 'DD') AS "day",

COUNT(*) AS "count"

FROM "lightup_demo"."dqfreshness"

WHERE "timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY "day"
SELECT

date_trunc(hour, "timestamp") AS "hour",

COUNT(*) as "value"

FROM

"lightup_demo"."dqfreshness"

WHERE

"timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY

"hour"
SELECT

DATE_TRUNC(hh, timestamp) AS "hour",

COUNT(*) AS "count"

FROM lightup_demo.dqfreshness

WHERE DATE_TRUNC(hh, timestamp) >= {start_ts}

AND DATE_TRUNC(hh, timestamp) < {end_ts}

GROUP BY "hour"
SELECT

TRUNC("timestamp", hh) AS "hour",

COUNT(*)

FROM "lightup_demo"."dqfreshness"

WHERE TRUNC("timestamp", hh) >= {start_ts}

AND TRUNC("timestamp", hh) < {end_ts}

GROUP BY "hour"
SELECT
    CAST(COUNT(DISTINCT "city") AS INT) AS "unique_count",
    CAST(COUNT(*) AS INT) AS "total_count"
FROM
    "testdata"."customer_orders_fact"
WHERE
    "timestamp" >= {start_ts}
    AND "timestamp" < {end_ts}

📘

Nested query support

You can nest SELECT statements in your SQL. If you do, the clauses described above must be in the innermost SELECT statement.

Step 3 (Preview)

1135

📘

Editing a metric

When you edit an existing metric:

  • You'll see an option to view the metric's recent query history as well as a preview.
  • The Create command changes to Save when you edit an existing metric
  1. Optionally, adjust the date range for the preview. If you adjust the range, you must pick a start date and an end date.
  2. Select Preview to see a chart of the metric for the date range from the preceding step.

📘

Cancel a preview

Some datasources let you cancel a preview, for example because you noticed that date range was wrong. If cancelling is supported, you'll see a Cancel button.

  1. After you're satisfied with the preview, select Create at the top right corner. The Explorer tab opens and displays your new metric in the Explorer tree.