Note

This documentation is for a prior release of Kinetica. For the latest documentation, click here.

Virtual Catalogs

Kinetica provides metadata tables, or virtual catalogs, as a means of querying the database for information about its objects, relationships, and permissions.

Virtual catalogs can be used to easily search for database tables & views by name without knowing the containing schema name, or for database columns by name without knowing the containing table or view name. They can be used to generate reports of the database structure or audit the creation & modification of that structure.

Kinetica provides its own set of virtual catalog tables, and supports both ANSI & PostgreSQL metadata queries.

The information returned when querying the virtual catalogs is automatically filtered by the permissions of the querying user. For instance, regular users will only see the tables & views to which they have access when querying a virtual catalog table that lists all of the tables in the database, while system administrators will see all tables & views.

Kinetica Virtual Catalog

The Kinetica virtual catalog tables reside in the ki_catalog schema.

Virtual Catalog Table	Description
`ki_columns`	Columns of tables & views and their respective types & statistics.
`ki_contexts`	SQL-GPT contexts and their configuration.
`ki_datasource_subscriptions`	Kafka data subscriptions, associating Kafka data sources with their target load tables.
`ki_datasources`	External data sources, their containing schemas and types.
`ki_datatypes`	Supported column data types.
`ki_depend`	Object dependencies for views, procedures, and result tables.
`ki_fk_constraints`	Foreign keys, one record per column specified in each foreign key.
`ki_functions`	Scalar, aggregate, window, and user-defined functions.
`ki_ingest_file_info`	Name & date of data files loaded into the database.
`ki_load_history`	Record & statistics of each data load or export, subscription event, and data refresh.
`ki_obj_stat`	Row & byte counts for each object in the database.
`ki_object_permissions`	Privileges granted, including the grantee, privilege type, and object to which access was granted.
`ki_objects`	Tables & views and their respective types & statistics.
`ki_query_active_all`	Actively running SQL statements per worker rank, including DML & DDL.
`ki_query_history`	History of SQL statements run within the database, including DML & DDL; will not include multi-head ingest/egress.
`ki_query_span_metrics_all`	Processing metrics captured during the execution of commands on worker ranks.
`ki_query_workers`	Worker ranks' status and their actively running tasks.
`ki_role_members`	Users & roles and their granted roles, one record per user/role or role/role pair.
`ki_schemas`	Schemas, containing all database objects.
`ki_tiered_objects`	Tiered objects and their size within those tiers. Tip To retrieve the tiering information for a particular object, the object must be queried by fully-qualified name using the pseudo-column `outer_object`: SELECT * FROM ki_tiered_objects WHERE outer_object = '<schema_name>.<object_name>' To find the total RAM usage of an employee table, one could use: SELECT SUM(size)/1000000.0 AS ram_size_in_mb FROM ki_tiered_objects WHERE outer_object = 'example.employee' AND tier = 'RAM'
`ki_users_and_roles`	Users & user roles.

ANSI Virtual Catalog

The ANSI virtual catalog tables reside in the INFORMATION_SCHEMA schema.

