Yandex.Cloud
  • Services
  • Why Yandex.Cloud
  • Pricing
  • Documentation
  • Contact us
Get started
Use cases
  • Web service
    • All use cases
    • Static website in Object Storage
    • Website on LAMP or LEMP stack
    • Fault-tolerant website with load balancing from Yandex Load Balancer
    • Fault-tolerant website using DNS load balancing
    • Joomla-based website with PostgreSQL
    • WordPress website
    • WordPress website on a MySQL database
    • 1C-Bitrix website
  • Online stores
    • All use cases
    • 1C-Bitrix online store
    • Opencart online store
  • Data archive
    • All use cases
    • Single-node file server
    • Configuring an SFTP server on Centos 7
    • Backup to Object Storage via Acronis Backup
    • Backup to Object Storage via CloudBerry Desktop Backup
    • Backup to Object Storage via Duplicati
    • Backup to Object Storage via Bacula
    • Digitizing archives in Yandex Vision
  • Test environment
    • All use cases
    • Testing applications with GitLab
    • Creating test VMs using GitLab CI
    • High-performance computing on preemptible VMs
  • Infrastructure management
    • All use cases
    • Getting started with Terraform
    • Uploading Terraform states to Object Storage
    • Getting started with Packer
    • VM images building automation using Jenkins
    • Continuous deployment of containerized applications using GitLab
    • Creating a cluster of 1C:Enterprise Linux servers with a Managed Service for PostgreSQL cluster
    • Creating a cluster of 1C:Enterprise Windows servers with MS SQL Server
    • Migrating to Yandex.Cloud using Hystax Acura
    • Emergency recovery in Yandex.Cloud using Hystax Acura
    • Configuring a fault-tolerant architecture in Yandex.Cloud
  • Windows in Yandex.Cloud
    • All use cases
    • Deploying Active Directory
    • Deploying Microsoft Exchange
    • Deploying Remote Desktop Services
    • Deploying an Always On availability group
    • Deploying an Always On availability group with an internal load balancer
  • Network routing
    • All use cases
    • Routing through a NAT instance
    • Creating a VPN tunnel
    • Installing a Cisco CSR1000v virtual router
    • Installing a Mikrotik CHR virtual router
    • Creating a VPN connection using OpenVPN
  • Data visualization and analytics
    • All use cases
    • Visualizing data from a CSV file
    • Visualizing data from a ClickHouse database
    • Visualizing data from Yandex.Metrica
    • Visualizing data from Yandex.Metrica Logs API
    • Publishing a chart with a map from a CSV file to DataLens Public
    • Visualizing data from AppMetrica
    • Visualizing geodata from a CSV file
  • Internet of things
    • Use cases for the internet of things
    • Status monitoring of geographically distributed devices
    • Monitoring sensor readings and event notifications
  1. Data visualization and analytics
  2. Visualizing data from a ClickHouse database

Visualizing data from a ClickHouse database

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

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 column 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.

    image

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

    image

  4. Drag the MS_SalesFacts table to the workspace.

    image

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

    image

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

    image

  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.

    image

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

    image

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

    image

Step 2. Configure the dataset fields

  1. Go to the Fields tab.

    image

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

    image

  3. Create an order date field named OrderDate.

    1. Duplicate the OrderDatetime field.

      image

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

      image

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

      image

  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.

    image

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

    image

  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.

    image

  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.

      image

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

    image

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

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

    image

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.

    image

  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.

    image

  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.

    image

  6. Save the chart.

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

      image

    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 column chart

To visualize sales by brands and product categories, create a chart: column 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.

      image

    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.

    image

  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.

    image

  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.

    image

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

    image

  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.

    image

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

    image

  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.

      image

  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.

    image

  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.

    image

  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.

    image

  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 Points (heat map) section.

    image

  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.

    image

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

Step 8. Add the 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.

    image

  2. Click Add and select Chart.

    image

  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.

    image

  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.

    image

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.

    image

  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.

    image

  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.

    image

  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.

    image

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

    image

In this article:
  • Before you start
  • Step 1. Define the data source for the dataset
  • Step 2. Configure the dataset fields
  • Step 3. Create a line chart
  • Step 4. Create a column chart
  • Step 5. Create a pivot table chart
  • Step 6. Create a heat map
  • Step 7. Create a dashboard
  • Step 8. Add the charts to the dashboard
  • Step 9. Add selectors to the dashboard
Language
Careers
Privacy policy
Terms of use
© 2021 Yandex.Cloud LLC