Visualizing data from CHYT
This section explains how to connect CHYT using public clique as a data source for DataLens.
Getting started
To use a YTsaurus table as a data source, select the CHYT clique you will be working with.
Note
A clique is a virtual cluster in CHYT. It is represented by an operation and has an ID (operation_id) or alias (alias). There are public and private cliques.
When working with CHYT, you can use the public *ch_public clique.
Prepare a data table on YTsaurus
Before creating a connector in CHYT, prepare your table on YTsaurus:
-
The table must be static. Learn more about static tables in YTsaurus.
-
The table must be schematized. DataLens executes a query to a table and receives its schema to form a dataset. Learn more about schemas in YTsaurus.
Tip
We recommend:
- Sorting tables by the basic fields the filtering is done by. For instance, you can sort tables by fields that contain dates.
- Enabling columnar chunk storage format using the
optimize_for = scanattribute. This will enable DataLens to handle a table faster.
Create a chart
To create a chart in DataLens, you need to create a connection and dataset over it.
Create a connection
To work with CHYT, create a CHYT connection.
To create a YTsaurus CHYT connection:
-
Open the page for creating a new connection.
-
Under Databases, select the YTsaurus CHYT connection.
-
Specify the connection parameters:
- Hostname: Specify the YTsaurus proxy server address.
- Port: Specify the CHYT connection port.
- Clique alias: Specify the alias of a running clique. By default, the
*ch_publicpublic clique is used. - YTsaurus token: Manually set the OAuth token to use with YTsaurus. For more information, see the YTsaurus documentation.
- Cache TTL in seconds: Specify the cache time-to-live or leave the default value. The recommended value is 300 seconds (5 minutes).
- Raw SQL level: Select the SQL query access level for the user.
- HTTPS: Enable the secure connection option if your server supports HTTPS.
-
(Optional) Test the connection. To do this, click Check connection.
-
Click Create connection.
-
Select a workbook to save your connection to or create a new one. If using legacy folder navigation, select a folder to save the connection to. Click Create.
-
Enter a name for the connection and click Create.
Additional settings
You can limit data export from charts by enabling Disable data export under Advanced connection settings. With this option enabled, the export data button will be hidden in the charts based on this connection. However, you will still be able to copy chart data and take screenshots.
You can go straight to creating a dataset from the connection creation interface. To do this, in the upper-right corner, click Create dataset.
Create a dataset
After creating a connection, you can create a dataset over it.
-
In the left-hand navigation menu, click and then click Create dataset in the top-right corner.
-
In the window that opens, in the Connections section, click + Add. Select the CHYT connection that you created earlier.
-
Select the source indication method:
-
Table: One table on YTsaurus. URL from the browser or a full path to a table.
-
List: Multiple tables on YTsaurus. URL from the browser or a full path to a table. Use a new row for each table.
-
Range: A range of tables on YTsaurus. You can set your own table range using the Start with and Finish at fields. DataLens will add all the tables specified in the range to the dataset in alphabetical order. If the limit values are left blank, DataLens will use all the tables in the specified folder. You can also set only one limit value.
Note
For example, there are twenty tables in a folder and they are named
table_1,table_2,table_3, ... . If you specify the limit values as1and5, DataLens will use only five tables from the folder. -
SQL: Selecting data for a dataset via an SQL query. We recommend copying a query from Query in ClickHouse® syntax and then pasting it into the field.
Warning
First process a query from Query: delete all
;symbols. An entire Query query from DataLens is executed as a subquery.
-
-
Click Save. Make sure that the data is displayed in the dataset preview.
-
Click Save in the upper-right corner. Enter a name for the dataset and click Create. The dataset will appear in the list of datasets available to you.
Above the created dataset, you can create various charts and place them on dashboards.
Frequently asked questions
Error in the interface Access to table was denied
ERR.DS_API.DB.CHYT.TABLE_ACCESS_DENIED
The YTsaurus token user has no access to the selected table. Use a different token (from a user who has access to the table) or grant access to the table to the user with a token specified in the connection.
If you do not know who created the connection, create a new one.
Error in the interface Clique not running
ERR.DS_API.CLIQUE_STOPPED
The CHYT clique specified in the current connection is not running. Restart the clique.
Error in the interface Column used in join expression is not a key column
ERR.DS_API.DB.CHYT.INVALID_SORTED_JOIN
When you use a CHYT connection, key columns of YTsaurus tables are significant. In multi-table datasets, you can link tables (execute JOIN) only by key columns of joined tables. To do this, the following requirements must be met:
- All the columns used to join two tables must form a part of the key for both of them.
- The keys of both tables must begin with exactly those columns.
- The columns must be listed in the same order in the keys of both tables.
To work around this issue, recreate the source tables and specify the keys that will meet the above requirements.
Error in the interface YTsaurus table has no schema. Only schematized tables are supported
ERR.DS_API.DB.CHYT.TABLE_HAS_NO_SCHEMA
The YTsaurus table that I use has no schema. You cannot use such tables in DataLens. Recreate the table with a schema (specify the data types)
Requested database column does not exist error in the interface
See more info.
ClickHouse® is a registered trademark of ClickHouse, Inc.