Catalog Reference¶

SQream DB contains a schema called sqream_catalog that contains information about your database’s objects - tables, columns, views, permissions, and more.

Some additional catalog tables are used primarily for internal introspection, which could change across SQream DB versions.

In this topic:

Types of data exposed by `sqream_catalog`¶

Database objects¶
Object	Table
Clustering keys	`clustering_keys`
Columns	`columns`, `external_table_columns`
Databases	`databases`
Permissions	`table_permissions`, `database_permissions`, `schema_permissions`, `permission_types`, `udf_permissions`
Roles	`roles`, `roles_memeberships`
Schemas	`schemas`
Sequences	`identity_key`
Tables	`tables`, `external_tables`
Views	`views`
UDFs	`user_defined_functions`

The catalog contains a few more tables which contain storage details for internal use

Storage objects¶
Object	Table
Extents	`extents`
Chunks	`chunks`
Delete predicates	`delete_predicates`

Tables in the catalog ¶

clustering_keys ¶

Explicit clustering keys for tables.

When more than one clustering key is defined, each key is listed in a separate row.

Column	Description
`database_name`	Name of the database containing the table
`table_id`	ID of the table containing the column
`schema_name`	Name of the schema containing the table
`table_name`	Name of the table containing the column
`clustering_key`	Name of the column that is a clustering key for this table

columns ¶

Column objects for standard tables

Column	Description
`database_name`	Name of the database containing the table
`schema_name`	Name of the schema containing the table
`table_id`	ID of the table containing the column
`table_name`	Name of the table containing the column
`column_id`	Ordinal of the column in the table (begins at 0)
`column_name`	Name of the column
`type_name`	Data type of the column
`column_size`	The maximum length in bytes.
`has_default`	`NULL` if the column has no default value. `1` if the default is a fixed value, or `2` if the default is an Identity
`default_value`	Default value for the column
`compression_strategy`	User-overridden compression strategy
`created`	Timestamp when the column was created
`altered`	Timestamp when the column was last altered

external_tables ¶

external_tables identifies external tables in the database.

For TABLES see tables

Column	Description
`database_name`	Name of the database containing the table
`table_id`	Database-unique ID for the table
`schema_name`	Name of the schema containing the table
`table_name`	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

external_table_columns ¶

Column objects for external tables

databases ¶

Column	Description
`database_Id`	Unique ID of the database
`database_name`	Name of the database
`default_disk_chunk_size`	Internal use
`default_process_chunk_size`	Internal use
`rechunk_size`	Internal use
`storage_subchunk_size`	Internal use
`compression_chunk_size_threshold`	Internal use

database_permissions ¶

database_permissions identifies all permissions granted to databases.

There is one row for each combination of role (grantee) and permission granted to a database.

Column	Description
`database_name`	Name of the database the permission applies to
`role_id`	ID of the role granted permissions (grantee)
`permission_type`	Identifies the permission type

identity_key ¶

permission_types ¶

permission_types Identifies the permission names that exist in the database.

Column	Description
`permission_type_id`	ID of the permission type
`name`	Name of the permission type

roles ¶

roles identifies the roles in the database.

Column	Description
`role_id`	Database-unique ID of the role
`name`	Name of the role
`superuser`	Identifies if this role is a superuser. `1` for superuser or `0` otherwise.
`login`	Identifies if this role can be used to log in to SQream DB. `1` for yes or `0` otherwise.
`has_password`	Identifies if this role has a password. `1` for yes or `0` otherwise.
`can_create_function`	Identifies if this role can create UDFs. `1` for yes, `0` otherwise.

roles_memberships ¶

roles_memberships identifies the role memberships in the database.

Column	Description
`role_id`	Role ID
`member_role_id`	ID of the parent role from which this role will inherit
`inherit`	Identifies if permissions are inherited. `1` for yes or `0` otherwise.

savedqueries ¶

savedqueries identifies the saved_queries in the database.

Column	Description
`name`	Saved query name
`num_parameters`	Number of parameters to be replaced at run-time

schemas ¶

schemas identifies all the database’s schemas.

Column	Description
`schema_id`	Unique ID of the schema
`schema_name`	Name of the schema
`schema_owner`	Name of the role who owns this schema
`rechunker_ignore`	Internal use

schema_permissions ¶

schema_permissions identifies all permissions granted to schemas.

There is one row for each combination of role (grantee) and permission granted to a schema.

Column	Description
`database_name`	Name of the database containing the schema
`schema_id`	ID of the schema the permission applies to
`role_id`	ID of the role granted permissions (grantee)
`permission_type`	Identifies the permission type

tables ¶

tables identifies proper SQream tables in the database.

For EXTERNAL TABLES see external_tables

