Source parameterization in DataLens

In this tutorial, you will create a connection to a data source and learn how to use dataset source parameterization in DataLens.

Parameterization will enable you to:

• Substitute a table in queries.
• Provide a parameter to the dataset's SQL as part of a query.

We will use a Moscow retail chain's demo sales ClickHouse® database as our data source.

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

1. Create a workbook.
2. Create a connection.
3. Create a dataset with a table substitution parameter.
4. Create a chart with a table selection parameter.
5. Create a dataset with a subquery parameter.
6. Create a chart with a parameter for selecting a subquery condition.

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 workbookCreate a workbook

1. Go to the DataLens home page.
2. In the left-hand panel, select Collections and workbooks.
3. In the top-right corner, click Create → Create workbook.
4. Enter a name for the workbook: Dataset parametrisation.
5. Click Create.

Create a connectionCreate a connection

A connection named Sample ClickHouse will be created for database access.

1. In the top-right corner of the workbook, click Create → Connection.

2. Under Databases, select the ClickHouse® connection.

   1. In the window that opens, select Specify manually for the connection type and provide the following connection parameters:

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

   2. Enable the SQL query access level option and select Allow subqueries in datasets and source parameterization.

   3. Click Check connection and make sure you get a green check mark.

   4. Click Create connection.

   5. Enter the connection name: Sample ClickHouse.

   6. Click Create.

      

Wait for the connection to be saved.

Create a dataset with a table substitution parameterCreate a dataset with a table substitution parameter

Create a dataset based on the Sample ClickHouse connection:

1. In the top-right corner of the connection page, click Create dataset.

2. Drag the samples.MS_SalesFacts table to the workspace.

3. Enable parameterization in the dataset settings. To do this, click at the top and select Enable parameterization.

   

4. Save the dataset:

   1. In the top-right corner, click Save.
   2. Enter Dataset with parametrisation for the dataset name and click Create.

5. Add a parameter with the table name:

   1. Go to the Parameters tab.

   2. Click Add and configure as follows:

      • Name: table_name.
      • Type: String.
      • Default value: MS_SalesFacts.
      • Enable Allow use in source settings and keep the default validation.

      

      When you click Add, the system will show an error saying that the dataset validation failed. By default, the value of a parameter allowed for use in the source cannot contain _.

      

6. Adjust the dataset validation settings to allow the MS_SalesFacts value in the parameter. Proceed as follows:

   1. Click Add and specify Name, Type, and Default value as in the previous step.

   2. Enable Allow use in source settings and select Regular expression for the value validation.

   3. In the field, enter this Python regular expression to enable using uppercase and lowercase letters, digits, and underscores:

      ^[a-zA-Z0-9_]+$
      

   

   The MS_SalesFacts table name matches this regular expression. When you click Add, the dataset validation will succeed.

7. Save the dataset.

8. Change the settings to define the table name with a parameter:

   1. Go to the Sources tab.

   2. In the table list next to MS_SalesFacts go → Change settings.

   3. To the right of the Table name field set to MS_SalesFacts, click and select the table_name parameter. The selected parameter will now define the table name.

      

   4. Click Apply and save the dataset.

Create a chart with a table selection parameterCreate a chart with a table selection parameter

1. At the top of your dataset, click Create chart.

2. Select the Table visualization type.

3. Create a calculated field:

   1. On the left side of the screen above the list of dataset fields, click and select Field.
   2. Enter the name: Year.
   3. Enter the formula: YEAR([OrderDatetime]).
   4. Click Create.

4. Add the new field to the chart. To do this, drag the Year field from Dimensions to the Columns section.

5. Add total sales to the chart. To do this, drag the Sales field from Dimensions to the Columns section and change its aggregation settings:

   1. In the Columns section, click # next to the Sales field.
   2. In the Aggregation field, select Sum and click Apply.

   

6. Open the chart inspector and make sure the data comes from the MS_SalesFacts table. To do this, click in the top-right corner of the chart and select Inspector.

   

7. Save the chart:

   1. In the top-right corner, click Save.
   2. In the window that opens, enter Table with parametrisation for chart name and click Save.

   

8. Change the table name in the relevant chart parameter:

   1. Under Parameters, click the icon next to table_name.

   2. Set the default value to MS_SalesFacts_up.

      

   3. Click Save. Now, the chart uses data from the MS_SalesFacts_up table.

      

Create a dataset with a subquery parameterCreate a dataset with a subquery parameter

Create another dataset based on the Sample ClickHouse connection:

1. Navigate to the Sample ClickHouse connection and click Create dataset in the top-right corner.

2. Add an SQL query to the source:

   1. At the bottom left under the table list, click → Add.

   2. Open the SQL tab and enter the following query text in the Subquery field:

      SELECT * FROM samples.MS_SalesFacts
      

   3. Click Apply.

3. Enable parameterization in the dataset settings. To do this, click at the top and select Enable parameterization.

4. Save the dataset:

   1. In the top-right corner, click Save.
   2. Enter Dataset with parametrisation for sql for the dataset name and click Create.

5. Add a parameter with the subquery condition:

   1. Go to the Parameters tab.

   2. Click Add and configure as follows:

      • Name: custom_where.

      • Type: String.

      • Default value: 1=1. This condition will always return True.

      • Enable Allow use in source settings and select Regular expression for the value validation. In the field, enter this Python regular expression to allow characters you may need to use when writing SQL queries:

        ^[a-zA-Z0-9а-яАА-ЯёЁ_\s\(\)\.\'\=\-\+\*/\,\<\>!]+$
        

      When you click Add, the dataset validation will succeed.

6. Save the dataset.

7. Add a condition to the SQL query text using the parameter:

   1. Go to the Sources tab.

   2. In the table list, click next to SQL and select Change settings.

   3. In the Subquery field, add a WHERE clause to your query:

      SELECT * FROM samples.MS_SalesFacts
      WHERE
      

   4. Under the query input field, click Insert parameter and select custom_where. The selected parameter will now define the SQL query condition.

      SELECT * FROM samples.MS_SalesFacts
      WHERE {{custom_where}}
      

      

8. Save the dataset.

Create a chart with a parameter for selecting a subquery conditionCreate a chart with a parameter for selecting a subquery condition

1. At the top of your dataset, click Create chart.

2. Select the Indicator visualization type.

3. Drag the Sales field from Dimensions to the Measure section. This will automatically apply aggregation to this field, making it a measure.

   

4. Open the chart inspector and make sure the SQL query uses the 1=1 value of the dataset parameter.

   

5. Change the SQL query condition in the parameter:

   1. Under Parameters, click the icon next to table_name.

   2. Set the default value to ProductID IN (SELECT ProductID FROM samples.MS_Products WHERE ProductCategory='Home appliances').

   3. Click Save. This will filter the chart data to only show products in the Home appliances category.

      

   4. Open the chart inspector and make sure the SQL query now uses the new parameter value.

      

6. Save the chart:

   1. In the top-right corner, click Save.
   2. In the window that opens, enter Chart with parametrisation sql for chart name and click Save.

ClickHouse® is a registered trademark of ClickHouse, Inc.