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.
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.
Common fields for #tasks and #events
This section presents all fields that are available for use in both tables.
Note: When using these parameters with the #tasks table, the result will be the current state of the field; when called in #events, the result will be the value in the considered time interval.
| Field | Description | ClickHouse type | Default value |
|---|---|---|---|
| form_id | Form ID | UInt32 | — |
| task_id | Task ID | UInt32 | — |
| ${Выбор} | 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 |
| is_closed | Open / Completed type field, true / false | Bool | False |
| responsible_id | Responsible type field, user ID | Int32 | 0 |
| create_date | Creation date type field | DateTime64(3,'UTC') | — |
| step | Step type field, step ID | Int16 | 0 |
| status | Status type field, status ID | Int16 | 0 |
The #tasks table
This table contains the current status of tasks (for example, form fields, task status). In addition to system fields, all fields created in the form are available for selection from this table through their parameters. Each task corresponds to exactly one row. The fields listed in this section are unique and not available for use in the #events table.
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 |
|---|---|---|---|
| due | Due date type fields | DateTime64(3,'UTC') | 2200-01-01 |
| is_overdue | Task overdue | Bool | False |
| creator_id | Task creator ID | UInt32 | System: 1730 Subprocess: 656701 |
| last_note_id | ID of last comment | UInt64 | — |
| last_modified_date | Date of last modification | DateTime64(3,'UTC') | — |
| close_date | Date task was closed | DateTime64(3,'UTC') | 2200-01-01 |
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 |
|---|---|---|---|
| note_id | Comment ID | UInt64 | — |
| 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 |
| 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 |
| 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 |
| is_responsible_changed | The assignee has changed in this event | Bool | False |
| previous_responsible_id | Previous responsible person | Int32 | -1 |
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 10 seconds.