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:
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.
System Dashboards
Release 6.0 includes preconfigured system dashboards built on various system tables. Specifically, two dashboards named System Resources and User Roles and Permissions are available 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.

Tables

Views

Last modified 1mo ago

Export as PDF

Copy link

Contents