SQL metrics

Step 2 (Configure Metric)

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

24242424
  1. Unlike most metrics, a custom SQL metric isn't automatically associated with a data asset. To give your metric a home in the Explorer tree, under Place metric in Explorer select a data asset.

  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.

  4. Add any tags, then in the top right corner select Next to proceed to Step 3 (Preview).

SQL examples for each supported datasource

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

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"

📘

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)

11351135

📘

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.

Did this page help you?