User Defined Session Variables

This topic covers:

Overview

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

Syntax

Setting a session variable

User defined session variable can be set these ways:

  1. Using the OPTIONS clause of CREATE PUMP. For example:
    create pump p1 stopped options(cutoff '12') as
    insert into s2 select stream * from s1 where i <= $(cutoff as int);
    
  2. Using the SET <variable> = <value> clause of ALTER PUMP sometime after creating a pump (either before or after starting the pump). For example:
    create pump p2 stopped as
    insert into s3 select stream * from s1 where i > $(cutoff as int);
    
    alter pump p2 set cutoff = '4';
    
  3. Using the SET <variable> = <value> clause of ALTER SESSION. The variable can be referenced in any SQL used later in the session
    • NOTE: session variables defined in a session context are not inherited by pumps that are either created by or started from those sessions. The pump session is entirely independent.

Referencing session variables

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.

Example

In these examples we also create a pumpOptions 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);

create or replace view pumpOptions as select p."name" pump_name, o."name", o."value"
from sys_fem.med."StorageOption" o join sys_aem."Pump"."Pump" p on p."mofId"=o."StoredElement";

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 pumpOptions;
alter pump test.* stop;

Sample Output

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 * from pumpOptions;
PUMP_NAME name value
P1 CUTOFF 5
P2 CUTOFF 5
alter pump p2 set cutoff = '4';
select * from pumpOptions;
PUMP_NAME name value
P1 CUTOFF 5
P2 CUTOFF 4
alter pump p2 set cutoff = '9';
select * from pumpOptions;
PUMP_NAME name value
P1 CUTOFF 5
P2 CUTOFF 9