SQL engine and data schema
The Pyrus visualization engine uses ClickHouse — a high-performance column-oriented database management system, ideally suited for complex analytical queries (OLAP). This allows for fast processing of large volumes of data and building reports in real time.
The Pyrus analytical model is built on two types of data:
System tables – the current state table (#tasks) and the event history table (#events);
Custom fields – form fields that supplement the system tables and are made available to SQL queries via parameters.
How report creation works
The process of transforming your SQL query into a ready-made widget goes through several steps.
- You write an SQL query in the editor, which may include aliases and parameters.
- When you click the Execute button, the parameters are replaced with actual values from the user interface (for example, selected filters, task field values).
- The final query is sent to ClickHouse, which forms a selection from the #tasks or #events tables into a single table.
- After receiving a response from ClickHouse, aliases are recognized and applied (for example, the task ID is converted into an active link to the task).
- The final, formatted result is displayed to the user as a widget.
Note: if the query is incorrect, the system immediately reports an error (for example, syntax errors).
Terminology
Two types of special markers are used in SQL queries to help link a static query with the dynamic context of Pyrus.
| Term | Description | Usage in SQL |
|---|---|---|
| Aliases | Transform system numeric IDs (statuses, users, stages) into human-readable names or links after receiving data from ClickHouse | Exclusively in SELECT AS. |
| Parameters | Dynamically embed actual values from tasks, as well as selected interface filter values before sending to ClickHouse | FROM, WHERE, HAVING, ORDER BY. |
Data sources
Two main tables are used for building reports, differing in nature: the current state table #tasks and the event history table #events.
The #tasks table
This table contains the current status of tasks (for example, form fields, task status).
Pyrus Tip: use to analyze current metrics, for example: how many tasks are in progress, what their current status is, etc.
| Field | Description | ClickHouse type | Default value |
|---|---|---|---|
| form_id | Form ID | UInt32 | — |
| task_id | Task ID | UInt32 | — |
| create_date | Field type: Creation Date | DateTime64(3,'UTC') | — |
| is_closed | Field type Open / Closed, true / false | Bool | False |
| due | Due field type | DateTime64(3,'UTC') | 2200-01-01 |
| is_overdue | Task overdue | Bool | False |
| step_due | Current step SLA due | DateTime64(3,'UTC') | 2200-01-01 |
| is_step_overdue | SLA step is overdue | Bool | False |
| status_due | Current status SLA due | DateTime64(3,'UTC') | 2200-01-01 |
| is_status_overdue | SLA status is overdue | Bool | False |
| creator_id | Task creator ID | UInt32 | 0 |
| last_note_id | Last comment ID | UInt64 | — |
| last_modified_date | Date of last modification | DateTime64(3,'UTC') | — |
| close_date | Date task was closed | DateTime64(3,'UTC') | 2200-01-01 |
| step | Step field type, step ID | Int16 | 0 |
| status | Status field type, status ID | Int16 | 0 |
| responsible_id | Responsible field type, user ID | Int16 | 0 |
The #events table
This table stores the chronology of all changes to the task. Each row is a separate recorded system event (for example, change of responsible person, addition of a comment, change of status). At the same time, one row can correspond to multiple changes within the system.
Pyrus tip: Use #events for metrics related to time, transitions, and auditing: how long a stage took, who and when made changes to the task, etc.
| Field | Description | ClickHouse type | Default value |
|---|---|---|---|
| form_id | Form ID | UInt32 | — |
| task_id | Task ID | UInt32 | — |
| note_id | Comment ID | UInt64 | — |
| create_date | Creation Date field type | DateTime64(3,'UTC') | — |
| author_id | Event author ID | UInt32 | 0 |
| task_created | Task generated by this event | Bool | False |
| operator_reply | Operator reply (outgoing message to an external channel) | Bool | False |
| is_closed | Field type Open / Closed, true / false | Bool | False |
| task_due | Due date type field | DateTime64(3,'UTC') | 2200-01-01 |
| is_task_overdue | The task is overdue at the time of event | Bool | False |
| task_create_date | Date task was created | DateTime64(3,'UTC') | — |
| task_creator_id | Task creator ID | UInt32 | System: 1730 Subprocess: 656701 |
approval_type Current values: 0 – not indicated 1 – approved 2 – denied 3 – deferred 4 – reviewed | Resolution: Approved, Reviewed, etc. | Int8 | 0 |
| spent_time | Time spent on task, in minutes | Int32 | 0 |
| comment_as_role_id | ID of role in whose name the comment is written | Int32 | 0 |
| step | Step field type, step ID | Int16 | 0 |
| is_step_changed | The step has changed in this event | Bool | False |
| previous_step | Previous step | Int16 | -1 |
| seconds_in_step | How many seconds were spent on the previous step | Int64 | 0 |
| is_step_frozen | Is the SLA frozen on the previous step | Bool | False |
| is_step_overdue | Has the previous stage ended after its established deadline | Bool | — |
| is_status_changed | The status has changed in this event | Bool | False |
| previous_status | Previous status | Int16 | -1 |
| is_status_frozen | Is the SLA frozen at the previous status | Bool | False |
| is_status_overdue | Has the previous status changed after its established deadline | Bool | False |
| responsible_id | Responsible field type, user ID | Int32 | 0 |
| is_responsible_changed | The assignee has changed in this event | Bool | False |
| previous_responsible_id | Previous responsible person | Int32 | -1 |
Custom fields
This section covers the field types you add in the form template settings. In SQL queries, these fields appear as extra parameters in the system tables.
Note: With the #tasks table, these parameters return the form field’s current value. With the #events table, they return the field value for the selected time range.
| Field | Description | ClickHouse type | Default value |
|---|---|---|---|
| ${Choice} | Choice type field, value ID | Nullable(Int64) | Null |
| ${Checkmark} | Checkmark type field, true / false | Nullable(Bool) | Null |
| ${Catalog:N} | Catalog type field, selected text value from column N in the catalog | Array(Nullable(String)) | Null |
| ${Form} | Form type field, task ID | Nullable(Int64) | Null |
| ${Number} | Number type field, entered number | Nullable(Float64) | Null |
| ${Money} | Money type field, entered number | Nullable(Float64) | Null |
| ${Date} | Date type field, entered number | Nullable(DateTime64(9,'UTC')) | Null |
| ${Time} | Time type field, entered number | Nullable(DateTime64(9,'UTC')) | Null |
| ${Contact} | Contact type field, user ID | Nullable(Int64) | Null |
Pyrus Tip: the following form fields are not supported in the visualization engine:
- User fields: Text, Telephone, Email;
- Formatting fields: Table, Comment.
Alias and parameter system
Aliases and parameters are special markers that allow linking a static SQL query with the dynamic context of Pyrus.
Aliases (SELECT AS)
Aliases are used only as aliases (AS) in the SELECT section. They convert numeric data into text or links.
Pyrus Tip: if you do not use an alias, the original numeric value (ID, status code, etc.) will be displayed in the column.
| Alias description | Alias | Usage |
|---|---|---|
| User ID → FullName | #person_full_ | responsible_id AS "#person_full_ |
| User ID → Name of department | #person_ | responsible_id AS "#person_ |
| Status ID → Status name | #status_name('Heading') | status AS "#status_name('Status')" |
| Step ID → Name of step | #step_name('Heading') | step AS "#step_name('Step')" |
| Selection type field ID → Value of Selection type field | #choice_name(${Field code}) #choice_name(${Field code}, 'Heading') | ${Channel} AS "#choice_name(${Channel}, |
| Task ID → Task name | #task('Heading') | task_id AS "#task('Task')" |
Parameters (embedded in SQL)
Parameters dynamically influence what is queried from the database, and how.
| Parameter description | Parameter | Usage |
|---|---|---|
| Selection of data source | #tasks / #events | FROM #tasks (for current state); FROM #events (for history) |
| Beginning/end of selected date range. | @period_start / @period_end | WHERE create_date >= @period_start |
| The expression corresponding to the filters that the user selected in the summary interface for the request on the current status of the task. | #task_filters | WHERE #task_filters — uses filters selected by the user in the report interface. |
Similar to the parameter #task_filters, but used in HAVING for queries to historical data in #events. | #event_filters | HAVING #event_filters — uses filters specific to working with the #events table. |
Used in ORDER BY for correct sorting of statuses according to their order in the form settings | #ordered_statuses | ORDER BY #ordered_statuses — ensures correct sorting of statuses in the output according to their order in the form settings. |
Restrictions
- All SQL queries to the data are executed in read mode. Only SELECT operations are available.
- The maximum number of rows in the result table is 1000.
- The maximum processing time for an SQL query is 5 seconds.