Column	Description
`database_name`	Name of the database containing the table
`table_id`	Database-unique ID for the table
`schema_name`	Name of the schema containing the table
`table_name`	Name of the table
`row_count_valid`	Identifies if the `row_count` can be used
`row_count`	Number of rows in the table
`rechunker_ignore`	Internal use

table_permissions ¶

table_permissions identifies all permissions granted to tables.

There is one row for each combination of role (grantee) and permission granted to a table.

Column	Description
`database_name`	Name of the database containing the table
`table_id`	ID of the table the permission applies to
`role_id`	ID of the role granted permissions (grantee)
`permission_type`	Identifies the permission type

udf_permissions ¶

user_defined_functions ¶

user_defined_functions identifies UDFs in the database.

Column	Description
`database_name`	Name of the database containing the view
`function_id`	Database-unique ID for the UDF
`function_name`	Name of the UDF

views ¶

views identifies views in the database.

Column	Description
`view_id`	Database-unique ID for the view
`view_schema`	Name of the schema containing the view
`view_name`	Name of the view
`view_data`	Internal use
`view_query_text`	Identifies the `AS` clause used to create the view

Additional tables ¶

There are additional tables in the catalog that can be used for performance monitoring and inspection.

The definition for these tables is provided below could change across SQream DB versions.

extents ¶

extents identifies storage extents.

Each storage extents can contain several chunks.

Note

This is an internal table designed for low-level performance troubleshooting.

Column	Description
`database_name`	Name of the databse containing the extent
`table_id`	ID of the table containing the extent
`column_id`	ID of the column containing the extent
`extent_id`	ID for the extent
`size`	Extent size in megabytes
`path`	Full path to the extent on the file system

chunk_columns ¶

chunk_columns lists chunk information by column.

Column	Description
`database_name`	Name of the databse containing the extent
`table_id`	ID of the table containing the extent
`column_id`	ID of the column containing the extent
`chunk_id`	ID for the chunk
`extent_id`	ID for the extent
`compressed_size`	Actual chunk size in bytes
`uncompressed_size`	Uncompressed chunk size in bytes
`compression_type`	Actual compression scheme for this chunk
`long_min`	Minimum numeric value in this chunk (if exists)
`long_max`	Maximum numeric value in this chunk (if exists)
`string_min`	Minimum text value in this chunk (if exists)
`string_max`	Maximum text value in this chunk (if exists)
`offset_in_file`	Internal use

Note

This is an internal table designed for low-level performance troubleshooting.

chunks ¶

chunks identifies storage chunks.

Note

This is an internal table designed for low-level performance troubleshooting.

Column	Description
`database_name`	Name of the databse containing the chunk
`table_id`	ID of the table containing the chunk
`column_id`	ID of the column containing the chunk
`rows_num`	Amount of rows contained in the chunk
`deletion_status`	When data is deleted from the table, it is first deleted logically. This value identifies how much data is deleted from the chunk. `0` for no data, `1` for some data, `2` to specify the entire chunk is deleted.

delete_predicates ¶

delete_predicates identifies the existing delete predicates that have not been cleaned up.

Each DELETE command may result in several entries in this table.

Note

This is an internal table designed for low-level performance troubleshooting.

Column	Description
`database_name`	Name of the databse containing the predicate
`table_id`	ID of the table containing the predicate
`max_chunk_id`	Internal use. Placeholder marker for the highest `chunk_id` logged during the DELETE operation.
`delete_predicate`	Identifies the DELETE predicate

Examples ¶

List all tables in the database ¶

master=> SELECT * FROM sqream_catalog.tables;
database_name | table_id | schema_name | table_name     | row_count_valid | row_count | rechunker_ignore
--------------+----------+-------------+----------------+-----------------+-----------+-----------------
master        |        1 | public      | nba            | true            |       457 |                0
master        |       12 | public      | cool_dates     | true            |         5 |                0
master        |       13 | public      | cool_numbers   | true            |         9 |                0
master        |       27 | public      | jabberwocky    | true            |         8 |                0

List all schemas in the database ¶

master=> SELECT * FROM sqream_catalog.schemas;
schema_id | schema_name   | schema_owner | rechunker_ignore
----------+---------------+--------------+-----------------
        0 | public        | sqream       | false
        1 | secret_schema | mjordan      | false

List columns and their types for a specific table ¶

SELECT column_name, type_name
FROM sqream_catalog.columns
WHERE table_name='cool_animals';

List delete predicates ¶

SELECT  t.table_name, d.*  FROM
sqream_catalog.delete_predicates AS d
INNER JOIN sqream_catalog.tables AS t
ON d.table_id=t.table_id;

List Saved Queries ¶

SELECT * FROM sqream_catalog.savedqueries;