This topic covers:
As well as system-defined variables such as checkpoint.enabled and checkpoint.interval that control server properties per session / pump basis, SQLstream now supports user-defined session variables that can be used to modify the behaviour of queries at run time.
You can start using a user-defined session variable without declaration by just assigning a value to it.
As a part of cloud-native deployments, session variables can enable the passing of dynamic parameters to pump sessions. These “session variables” could be used to dynamically supply sharding parameters such as the assignment of list/range of source partitions to the shard.
Item | Description |
---|---|
Session Variable Name | User Defined |
Declaration | Not Required |
Data Type | VARCHAR, may be CAST to other types |
Scope | Current Session Only |
User defined session variable can be set these ways:
create pump p1 stopped options(cutoff '12') as
insert into s2 select stream * from s1 where i <= $(cutoff as int);
create pump p2 stopped as
insert into s3 select stream * from s1 where i > $(cutoff as int);
alter pump p2 set cutoff = '4';
The following represents the syntax for referencing user-defined session variables:
$(<session variable name> [AS <type name>])
-- where <type name> can be BOOLEAN, TIMESTAMP, INTEGER, INT, or BIGINT
Note how the session variable (which is always considered as VARCHAR) can easily be CAST to a more suitable datatype when needed.
In these examples we also use the PUMP_OPTIONS view to show the current values of session variables associated with pumps.
create or replace schema test;
set schema 'TEST';
values $(UNSET);
create or replace stream s1(i int);
create or replace stream s2(i int);
create stream s3(i int);
alter session set aVar = 'a value';
values $(aVar);
-- aVar can also accept other data types such as bigint, double, boolean, timestamp values except String value.
alter session set aVar = '1';
values $(aVar as int);
create or replace pump p1 stopped options(cutoff '12') as
insert into s2 select stream * from s1 where i <= $(cutoff as int);
create or replace pump p2 stopped as
insert into s3 select stream * from s1 where i > $(cutoff as int);
-- To alter variables for pumps that are stopped or running you can set variables for all the pumps in a schema as follows
alter pump test.* set cutoff = '5';
-- To alter variables for single pump
alter pump p2 set cutoff = '4';
alter pump test.* start;
-- To alter a pump while it is running, the behavior of that pump will be altered some number of rows after the alter pump is performed.
alter pump p2 set cutoff = '9';
select * from SYS_BOOT.MGMT.PUMP_OPTIONS;
alter pump test.* stop;
The following represents the sample output generated from above example:
values $(UNSET);
EXPR$0 |
---|
alter session set aVar = 'a value';
values $(aVar);
EXPR$0 |
---|
a value |
alter session set aVar = '1';
values $(aVar as int);
EXPR$0 |
---|
1 |
alter pump test.* set cutoff = '5';
select "pump_name","name","value"
from SYS_BOOT.MGMT.PUMP_OPTIONS
where "catalog" = 'LOCALDB';
pump_name | name | value |
---|---|---|
P1 | CUTOFF | 5 |
P2 | CUTOFF | 5 |
alter pump p2 set cutoff = '4';
select "pump_name","name","value"
from SYS_BOOT.MGMT.PUMP_OPTIONS
where "catalog" = 'LOCALDB';
pump_name | name | value |
---|---|---|
P1 | CUTOFF | 5 |
P2 | CUTOFF | 4 |
alter pump p2 set cutoff = '9';
select "pump_name","name","value"
from SYS_BOOT.MGMT.PUMP_OPTIONS
where "catalog" = 'LOCALDB';
pump_name | name | value |
---|---|---|
P1 | CUTOFF | 5 |
P2 | CUTOFF | 9 |