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
¶
Object |
Table |
---|---|
Clustering keys |
|
Columns |
|
Databases |
|
Permissions |
|
Roles |
|
Schemas |
|
Sequences |
|
Tables |
|
Views |
|
UDFs |
|
The catalog contains a few more tables which contain storage details for internal use
Object |
Table |
---|---|
Extents |
|
Chunks |
|
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 |
---|---|
|
Name of the database containing the table |
|
ID of the table containing the column |
|
Name of the schema containing the table |
|
Name of the table containing the column |
|
Name of the column that is a clustering key for this table |
columns¶
Column objects for standard tables
Column |
Description |
---|---|
|
Name of the database containing the table |
|
Name of the schema containing the table |
|
ID of the table containing the column |
|
Name of the table containing the column |
|
Ordinal of the column in the table (begins at 0) |
|
Name of the column |
|
Data type of the column |
|
The maximum length in bytes. |
|
|
|
Default value for the column |
|
User-overridden compression strategy |
|
Timestamp when the column was created |
|
Timestamp when the column was last altered |
external_tables¶
external_tables
identifies external tables in the database.
For TABLES
see tables
Column |
Description |
---|---|
|
Name of the database containing the table |
|
Database-unique ID for the table |
|
Name of the schema containing the table |
|
Name of the table |
|
Identifies the foreign data wrapper used.
|
|
Identifies the clause used to create the table |
external_table_columns¶
Column objects for external tables
databases¶
Column |
Description |
---|---|
|
Unique ID of the database |
|
Name of the database |
|
Internal use |
|
Internal use |
|
Internal use |
|
Internal use |
|
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 |
---|---|
|
Name of the database the permission applies to |
|
ID of the role granted permissions (grantee) |
|
Identifies the permission type |
permission_types¶
permission_types
Identifies the permission names that exist in the database.
Column |
Description |
---|---|
|
ID of the permission type |
|
Name of the permission type |
roles¶
roles
identifies the roles in the database.
Column |
Description |
---|---|
|
Database-unique ID of the role |
|
Name of the role |
|
Identifies if this role is a superuser. |
|
Identifies if this role can be used to log in to SQream DB. |
|
Identifies if this role has a password. |
|
Identifies if this role can create UDFs. |
roles_memberships¶
roles_memberships
identifies the role memberships in the database.
Column |
Description |
---|---|
|
Role ID |
|
ID of the parent role from which this role will inherit |
|
Identifies if permissions are inherited. |
savedqueries¶
savedqueries
identifies the saved_queries in the database.
Column |
Description |
---|---|
|
Saved query name |
|
Number of parameters to be replaced at run-time |
schemas¶
schemas
identifies all the database’s schemas.
Column |
Description |
---|---|
|
Unique ID of the schema |
|
Name of the schema |
|
Name of the role who owns this schema |
|
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 |
---|---|
|
Name of the database containing the schema |
|
ID of the schema the permission applies to |
|
ID of the role granted permissions (grantee) |
|
Identifies the permission type |
tables¶
tables
identifies proper SQream tables in the database.
For EXTERNAL TABLES
see external_tables
Column |
Description |
---|---|
|
Name of the database containing the table |
|
Database-unique ID for the table |
|
Name of the schema containing the table |
|
Name of the table |
|
Identifies if the |
|
Number of rows in the table |
|
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 |
---|---|
|
Name of the database containing the table |
|
ID of the table the permission applies to |
|
ID of the role granted permissions (grantee) |
|
Identifies the permission type |
user_defined_functions¶
user_defined_functions
identifies UDFs in the database.
Column |
Description |
---|---|
|
Name of the database containing the view |
|
Database-unique ID for the UDF |
|
Name of the UDF |
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 |
---|---|
|
Name of the databse containing the extent |
|
ID of the table containing the extent |
|
ID of the column containing the extent |
|
ID for the extent |
|
Extent size in megabytes |
|
Full path to the extent on the file system |
chunk_columns¶
chunk_columns
lists chunk information by column.
Column |
Description |
---|---|
|
Name of the databse containing the extent |
|
ID of the table containing the extent |
|
ID of the column containing the extent |
|
ID for the chunk |
|
ID for the extent |
|
Actual chunk size in bytes |
|
Uncompressed chunk size in bytes |
|
Actual compression scheme for this chunk |
|
Minimum numeric value in this chunk (if exists) |
|
Maximum numeric value in this chunk (if exists) |
|
Minimum text value in this chunk (if exists) |
|
Maximum text value in this chunk (if exists) |
|
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 |
---|---|
|
Name of the databse containing the chunk |
|
ID of the table containing the chunk |
|
ID of the column containing the chunk |
|
Amount of rows contained in the chunk |
|
When data is deleted from the table, it is first deleted logically. This value identifies how much data is deleted from the chunk. |
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 |
---|---|
|
Name of the databse containing the predicate |
|
ID of the table containing the predicate |
|
Internal use. Placeholder marker for the highest |
|
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;