# System Tables

HeavyDB system tables provide a way to access information about database objects, database object permissions, and system resource (storage, CPU, and GPU memory) utilization. These system tables can be found in the `information_schema` database that is available by default on server startup. You can query system tables in the same way as regular tables, and you can use the `SHOW CREATE TABLE` command to view the table schemas.

## Users

The `users` system table provides information about all database users and contains the following columns:

| **Column Name**   | **Column Type** | **Description**                                                                 |
| ----------------- | --------------- | ------------------------------------------------------------------------------- |
| user\_id          | INTEGER         | ID of database user.                                                            |
| user\_name        | TEXT            | Username of database user.                                                      |
| is\_super\_user   | BOOLEAN         | Indicates whether or not the database user is a super user.                     |
| default\_db\_id   | INTEGER         | ID of user’s default database on login.                                         |
| default\_db\_name | TEXT            | Name of user’s default database on login.                                       |
| can\_login        | BOOLEAN         | Indicates whether or not the database user account is activated and can log in. |

## Databases

The `databases` system table provides information about all created databases on the server and contains the following columns:

| **Column Name**   | **Column Type** | **Description**             |
| ----------------- | --------------- | --------------------------- |
| database\_id      | INTEGER         | ID of database.             |
| database\_name    | TEXT            | Name of database.           |
| owner\_id         | INTEGER         | User ID of database owner.  |
| owner\_user\_name | TEXT            | Username of database owner. |

## Permissions

The `permissions` system table provides information about all user/role permissions for all database objects and contains the following columns:

| **Column Name**           | **Column Type** | **Description**                                                                       |
| ------------------------- | --------------- | ------------------------------------------------------------------------------------- |
| role\_name                | TEXT            | Username or role name associated with permission.                                     |
| is\_user\_role            | BOOLEAN         | Boolean indicating whether or not the `role_name` column identifies a user or a role. |
| database\_id              | INTEGER         | ID of database that contains the database object for which permission was granted.    |
| database\_name            | TEXT            | Name of database that contains the database object on which permission was granted.   |
| object\_name              | TEXT            | Name of database object on which permission was granted.                              |
| object\_id                | INTEGER         | ID of database object on which permission was granted.                                |
| object\_owner\_id         | INTEGER         | User id of the owner of the database object on which permission was granted.          |
| object\_owner\_user\_name | TEXT            | Username of the owner of the database object on which permission was granted.         |
| object\_permission\_type  | TEXT            | Type of database object on which permission was granted.                              |
| object\_permissions       | TEXT\[]         | List of permissions that were granted on database object.                             |

## Roles

The `roles` system table lists all created database roles and contains the following columns:

| **Column Name** | **Column Type** | **Description** |
| --------------- | --------------- | --------------- |
| role\_name      | TEXT            | Role name.      |

## Tables

The `tables` system table provides information about all database tables and contains the following columns:

| **Column Name**       | **Column Type** | **Description**                                                                        |
| --------------------- | --------------- | -------------------------------------------------------------------------------------- |
| database\_id          | INTEGER         | ID of database that contains the table.                                                |
| database\_name        | TEXT            | Name of database that contains the table.                                              |
| table\_id             | INTEGER         | Table ID.                                                                              |
| table\_name           | TEXT            | Table name.                                                                            |
| owner\_id             | INTEGER         | User ID of table owner.                                                                |
| owner\_user\_name     | TEXT            | Username of table owner.                                                               |
| column\_count         | INTEGER         | Number of table columns. Note that internal system columns are included in this count. |
| table\_type           | TEXT            | Type of tables. Possible values are `DEFAULT`, `VIEW`, `TEMPORARY` , and `FOREIGN`.    |
| view\_sql             | TEXT            | For views, SQL statement used in the view.                                             |
| max\_fragment\_size   | INTEGER         | Number of rows per fragment used by the table.                                         |
| max\_chunk\_size      | BIGINT          | Maximum size (in bytes) of table chunks.                                               |
| fragment\_page\_size  | INTEGER         | Size (in bytes) of table data pages.                                                   |
| max\_rows             | BIGINT          | Maximum number of rows allowed by table.                                               |
| max\_rollback\_epochs | INTEGER         | Maximum number of epochs a table can be rolled back to.                                |
| shard\_count          | INTEGER         | Number of shards that exists for table.                                                |
| ddl\_statement        | TEXT            | `CREATE TABLE` DDL statement for table.                                                |

