Product Documentation

Appearance

All platforms

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 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.

Adjust Memory for a User ​

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

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

When bobr runs a CTAS query, requested memory is set accordingly.

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.