> For clean Markdown of any page, append .md to the page URL. > For a complete documentation index, see https://ocient.ferndocs.com/llms.txt. > For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://ocient.ferndocs.com/_mcp/server. # Information Schema 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_table_metrics` * `information_schema.pipelines` * `information_schema.reserved_words` * `information_schema.schemata` * `information_schema.table_privileges` * `information_schema.table_storage` * `information_schema.tables` * `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 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_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) 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. | ### 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.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**](https://docs.ocient.com/sql-reference) [**Database Administration**](https://docs.ocient.com/database-administration) [**System Catalog**](https://docs.ocient.com/system-catalog)