Information Schema - Ocient Documentation

The system catalog exposes a set of system views in the information_schema schema that contain metadata about the 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.

SQL Reference Database Administration System Catalog

​Alphabetical List of Views

​System View Descriptions

​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

​SHOW Commands

​Related Links