Visualizing data from a ClickHouse database

As a data source, we'll use a demo ClickHouse database on sales in a Moscow chain of stores.
The connection to this database is automatically created when a DataLens instance is created.

In this example, DataLens is used to visualize:

• Sales dynamics by day and month.
• Sales by product categories.
• Order heat map.

A demo Sample ClickHouse connection is used.

To visualize and analyze the data, make sure you have a ready-to-use cloud and follow these steps:

1. Define the data source for the dataset.
2. Configure the dataset fields.
3. Create a line chart.
4. Create a bar chart.
5. Create a pivot table chart.
6. Create a heat map chart.
7. Create a dashboard.
8. Add the charts to the dashboard.
9. Add selectors to the dashboard.

Create a connection to the ClickHouse database with the table.

Before you start

To use DataLens, sign up for Yandex.Cloud and create a billing account (without a billing account, DataLens is only available in demo mode):

1. Go to the management console. Then log in to Yandex.Cloud or sign up if don't already have an account.
2. On the billing page, make sure you linked a billing account, and it has the ACTIVE or TRIAL_ACTIVE status. If you don't have a billing account, create one.

When you activate your billing account, you automatically switch to the Free DataLens plan. By default, you can create a DataLens instance for no more than 1 folder. If you haven't done this yet:

1. Select a folder on the cloud page.
2. Select the DataLens service.
3. Click Activate DataLens.

An instance of the service is created in the selected folder and you can go directly to the DataLens interface.

Learn more about clouds and folders.

Step 1. Define the data source for the dataset

Create a dataset using the connection Sample ClickHouse created from a ClickHouse database.

1. Go to DataLens.

2. Click Create dataset.

   

3. Click Add under Connections on the selection panel. Select the Sample ClickHouse connection.

   

4. Drag the MS_SalesFacts table to the workspace.

   

5. Drag the MS_Clients table to the workspace. The tables are automatically linked.

   

6. To check the link, click the link icon between the tables.

   

7. Tables are linked by the ClientID field. If needed, you can modify or expand the link by specifying another pair of fields. To close the link settings window, click the cross button or anywhere outside the window.

   

8. Drag the MS_Products table to the workspace. The table is automatically linked to the left (root) table MS_SalesFacts.

   

9. Drag the MS_Shops table to the workspace. The table is automatically linked to the left (root) table MS_SalesFacts.

   

Step 2. Set up the dataset fields

1. Go to the Fields tab.

   

2. Delete the duplicate fields left over from joining the tables: ClientID (1), ProductID (1), and ShopID (1).

   

3. Create an order date field named OrderDate.

   1. Duplicate the OrderDatetime field.

      

   2. Rename the OrderDatetime (1) duplicate field to OrderDate: click the row name, delete the current name, and enter the new one.

      

   3. Change the data type from Date and time to Date.

      

4. For the ShopDistrictCoordinates field, change the data type to Geopolygon.

5. For the DeliveryDistrictCoordinates field, change the data type to Geopoint.

6. In the Aggregation column, select Sum for the Sales field.

   

   The aggregation field changes color to blue: Sales is now a measure.

   

7. Create a measure for the number of orders.

   1. Duplicate the OrderID field.

   2. Rename the OrderID (1) duplicate field to OrderCount.

   3. Change the aggregation type to Number of unique.

   

8. Create a calculated field for the average sales amount per order.

   1. In the upper-right corner, click Add field.

   2. In the Name field, specify Sales per Order.

   3. In the column to the left, click on the Sales field.

   4. Enter a /.

   5. In the column to the left, click the OrderCount field.

   6. Click Create.

      

9. Click Save in the upper-right corner to save the dataset.

   

10. Enter a name for the dataset: Moscow Sales dataset, then click Create.

11. When the dataset is saved, click Create chart.

    

Step 3. Create a line chart

To visualize sales dynamics by month, create a chart: line chart.

1. For the visualization type, choose Line chart.

   

2. Add the sales date to the chart. Drag the OrderDate field from the Dimensions section to the X section.

3. Add a sales measure to the chart. To do this, drag the Sales field from the Measures section to the Y section.

4. Add the delivery type to the chart. To do this, drag the PaymentType field from the Dimensions section to the Colors section.

   

5. Display the chart by month.

   1. Click the calendar icon next to the OrderDate field in the X section.
   2. In the drop-down list of grouping options, select Month under Rounding.
   3. Click Apply.

   

