> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Information Schema export const Ocient = "Ocient®"; The system catalog exposes a set of system views in the `information_schema` schema that contain metadata about the {Ocient} System. Most of these views follow the SQL standard with additional views specific to the system. ## Alphabetical List of Views * `information_schema.columns` * `information_schema.data_types` * `information_schema.databases` * `information_schema.geometry_columns` * `information_schema.groups` * `information_schema.index_recommendations` * `information_schema.indexes` * `information_schema.information_schema_catalog_name` * `information_schema.nodes` * `information_schema.pipeline_status` * `information_schema.pipeline_status_historical` * `information_schema.pipeline_table_metrics` * `information_schema.pipelines` * `information_schema.pipelines_historical` * `information_schema.reserved_words` * `information_schema.schemata` * `information_schema.table_privileges` * `information_schema.table_storage` * `information_schema.tables` * `information_schema.transactional_pipeline_status` * `information_schema.users` * `information_schema.views` ## System View Descriptions #### information\_schema.columns The `columns` view shows information for all columns in the system. | Column Name | Column Type | Column Description | | ----------------- | ----------- | -------------------------------------------------------- | | table\_catalog | VARCHAR | Name of the database. | | table\_schema | VARCHAR | Name of the schema. | | table\_name | VARCHAR | Name of the table. | | column\_name | VARCHAR | Name of the column. | | ordinal\_position | BIGINT | Ordinal of the column with respect to its table. | | data\_type | VARCHAR | Data type of the column (INT, CHAR, BOOLEAN, etc.). | | is\_nullable | VARCHAR | Specifies whether the values in this column can be NULL. | | column\_default | VARCHAR | The default expression of this column, if it exists. | #### information\_schema.data\_types The `data_types` view shows all data types in the system. | Column Name | Column Type | Column Description | | ----------- | ----------- | ------------------ | | data\_type | VARCHAR | Data type. | #### information\_schema.databases The `databases` view shows all databases where the current user has access. | Column Name | Column Type | Column Description | | -------------- | ----------- | ------------------------------------------------------- | | database\_name | VARCHAR | Name of the database. | | created\_at | TIMESTAMP | Timestamp that specifies when the database was created. | #### information\_schema.geometry\_columns The `geometry_columns` view shows information about all geometry type columns in the system. | Column Name | Column Type | Column Description | | ------------------- | ----------- | ----------------------------------------------------------- | | f\_table\_catalog | VARCHAR | Name of the database. | | f\_table\_schema | VARCHAR | Name of the schema. | | f\_table\_name | VARCHAR | Name of the table. | | f\_geometry\_column | VARCHAR | Name of the column. | | coord\_dimension | INT | The coordinate dimension. | | srid | INT | The ID of the spatial reference system. | | type | VARCHAR | Data type of the column (POINT, LINESTRING, POLYGON, etc.). | #### information\_schema.groups The `groups` view shows all groups in the system. | Column Name | Column Type | Column Description | | -------------- | ----------- | --------------------- | | database\_name | VARCHAR | Name of the database. | | group\_name | VARCHAR | Name of the group. | #### information\_schema.index\_recommendations This view contains index recommendations. | Column Name | Column Type | Column Description | | ------------ | ----------- | ------------------------------------------------------ | | table\_name | VARCHAR | Name of the table. | | column\_name | VARCHAR | Name of the column. | | sql | VARCHAR | SQL statement to create or drop the recommended index. | #### information\_schema.indexes The `indexes` view shows all indexes used in the system. | Column Name | Column Type | Column Description | | -------------- | ----------- | --------------------- | | table\_catalog | VARCHAR | Name of the database. | | table\_schema | VARCHAR | Name of the schema. | | table\_name | VARCHAR | Name of the table. | | index\_name | VARCHAR | Name of the index. | | index\_type | VARCHAR | Type of the index. | | column\_name | VARCHAR | Name of the column. | #### information\_schema.information\_schema\_catalog\_name The `information_schema_catalog_name` view shows the current database. | Column Name | Column Type | Column Description | | ------------- | ----------- | --------------------- | | catalog\_name | VARCHAR | Name of the database. | #### information\_schema.nodes The `nodes` view shows all nodes defined in the system. | Column Name | Column Type | Column Description | | -------------------- | ----------- | ---------------------------------------------------------------------------------------------------------- | | name | VARCHAR | Name of the node. | | service\_role\_types | VARCHAR\[] | The type of service roles on this node. | | operational\_status | VARCHAR | The operational status of the node. Values are ACTIVE, STARTING, STOPPING, ERROR, UNKNOWN, or UNREACHABLE. | | software\_version | VARCHAR | Version of the software running on this node. | #### information\_schema.pipeline\_status To view dynamic information about the status of the pipeline, query from information\_schema.pipeline\_status, or you can use the SHOW PIPELINE\_STATUS command. | Column Name | Column Type | Column Description | | ------------------ | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | database\_name | VARCHAR | Name of the database where the pipeline was created. | | pipeline\_name | VARCHAR | The name of this pipeline. | | table\_names | VARCHAR\[] | An array of fully-qualified table names where the pipeline loads data. | | status | VARCHAR | Status of the pipeline (RUNNING, STOPPED, COMPLETED, FAILED). | | status\_message | VARCHAR | The corresponding event message of the last event that the Ocient System sees for this pipeline from the event\_message column of the sys.pipeline\_events system catalog table. | | duration\_seconds | INT | Duration, in seconds, of how long the pipeline has been running. | | files\_processed | BIGINT | The number of files that have been processed for file-based loads. (includes files with these statuses: LOADED, LOADED\_WITH\_ERRORS, and SKIPPED) | | files\_failed | BIGINT | The number of files that have failed and have a file status of FAILED for file-based loads. | | files\_remaining | BIGINT | The number of remaining files for file-based loads. (includes these file statuses: PENDING, QUEUED, and LOADING) | | files\_total | BIGINT | The total number of files for file-based loads. | | fraction\_complete | FLOAT | The estimated fraction completion as a value from 0.0 to 1.0 for file-based batch or transactional loads. The calculation is files processed or failed divided by the total files. | | records\_processed | BIGINT | The number of records that the pipeline has processed. The pipeline attempts to process and load a record. This number can be greater than the value of records\_loaded due to the deduplication of records that the system processes twice or where record-level errors occur. | | records\_loaded | BIGINT | The number of records that have successfully loaded into the system. This number reflects the records in the system and does not include records that failed to load or were eliminated as duplicates. | | records\_failed | BIGINT | Number of records that have failed. | #### information\_schema.pipeline\_status\_historical To view information about the status of dropped pipelines, query from information\_schema.pipeline\_status\_historical. This system view is the historical equivalent of information\_schema.pipeline\_status. | Column Name | Column Type | Column Description | | ------------------ | ----------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | database\_name | VARCHAR | Name of the database where the pipeline was created. | | pipeline\_name | VARCHAR | The name of this pipeline. | | status | VARCHAR | Status of the pipeline (RUNNING, STOPPED, COMPLETED, FAILED). | | status\_message | VARCHAR | The corresponding event message of the last event that the Ocient System sees for this pipeline from the `event_message` column of the `sys.pipeline_events` system catalog table. | | duration\_seconds | INT | Duration, in seconds, of how long the pipeline ran. | | files\_processed | BIGINT | The number of files that have been processed for file-based loads. (includes files with these statuses: LOADED, LOADED\_WITH\_ERRORS, and SKIPPED) | | files\_failed | BIGINT | The number of files that have failed and have a file status of FAILED for file-based loads. | | files\_remaining | BIGINT | The number of remaining files for file-based loads. (includes PENDING, QUEUED, and LOADING status files) | | files\_total | BIGINT | The total number of files for file-based loads. | | fraction\_complete | FLOAT | The estimated fraction completion as a value from 0.0 to 1.0 for file-based batch or transactional loads. The calculation is files processed or failed divided by the total files. | | records\_processed | BIGINT | The number of records that the pipeline has processed. | | records\_loaded | BIGINT | The number of records that have successfully loaded into the system. | | records\_failed | BIGINT | Number of records that have failed. | | pipeline\_id | UUID | Universally Unique IDentifier (UUID) of the pipeline. | #### information\_schema.pipeline\_table\_metrics To view dynamic information about the metrics specific to each of the target tables of the pipeline, query from the information\_schema.pipeline\_table\_metrics view. | Column Name | Column Type | Column Description | | -------------------- | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | database\_name | VARCHAR | The name of the database where the pipeline was created. | | pipeline\_name | VARCHAR | The name of this pipeline. | | table\_name | VARCHAR | The fully-qualified name of the table where the pipeline loads data. | | records\_processed | BIGINT | The number of records that the pipeline has processed for loading into the table. The pipeline attempts to process and load a record. This number can be greater than the value of the records\_loaded column due to the deduplication of records that the system processes twice or where the record-level errors occur. | | records\_loaded | BIGINT | The number of records the Ocient System has successfully loaded into the table. This number reflects the records in the table and does not include records that the pipeline failed to load or eliminated as duplicates. | | records\_failed | BIGINT | The number of records the pipeline failed to load into the table. This number does not include records the pipeline failed to load to all tables, such as failed records during extraction. | | processing\_duration | BIGINT | Duration, in milliseconds, indicating how long the pipeline has been processing records for the table. | | loading\_duration | BIGINT | Duration, in milliseconds, indicating how long the pipeline has been loading records into the table. | | bytes\_processed | BIGINT | Estimate of the amount of source data, in bytes, that the pipeline has processed for the table. | #### information\_schema.pipelines To view static information that stays the same after the pipeline has been created, query from information\_schema.pipelines, or you can use the SHOW PIPELINES command. The columns in the information\_schema.pipelines view derive from the underlying sys.pipelines system catalog table. | Column Name | Column Type | Column Description | | --------------- | ----------- | ---------------------------------------------------------------------------------------------------------------------------------- | | database\_name | VARCHAR | Name of the database where the pipeline was created. | | pipeline\_name | VARCHAR | Name of the pipeline. | | loading\_mode | VARCHAR | Specifies whether the pipeline runs in a one-time (BATCH), one-time transactional (TRANSACTIONAL), or continuous (CONTINUOUS) way. | | source\_type | VARCHAR | Source of the data (S3, KAFKA, FILESYSTEM). | | status | VARCHAR | Status of the pipeline (RUNNING, STOPPED, COMPLETED, FAILED). | | data\_format | VARCHAR | Structure of the data (DELIMITED, CSV, JSON, PARQUET). | | table\_names | VARCHAR\[] | An array of fully-qualified table names where the pipeline loads data. | | created\_at | TIMESTAMP | Timestamp of when the pipeline was created. | | altered\_at | TIMESTAMP | Timestamp that indicates when the pipeline was last altered. | | creator\_id | UUID | Universally Unique IDentifier (UUID) of the creator of the pipeline. | | transaction\_id | UUID | The UUID of the transaction scope if this pipeline runs in a one-time transactional way. | #### information\_schema.pipelines\_historical To view static information about pipelines that have been dropped from the system, query from the `information_schema.pipelines_historical` system view. This view is the historical equivalent of the `information_schema.pipelines` view. | Column Name | Column Type | Column Description | | ------------------ | ----------- | ---------------------------------------------------------------------------------------------------------------------------------- | | database\_name | VARCHAR | Name of the database where the pipeline was created. | | pipeline\_name | VARCHAR | The name of this pipeline. | | loading\_mode | VARCHAR | Specifies whether the pipeline runs in a one-time (BATCH), one-time transactional (TRANSACTIONAL), or continuous (CONTINUOUS) way. | | source\_type | VARCHAR | Source of the data (S3, KAFKA, FILESYSTEM). | | status | VARCHAR | Status of the pipeline (RUNNING, STOPPED, COMPLETED, FAILED). | | data\_format | VARCHAR | Structure of the data (CSV, DELIMITED, JSON, BINARY, PARQUET, XML, ASN1, AVRO). | | created\_at | TIMESTAMP | Timestamp that indicates when this pipeline was created. | | altered\_at | TIMESTAMP | Timestamp that indicates when this pipeline was last updated. | | creator\_id | UUID | The Universally Unique IDentifier (UUID) of the user or group who created the pipeline (sys.users/sys.groups). | | transaction\_id | UUID | The UUID of the transaction scope if this pipeline runs in a one-time transactional way. | | dropped\_at | TIMESTAMP | Timestamp that indicates when the pipeline was dropped. | | rolehostd\_version | VARCHAR | The rolehostd version in which this pipeline was created. | | pipeline\_id | UUID | UUID of the pipeline. | #### information\_schema.reserved\_words The `reserved_words` view shows all reserved words in the system. | Column Name | Column Type | Column Description | | -------------- | ----------- | ---------------------------------------------------- | | reserved\_word | VARCHAR | A word that is a reserved keyword for use by Ocient. | #### information\_schema.schemata The `schemata` view shows information for all schemas in the system. | Column Name | Column Type | Column Description | | ------------- | ----------- | --------------------- | | schema\_name | VARCHAR | Name of the schema. | | catalog\_name | VARCHAR | Name of the database. | #### information\_schema.table\_privileges The `table_privileges` view shows information for all table privileges in the system. | Column Name | Column Type | Column Description | | --------------- | ----------- | ---------------------------------------------------------- | | grantor | VARCHAR | The user who granted this privilege. | | grantee | VARCHAR | The user who received this privilege. | | table\_catalog | VARCHAR | Name of the database. | | table\_schema | VARCHAR | Name of the schema. | | table\_name | VARCHAR | Name of the table. | | privilege\_type | VARCHAR | The privilege for the grant. | | is\_grantable | VARCHAR | Whether the user can grant this privilege to another user. | #### information\_schema.table\_storage The `tables_storage` view shows information about the storage used by tables. | Column Name | Column Type | Column Description | | ------------------- | ----------- | ------------------------------------------------- | | table\_catalog | VARCHAR | Name of the database. | | table\_schema | VARCHAR | Name of the schema. | | table\_name | VARCHAR | Name of the table. | | row\_count | BIGINT | The number of rows in the table. | | deleted\_row\_count | BIGINT | The number of deleted rows in the table. | | segment\_count | BIGINT | The number of segments that constitute the table. | | size | BIGINT | The size of the table in bytes. | #### information\_schema.tables The `tables` view shows information for all tables and views in the system, including user-defined and system objects. | Column Name | Column Type | Column Description | | -------------------- | ----------- | ----------------------------------------------------------------- | | table\_catalog | VARCHAR | Name of the database. | | table\_schema | VARCHAR | Name of the schema. | | table\_name | VARCHAR | Name of the table. | | table\_type | VARCHAR | Type of the table. | | is\_insertable\_into | VARCHAR | Whether an INSERT command can target this table. | | created\_at | TIMESTAMP | Timestamp that represents when the table was created. | | creator\_id | UUID | Universally Unique IDentifier (UUID) of the creator of the table. | #### information\_schema.transactional\_pipeline\_status To view information about the status of transactional pipelines (both active and dropped), query from information\_schema.transactional\_pipeline\_status. This system view combines information\_schema.pipeline\_status and information\_schema.pipeline\_status\_historical, filtered to pipelines with a non-NULL `transaction_id` column. | Column Name | Column Type | Column Description | | ------------------ | ----------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | transaction\_id | UUID | The Universally Unique IDentifier (UUID) of the transaction scope for this pipeline. | | database\_name | VARCHAR | Name of the database where the pipeline was created. | | pipeline\_name | VARCHAR | The name of this pipeline. | | table\_names | VARCHAR\[] | An array of fully-qualified table names where the pipeline loads data. This value is NULL for dropped pipelines. | | status | VARCHAR | Status of the pipeline (RUNNING, STOPPED, COMPLETED, FAILED). | | status\_message | VARCHAR | The corresponding event message of the last event that the Ocient System sees for this pipeline from the `event_message` column of the `sys.pipeline_events` system catalog table. | | duration\_seconds | INT | Duration, in seconds, of how long the pipeline has been running. | | files\_processed | BIGINT | The number of files that have been processed for file-based loads. (includes files with these statuses: LOADED, LOADED\_WITH\_ERRORS, and SKIPPED) | | files\_failed | BIGINT | The number of files that have failed and have a file status of FAILED for file-based loads. | | files\_remaining | BIGINT | The number of remaining files for file-based loads. (includes PENDING, QUEUED, and LOADING status files) | | files\_total | BIGINT | The total number of files for file-based loads. | | fraction\_complete | FLOAT | The estimated fraction completion as a value from 0.0 to 1.0 for file-based batch or transactional loads. The calculation is files processed or failed divided by the total files. | | records\_processed | BIGINT | The number of records that the pipeline has processed. | | records\_loaded | BIGINT | The number of records that have successfully loaded into the system. | | records\_failed | BIGINT | Number of records that have failed. | | pipeline\_id | UUID | UUID of the pipeline. | #### information\_schema.users The `users` view shows all users in the system. | Column Name | Column Type | Column Description | | -------------- | ----------- | --------------------- | | database\_name | VARCHAR | Name of the database. | | user\_name | VARCHAR | Username of the user. | #### information\_schema.views The `views` view shows information for all user-defined and system views in the system. | Column Name | Column Type | Column Description | | ---------------- | ----------- | ---------------------------------------------------------------------------- | | table\_catalog | VARCHAR | Name of the database. | | table\_schema | VARCHAR | Name of the schema. | | table\_name | VARCHAR | Name of the table. | | view\_definition | VARCHAR | Query used to generate the view content. | | created\_at | TIMESTAMP | Timestamp that represents the date and time for the creation of the view. | | updated\_at | TIMESTAMP | Timestamp that represents the date and time for the last update of the view. | | creator\_id | UUID | Universally Unique IDentifier (UUID) of the creator of the view. | ## SHOW Commands The `SHOW` commands are additional commands you can use to retrieve metadata about the system. These commands are tightly coupled with the `information_schema` schema and offer a more user-friendly syntax alternative. Sometimes, unlike their `information_schema` counterparts, `SHOW` commands only return user-defined objects. | SHOW Command | Description | | ---------------------- | -------------------------------------------------------------------------------------------------------- | | SHOW COLUMNS | This statement returns columns from user-defined tables and views where the current user has access. | | SHOW DATA\_TYPES | This statement returns all data types in the system. | | SHOW DATABASES | This statement returns all databases where the current user has access. | | SHOW GEOMETRY\_COLUMNS | This statement returns all geometry type columns where the current user has access. | | SHOW GROUPS | This statement returns all groups in the system where the current user has read access. | | SHOW INDEXES | This statement returns all indexes on user-defined tables where the current user has access. | | SHOW NODES | This statement returns all nodes in the system where the current user has read access. | | SHOW PIPELINE\_STATUS | This statement returns the status of all pipelines in the system where the current user has read access. | | SHOW PIPELINES | This statement returns all pipelines in the system where the current user has read access. | | SHOW RESERVED\_WORDS | This statement returns all reserved words in the system. | | SHOW SCHEMATA | This statement returns all user-defined schemas where the current user has access. | | SHOW SYSTEM TABLES | This statement returns all system tables in the `sys` or `information_schema` schemas. | | SHOW TABLE\_PRIVILEGES | This statement returns all table privileges for tables where the current user has access. | | SHOW TABLES | This statement returns all user-defined tables where the current user has access. | | SHOW USERS | This statement returns all users in the system where the current user has read access. | | SHOW VIEWS | This statement returns all user-defined views where the current user has access. | ## Related Links [SQL Reference](/sql-reference) [Database Administration](/database-administration) [System Catalog](/system-catalog)