Visualizing data with DataLens

This tutorial explains how to regularly export data from external Tracker to ClickHouse and build a chart showing issue counts by status. Data is updated on a schedule, and the Tracker account permissions determine which issues are exported.

To visualize the data, follow these steps:

  1. Prepare your cloud.
  2. Create a DB for storing the Tracker data.
  3. Create an OAuth token for Tracker access.
  4. Prepare the importer archive.
  5. Create a Cloud Functions function for importing the data.
  6. Create a connection to DataLens.
  7. Create a dataset.
  8. Create a chart.
  9. Create a dashboard in DataLens and add charts to it.

Getting started

To obtain data, log in to Tracker under the account of a user with full access to the service.

Sign up for Yandex Cloud and create a billing account:

  1. Go to the management console and log in to Yandex Cloud or create an account if you do not have one yet.
  2. On the Billing page, make sure you have a billing account activated and its status is ACTIVE or TRIAL_ACTIVE. If you do not have a billing account, create one.

If you have an active billing account, you can create or select a folder on the cloud page to host your infrastructure.

Learn more about clouds and folders.

If you no longer need the resources you created, delete them.

Create a DB for storing the Tracker data

  1. Go to the management console.
  2. In the top-left corner, click All services.
  3. Select Data platform → Managed Service for ClickHouse.
  4. Click Create ClickHouse cluster.
  5. Specify the cluster parameters:
    • Basic parameters:
      • Environment: PRODUCTION.
      • Version: A supported ClickHouse version from the console list.
    • Resources:
      • Platform: Intel Ice Lake.
      • Type: Standard.
      • Host class: s3-c2-m8 (2 vCPU, 8 GB).
    • Storage size: 30 GB.
    • Hosts:
      • Public access: Enabled.
    • DBMS settings:
      • User management via SQL: Disabled.
      • Database management via SQL: Disabled.
      • Username: tracker_data.
      • Password: Set and save the user password for the importer and the DataLens connection.
      • DB name: db1.
    • Service settings:
  6. Click Create cluster. Wait for the created cluster status to become Running and its state to become Alive.
  7. Copy and save the host name for further Cloud Functions setup.

Create an OAuth token for Tracker access

If you use a federated or service account, authenticate using an IAM token.

To get a token:

  1. Go to https://oauth.yandex.com/.

  2. On the Your applications page, click Create.

  3. In the window that opens, select the For API access or debugging option and click Proceed to creation.

  4. Specify the application name and contact email.

  5. Add permissions to access user data. To select a permission, start typing its name in the Access name field:

    • Write to tracker (tracker:write) — all data operations: create, delete, edit.
    • Read from tracker (tracker:read) — read-only access to data.
  6. Click Create application.

  7. In the Yandex OAuth personal account, select the previously created application and copy its ID from the ClientID field.

  8. Log in to the account on whose behalf you will work with the API.

  9. Generate the token request link and follow it:

    https://oauth.yandex.com/authorize?response_type=token&client_id=APPLICATION_ID
    

    A sequence of characters will appear on the page — this is the OAuth token. Copy it and save it.

To check if you have access to the API, execute the request for information about the current user. If access was not obtained, the request will return a response with code 401 Unauthorized.

Example request for information about the current user using curl:

curl -X GET 'https://api.tracker.yandex.net/v3/myself' \
     -H 'Authorization: OAuth y0__xAbc******' \
     -H 'X-Org-ID: 1234******'
curl -X GET "https://api.tracker.yandex.net/v3/myself" ^
     -H "Authorization: OAuth y0__xAbc******" ^
     -H "X-Org-ID: 1234******"

Prepare the importer archive

  1. Download and extract the sample archive.

  2. Open tracker_import.py. In the TRACKER_HEADERS dictionary, select the organization header:

    • For a Yandex 360 organization, keep X-Org-ID.
    • For a Yandex Cloud organization, replace the X-Org-ID key with X-Cloud-Org-ID.

    In both cases, TRACKER_ORG_ID must contain the ID of the corresponding organization. The importer uses an OAuth token. Using an IAM token requires separate changes to authentication and token renewal; this tutorial uses OAuth.

  3. In get_issues_query_text, replace the elif TRACKER_INITIAL_HISTORY_DEPTH branch so the filter also applies to the initial import:

    elif TRACKER_INITIAL_HISTORY_DEPTH:
        tracker_query_text = 'updated: >now()-' + TRACKER_INITIAL_HISTORY_DEPTH + ' ' + FILTER
        return tracker_query_text
    
  4. Replace upload_data_to_db with the following code. It uploads issues even when there are no workflow events:

    def upload_data_to_db(issues, issues_chagelogs):
        if issues:
            issues_df = shape_issues_data(issues)
            issues_content = issues_df.replace("\n", "\\\n", regex=True).to_csv(index=False, sep='\t')
            upload_clickhouse_data(issues_content.encode('utf-8'), CH_ISSUES_TABLE)
        if issues_chagelogs:
            changelog_df = shape_issue_changelog_data(issues_chagelogs)
            changelog_content = changelog_df.replace("\n", "\\\n", regex=True).to_csv(index=False, sep='\t')
            upload_clickhouse_data(changelog_content.encode('utf-8'), CH_CHANGELOG_TABLE)
    
  5. Save the changes and recreate the archive. The tracker_import.py and requirements.txt files and the isoduration directory must be at the ZIP archive root.

