System Tables | HEAVY.AI Documentation

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:


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

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

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):


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.

Web Server Access Logs

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.

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 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.