System Views
This section is a reference for a set of system views that capture information about resource
utilization, inflight queries, active loads, and other operational statistics. You can query
these views via ybsql
or other SQL query tools.
information_schema
tables and views are provided only for
compatibility with Postgres-compliant tools. For database administration, Yellowbrick
recommends using the views in the sys
schema, as documented in this
section. These views provide significant Yellowbrick-specific information that is not
available under information_schema
. sys
schema. For example, run this command:
premdb=# set schema 'sys';
SET
yellowbrick=# \dvS
List of relations
Schema | Name | Type | Owner
--------+----------------------+------+----------
sys | alert | view | ybdadmin
sys | backup | view | ybdadmin
sys | backup_chain | view | ybdadmin
sys | backup_legacy | view | ybdadmin
sys | backup_snapshot | view | ybdadmin
sys | blade | view | ybdadmin
sys | column | view | ybdadmin
sys | database | view | ybdadmin
...
premdb=# select * from sys.log_query;
...
sys
schema.premdb=# create table table2(c1 int, c2 int) distribute on(c1) sort on(c2);
ERROR: permission denied to create "sys.table2"
DETAIL: System catalog modifications are currently disallowed.
You cannot combine user-defined tables and system tables or views in the same query. One exception to this rule is the sys.const table, which can be queried in conjunction with regular tables.
sys.log_*
views: The retention policy is adjustable in the SMC. Go to
.
Visibility of Data in System Views
premdb=# set schema 'sys';
SET
premdb=# \dS
List of relations
Schema | Name | Type | Owner
--------+----------------------+------+----------
sys | alert | view | ybdadmin
sys | backup | view | ybdadmin
...
However, regular users may not have access to the data they return. Superusers can see all rows for all databases. The visibility of system views for non-superusers depends on the privileges those users have been granted.
VIEW QUERY TEXT
or TRACE QUERY
privileges so
that non-superusers can see rows for queries that they did not run
themselves:premdb=# create user tracer1 password '******';
CREATE ROLE
premdb=# grant trace query on system to tracer1;
GRANT
User tracer1
can now see all queries that are running or have been run on
the current database but has visibility into all databases on the system. The user
would have to connect to different databases to see the queries for each database.
System View(s) | Regular Users Can See: |
---|---|
sys.query , sys.log_query ,
sys.query_explain , sys.query_analyze |
|
sys.session , sys.log_session |
|
sys.database |
Databases on which they have one or more of the following privileges:
|
sys.schema |
Schemas in the current database if they have one or more of the following
privileges:
|
sys.table |
Their own tables and any tables they have been granted access to in the
current database. Users who have |
sys.role , sys.user |
|