Creating a QL chart in Yandex DataLens

You can create a QL chart using one of the following methods:

• From the home page.
• From a connection.

QL charts have the same general settings and section settings as the dataset-based charts. Only certain measure settings are supported for chart fields.

At each step, you can undo/redo any change introduced within the current version.

Creating a QL chart from the home pageCreating a QL chart from the home page

1. Go to the DataLens home page.

2. In the left-hand panel, select Charts.

3. Click Create chart → QL chart.

4. In the window that opens, select the Chart type:

   SQL

   Prometheus

   Monitoring

   1. Click Select connection.
   2. In the list, select a database connection you have access to. Make sure Raw SQL level → Allow subqueries in datasets and queries from charts is enabled.
   3. Click Create.
   4. In the Query tab, enter your query using the SQL dialect of the database you are querying.
   5. In the bottom-left corner, click Start.

   After the query runs, your data will be visualized.

   Example of a ClickHouse® database query

   SELECT Category, Month, ROUND(SUM(Sales))
   FROM samples.SampleLite
   WHERE Category in {{category}} -- Variable used in the selector
   GROUP BY Category, Month -- Grouping by category and month
   ORDER BY Category, Month -- Sorting by category and month
   

   1. Click Select connection.
   2. In the list, select a Prometheus connection you have access to.
   3. Click Create.
   4. Click Add query and specify a query in the Prometheus language.
   5. In the bottom-left corner, click Start.

   After the query runs, your data will be visualized.

   Example of a query for Prometheus

   sum by (name) (rate(container_cpu_user_seconds_total{container_label_org_label_schema_group="monitoring"}[1m]) * 100  / scalar(count(node_cpu_seconds_total{mode="user"})))
   

   QL charts built on the Prometheus data can be parameterized similarly to SQL charts. This type of charts also has required global parameters created automatically when building a QL chart. You can edit default values of these parameters.

   Parameter	Data type	Description
   from	datetime	Lower time limit
   to	datetime	Upper time limit
   step	number	Step on the time scale in seconds

   Note

   On the dashboard, you can use a single selector for the date with the special interval name instead of the two selectors, from and to.

   1. Click Select connection.
   2. In the list, select a Monitoring connection you have access to.
   3. Click Create.
   4. Click Add query and specify a query in the Monitoring language.
   5. In the bottom-left corner, click Start.

   After the query runs, your data will be visualized.

   Example of a query for Monitoring

   {project="monitoring", sensor="nodejs.heap_space.size.bytes"}
   

   QL charts built on the Monitoring data can be parameterized similarly to SQL charts. This type of charts also has required global parameters created automatically when building a QL chart. You can edit default values of these parameters.

   Parameter	Data type	Description
   from	datetime	Lower time limit
   to	datetime	Upper time limit

   Note

   On the dashboard, you can use a single selector for the date with the special interval name instead of the two selectors, from and to.

Creating a QL chart from a connectionCreating a QL chart from a connection

1. Go to an existing database connection.
2. Make sure Raw SQL level → Allow subqueries in datasets and queries from charts is enabled.
3. In the top-right corner, click Create QL chart.
4. In the Query tab, enter your query using the SQL dialect of the database you are querying.
5. In the bottom-left corner, click Start.

After the query runs, your data will be visualized.

Adding selector parametersAdding selector parameters

In QL charts, you can control selector parameters from the Parameters tab in the chart editing area and use the Query tab to specify a variable in the query itself in {{variable}} format.

To add a parameter:

1. Go to the Parameters tab when creating a chart.

2. Click Add parameter.

3. Set the value type for the parameter, e.g., date-interval.

4. Name the parameter, e.g., interval.

5. Set the default values, e.g., 2017-01-01 — 2019-12-31.

   

   There are several ways to configure the parameters of the date, datetime, date-interval, and datetime-interval types:

   • Exact date to specify an exact value.
   • Offset from the current date to specify a relative value that will be updated automatically.

   Use presets to quickly fill in the values.

To manage parameter values on the dashboard, create a selector with manual input and specify a parameter name in the Field or parameter name field.

IntervalsIntervals

The date-interval and the datetime-interval type parameters can be used in query code only with the _from and _to postfixes. For example, for the interval parameter set to 2017-01-01 — 2019-12-31, specify:

• interval_from to get the start of the interval (2017-01-01).
• interval_to to get the end of the interval (2019-12-31).

SELECT toDate(Date) as datedate, count ('Oreder ID')
FROM samples.SampleLite
WHERE {{interval_from}} < datedate AND datedate < {{interval_to}}
GROUP BY datedate
ORDER BY datedate

Substituting parameter values in a QL chart querySubstituting parameter values in a QL chart query

A QL chart gets parameter values from a selector as:

• Single value if one element is selected.
• Tuple if multiple values are selected.

If a query for ClickHouse® or PostgreSQL connections has the in operator before a parameter, the substituted value is always converted into a tuple. In the case of other connections, there is no automatic conversion. A query with the in operator will run correctly if you select one or more values.

SELECT sum (Sales) as Sales, Category
FROM samples.SampleLite
WHERE Category in {{category}} 
GROUP BY Category
ORDER BY Category

If the query has = before a parameter, the query will only run correctly if a single value is selected.

SELECT sum (Sales) as Sales, Category
FROM samples.SampleLite
WHERE Category = {{category}} 
GROUP BY Category
ORDER BY Category

Null choice in a selector and parametersNull choice in a selector and parameters

If a selector has no value selected and no default value is set for a parameter, a null value is provided to a query. In this case, all values will be selected in dataset-based charts, and the filter for the relevant column will disappear when generating a query.

To enable a similar behavior in QL charts, you can use a statement like this in your query:

AND
CASE
    WHEN LENGTH({{param}}::VARCHAR)=0 THEN TRUE
    ELSE column IN {{param}}
END

Undoing and redoing changes in chartsUndoing and redoing changes in charts

When editing a QL chart, you can now undo/redo any change introduced within the current version.

See alsoSee also

• Yandex DataLens chart
• Adding a chart to a dashboard in Yandex DataLens

ClickHouse® is a registered trademark of ClickHouse, Inc.