## Dashboards

The `dashboards` system table provides information about created dashboards (enterprise edition only) and contains the following columns:

| **Column Name**   | **Column Type** | **Description**                                |
| ----------------- | --------------- | ---------------------------------------------- |
| database\_id      | INTEGER         | ID of database that contains the dashboard.    |
| database\_name    | TEXT            | Name of database that contains the dashboard.  |
| dashboard\_id     | INTEGER         | Dashboard ID.                                  |
| dashboard\_name   | TEXT            | Dashboard name.                                |
| owner\_id         | INTEGER         | User ID of dashboard owner.                    |
| owner\_user\_name | TEXT            | Username of dashboard owner.                   |
| last\_updated\_at | TIMESTAMP       | Timestamp of last dashboard update.            |
| data\_sources     | TEXT\[]         | List to data sources/tables used by dashboard. |

## Role Assignments

The `role_assignments` system table provides information about database roles that have been assigned to users and contains the following columns:

| **Column Name** | **Column Type** | **Description**                              |
| --------------- | --------------- | -------------------------------------------- |
| role\_name      | TEXT            | Name of assigned role.                       |
| user\_name      | TEXT            | Username of user that was assigned the role. |

## Memory Summary

The `memory_summary` system table provides high level information about utilized memory across CPU and GPU devices and contains the following columns:

| **Column Name**        | **Column Type** | **Description**                                                     |
| ---------------------- | --------------- | ------------------------------------------------------------------- |
| node                   | TEXT            | Node from which memory information is fetched.                      |
| device\_id             | INTEGER         | Device ID.                                                          |
| device\_type           | TEXT            | Type of device. Possible values are `CPU` and `GPU`.                |
| max\_page\_count       | BIGINT          | Maximum number of memory pages that can be allocated on the device. |
| page\_size             | BIGINT          | Size (in bytes) of a memory page on the device.                     |
| allocated\_page\_count | BIGINT          | Number of allocated memory pages on the device.                     |
| used\_page\_count      | BIGINT          | Number of used allocated memory pages on the device.                |
| free\_page\_count      | BIGINT          | Number of free allocated memory pages on the device.                |

## Memory Details

The `memory_details` system table provides detailed information about allocated memory segments across CPU and GPU devices and contains the following columns:

| **Column Name**      | **Column Type** | **Description**                                                         |
| -------------------- | --------------- | ----------------------------------------------------------------------- |
| node                 | TEXT            | Node from which memory information is fetched.                          |
| database\_id         | INTEGER         | ID of database that contains the table that memory was allocated for.   |
| database\_name       | TEXT            | Name of database that contains the table that memory was allocated for. |
| table\_id            | INTEGER         | ID of table that memory was allocated for.                              |
| table\_name          | TEXT            | Name of table that memory was allocated for.                            |
| column\_id           | INTEGER         | ID of column that memory was allocated for.                             |
| column\_name         | TEXT            | Name of column that memory was allocated for.                           |
| chunk\_key           | INTEGER\[]      | ID of cached table chunk.                                               |
| device\_id           | INTEGER         | Device ID.                                                              |
| device\_type         | TEXT            | Type of device. Possible values are `CPU` and `GPU`.                    |
| memory\_status       | TEXT            | Memory segment use status. Possible values are `FREE` and `USED`.       |
| page\_count          | BIGINT          | Number pages in the segment.                                            |
| page\_size           | BIGINT          | Size (in bytes) of a memory page on the device.                         |
| slab\_id             | INTEGER         | ID of slab containing memory segment.                                   |
| start\_page          | BIGINT          | Page number of the first memory page in the segment.                    |
| last\_touched\_epoch | BIGINT          | Epoch at which the segment was last accessed.                           |

## Storage Details

The `storage_details` system table provides detailed information about utilized storage per table and contains the following columns:

<table data-header-hidden>
  <thead><tr><th /><th width="200.33333333333331" /><th /></tr></thead>

  <tbody><tr><td><strong>Column Name</strong></td><td><strong>Column Type</strong></td><td><strong>Description</strong></td></tr><tr><td>node</td><td>TEXT</td><td>Node from which storage information is fetched.</td></tr><tr><td>database_id</td><td>INTEGER</td><td>ID of database that contains the table.</td></tr><tr><td>database_name</td><td>TEXT</td><td>Name of database that contains the table.</td></tr><tr><td>table_id</td><td>INTEGER</td><td>Table ID.</td></tr><tr><td>table_name</td><td>TEXT</td><td>Table Name.</td></tr><tr><td>epoch</td><td>INTEGER</td><td>Current table epoch.</td></tr><tr><td>epoch_floor</td><td>INTEGER</td><td>Minimum epoch table can be rolled back to.</td></tr><tr><td>fragment_count</td><td>INTEGER</td><td>Number of table fragments.</td></tr><tr><td>shard_id</td><td>INTEGER</td><td>Table shard ID. This value is only set for sharded tables.</td></tr><tr><td>data_file_count</td><td>INTEGER</td><td>Number of data files created for table.</td></tr><tr><td>metadata_file_count</td><td>INTEGER</td><td>Number of metadata files created for table.</td></tr><tr><td>total_data_file_size</td><td>BIGINT</td><td>Total size (in bytes) of data files.</td></tr><tr><td>total_data_page_count</td><td>BIGINT</td><td>Total number of pages across all data files.</td></tr><tr><td>total_free_data_page_count</td><td>BIGINT</td><td>Total number of free pages across all data files.</td></tr><tr><td>total_metadata_file_size</td><td>BIGINT</td><td>Total size (in bytes) of metadata files.</td></tr><tr><td>total_metadata_page_count</td><td>BIGINT</td><td>Total number of pages across all metadata files.</td></tr><tr><td>total_free_metadata_page_count</td><td>BIGINT</td><td>Total number of free pages across all metadata files.</td></tr><tr><td>total_dictionary_data_file_size</td><td>BIGINT</td><td>Total size (in bytes) of string dictionary files.</td></tr></tbody>
</table>

## Log-Based System Tables

<Info>
  Log-based system tables are considered beta functionality in Release 6.1.0 and are disabled by default.
</Info>

### Request Logs

The `request_logs` system table provides information about HeavyDB Thrift API requests and contains the following columns:

| **Column Name**       | **Column Type** | **Description**                                                                                                                                             |
| --------------------- | --------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| log\_timestamp        | TIMESTAMP       | Timestamp of log entry.                                                                                                                                     |
| severity              | TEXT            | Severity level of log entry. Possible values are F (fatal), E (error), W (warning), and I (info).                                                           |
| process\_id           | INTEGER         | Process ID of the HeavyDB instance that generated the log entry.                                                                                            |
| query\_id             | INTEGER         | ID associated with a SQL query. A value of 0 indicates that either the log entry is unrelated to a SQL query or no query ID has been set for the log entry. |
| thread\_id            | INTEGER         | ID of thread that generated the log entry.                                                                                                                  |
| file\_location        | TEXT            | Source file name and line number where the log entry was generated.                                                                                         |
| api\_name             | TEXT            | Name of Thrift API that the request was sent to.                                                                                                            |
| request\_duration\_ms | BIGINT          | Thrift API request duration in milliseconds.                                                                                                                |
| database\_name        | TEXT            | Request session database name.                                                                                                                              |
| user\_name            | TEXT            | Request session username.                                                                                                                                   |
| public\_session\_id   | TEXT            | Request session ID.                                                                                                                                         |
| query\_string         | TEXT            | Query string for SQL query requests.                                                                                                                        |
| client                | TEXT            | Protocol and IP address of client making the request.                                                                                                       |
| dashboard\_id         | INTEGER         | Dashboard ID for SQL query requests coming from Immerse dashboards.                                                                                         |
| dashboard\_name       | TEXT            | Dashboard name for SQL query requests coming from Immerse dashboards.                                                                                       |
| chart\_id             | INTEGER         | Chart ID for SQL query requests coming from Immerse dashboards.                                                                                             |
| execution\_time\_ms   | BIGINT          | Execution time in milliseconds for SQL query requests.                                                                                                      |
| total\_time\_ms       | BIGINT          | Total execution time (execution\_time\_ms + serialization time) in milliseconds for SQL query requests.                                                     |

