Examples of creating QL charts

In this scenario, you will create charts using SQL queries. SQL queries enable you to configure data for visualization in a more flexible way than the standard dataset method does. For example, you can add parameters from dashboards to a SQL query.

We recommend using a QL chart if creating a regular chart from a dataset is not suitable for your situation or if you wish to experiment with the data.

A direct connection to a demo database will be used as your data source.

To visualize and explore data, set up DataLens and follow the steps below:

1. Create a connection.
2. Create a QL chart.
3. Create a dashboard.
4. Add the QL chart to the dashboard.
5. Add selectors to the dashboard.

Getting startedGetting started

To get started with DataLens:

If you have any technical questions about the service, contact Yandex Cloud support. To ask for advice, discuss the solution to your problem or best practices of the service, write to the DataLens chat in Telegram.

Create a connectionCreate a connection

1. Go to the connections page.

2. Click Create connection.

3. Select the ClickHouse connection.

   1. In the window that opens, specify the connection parameters:

      • Connection type: Specify manually.
      • Host name: rc1a-ckg8nrosr2lim5iz.mdb.yandexcloud.net.
      • HTTP interface port: 8443 (default).
      • Username: samples_ro.
      • Password: MsgfcjEhJk.

   2. Enable Allow subqueries in datasets and queries from charts.

   3. Check the connection and click Create connection.

   4. Enter the connection name: Sample ClickHouse.

   5. Click Create.

   

Wait for the connection to be saved.

Create a QL chartCreate a QL chart

1. Go to an existing database connection.

2. Make sure SQL query access level → Allow subqueries in datasets and queries from charts is enabled.

3. In the top-right corner, click Create QL chart.

4. On the Query tab, enter the query code:

   SELECT
      toDate(t1.OrderDatetime) as "Date",
      COUNT(t1.ClientID) as "Number of new customers", t3.ClientStatus as "Status"
   FROM
      samples.MS_SalesFacts t1,
      (SELECT
   	      ClientID,
         MIN(OrderDatetime) as FirstDate
      FROM samples.MS_SalesFacts
      GROUP BY ClientID) as t2,
         samples.MS_Clients t3
   WHERE {{interval_from}} < "Date" and "Date" < {{interval_to}} and t1.ClientID=t2.ClientID and t3.ClientID=t2.ClientID and t3.ClientStatus in {{status}} -- status, interval_from and interval_to - variables associated with parameters affected by selectors
   GROUP BY "Status", "Date"
   ORDER BY "Date"
   

5. On the Parameters tab, click Add parameter and complete the input fields:

   • From the drop-down list, select string (default).

   • In the Name field, enter status.

   • In the Default value field, enter Gold.

   • Click Add value below and enter Silver.

   • Click Add value below and enter Basic.

     

   The added parameter will be associated with the {{status}} variable in the query.

6. Click Add parameter and complete the input fields:

   • In the drop-down list, select date-interval.

   • In the Name field, enter interval.

   • Click the field with the interval value and enter:

     • From: 2017-03-01.

     • To: 2017-03-31.

       

     The From: and To: values of the added parameter will be bound to the {{interval_from}} and the {{interval_to}} variables in the SQL query.

     Note

     You can control the parameter values using dashboard selectors.

7. Go back to the Query tab. In the bottom-left corner, click Start. Running the query will produce a visualization in the right-hand window.

8. Configure the visualization:

   • Select Bar chart as chart type.

     

   • Make sure that the Date field is in the X section.

   • Make sure that the Number of new clients field is in the Y section.

   • Make sure that the Status field is in the Colors section.

   • Make sure the Available section is empty. This is the section that contains fields not involved in calculations. Otherwise, the visualization will display incorrectly.

     

9. In the top-right corner, click Save.

10. Enter New clients as your chart name and click Save.

You can place the chart created on the dashboard. You can also equip the dashboard with selectors to control the values of the QL chart status and interval parameters.

Create a dashboardCreate a dashboard

Create a dashboard to add these charts to.

1. Go to the DataLens home page.

2. Click Create dashboard.

   

Add the QL chart to the dashboardAdd the QL chart to the dashboard

1. At the top of the page, click Add and select Chart.

   

2. In the Chart field, click Select and select the previously created New clients chart from the chart list.

   

   Click Add.

3. The chart is displayed on the dashboard. Stretch it to improve visualization.

   

Add selectors to the dashboardAdd selectors to the dashboard

1. Add a selector to be able to filter the chart by client status:

   1. At the top of the page, click Add.

   2. Choose Selector.

      

   3. Select Manual input as the source type.

   4. In the Field name field, enter status. Certain selector values will be passed into this SQL query variable.

   5. Choose List as your selector type.

   6. Enable the Multiple choice option.

   7. Click the input field next to the Default value parameter. In the resulting window, add the following values:

      • Gold
      • Silver
      • Basic

      

      Click Apply.

   8. In the Default value list, specify Select all.

      

   9. Enable the Name option and enter Select client status.

   10. Verify the specified selector parameters.

       

   11. Click Add.

   12. Place the selector on the dashboard under the chart.

2. Add another selector to be able to filter the chart based on a date range:

   1. At the top of the page, click Add.

   2. Choose Selector.

   3. Select Manual input as the source type.

   4. Under Field name, enter interval. The interval start and end values from the selector will be passed into the SQL query {{interval_from}} and the {{interval_to}} variables.

   5. Choose Calendar as your selector type.

   6. Enable Range.

   7. Enable the Name option and enter Order interval.

   8. Verify the specified selector parameters.

      

   9. Click Add.

   10. Place the selector on the dashboard on top of the chart next to the first selector.

   11. In the top-right corner of the dashboard, click Save.

   12. Enter Change in number of clients by year as the dashboard name and click Create.

       

3. Your dashboard is ready. Now you can filter the chart by status using a selector.

   

   You can also filter the chart by a date range using the second selector.

   

See alsoSee also

• Adding a chart to a dashboard
• Adding a selector to a dashboard
• Creating a QL chart
• QL charts