6. Save the chart.

   1. Click Save in the upper-right corner to save the chart.

      

   2. In the window that opens, enter a name for the chart: Sales dynamics by month and payment type, and click Save.

Step 4. Create a bar chart

To visualize sales by brands and product categories, create a chart: bar chart.

1. Copy the chart from the previous step.

   1. Click the down arrow next to the Save button in the upper-right corner.

   2. Choose Save as.

      

   3. In the window that opens, enter the name Sales by brand and category for the new chart and click Save.

2. For the visualization type, choose Column chart.

   

3. The OrderDate, Sales, and PaymentType fields are copied automatically to the X, Y, and Colors sections, respectively.

4. Replace the months with brands on the X-axis. From the Dimensions section, drag the ProductBrand field to the X section and hold it over the OrderDate field until it turns red.

   

5. Replace payment types for product categories in Colors. Drag ProductCategory from the Dimensions section to the X section and hold it over the PaymentType field until it turns red.

   

6. Sort the chart in descending order of sales measure. Drag the Sales field from the Measures section to the Sorting section.

   

7. Save the chart.

Step 5. Create a pivot table chart

To visualize sales in terms of products and time, create a chart: pivot table.

1. Copy the chart from the previous step.

   1. Click the down arrow next to the Save button in the upper-right corner.
   2. Click Save as.
   3. In the window that opens, enter the name Sales by year and product for the new chart.
   4. Click Ready.

2. Choose the Pivot table visualization type.

   

3. The ProductBrand and Sales fields are automatically copied to the Columns and Measures sections, respectively.

   

4. Remove ProductBrand from the table.

5. Add the order date to the table. To do this, drag the OrderDate field from the Dimensions section to the Columns section.

6. Change the display format in the OrderDate field to years.

   1. Click the calendar icon next to the OrderDate field in the Columns section.

   2. In the drop-down list of grouping options, select Year under Date part.

   3. Click Apply.

      

7. Add the product category and subcategory to the table. To do this, drag the ProductCategory and ProductSubcategory fields from the Dimensions section to the Rows section.

   

8. Change the color of the sales measure in the table. To do this, drag the Sales field from the Measures section to the Colors section.

   

9. Save the chart.

Step 6. Create a heat map

To visualize the density of orders on the Moscow map, create a chart: heat map.

1. Copy the chart from the previous step.

   1. Click the icon next to the Save button in the upper-right corner and select Save as.
   2. In the window that opens, enter the name Sales heat map for the new chart.
   3. Click Ready.

2. Select the Map visualization type.

   

3. Delete the Sales field from the Colors section.

4. Select the Points (heat map) layer type.

5. Add the delivery point coordinates to the map. To do this, drag the DeliveryAddressCoord field from the Dimensions section to the Geopoints section.

   

6. Save the chart.

Step 7. Create a dashboard

Create a Dashboard to add your charts to.

1. Go to the DataLens homepage.

2. Click Create dashboard.

   

3. Enter the name Moscow Shops dashboard for the dashboard and click Create.

Step 8. Add charts to the dashboard

1. The first time you open the dashboard after saving, it opens in edit mode. If you open it later, click Edit in the upper-right corner.

   

2. Click Add and select Chart.

   

3. In the window that opens, click Select.

4. Select the chart Sales dynamics by month and payment type. This automatically fills in the Title field with the name of the selected chart.

5. Click Add.

   

6. Similarly, add the following charts:

   • Sales by brand and category
   • Sales by year and product
   • Sales heat map

7. Position the charts on the dashboard however you like.

   

Step 9. Add selectors to the dashboard

Add selectors to filter the charts by date, Moscow districts, products, and customer statuses.

1. Click Add.

2. Choose Selector.

   

3. Add the calendar selector for the order date.

   1. Select the Moscow Sales dataset.
   2. Select the OrderDate field.
   3. This automatically fills in Title with the name of the selected field. Click the Show checkbox next to the selector title.
   4. Select the Calendar type.
   5. Enable Range.
   6. Click Add.

   

4. Add a product category selector.

   1. Select the Moscow Sales dataset.
   2. Select the ProductCategory field.
   3. This automatically fills in Title with the name of the selected field. Click the Show checkbox next to the selector title.
   4. Enable the Multiple choice option.
   5. Click Add.

   

5. Similarly, add selectors for the following field:

   • ProductBrand
   • DeliveryDistrictName
   • DeliveryType
   • PaymentType

6. Position the selectors on the dashboard however you like.

7. Save the dashboard.

   

8. Your dashboard is ready. Now you can filter charts using selectors.