This example exports data through the external Tracker API. The token must belong to an account with access to the issues you need. Do not grant access to the database or reports to users who cannot access the exported data.

Create a Cloud Functions function for importing the data

  1. Go to the management console.

  2. In the top-left corner, click All services.

  3. Select Serverless computing → Cloud Functions.

  4. Click Create function.

  5. Specify a name for the function and click Create.

  6. In the Editor window that opens, select Python / 3.12 runtime environment.

  7. Click Next.

  8. In the Method field, click ZIP archive.

  9. Attach the prepared archive.

  10. In the Entry point field, specify tracker_import.handler.

  11. Under Parameters, specify:

    • Timeout, sec: 60.
    • RAM: 1024.
    • Environment variables:
      • TRACKER_ORG_ID: ID of the organization connected to Tracker. Its type must match the header selected when preparing the archive.

        To get the organization ID, go to Administration → Organizations and copy the value from the ID field.

      • TRACKER_OAUTH_TOKEN: OAuth token of the Tracker account

      • CH_HOST: Host name

      • CH_DB: Database name

      • CH_USER: Username

      • CH_PASSWORD: Password

      • CH_ISSUES_TABLE: tracker_issues

      • CH_CHANGELOG_TABLE: tracker_changelog

      • TRACKER_INITIAL_HISTORY_DEPTH: 1d for a test import of issues updated in the last day. For a longer initial import, increase this value, e.g., to 365d.

      • CH_STATUSES_VIEW: v_tracker_statuses

      • FILTER: An additional search condition, e.g., Queue: TEST, where TEST is your queue key. Without a filter, the importer exports all issues accessible to the account within the selected period.

  12. Click Save changes.

  13. In the Testing tab, click Run test.

  14. On a successful invocation, the function returns:

    {
        "statusCode": 200,
        "headers": {
        "Content-Type": "text/plain"
        },
        "isBase64Encoded": false,
        "body": "OK"
    }
    

    The OK response confirms that the invocation finished, not that the export is complete. In ClickHouse, check that db1.v_tracker_issues contains the expected issues. If it is empty, check the account permissions, FILTER, and the initial import period.

    Subsequent invocations resume from the latest imported updatedAt with a five-minute overlap. Changing TRACKER_INITIAL_HISTORY_DEPTH after the first import does not reload older issues. The 60-second timeout and 1024 MB of memory are starting settings for a small export. If the import times out, reduce the selection or increase the resources.

  15. Create a trigger for regular exports of new data to the database:

    1. Go to the Cloud Functions section.
    2. Click → Create trigger.
    3. Set the trigger type to Timer.
    4. In the Cron expression field, select Every day.
    5. Under Function settings, select the function you created and its $latest version tag.
    6. Create or select a service account with the functions.functionInvoker role to invoke the function. To create an account in this window, click Create new.
    7. If creating an account, enter its name and click Create.
    8. Click Create trigger.

Create a connection in DataLens

  1. Open your Managed Service for ClickHouse® cluster.

  2. On the left side of the window, select DataLens.

  3. Click Create connection.

  4. Specify the connection settings:

  5. Click Check connection, then Create connection.

  6. Select or create a workbook, name the connection, and save it. The cluster and the DataLens instance must belong to the same organization. If the cluster is missing, check the organization and access permissions. For more information, see the DataLens guide.

Create a dataset

  1. Go to the connections page.

  2. Select a connection.

  3. In the top-right corner, click Create dataset.

  4. For the first chart, drag the db1.v_tracker_issues view to the workspace. It contains the latest imported state of each issue.

    You can also create separate datasets based on:

    • db1.v_tracker_changelog: Workflow events for imported issues, including status transitions.
    • db1.v_tracker_statuses: Intervals between transitions and time from issue creation to a transition, in minutes.

    The importer code defines the available fields. Custom fields are not automatically added to the schema. Account for relationships when joining these views: multiple events for one issue can increase the row count.

  5. Click Save.

Create a chart

  1. Go to the DataLens home page.
  2. Click Create chart.
  3. In the top left corner, click Select dataset.
  4. In the Datasets drop-down list, select the dataset you created in the previous step.
  5. On the top panel, select the Column chart visualization type.
  6. Add status_display as the X-axis dimension.
  7. Create a calculated field named Issue count with the COUNTD([id]) formula and add it as the Y-axis measure.
  8. Save the chart as Issues by status. Each column shows the number of unique issues in a status within the exported data.

Create a dashboard and add charts to it

  1. On the DataLens home page, click Create dashboard.

  2. At the top of the dashboard page, click Add → Chart.

  3. Fill in the widget parameters. Pay close attention to the following fields:

    • Name: Sets the name of the widget. It is displayed at the top of the widget.
    • Chart: Select the saved Issues by status chart.
    • Description: Sets the description of the widget. It is displayed at the bottom of the widget.
    • Auto height: Sets the automatic height for Table and Markdown widgets. If this parameter is disabled, you can set the height of the widget on the page using the mouse.
  4. Click Add. The widget will be displayed on the dashboard.

  5. Save the dashboard:

    1. In the top-right corner of the dashboard, click Save.
    2. Enter a name for the dashboard and click Create.

    For more information about setting up dashboards, see DataLens dashboard.

How to delete the resources you created

To stop scheduled invocations and release resources:

  1. Delete the trigger.
  2. Delete the ClickHouse® cluster.
  3. Delete the Cloud Functions function.

ClickHouse® is a registered trademark of ClickHouse, Inc.