Catalog Tables

The sqream_catalog includes the following tables:

Clustering Keys

The clustering_keys data object is used for explicit clustering keys for tables. If you define more than one clustering key, each key is listed in a separate row, and is described in the following table:

Column

Description

database_name

Shows the name of the database containing the table.

table_id

Shows the ID of the table containing the column.

schema_name

Shows the name of the schema containing the table.

table_name

Shows the name of the table containing the column.

clustering_key

Shows the name of the column used as a clustering key for this table.

Columns

The Columns database object shows the following tables:

Columns

The column data object is used with standard tables and is described in the following table:

Column

Description

database_name

Shows the name of the database containing the table.

schema_name

Shows the name of the schema containing the table.

table_id

Shows the ID of the table containing the column.

table_name

Shows the name of the table containing the column.

column_id

Shows the ordinal number of the column in the table (begins at 0).

column_name

Shows the column’s name.

type_name

Shows the column’s data type. For more information see Supported Data Types.

column_size

Shows the maximum length in bytes.

has_default

Shows NULL if the column has no default value, 1 if the default is a fixed value, or 2 if the default is an identity. For more information, see Identity.

default_value

Shows the column’s default value. For more information, see Default Value Constraints.

compression_strategy

Shows the compression strategy that a user has overridden.

created

Shows the timestamp displaying when the column was created.

altered

Shows the timestamp displaying when the column was last altered.

External Table Columns

The external_table_columns is used for viewing data from foreign tables.

For more information on foreign tables, see CREATE FOREIGN TABLE.

Databases

The databases data object is used for displaying database information, and is described in the following table:

Column

Description

database_Id

Shows the database’s unique ID.

database_name

Shows the database’s name.

default_disk_chunk_size

Reserved for internal use.

default_process_chunk_size

Reserved for internal use.

rechunk_size

Reserved for internal use.

storage_subchunk_size

Reserved for internal use.

compression_chunk_size_threshold

Reserved for internal use.

Parameters

The parameters object is used for displaying all flags, providing the scope (default, cluster and session), description, default value and actual value.

Column

Description

flag_name

Shows the flag name

value

Shows the current flag configured value

default_value

Shows the flag default value

scope

Shows whether flag configuration is session-based or cluster-based

description

Describes the purpose of the flag

Permissions

The permissions data object is used for displaying permission information, such as roles (also known as grantees), and is described in the following tables:

Permission Types

The permission_types object identifies the permission names existing in the database.

Column

Description

permission_type_id

Shows the permission type’s ID.

name

Shows the name of the permission type.

Default Permissions

The commands included in the Default Permissions section describe how to check the following default permissions:

Default Table Permissions

The sqream_catalog.table_default_permissions command shows the columns described below:

Column

Description

database_name

Shows the database that the default permission rule applies to.

schema_id

Shows the schema that the rule applies to, or NULL if the ALTER statement does not specify a schema.

modifier_role_id

Shows the role to apply the rule to.

getter_role_id

Shows the role that the permission is granted to.

permission_type

Shows the type of permission granted.

Default Schema Permissions

The sqream_catalog.schema_default_permissions command shows the columns described below:

Column

Description

database_name

Shows the database that the default permission rule applies to.

modifier_role_id

Shows the role to apply the rule to.

getter_role_id

Shows the role that the permission is granted to.

permission_type

Shows the type of permission granted.

getter_role_type

Shows the type of role that is granted permissions.

For an example of using the sqream_catalog.table_default_permissions command, see Granting Default Table Permissions.

Table Permissions

The table_permissions data object identifies all permissions granted to tables. Each role-permission combination displays one row.

The following table describes the table_permissions data object:

Column

Description

database_name

Shows the name of the database containing the table.

table_id

Shows the ID of the table the permission applies to.

role_id

Shows the ID of the role granted permissions.

permission_type

Identifies the permission type.

Database Permissions

The database_permissions data object identifies all permissions granted to databases. Each role-permission combination displays one row.

The following table describes the database_permissions data object:

Column

Description

database_name

Shows the name of the database the permission applies to

role_id

Shows the ID of the role granted permissions.

permission_type

Identifies the permission type.

Schema Permissions

The schema_permissions data object identifies all permissions granted to schemas. Each role-permission combination displays one row.

The following table describes the schema_permissions data object:

Column

Description

database_name

Shows the name of the database containing the schema.

schema_id

Shows the ID of the schema the permission applies to.

role_id

Shows the ID of the role granted permissions.

permission_type

Identifies the permission type.

Queries

The savedqueries data object identifies the saved queries in the database, as shown in the following table:

Column

Description

name

Shows the saved query name.

num_parameters

Shows the number of parameters to be replaced at run-time.

For more information, see Saved Queries.

Roles

The roles data object is used for displaying role information, and is described in the following tables:

Roles

The roles data object identifies the roles in the database, as shown in the following table:

Column

Description

role_id

Shows the role’s database-unique ID.

name

Shows the role’s name.

superuser

Identifies whether the role is a superuser (1 - superuser, 0 - regular user).

login

Identifies whether the role can be used to log in to SQream (1 - yes, 0 - no).

has_password

Identifies whether the role has a password (1 - yes, 0 - no).

Role Memberships

The roles_memberships data object identifies the role memberships in the database, as shown below:

Column

Description

role_id

Shows the role ID.

member_role_id

Shows the ID of the parent role that this role inherits from.

inherit

Identifies whether permissions are inherited (1 - yes, 0 - no).

admin

Identifies whether role is admin (1 - yes, 0 - no).

Schemas

The schemas data object identifies all the database’s schemas, as shown below:

Column

Description

schema_id

Shows the schema’s unique ID.

schema_name

Shows the schema’s name.

schema_owner

Shows the name of the role that owns the schema.

rechunker_ignore

Reserved for internal use.

Tables

The tables data object is used for displaying table information, and is described in the following tables:

Tables

The tables data object identifies proper (Comment - What does “proper” mean?) SQream tables in the database, as shown in the following table:

Column

Description

database_name

Shows the name of the database containing the table.

table_id

Shows the table’s database-unique ID.

schema_name

Shows the name of the schema containing the table.

table_name

Shows the name of the table.

row_count_valid

Identifies whether the row_count can be used.

row_count

Shows the number of rows in the table.

rechunker_ignore

Relevant for internal use.

Foreign Tables

The external_tables data object identifies foreign tables in the database, as shown below:

Column

Description

database_name

Shows the name of the database containing the table.

table_id

Shows the table’s database-unique ID.

schema_name

Shows the name of the schema containing the table.

table_name

Shows the name of the table.

format

Identifies the foreign data wrapper used. 0 for csv_fdw, 1 for parquet_fdw, 2 for orc_fdw.

created

Identifies the clause used to create the table.

Views

The views data object is used for displaying views in the database, as shown below:

Column

Description

view_id

Shows the view’s database-unique ID.

view_schema

Shows the name of the schema containing the view.

view_name

Shows the name of the view.

view_data

Reserved for internal use.

view_query_text

Identifies the AS clause used to create the view.

User Defined Functions

The udf data object is used for displaying UDFs in the database, as shown below:

Column

Description

database_name

Shows the name of the database containing the view.

function_id

Shows the UDF’s database-unique ID.

function_name

Shows the name of the UDF.