Product Documentation

Appearance

Assemble Rule Examples ​

The following examples are evaluated and applied during the assemble phase of query execution.

Abort Queries via a SQL Condition ​

Rule Preview:
if ((String(w.SQLText).indexOf('match') >= 0) &&
   w.application === 'ybsql') {
	w.abort('Flex profile: ybsql users cannot query match tables!');
}

Any ybsql query that is run by a non-superuser and contains the string match (such as in a table name) will be aborted. For example:

premdb=> select count(*) from match;
ERROR:  WLM: Denied by administrator: Flex profile: ybsql users cannot query match tables!
CONTEXT:  select count(*) from match;
premdb=> select count(*) from matchstats;
ERROR:  WLM: Denied by administrator: Flex profile: ybsql users cannot query match tables!
CONTEXT:  select count(*) from matchstats;
premdb=> select count(*) from newmatchstats;
ERROR:  WLM: Denied by administrator: Flex profile: ybsql users cannot query match tables!
CONTEXT:  select count(*) from newmatchstats;

This rule is enabled for the flex profile only. When a different profile is activated, this rule will have no effect. In the SMC Rule Processing screen, the administrator sees:

However, note that a superuser can run this type of query:

premdb=> \c premdb yellowbrick
Password for user yellowbrick: 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "yellowbrick".
premdb=# select count(*) from matchstats;
   count   
-----------
 147937140
(1 row)

In this case the administrator sees that the rule was ignored:

Adjust Memory for a User ​

This example makes sure that a large amount of memory is available to CTAS statements run by superuser bobr:

Rule Preview:
if (w.memoryEstimate < 20000 &&
   w.user === 'bobr' &&
   w.type === 'ctas') {
	w.requestedMemoryMB = 20000;
}

When bobr runs a CTAS query, the administrator sees:

The sys.log_query view records requested versus estimated memory use:

yellowbrick=# select query_id, state, username, substr(query_text,1,33), memory_estimated_bytes, memory_granted_bytes 
from sys.log_query 
where query_id=3490373;
 query_id | state | username |              substr               | memory_estimated_bytes | memory_granted_bytes 
----------+-------+----------+-----------------------------------+------------------------+----------------------
  3490373 | done  | bobr     | create table union_matchstats as  |             3617587200 |          20971520000
(1 row)

Note: If you are more concerned about memory requirements during query compilation, you could create an equivalent Compile rule that sets the requested memory during that phase of execution.

Parent topic:Rule Examples