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 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 char name of the database table schema char name of the schema table name char name of the table column name char name of the column ordinal position long ordinal of the column with respect to its table data type char data type of the column (int, char, boolean, etc ) is nullable char specifies whether the values in this column can be null column default char 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 char 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 char 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 char name of the database f table schema char name of the schema f table name char name of the table f geometry column char name of the column coord dimension int the coordinate dimension srid int the id of the spatial reference system type char 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 char name of the database group name char name of the group information schema index recommendations this view contains index recommendations column name column type column description table name char name of the table column name char name of the column sql char 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 char name of the database table schema char name of the schema table name char name of the table index name char name of the index index type char type of the index column name char 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 char name of the database information schema nodes the nodes view shows all nodes defined in the system column name column type column description name char name of the node service role types array(char) the type of service roles on this node operational status char the operational status of the node values are active, starting, stopping, error, unknown, or unreachable software version char 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 char name of the database where the pipeline was created pipeline name char the name of this pipeline table names array(char) an array of fully qualified table names where the pipeline loads data this value is always an array with one element status char status of the pipeline (running, stopped, completed, failed) status message char 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 long 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 long the number of files that have failed and have a file status of failed for file based loads files remaining long the number of remaining files for file based loads (includes these file statuses pending, queued, and loading) files total long 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 long 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 long 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 long number of records that have failed 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 char name of the database where the pipeline was created pipeline name char name of the pipeline loading mode char specifies whether the pipeline runs in a one time (batch) or continuous (continuous) way source type char source of the data (s3, kafka, filesystem) data format char structure of the data (delimited, csv, json, parquet) table names array(char) an array of fully qualified table names where the pipeline loads data this value is always an array with one element 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 char 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 char name of the schema catalog name char 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 char the user who granted this privilege grantee char the user who received this privilege table catalog char name of the database table schema char name of the schema table name char name of the table privilege type char the privilege for the grant is grantable char 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 char name of the database table schema char name of the schema table name char name of the table row count long the number of rows in the table deleted row count long the number of deleted rows in the table segment count long the number of segments that constitute the table size long 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 char name of the database table schema char name of the schema table name char name of the table table type char type of the table is insertable into char 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 char name of the database user name char 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 char name of the database table schema char name of the schema table name char name of the table view definition char 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 https //docs ocient com/sql reference https //docs ocient com/database administration https //docs ocient com/system catalog