### Server Logs

The `server_logs` system table provides HeavyDB server logs in tabular form and contains the following columns:

| **Column Name** | **Column Type** | **Description**                                                                                                                                             |
| --------------- | --------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| node            | TEXT            | Node containing logs.                                                                                                                                       |
| log\_timestamp  | TIMESTAMP       | Timestamp of log entry.                                                                                                                                     |
| severity        | TEXT            | Severity level of log entry. Possible values are F (fatal), E (error), W (warning), and I (info).                                                           |
| process\_id     | INTEGER         | Process ID of the HeavyDB instance that generated the log entry.                                                                                            |
| query\_id       | INTEGER         | ID associated with a SQL query. A value of 0 indicates that either the log entry is unrelated to a SQL query or no query ID has been set for the log entry. |
| thread\_id      | INTEGER         | ID of thread that generated the log entry.                                                                                                                  |
| file\_location  | TEXT            | Source file name and line number where the log entry was generated.                                                                                         |
| message         | TEXT            | Log message.                                                                                                                                                |

### Web Server Logs

The `web_server_logs` system table provides HEAVY.AI Web Server logs in tabular form and contains the following columns (Enterprise Edition only):

<table data-header-hidden>
  <thead><tr><th width="150" /><th width="236.32369942196533" /><th /></tr></thead>

  <tbody><tr><td><strong>Column Name</strong></td><td><strong>Column Type</strong></td><td><strong>Description</strong></td></tr><tr><td>log_timestamp</td><td>TIMESTAMP</td><td>Timestamp of log entry.</td></tr><tr><td>severity</td><td>TEXT</td><td>Severity level of log entry. Possible values are fatal, error, warning, and info.</td></tr><tr><td>message</td><td>TEXT</td><td>Log message.</td></tr></tbody>
</table>

### Web Server Access Logs

The `web_server_access_logs` system table provides information about requests made to the [HEAVY.AI](http://heavy.ai/) Web Server. The table contains the following columns:

<table data-header-hidden>
  <thead><tr><th /><th width="150" /><th /></tr></thead>

  <tbody><tr><td><strong>Column Name</strong></td><td><strong>Column Type</strong></td><td><strong>Description</strong></td></tr><tr><td>ip_address</td><td>TEXT</td><td>IP address of client making the web server request.</td></tr><tr><td>log_timestamp</td><td>TIMESTAMP</td><td>Timestamp of log entry.</td></tr><tr><td>http_method</td><td>TEXT</td><td>HTTP request method.</td></tr><tr><td>endpoint</td><td>TEXT</td><td>Web server request endpoint.</td></tr><tr><td>http_status</td><td>SMALLINT</td><td>HTTP response status code.</td></tr><tr><td>response_size</td><td>BIGINT</td><td>Response payload size in bytes.</td></tr></tbody>
</table>

### Refreshing Logs System Tables

The logs system tables must be refreshed manually to view new log entries. You can run the `REFRESH FOREIGN TABLES` SQL command (for example, `REFRESH FOREIGN TABLES server_logs, request_logs;` ), or click the **Refresh Data Now** button on the table’s Data Manager page in Heavy Immerse.

### Request Logs and Monitoring System Dashboard

The Request Logs and Monitoring system dashboard is built on the log-based system tables and provides visualization of request counts, performance, and errors over time, along with the server logs.

## System Dashboards

Preconfigured [*system dashboards*](/immerse/admin-portal) are built on various system tables. Specifically, two dashboards named System Resources and User Roles and Permissions are available by default. The Request Logs and Monitoring system dashboard is considered beta functionality and disabled by default. These dashboards can be found in the `information_schema` database, along with the system tables that they use.

<Info>
  Access to system dashboards is controlled using Heavy Immerse privileges; only users with Admin privileges or users/roles with access to the `information_schema` database can access the system dashboards.
</Info>

Cross-linking must be enabled to allow cross-filtering across charts that use different system tables. Enable cross-linking by adding `"ui/enable_crosslink_panel": true` to the `feature_flags` section of the servers.json file.