Creating a QL chart
You can create a QL chart using one of the following methods:
Warning
Access rights to QL charts by Monitoring and Prometheus data are managed both on the DataLens side and in Monitoring and Prometheus.
To view a chart in DataLens, the user needs chart access and connection permissions, as well as permissions to read data from a specific Prometheus API or project in Monitoring.
QL charts have the same general settings and section settings available as charts based on a dataset. Only certain measure settings are supported for chart fields.
Creating a QL chart from the main page
Warning
If you use a new DataLens object model with workbooks and collections:
- Go to the DataLens home page
. In the left-hand panel, select Collections and workbooks. - Open the workbook, click Create in the top-right corner, and select the appropriate object.
Follow the guide from step 3.
-
Go to the DataLens home page
. -
In the left-hand operating panel, select
Charts. -
Click Create chart → QL chart.
-
In the window that opens, select a Chart type:
SQLPrometheusMonitoring- Click Select connection.
- In the list, select a database connection that you have access to. Make sure Raw SQL level → Allow subqueries in datasets and queries from charts is enabled.
- Click Create.
- In the Query tab, enter your query using the SQL dialect of the database you are querying.
- In the bottom-left corner, click Start.
After the query runs, a visualization of your data will be displayed.
Example database query ClickHouse®
SELECT Category, Month, ROUND(SUM(Sales)) FROM samples.SampleLite WHERE Category in {{category}}: A variable used in the selector GROUP BY Category, Month: Grouping by category and month ORDER BY Category, Month: Sorting by category and month
- Click Select connection.
- In the list, select a Prometheus connection that you have access to.
- Click Create.
- Click Add query and specify a query in the Prometheus language.
- In the bottom-left corner, click Start.
After the query runs, a visualization of your data will be displayed.
Example 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 mandatory global parameters created automatically when building a QL chart. You can override 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 (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
andto
.- Click Select connection.
- In the list, select a Monitoring connection that you have access to.
- Click Create.
- Click Add query and specify a query in the Monitoring language.
- In the bottom-left corner, click Start.
After the query runs, a visualization of your data will be displayed.
Example 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 mandatory global parameters created automatically when building a QL chart. You can override 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
andto
.
Creating a QL chart using a connection
- Go to an existing database connection.
- Make sure Raw SQL level → Allow subqueries in datasets and queries from charts is enabled.
- In the top-right corner, click Create QL chart.
- In the Query tab, enter your query using the SQL dialect of the database you are querying.
- In the bottom-left corner, click Start.
After the query runs, a visualization of your data will be displayed.
Adding 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:
-
Go to the Parameters tab when creating a chart.
-
Click Add parameter.
-
Set the value type for the parameter, e.g.,
date-interval
. -
Name the parameter, e.g.,
interval
. -
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
, anddatetime-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.
Intervals
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
).
Request example
SELECT toDate(Date) as datedate, count ('Order 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 query
Parameter values from a selector arrive to a QL chart as a:
- 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.
Example of a query with in
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.
Example of a query with =
SELECT sum (Sales) as Sales, Category
FROM samples.SampleLite
WHERE Category = {{category}}
GROUP BY Category
ORDER BY Category
Null choice in 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
ClickHouse® is a registered trademark of ClickHouse, Inc