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.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. |
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. |
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. |
The
roles system table lists all created database roles and contains the following columns:Column Name | Column Type | Description |
role_name | TEXT | Role name. |
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. |
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. |
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. |
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. |
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. |
The
storage_details system table provides detailed information about utilized storage per table and contains the following columns:Column Name | Column Type | Description |
node | TEXT | Node from which storage information is fetched. |
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. |
epoch | INTEGER | Current table epoch. |
epoch_floor | INTEGER | Minimum epoch table can be rolled back to. |
fragment_count | INTEGER | Number of table fragments. |
shard_id | INTEGER | Table shard ID. This value is only set for sharded tables. |
data_file_count | INTEGER | Number of data files created for table. |
metadata_file_count | INTEGER | Number of metadata files created for table. |
total_data_file_size | BIGINT | Total size (in bytes) of data files. |
total_data_page_count | BIGINT | Total number of pages across all data files. |
total_free_data_page_count | BIGINT | Total number of free pages across all data files. |
total_metadata_file_size | BIGINT | Total size (in bytes) of metadata files. |
total_metadata_page_count | BIGINT | Total number of pages across all metadata files. |
total_free_metadata_page_count | BIGINT | Total number of free pages across all metadata files. |
total_dictionary_data_file_size | BIGINT | Total size (in bytes) of string dictionary files. |
Log-based system tables are considered beta functionality in Release 6.1.0 and are disabled by default.
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. |
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. |
The
web_server_logs system table provides HEAVY.AI Web Server logs in tabular form and contains the following columns (Enterprise Edition only):Column Name | Column Type | Description |
log_timestamp | TIMESTAMP | Timestamp of log entry. |
severity | TEXT | Severity level of log entry. Possible values are fatal, error, warning, and info. |
message | TEXT | Log message. |
The
web_server_access_logs system table provides information about requests made to the HEAVY.AI Web Server. The table contains the following columns:Column Name | Column Type | Description |
ip_address | TEXT | IP address of client making the web server request. |
log_timestamp | TIMESTAMP | Timestamp of log entry. |
http_method | TEXT | HTTP request method. |
endpoint | TEXT | Web server request endpoint. |
http_status | SMALLINT | HTTP response status code. |
response_size | BIGINT | Response payload size in bytes. |
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.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.
Preconfigured system dashboards 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.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.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.Last modified 1yr ago