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 Lists columns of tables & views and their respective types & statistics.
ki_datatypes Lists supported column data types.
ki_fk_constraints Lists foreign keys, one record per column specified in each foreign key
ki_functions Lists scalar, aggregate, window, and user-defined functions.
ki_object_permissions Lists privileges granted, including the grantee, privilege type, and object to which access was granted.
ki_objects Lists tables & views and their respective types & statistics.
ki_role_members Lists users & roles and their granted roles, one record per user/role or role/role pair.
ki_schemas Lists schemas.
ki_tiered_objects

Lists 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 Lists users & roles.

ANSI Virtual Catalog

The ANSI virtual catalog tables reside in the INFORMATION_SCHEMA schema.

Virtual Catalog Table Description
APPLICABLE_ROLES

Lists 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 populated.
COLUMNS Lists columns of tables & views and their respective types & statistics.
DATABASES Contains a single record with the database name, root.
ENABLED_ROLES Lists users & roles.
FIELDS List is the same as the COLUMNS table, but includes the object type of the containing table or view.
INFORMATION_SCHEMA_CATALOG_NAME Contains a single record with the catalog name, kinetica.
KEY_COLUMN_USAGE Lists primary & foreign keys and their associated columns.
OBJECT_PRIVILEGES Lists privileges granted, including the grantee, privilege type, and object to which access was granted.
REFERENTIAL_CONSTRAINTS Lists foreign keys and their referenced unique constraints.
ROLE_TABLE_GRANTS List is the same as the TABLE_PRIVILEGES table.
SCHEMATA Lists schemas.
TABLES Lists tables & views and their respective types & statistics.
TABLE_CONSTRAINTS Lists primary & foreign keys and their respective types & statistics.
TABLE_PRIVILEGES Lists privileges granted on tables & views.
VIEWS Lists views and their respective definitions & statistics.
functions Lists scalar, aggregate, window, and user-defined functions.

PostgreSQL Virtual Catalog

The PostgreSQL virtual catalog tables reside in the pg_catalog schema.

Virtual Catalog Table Description
pg_attrdef Lists columns that have default values.
pg_attribute Lists columns of tables & views and their respective types & statistics.
pg_auth_members Lists users & roles and their granted roles, one record per user/role or role/role pair.
pg_authid Lists users & roles and their respective attributes.
pg_class Lists tables & views and their respective types and column counts.
pg_constraint Lists primary & foreign keys.
pg_database Contains a single record with the database name, kinetica.
pg_default_acl Contains a single record with the default table ACL for the public role.
pg_description Lists comments for tables & views.
pg_index Lists indexes and their respective configurations.
pg_language Contains a single record with the supported procedure language, SQL.
pg_namespace Lists schemas.
pg_proc Lists scalar, aggregate, window, and user-defined functions.
pg_sequence Not populated.
pg_type Lists supported column data types.