Virtual Catalog Table	Description
`APPLICABLE_ROLES`	Users & roles. This is only a simple list, one record per unique user or role--it is not a list of mappings of roles to users and roles to roles. `ROLE_NAME` - name of user or role `IS_GRANTABLE` - `TRUE` for roles and `FALSE` for users
`ATTRIBUTES`	Not used.
`CHARACTER_SETS`	Contains a single record with the supported character set, UTF-8.
`COLUMNS`	Columns of tables & views and their respective types & statistics.
`CONSTRAINT_COLUMN_USAGE`	Columns with constraints and their containing schemas.
`CONTEXT_RULES`	Rules sections of SQL-GPT contexts.
`CONTEXT_SAMPLES`	Samples sections of SQL-GPT contexts.
`CONTEXT_TABLE_COLUMNS`	Columns of tables associated with SQL-GPT contexts.
`CONTEXT_TABLES`	Tables associated with SQL-GPT contexts.
`CONTEXTS`	SQL-GPT contexts and their configuration.
`DATABASES`	Contains a single record with the database name and owner.
`ENABLED_ROLES`	Users & roles.
`FIELDS`	Columns of tables & views and their respective types & statistics, also containing the object type of the containing table/view.
`FUNCTIONS`	Scalar, aggregate, window, and user-defined functions.
`INFORMATION_SCHEMA_CATALOG_NAME`	Contains a single record with the catalog name, `kinetica`.
`KEY_COLUMN_USAGE`	Primary & foreign keys and their associated columns.
`KI_CATALOG_VERSION`	Contains a single record with the virtual catalog schema version.
`KI_LOAD_HISTORY`	Record & statistics of each data load or export, subscription event, and data refresh.
`KI_QUERY_ACTIVE`	Actively running SQL statements, including DML & DDL.
`KI_QUERY_HISTORY`	History of SQL statements run within the database, including DML & DDL; will not include multi-head ingest/egress.
`KI_QUERY_SPAN_METRICS`	Processing metrics of SQL statements run within the database, including DML & DDL; will not include multi-head ingest/egress.
`KI_QUERY_SPAN_METRICS_BY_SQL_STEP`	Processing metrics of SQL statements run within the database, aggregated on each execution phase of a given query/command.
`KI_QUERY_WORKERS`	Worker ranks' status and their actively running tasks.
`OBJECT_PRIVILEGES`	Privileges granted, including the grantee, privilege type, and object to which access was granted.
`REFERENTIAL_CONSTRAINTS`	Foreign keys and their referenced unique constraints.
`ROLE_TABLE_GRANTS`	Alias for the TABLE_PRIVILEGES table.
`SCHEMATA`	Schemas, containing all database objects.
`SEQUENCES`	Not used.
`TABLE_CONSTRAINTS`	Primary & foreign keys and their respective types & statistics.
`TABLE_PRIVILEGES`	Privileges granted on tables & views.
`TABLES`	Tables & views and their respective types & statistics.
`VIEWS`	Views and their respective definitions & statistics.

PostgreSQL Virtual Catalog

The PostgreSQL virtual catalog tables reside in the pg_catalog schema.

Virtual Catalog Table	Description
`geography_columns`	Not used.
`geometry_columns`	Columns of tables & views that have a GEOMETRY data type.
`pg_aggregate`	Not used.
`pg_am`	Not used.
`pg_amop`	Not used.
`pg_amproc`	Not used.
`pg_attrdef`	Columns that have default values.
`pg_attribute`	Columns of tables & views and their respective types & statistics.
`pg_auth_members`	Users & roles and their granted roles, one record per user/role or role/role pair.
`pg_authid`	Users & roles and their respective attributes.
`pg_class`	Tables & views and their respective types and column counts.
`pg_collation`	Not used.
`pg_constraint`	Primary & foreign keys.
`pg_database`	Contains a single record with the database name and information.
`pg_default_acl`	Contains a single record with the default table ACL for the `public` role.
`pg_depend`	Not used.
`pg_description`	Comments for tables & views.
`pg_enum`	Not used.
`pg_event_trigger`	Not used.
`pg_extension`	Not used.
`pg_foreign_data_wrapper`	Not used.
`pg_foreign_server`	Not used.
`pg_foreign_table`	Not used.
`pg_group`	Not used.
`pg_index`	Indexes and their respective configurations.
`pg_inherits`	Not used.
`pg_language`	Contains a single record with the supported procedure language, `SQL`.
`pg_locks`	Not used.
`pg_matviews`	Materialized views and their backing SQL, if created using SQL.
`pg_namespace`	Schemas, containing all database objects.
`pg_opclass`	Not used.
`pg_operator`	Not used.
`pg_opfamily`	Not used.
`pg_partitioned_table`	Not used.
`pg_policy`	Not used.
`pg_proc`	Scalar, aggregate, window, and user-defined functions.
`pg_range`	Not used.
`pg_rewrite`	Not used.
`pg_roles`	Users & roles and their respective attributes.
`pg_sequence`	Not used.
`pg_settings`	Not used.
`pg_shdescription`	Not used.
`pg_stat_activity`	Not used.
`pg_stat_all_tables`	Row & byte counts for each object in the database.
`pg_stats`	Columns of tables & views and their respective types.
`pg_tables`	Tables & views.
`pg_tablespace`	Not used.
`pg_timezone_abbrevs`	Contains a single record with the supported UTC timezone and its GMT offset.
`pg_timezone_names`	Contains a single record with the supported UTC timezone name, abbreviation, and its GMT offset.
`pg_trigger`	Not used.
`pg_type`	Supported PostgreSQL column data types mapped to Kinetica types.
`pg_user`	Users and their respective attributes.
`pg_views`	Logical views and their backing queries.