Overview
The ANSI Virtual Catalog tables represent Kinetica's implementation of the Schemata section of the ISO/IEC 9075 (SQL:2003) database specification. This section defines the metadata tables that exist under the INFORMATION_SCHEMA schema. These tables contain information about the database's objects, relationships, & permissions.
Most of the metadata tables are implemented as logical views of the Kinetica Virtual Catalog tables, while some are logical views of other ANSI Virtual Catalog logical views. As such, they will be updated automatically to reflect changes in the database structure and processing state.
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.
Catalog View List
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. |
| 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_DATASOURCE_SUBSCRIPTIONS | Remote data subscriptions, associating data sources with their target load tables. |
| 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. |
| MV_DEPENDENCIES | Object dependencies for materialized views. |
| 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 | Effectively, an alias for the TABLE_PRIVILEGES table. |
| SCHEMATA | Schemas, containing all database objects. |
| 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. |
Catalog Column List
APPLICABLE_ROLES
Each record in APPLICABLE_ROLES represents a user or role.
| Column Name | Column Type | Description |
|---|---|---|
| ROLE_NAME | VARCHAR(256) | Name of this user or role. |
| GRANTEE | VARCHAR | Not used. |
| ROLE_OWNER | VARCHAR(8) | Always admin. |
| IS_GRANTABLE | BOOLEAN | Whether this is a role or not. |
CHARACTER_SETS
Each record in CHARACTER_SETS represents an available character set. Kinetica supports the UTF-8 character set only.
| Column Name | Column Type | Description |
|---|---|---|
| CHARACTER_SET_CATALOG | VARCHAR | Not used. |
| CHARACTER_SET_SCHEMA | VARCHAR | Not used. |
| CHARACTER_SET_NAME | VARCHAR(4) | Always UTF8. |
| CHARACTER_REPERTOIRE | VARCHAR(4) | Always UCS. |
| FORM_OF_USE | VARCHAR(4) | Always UTF8. |
| DEFAULT_COLLATE_CATALOG | VARCHAR(8) | Always KINETICA. |
| DEFAULT_COLLATE_SCHEMA | VARCHAR | Not used. |
| DEFAULT_COLLATE_NAME | VARCHAR | Not used. |
COLUMNS
Each record in COLUMNS represents a single column in a table or view for all tables & views in the database, including those in virtual catalog tables.
| Column Name | Column Type | Description |
|---|---|---|
| TABLE_CATALOG | VARCHAR(8) | Always kinetica. |
| TABLE_SCHEMA | VARCHAR(256) | Name of the schema containing this column's table. |
| TABLE_NAME | VARCHAR(256) | Name of the table containing this column. |
| COLUMN_NAME | VARCHAR(256) | Name of this column. |
| ORDINAL_POSITION | INTEGER | Position of this column in its table, using a 0-based index. |
| COLUMN_DEFAULT | VARCHAR(256) | The default value expression applied to this column. See Data Replacement for options. |
| IS_NULLABLE | BOOLEAN | Whether this column can be set to null. |
| DATA_TYPE | VARCHAR(256) | Name of the SQL data type. |
| CHARACTER_MAXIMUM_LENGTH | INTEGER | Size, in bytes, of this data type; -1 indicates variable length. |
| CHARACTER_OCTET_LENGTH | INTEGER | Always 0. |
| NUMERIC_PRECISION | INTEGER | Precision of fixed-precision data types. |
| NUMERIC_PRECISION_RADIX | INTEGER | Always 0. |
| NUMERIC_SCALE | INTEGER | Scale of fixed-scale data types. |
| DATETIME_PRECISION | INTEGER | Precision of fixed-precision data types. |
| INTERVAL_TYPE | VARCHAR(4) | Not used. |
| INTERVAL_PRECISION | INTEGER | Precision of fixed-precision data types. |
| CHARACTER_SET_CATALOG | VARCHAR | Not used. |
| CHARACTER_SET_SCHEMA | VARCHAR | Not used. |
| CHARACTER_SET_NAME | VARCHAR | Not used. |
| COLLATION_CATALOG | VARCHAR | Not used. |
| COLLATION_SCHEMA | VARCHAR | Not used. |
| COLLATION_NAME | VARCHAR | Not used. |
| DOMAIN_CATALOG | VARCHAR | Not used. |
| DOMAIN_SCHEMA | VARCHAR | Not used. |
| DOMAIN_NAME | VARCHAR | Not used. |
| UDT_CATALOG | VARCHAR | Not used. |
| UDT_SCHEMA | VARCHAR | Not used. |
| UDT_NAME | VARCHAR(256) | Name of the data type. |
| SCOPE_CATALOG | VARCHAR | Not used. |
| SCOPE_SCHEMA | VARCHAR | Not used. |
| SCOPE_NAME | VARCHAR | Not used. |
| MAXIMUM_CARDINALITY | INTEGER | Always 0. |
| DTD_IDENTIFIER | VARCHAR | Not used. |
| IS_SELF_REFERENCING | VARCHAR | Not used. |
| IS_IDENTITY | BOOLEAN | Always FALSE. |
| IDENTITY_GENERATION | BOOLEAN | Always FALSE. |
| IDENTITY_START | VARCHAR | Not used. |
| IDENTITY_INCREMENT | VARCHAR | Not used. |
| IDENTITY_MAXIMUM | VARCHAR | Not used. |
| IDENTITY_MINIMUM | VARCHAR | Not used. |
| IDENTITY_CYCLE | VARCHAR | Not used. |
| COMMENT | VARCHAR | User comment associated with this column. |
CONSTRAINT_COLUMN_USAGE
Each record in CONSTRAINT_COLUMN_USAGE represents a constrained primary key column from either a primary key or foreign key relationship. For example, a table with a single primary key column will have one record in this table, while a composite foreign key linking two columns of one table to two columns of another will have two records in this table--one for each of the columns on the target side of the relation. Effectively, primary keys will be listed once for themselves, and then once for each instance of their participation in a foreign key relationship.
For foreign keys, the table/column information in CONSTRAINT_COLUMN_USAGE refers to the target side of the relation (the foreign table referenced by the key), whereas the table/column information in KEY_COLUMN_USAGE refers to the source side of the relation (where the key is defined).
| Column Name | Column Type | Description |
|---|---|---|
| CONSTRAINT_CATALOG | VARCHAR(8) | Always kinetica. |
| CONSTRAINT_SCHEMA | VARCHAR(256) | For primary keys, the name of the schema containing this constrained table; for foreign keys, the name of the schema containing the foreign table referenced by this constraint. |
| CONSTRAINT_NAME | VARCHAR(256) | Name of this constraint. |
| TABLE_CATALOG | VARCHAR(8) | Always kinetica. |
| TABLE_SCHEMA | VARCHAR(256) | For primary keys, the name of the schema containing this constrained table; for foreign keys, the name of the schema containing the foreign table referenced by this constraint. |
| TABLE_NAME | VARCHAR(256) | For primary keys, the name of the table containing this constrained column; for foreign keys, the name of the foreign table referenced by this constraint. |
| CONSTRAINT_TYPE | VARCHAR(16) | Type of constraint; one of:
|
| COLUMN_NAME | VARCHAR(256) | For primary keys, the name of this constrained column; for foreign keys, the name of this constrained column in the foreign table referenced by this constraint. |
CONTEXT_RULES
Each record in CONTEXT_RULES represents a rule that applies within a SQL-GPT context. For instance, a context with two globally-applicable context rules and three table-specific context rules will have five entries in this table.
| Column Name | Column Type | Description | ||||||
|---|---|---|---|---|---|---|---|---|
| TABLE_OBJ_ID | VARCHAR(256) | Composited identifier for the group this context rule is in. A SQL-GPT context will have one unique ID for the group of globally-applicable rules and an individual unique ID for each group of table-specific rules. This ID and RULE_INDEX identify a unique record in this table. | ||||||
| CONTEXT_NAME | VARCHAR(256) | Fully-qualified name of the SQL-GPT context containing this context rule. | ||||||
| TABLE_NAME | VARCHAR(256) | Name of this SQL-GPT context rule object, denoting the type of context rule it is.
| ||||||
| RULE_INDEX | INTEGER | 0-based index of this context rule within its rule group. | ||||||
| RULE | VARCHAR | The context rule for the corresponding table, if TABLE_NAME is a table reference; or a global rule associated with the overall context, if TABLE_NAME is rules. |
CONTEXT_SAMPLES
Each record in CONTEXT_SAMPLES represents a question-to-query mapping that applies within a SQL-GPT context. For instance, a context with two context samples will have two entries in this table.
| Column Name | Column Type | Description |
|---|---|---|
| OBJ_ID | VARCHAR(256) | Composited unique identifier for this context sample. |
| CONTEXT_NAME | VARCHAR(256) | Fully-qualified name of the SQL-GPT context containing this context sample. |
| SAMPLE_INDEX | INTEGER | 0-based index of this context sample within the SQL-GPT context. |
| QUESTION | VARCHAR | The natural language question to associate with the corresponding SQL query in SQL_TEXT. |
| SQL_TEXT | VARCHAR | The SQL query that answers the natural language question posed in QUESTION. |
CONTEXT_TABLE_COLUMNS
Each record in CONTEXT_TABLE_COLUMNS represents a column in a table associated with a SQL-GPT context table definition and both the column comment from the table's schema and the context comment from the context table definition, if present. Each of the table's columns will have its own record, regardless of whether the column has an associated comment in the corresponding table definition or not. For instance, a context containing a table definition with three column comments for a table with five columns will have five records in this catalog table, three of which will be listed with their associated context comments.
| Column Name | Column Type | Description |
|---|---|---|
| TABLE_OBJ_ID | VARCHAR(256) | Composited identifier for the context table this column is in. A SQL-GPT context will have one unique ID for each table defined within it. This ID and either COLUMN_NAME or COLUMN_POSITION identify a unique record in this table. |
| CONTEXT_NAME | VARCHAR(256) | Fully-qualified name of the SQL-GPT context containing the table definition associated with the table containing this column. |
| TABLE_NAME | VARCHAR(256) | Name of the table containing this column. |
| COLUMN_NAME | VARCHAR(256) | Name of this column. |
| COLUMN_POSITION | INTEGER | 0-based index of this column within its containing table. |
| COLUMN_TYPE | VARCHAR(32) | SQL data type of this column. |
| COMMENT | VARCHAR | The table schema comment for this column, if present. |
| CONTEXT_COMMENT | VARCHAR | The context comment for this column, if present. |
CONTEXT_TABLES
Each record in CONTEXT_TABLES represents a table referenced within a SQL-GPT context table definition. For instance, a context referencing two tables will have two entries in this table.
| Column Name | Column Type | Description |
|---|---|---|
| TABLE_OBJ_ID | VARCHAR(256) | Composited identifier for this table, referenced in the associated context table definition. A SQL-GPT context will have one unique ID for each table referenced in its table definitions. |
| CONTEXT_NAME | VARCHAR(256) | Fully-qualified name of the SQL-GPT context referencing this table in one of its table definitions. |
| TABLE_NAME | VARCHAR(256) | Name of this table, referenced in the associated context table definition. |
| CONTEXT_COMMENT | VARCHAR | The context comment for this table, if present. |
| COMMENT | VARCHAR | The table schema comment for this table, if present. |
CONTEXTS
Each record in CONTEXTS represents a top-level object within a SQL-GPT context. Objects can be one of the following:
- Table Definition - Rules, samples & comments that apply to the use of the given table specifically
- Rules Definition - Rules that apply globally when using the context
- Samples Definition - Samples that apply globally when using the context
| Column Name | Column Type | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| OID | BIGINT | Unique identifier associated with this SQL-GPT context object. | ||||||||
| CONTEXT_NAME | VARCHAR(256) | Name of the SQL-GPT context containing this context object. | ||||||||
| SCHEMA_OID | BIGINT | Unique identifier associated with the schema containing the parent SQL-GPT context. See SCHEMATA. | ||||||||
| SCHEMA_NAME | VARCHAR(256) | Name of the schema containing the parent SQL-GPT context. | ||||||||
| OBJECT_NAME | VARCHAR(256) | Name of this SQL-GPT context object, denoting the type of context object it is.
| ||||||||
| OBJECT_DESCRIPTION | VARCHAR | The COMMENT associated with the table, if context object is a table definition. | ||||||||
| OBJECT_RULES | VARCHAR[] | The RULES associated with the table, if context object is a table definition; or the global rules associated with the overall context, if a rules definition. | ||||||||
| OBJECT_COMMENTS | JSON | The COMMENTS associated with the table, if context object is a table definition--essentially, the column definitions for the table. | ||||||||
| OBJECT_SAMPLES | JSON | The SAMPLES associated with the table, if context object is a table definition; or the global samples associated with the overall context, if a samples definition. | ||||||||
| IS_TEMP_CONTEXT | BOOLEAN | Whether this context is temporary and will be removed when either its time-to-live expires or the database is restarted, whichever comes first. | ||||||||
| TTL | INTEGER | Time-to-live, in seconds, for this context; if this context is not used for the associated TTL time span, it will be automatically removed. |
DATABASES
DATABASES contains a single record with the database name and owner.
| Column Name | Column Type | Description |
|---|---|---|
| DATABASE_NAME | VARCHAR(4) | Always root. |
| DATABASE_OWNER | VARCHAR(8) | Always admin. |
| IS_TRANSIENT | BOOLEAN | Always FALSE. |
| COMMENT | VARCHAR | Not used. |
| CREATED | BIGINT | Not used. |
| LAST_ALTERED | BIGINT | Not used. |
ENABLED_ROLES
Each record in ENABLED_ROLES represents either a user or role name.
| Column Name | Column Type | Description |
|---|---|---|
| ROLE_NAME | VARCHAR(256) | Name of this user or role. |
| ROLE_OWNER | VARCHAR(8) | Always admin. |
FIELDS
Each record in FIELDS represents a single column in a table or view for all tables & views in the database, including those in virtual catalog tables.
| Column Name | Column Type | Description |
|---|---|---|
| OBJECT_CATALOG | VARCHAR(8) | Always kinetica. |
| OBJECT_SCHEMA | VARCHAR(256) | Name of the schema containing this column's table. |
| OBJECT_NAME | VARCHAR(256) | Name of the table containing this column. |
| OBJECT_TYPE | VARCHAR(8) | Type of object containing this column; one of:
|
| ROW_IDENTIFIER | BIGINT | Not used. |
| FIELD_NAME | VARCHAR(256) | Name of this column. |
| ORDINAL_POSITION | INTEGER | Position of this column in its table, using a 0-based index. |
| IS_NULLABLE | BOOLEAN | Whether this column can be set to null. |
| DATA_TYPE | VARCHAR(256) | Name of the SQL data type. |
| CHARACTER_MAXIMUM_LENGTH | INTEGER | Size, in bytes, of this data type; -1 indicates variable length. |
| CHARACTER_OCTET_LENGTH | INTEGER | Always 0. |
| CHARACTER_SET_CATALOG | VARCHAR | Not used. |
| CHARACTER_SET_SCHEMA | VARCHAR | Not used. |
| CHARACTER_SET_NAME | VARCHAR | Not used. |
| COLLATION_CATALOG | VARCHAR | Not used. |
| COLLATION_SCHEMA | VARCHAR | Not used. |
| COLLATION_NAME | VARCHAR | Not used. |
| NUMERIC_PRECISION | INTEGER | Precision of fixed-precision data types. |
| NUMERIC_PRECISION_RADIX | INTEGER | Always 0. |
| NUMERIC_SCALE | INTEGER | Scale of fixed-scale data types. |
| DATETIME_PRECISION | INTEGER | Precision of fixed-precision data types. |
| INTERVAL_TYPE | VARCHAR(4) | Not used. |
| INTERVAL_PRECISION | INTEGER | Precision of fixed-precision data types. |
| DOMAIN_DEFAULT | VARCHAR | Not used. |
| UDT_CATALOG | VARCHAR | Not used. |
| UDT_SCHEMA | VARCHAR | Not used. |
| UDT_NAME | VARCHAR | Not used. |
| SCOPE_CATALOG | VARCHAR | Not used. |
| SCOPE_SCHEMA | VARCHAR | Not used. |
| SCOPE_NAME | VARCHAR | Not used. |
| MAXIMUM_CARDINALITY | INTEGER | Not used. |
| DTD_IDENTIFIER | INTEGER | Not used. |
FUNCTIONS
Each record in FUNCTIONS represents a native scalar or aggregate function, a UDF/UDTF, or a SQL procedure.
| Column Name | Column Type | Description |
|---|---|---|
| FUNCTION_CATALOG | VARCHAR(8) | Always kinetica. |
| FUNCTION_SCHEMA | VARCHAR(256) | Name of the schema containing this function. Native functions and UDFs are located in the pg_catalog schema, while SQL procedures are located in the schema specified by the user during creation. |
| FUNCTION_NAME | VARCHAR(256) | Name of this function. |
| FUNCTION_OWNER | VARCHAR(256) | Not used. |
| ARGUMENT_SIGNATURE | VARCHAR[] | Names of the data types of this function's parameters. See ki_datatypes. |
| DATA_TYPE | VARCHAR(256) | Name of the return type of this function. See ki_datatypes. |
| CHARACTER_MAXIMUM_LENGTH | INTEGER | Size, in bytes, of this function's return data type; -1 indicates variable length. |
| CHARACTER_OCTET_LENGTH | INTEGER | Always 0. |
| NUMERIC_PRECISION | INTEGER | Precision of this function's return data type, if fixed-precision. |
| NUMERIC_PRECISION_RADIX | INTEGER | Always 0. |
| NUMERIC_SCALE | INTEGER | Scale of this function's return data type, if fixed-scale. |
| FUNCTION_LANGUAGE | VARCHAR(256) | Language in which this SQL procedure was written (for SQL procedures; always SQL). |
| FUNCTION_DEFINITION | VARCHAR(256) | Not used. |
| VOLATILITY | BOOLEAN | Always FALSE. |
| IS_NULL_CALL | VARCHAR | Not used. |
| CREATED | TIMESTAMP | Timestamp of function creation. |
| LAST_ALTERED | TIMESTAMP | Timestamp of the most recent function update (initially, time of creation). |
| COMMENT | VARCHAR | Not used. |
INFORMATION_SCHEMA_CATALOG_NAME
INFORMATION_SCHEMA_CATALOG_NAME contains a single record with the database catalog name.
| Column Name | Column Type | Description |
|---|---|---|
| CATALOG_NAME | VARCHAR(8) | Always kinetica. |
KEY_COLUMN_USAGE
Each record in KEY_COLUMN_USAGE represents a key column from either a primary key or foreign key relationship. For example, a table with a single primary key column will have one record in this table, while a composite foreign key linking two columns of one table to two columns of another will have two records in this table--one for each of the columns on the source side of the relation.
For foreign keys, the table/column information in KEY_COLUMN_USAGE refers to the source side of the relation (where the key is defined), whereas the table/column information in CONSTRAINT_COLUMN_USAGE refers to the target side of the relation (the foreign table referenced by the key).
| Column Name | Column Type | Description |
|---|---|---|
| CONSTRAINT_CATALOG | VARCHAR(8) | Always kinetica. |
| CONSTRAINT_SCHEMA | VARCHAR(256) | For primary keys, the name of the schema containing this constrained table; for foreign keys, the name of the schema containing the table on the source side of this constraint. |
| CONSTRAINT_NAME | VARCHAR(256) | Name of this constraint. |
| TABLE_CATALOG | VARCHAR(8) | Always kinetica. |
| TABLE_SCHEMA | VARCHAR(256) | For primary keys, the name of the schema containing this constrained table; for foreign keys, the name of the schema containing the table on the source side of this constraint. |
| TABLE_NAME | VARCHAR(256) | For primary keys, the name of the table containing this constrained column; for foreign keys, the name of the table on the source side of this constraint. |
| COLUMN_NAME | VARCHAR(256) | For primary keys, the name of this constrained column; for foreign keys, the name of this constrained column in the table on the source side of this constraint. |
| ORDINAL_POSITION | INTEGER | 0-based index of the position of this key column among all columns in the constrained table. For primary key constraints, this will be the index of this column among all columns in the constrained table; for foreign key constraints, this will be the index of this column among all columns in the table on the source side of the constraint. |
KI_CATALOG_VERSION
KI_CATALOG_VERSION contains a single record with the database version.
| Column Name | Column Type | Description |
|---|---|---|
| CATALOG_VERSION | INTEGER | Version of the virtual catalog schema. |
KI_DATASOURCE_SUBSCRIPTIONS
Each record in KI_DATASOURCE_SUBSCRIPTIONS represents a subscription a single target table has through a single data source.
| Column Name | Column Type | Description |
|---|---|---|
| SCHEMA_NAME | VARCHAR(256) | Name of the schema containing the target table. |
| OBJECT_NAME | VARCHAR(256) | Name of the target table subscribed through the data source. |
| DATASOURCE_SCHEMA | VARCHAR(256) | Name of the schema containing the data source. |
| DATASOURCE_NAME | VARCHAR(256) | Name of the data source subscribed through by the table. |
| DATASOURCE_KIND | VARCHAR(16) | Type of the data source; see CREATE DATA SOURCE for supported providers. |
| STATUS | VARCHAR(16) | Current status of the subscription through the data source. |
| JOBID | BIGINT | System job ID associated with the data source. |
| INFO | VARCHAR | JSON block of details about the data source. |
KI_LOAD_HISTORY
Each record in KI_LOAD_HISTORY represents a process that was invoked during either an import into or export from the database. Multiple processes may be invoked for a single data processing task.
| Column Name | Column Type | Description |
|---|---|---|
| TABLE_SCHEMA | VARCHAR(256) | Name of the schema containing the table involved in the data transfer operation. |
| TABLE_NAME | VARCHAR(256) | Name of the table involved in the data transfer operation. |
| DATASOURCE_SCHEMA | VARCHAR(256) | Name of the schema containing the data source or data sink referenced in DATASOURCE_NAME. |
| DATASOURCE_NAME | VARCHAR(256) | Name of the data source or data sink used for this data transfer (for those that make use of either). |
| USER_NAME | VARCHAR(256) | Name of the user who initiated the data transfer. |
| JOB_ID | BIGINT | Unique identifier of the system job responsible for the data transfer. |
| START_TIME | TIMESTAMP | Timestamp of data transfer process start. |
| END_TIME | TIMESTAMP | Timestamp of data transfer process end. |
| ROWS_PROCESSED | BIGINT | Number of records processed for this import/export. |
| ROWS_INSERTED | BIGINT | Number of records processed for this data transfer, totalling those that were either successfully imported into Kinetica or exported into a remote database (for imports of any kind and exports to tables). |
| ROWS_SKIPPED | BIGINT | Number of records processed for this import, totalling those that were skipped due to errors in the data (for imports). |
| EVENT_MESSAGE | VARCHAR | System messages relevant to the load process, including:
|
| NUM_ERROR | BIGINT | Number of errors encountered during processing. |
| FILE_NAME | VARCHAR(64) | Name of data file transferred, if any. |
| FILE_LINE_NUM | BIGINT | Not used. |
KI_QUERY_ACTIVE
Each record in KI_QUERY_ACTIVE represents the portion of a given query or REST endpoint call being run on a given worker rank at the time this catalog table was queried. It returns general information about the query portion running on the given worker rank. See KI_QUERY_WORKERS for worker-specific details.
Note
This table will show one record per rank involved in multi-head requests; and will show one record, assessed at rank1, for non-multi-head requests.
| Column Name | Column Type | Description | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| JOB_ID | BIGINT | Unique identifier of the system job responsible for the query. | ||||||||||||||||||||||||
| QUERY_ID | UUID | Unique identifier of the query being run. | ||||||||||||||||||||||||
| USER_NAME | VARCHAR(64) | Name of the user who initiated the query. | ||||||||||||||||||||||||
| RESOURCE_GROUP | VARCHAR(64) | Name of resource group governing this query's resource management. | ||||||||||||||||||||||||
| SOURCE_IP | VARCHAR(64) | IP address of user who initiated the query. | ||||||||||||||||||||||||
| ENDPOINT | VARCHAR(64) | REST endpoint called to initiate the request (for SQL-based requests). | ||||||||||||||||||||||||
| EXECUTION_STATUS | VARCHAR(32) | Status of the query processing.
| ||||||||||||||||||||||||
| ERROR_MESSAGE | VARCHAR | Error message associated with failed query. | ||||||||||||||||||||||||
| START_TIME | TIMESTAMP | Timestamp of query processing start. | ||||||||||||||||||||||||
| QUERY_TEXT | VARCHAR | SQL command issued (for SQL-based requests). | ||||||||||||||||||||||||
| USER_DATA | VARCHAR | Supplemental information about the request. | ||||||||||||||||||||||||
| SQL_STEP_COUNT | INTEGER | Step number in processing this SQL command (for SQL-based requests). | ||||||||||||||||||||||||
| REFRESH_ID | BIGINT | Unique identifier of the materialized view refresh being performed (for materialized view refreshes). | ||||||||||||||||||||||||
| IS_MH | BOOLEAN | Whether the job is a multi-head ingest request. | ||||||||||||||||||||||||
| IS_PERPETUAL | BOOLEAN | Whether the job has no fixed end time (e.g., Kafka streaming data feed).
| ||||||||||||||||||||||||
| IS_CANCELLABLE | BOOLEAN | Whether the job can be cancelled by a user. | ||||||||||||||||||||||||
| IS_USING_TIMEOUT | BOOLEAN | Whether the job can time out or not. | ||||||||||||||||||||||||
| SOURCE_RANK | INTEGER | Number of the rank associated with this job. |
KI_QUERY_HISTORY
Each record in KI_QUERY_HISTORY represents a single query or REST endpoint call made to the database. It will include all SQL queries, but not direct calls to DML-related endpoints, like:
- /delete/records
- /insert/records
- /insert/records/fromfiles
- /insert/records/frompayload
- /insert/records/fromquery
- /insert/records/json
- /insert/records/random
- /update/records
- /update/records/byseries
Note
This table will only show queries run by the user making the request, or all queries if the user has system administrator privilege.
| Column Name | Column Type | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| JOB_ID | BIGINT | Unique identifier of the system job responsible for the query. | ||||||||||
| QUERY_ID | UUID | Unique identifier of the query. | ||||||||||
| USER_NAME | VARCHAR(64) | Name of the user who initiated the query. | ||||||||||
| ENDPOINT | VARCHAR(64) | REST endpoint called to initiate the request (for SQL-based requests). | ||||||||||
| EXECUTION_STATUS | VARCHAR(32) | Final status of the query processing.
| ||||||||||
| ERROR_MESSAGE | VARCHAR | Error message associated with failed query. | ||||||||||
| QUERY_TEXT | VARCHAR | SQL command issued (for SQL-based requests). | ||||||||||
| USER_DATA | VARCHAR | Supplemental information about the request. | ||||||||||
| START_TIME | TIMESTAMP | Timestamp of query processing start. | ||||||||||
| STOP_TIME | TIMESTAMP | Timestamp of query processing stop. | ||||||||||
| SQL_STEP_COUNT | INTEGER | Step number in processing this SQL command (for SQL-based requests). | ||||||||||
| REFRESH_ID | BIGINT | Unique identifier of the materialized view refresh performed (for materialized view refreshes). | ||||||||||
| RESOURCE_GROUP | VARCHAR(64) | Name of resource group that governed this query's resource management. | ||||||||||
| SOURCE_IP | VARCHAR(64) | IP address of user who initiated the query. |
KI_QUERY_SPAN_METRICS
Each record in KI_QUERY_SPAN_METRICS represents an independent operation that was processed on a given rank in order to fulfill a given query request, along with the statistics collected for that operation. This table will have one entry for each rank involved in processing a given operation and the statistics collected for that rank, per step of the SQL executed.
Note
This table will only show queries run by the user making the request, or all queries if the user has system administrator privilege.
| Column Name | Column Type | Description |
|---|---|---|
| QUERY_ID | UUID | Unique identifier of the query request. See KI_QUERY_ACTIVE & KI_QUERY_HISTORY. |
| SPAN_ID | UNSIGNED BIGINT | Unique identifier of the metric span for this query. |
| PARENT_SPAN_ID | UNSIGNED BIGINT | Not used. |
| OPERATOR | VARCHAR(32) | Internal name for operation performed. Many operators will roughly match the name of the REST endpoints they serve. |
| SQL_STEP | INTEGER | Step number in processing this SQL command (for SQL-based requests). |
| METRIC_DATA | JSON | JSON block of collected metrics. |
| START_TIME | TIMESTAMP | Timestamp of operation start on the associated rank. |
| STOP_TIME | TIMESTAMP | Timestamp of operation stop on the associated rank. |
| SOURCE_RANK | INTEGER | Number of the rank on which this portion of the operation was processed and metrics collected. |
KI_QUERY_SPAN_METRICS_BY_SQL_STEP
Each record in KI_QUERY_SPAN_METRICS_BY_SQL_STEP represents the aggregated statistics of independent operations that were processed in order to fulfill a given step of a query request. This table will have one entry for each SQL step and the aggregate statistics collected on operations that occurred during that step.
Note
This table will only show queries run by the user making the request, or all queries if the user has system administrator privilege.
| Column Name | Column Type | Description |
|---|---|---|
| QUERY_ID | UUID | Unique identifier of the query request. See KI_QUERY_ACTIVE & KI_QUERY_HISTORY. |
| SQL_STEP | INTEGER | Step number in processing this SQL command (for SQL-based requests). |
| INS_LOCK_WAIT | VARCHAR | Total time waiting for locks to process inserts. |
| INSERT_WAIT | VARCHAR | Total time for processing inserts. |
| MEMORY_VRAM_NUM_EVICTIONS | VARCHAR | Total number of times data was evicted from the VRAM tier. |
| MEMORY_VRAM_PEAK_BYTES_USED | VARCHAR | Largest number of VRAM bytes used among operations in this step. |
| MEMORY_RAM_NUM_EVICTIONS | VARCHAR | Total number of times data was evicted from the RAM tier. |
| MEMORY_RAM_PEAK_BYTES_USED | VARCHAR | Largest number of RAM bytes used among operations in this step. |
| IO_DISK_CACHE_BYTES_READ | VARCHAR | Total number of bytes read from the disk cache tier. |
| IO_DISK_CACHE_BYTES_WRITTEN | VARCHAR | Total number of bytes written to the disk cache tier. |
| IO_PERSIST_NUM_EVICTIONS | VARCHAR | Total number of times data was evicted from the persist tier. |
| IO_PERSIST_BYTES_READ | VARCHAR | Total number of bytes read from the persist tier. |
| IO_PERSIST_BYTES_WRITTEN | VARCHAR | Total number of bytes written to the persist tier. |
| IO_COLD_BYTES_READ | VARCHAR | Total number of bytes read from the cold storage tier. |
| IO_COLD_BYTES_WRITTEN | VARCHAR | Total number of bytes written to the cold storage tier. |
KI_QUERY_WORKERS
Each record in KI_QUERY_WORKERS represents the portion of a given query or REST endpoint call being run on a given worker rank at the time this catalog table was queried. It returns worker-specific information about the query portion running on the given worker rank. See KI_QUERY_ACTIVE for general query processing details.
| Column Name | Column Type | Description |
|---|---|---|
| JOB_ID | BIGINT | Unique identifier of the system job responsible for the query. |
| WORKER_ID | BIGINT | Unique identifier of the worker job responsible for its portion of the query. |
| TYPE | VARCHAR(64) | Type of worker process handling this portion of the query. |
| INTERNAL_NAME | VARCHAR(64) | Internal name of worker process handling this portion of the query, if present. |
| TASK_INFO | VARCHAR(64) | Worker task-specific information. |
| THREAD_POOL | VARCHAR(16) | Type of thread pool being used to service this portion of the query. |
| STATUS | VARCHAR(16) | Status of the processing of this portion of the query--one of:
|
| BLOCKERS | VARCHAR(128) | Any dependencies that may be blocking the execution of this portion of the query. |
| SQL_STEP | INTEGER | Step number in processing this SQL command (for SQL-based requests). |
| FINAL_MSG_SET | BOOLEAN | Whether the final status has been recorded for this portion of the query. |
| RUNNING_TASK_COUNT | INTEGER | Number of running tasks relating to this portion of the query. |
| QUEUED_TASK_COUNT | INTEGER | Number of queued tasks relating to this portion of the query. |
| PENDING_ASYNC_TASKS | INTEGER | Number of asynchronous tasks not yet complete for this portion of the query. |
| ELAPSED_TIME_MS | BIGINT | Number of milliseconds that have passed since processing began on this portion of the query. |
| EXCEPTION | VARCHAR(256) | Any exception thrown during processing this portion of the query. |
| SOURCE_RANK | INTEGER | Number of the rank on which this portion of the query is being processed. |
MV_DEPENDENCIES
Each record in MV_DEPENDENCIES represents a dependent relationship, between a "source" object and an object that depends on it, that exists to support a materialized view; for example, a materialized view will depend on all of the tables & views referenced in its corresponding query.
| Column Name | Column Type | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| MV_SCHEMA | VARCHAR(256) | The name of the schema containing the materialized view supported by this dependent relationship. | ||||||||||||
| MV_NAME | VARCHAR(256) | The name of the materialized view supported by this dependent relationship. | ||||||||||||
| SOURCE_TABLE_SCHEMA | VARCHAR(256) | The name of the schema containing the object depended on by DEST_TABLE_NAME. | ||||||||||||
| SOURCE_TABLE_NAME | VARCHAR(256) | The name of the object depended on by DEST_TABLE_NAME. | ||||||||||||
| SOURCE_TABLE_KIND | VARCHAR(1) | Type of the source table depended on.
| ||||||||||||
| DEST_TABLE_SCHEMA | VARCHAR(256) | The name of the schema containing the object that depends on SOURCE_TABLE_NAME. | ||||||||||||
| DEST_TABLE_NAME | VARCHAR(256) | The name of the object that depends on SOURCE_TABLE_NAME. |
OBJECT_PRIVILEGES
Each record in OBJECT_PRIVILEGES represents the mapping of a user or role to a system-level or object-level permission they have.
| Column Name | Column Type | Description | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| GRANTOR | VARCHAR(256) | Not used. | ||||||||||||||||||||||
| GRANTEE | VARCHAR(256) | Name of the user or role associated with this mapping. | ||||||||||||||||||||||
| OBJECT_CATALOG | VARCHAR(8) | Always kinetica. | ||||||||||||||||||||||
| OBJECT_SCHEMA | VARCHAR(256) | Name of the schema containing the granted object associated with this mapping. | ||||||||||||||||||||||
| OBJECT_NAME | VARCHAR(256) | Name of the granted object associated with this mapping. | ||||||||||||||||||||||
| OBJECT_TYPE | VARCHAR(256) | Type of object grant this mapping represents (for object-level permissions).
| ||||||||||||||||||||||
| PRIVILEGE_TYPE | VARCHAR(256) | Type of permission grant this mapping represents. | ||||||||||||||||||||||
| IS_GRANTABLE | BOOLEAN | Not used. | ||||||||||||||||||||||
| WITH_HIERARCHY | BOOLEAN | Not used. | ||||||||||||||||||||||
| CREATED | BIGINT | Not used. |
REFERENTIAL_CONSTRAINTS
Each record in REFERENTIAL_CONSTRAINTS represents a foreign key relationship, regardless of the number of columns involved in the key (whether a composite foreign key or not).
The table information in REFERENTIAL_CONSTRAINTS refers to the target side of the relation (the foreign table referenced by the key).
| Column Name | Column Type | Description |
|---|---|---|
| CONSTRAINT_CATALOG | VARCHAR(8) | Always kinetica. |
| CONSTRAINT_SCHEMA | VARCHAR(256) | The name of the schema containing the table on the source side of this constraint. |
| CONSTRAINT_NAME | VARCHAR(256) | Name of this constraint. |
| UNIQUE_CONSTRAINT_CATALOG | VARCHAR(8) | Always kinetica. |
| UNIQUE_CONSTRAINT_SCHEMA | VARCHAR(256) | The name of the schema containing the foreign table referenced by this constraint. |
| UNIQUE_CONSTRAINT_NAME | VARCHAR(256) | The auto-generated name of the primary key constraint that this foreign key references. |
| MATCH_OPTION | VARCHAR(8) | Always FULL. |
| UPDATE_RULE | VARCHAR(16) | Always NO ACTION. |
| DELETE_RULE | VARCHAR(16) | Always NO ACTION. |
| COMMENT | VARCHAR | Not used. |
| CREATED | TIMESTAMP | Timestamp of constraint creation. |
| LAST_ALTERED | TIMESTAMP | Timestamp of the most recent constraint update (initially, time of creation). |
ROLE_TABLE_GRANTS
This is effectively an alias for the TABLE_PRIVILEGES table, as that table has only an additional CREATED column, which is presently unused.
SCHEMATA
Each record in SCHEMATA represents a schema in the database.
| Column Name | Column Type | Description |
|---|---|---|
| CATALOG_NAME | VARCHAR(8) | Always kinetica. |
| SCHEMA_NAME | VARCHAR(256) | Name of this schema. |
| SCHEMA_OWNER | VARCHAR(256) | Username of user who created this schema. |
| IS_TRANSIENT | BOOLEAN | Not used. |
| RETENTION_TIME | BIGINT | Not used. |
| DEFAULT_CHARACTER_SET_CATALOG | VARCHAR(256) | Not used. |
| DEFAULT_CHARACTER_SET_SCHEMA | VARCHAR(256) | Not used. |
| DEFAULT_CHARACTER_SET_NAME | VARCHAR(256) | Not used. |
| SQL_PATH | VARCHAR(256) | Not used. |
| CREATED | TIMESTAMP | Timestamp of schema creation. |
| LAST_ALTERED | TIMESTAMP | Timestamp of the most recent schema modification (initially, time of creation). |
| COMMENT | VARCHAR | User comments associated with this schema. |
TABLE_CONSTRAINTS
Each record in TABLE_CONSTRAINTS represents a primary key or a foreign key relationship, regardless of the number of columns involved in the key (whether a composite primary/foreign key or not).
For foreign keys, the table information in TABLE_CONSTRAINTS refers to the source side of the relation (where the key is defined).
| Column Name | Column Type | Description | ||||||
|---|---|---|---|---|---|---|---|---|
| CONSTRAINT_CATALOG | VARCHAR(8) | Always kinetica. | ||||||
| CONSTRAINT_SCHEMA | VARCHAR(256) | The name of the schema containing the table containing this constraint. | ||||||
| CONSTRAINT_NAME | VARCHAR(256) | Name of this constraint. | ||||||
| TABLE_CATALOG | VARCHAR(8) | Always kinetica. | ||||||
| TABLE_SCHEMA | VARCHAR(256) | The name of the schema containing the table containing this constraint. | ||||||
| TABLE_NAME | VARCHAR(256) | The name of the table containing this constraint. | ||||||
| CONSTRAINT_TYPE | VARCHAR(16) | Type of constraint; one of:
| ||||||
| IS_DEFERRABLE | BOOLEAN | Whether the constraint is deferrable.
| ||||||
| INITIALLY_DEFERRED | BOOLEAN | Whether the constraint is initially deferred.
| ||||||
| ENFORCED | BOOLEAN | Whether the constraint is enforced.
| ||||||
| COMMENT | VARCHAR | Not used. | ||||||
| CREATED | TIMESTAMP | Timestamp of constraint creation. | ||||||
| LAST_ALTERED | TIMESTAMP | Timestamp of the most recent constraint update (initially, time of creation). |
TABLE_PRIVILEGES
Each record in TABLE_PRIVILEGES represents the mapping of a user or role to a table-level permission they have.
| Column Name | Column Type | Description |
|---|---|---|
| GRANTOR | VARCHAR(256) | Not used. |
| GRANTEE | VARCHAR(256) | Name of the user or role associated with this mapping. |
| TABLE_CATALOG | VARCHAR(8) | Always kinetica. |
| TABLE_SCHEMA | VARCHAR(256) | Name of the schema containing the granted object associated with this mapping. |
| TABLE_NAME | VARCHAR(256) | Name of the granted object associated with this mapping. |
| PRIVILEGE_TYPE | VARCHAR(256) | Type of permission grant this mapping represents. |
| IS_GRANTABLE | BOOLEAN | Not used. |
| WITH_HIERARCHY | BOOLEAN | Not used. |
| CREATED | TIMESTAMP | Not used. |
TABLES
Each record in TABLES represents a single table or view, within the database, including those in the virtual catalog tables.
| Column Name | Column Type | Description | ||||||
|---|---|---|---|---|---|---|---|---|
| TABLE_CATALOG | VARCHAR(8) | Always kinetica. | ||||||
| TABLE_SCHEMA | VARCHAR(256) | Name of the schema containing this table or view. | ||||||
| TABLE_NAME | VARCHAR(256) | Name of this table or view. | ||||||
| TABLE_OWNER | VARCHAR(256) | Username of user who created this table or view. | ||||||
| TABLE_TYPE | VARCHAR(16) | Table/view type.
| ||||||
| SELF_REFERENCING_COLUMN_NAME | VARCHAR(256) | Not used. | ||||||
| IS_TRANSIENT | BOOLEAN | Not used. | ||||||
| ROW_COUNT | INTEGER | Not used. | ||||||
| RETENTION_TIME | BIGINT | Time-to-live for this table/view; -9999 if no timeout exists. | ||||||
| BYTES | INTEGER | Not used. | ||||||
| REFERENCE_GENERATION | VARCHAR(256) | Not used. | ||||||
| USER_DEFINED_TYPE_CATALOG | VARCHAR(256) | Not used. | ||||||
| USER_DEFINED_TYPE_SCHEMA | VARCHAR(256) | Not used. | ||||||
| USER_DEFINED_TYPE_NAME | VARCHAR(256) | Not used. | ||||||
| IS_INSERTABLE_INTO | VARCHAR(256) | Not used. | ||||||
| IS_TYPED | VARCHAR(256) | Not used. | ||||||
| COMMIT_ACTION | VARCHAR(256) | Not used. | ||||||
| CREATED | TIMESTAMP | Timestamp of table/view creation. | ||||||
| LAST_ALTERED | TIMESTAMP | Timestamp of the most recent table/view definition update (initially, time of creation). | ||||||
| COMMENT | VARCHAR | User comments associated with this table/view. |
VIEWS
Each record in VIEWS represents a single logical view within the database, including those in the virtual catalog tables.
| Column Name | Column Type | Description |
|---|---|---|
| TABLE_CATALOG | VARCHAR(8) | Always kinetica. |
| TABLE_SCHEMA | VARCHAR(256) | Name of the schema containing this view. |
| TABLE_NAME | VARCHAR(256) | Name of this view. |
| TABLE_OWNER | VARCHAR(256) | Username of user who created this view. |
| VIEW_DEFINITION | VARCHAR(256) | Query that backs this view. |
| CHECK_OPTION | BOOLEAN | Not used. |
| IS_UPDATABLE | BOOLEAN | Not used. |
| INSERTABLE_INTO | BOOLEAN | Not used. |
| CREATED | TIMESTAMP | Timestamp of view creation. |
| LAST_ALTERED | TIMESTAMP | Timestamp of the most recent view definition update (initially, time of creation). |
| COMMENT | VARCHAR | User comments associated with this view. |
