User-Defined Session Parameters
User-defined session parameters allow you to write more generalized parameters than what Vertica provides. You can configure user-defined session parameters in these ways:
- From the client—for example, with
ALTER SESSION - Through the UDx itself
A user-defined session parameter can be passed into any type of UDx supported by Vertica. You can also set parameters for your UDx at the session level. By specifying a user-defined session parameter, you can have the state of a parameter saved continuously. Vertica saves the state of the parameter even when the UDx is invoked multiple times during a single session.
The RowCount example uses a user-defined session parameter. This parameter counts the total number of rows processed by the UDx each time it runs. RowCount then displays the aggregate number of rows processed for all executions. See C++ Example: Using Session Parameters and Java Example: Using Session Parameters for implementations.
Viewing the User-Defined Session Parameter
Enter the following command to see the value of the session parameter:
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+---------+-----+------- (0 rows)
Now, execute the UDx:
=> SELECT RowCount(5,5); RowCount ---------- 10 (1 row)
Again, enter the command to see the value of the session parameter:
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+-----------+----------+------- public | UDSession | rowcount | 1 (1 row)
Because the UDx has processed one row, the value of RowCount is now 1. Running the UDx two more times should increase the value of RowCount by 2:
=> SELECT RowCount(10,10); RowCount ---------- 20 (1 row) => SELECT RowCount(15,15); RowCount ---------- 30 (1 row)
You have now executed the UDx three times, obtaining the sum of 5 + 5, 10 + 10, and 15 + 15. Now, check the value of RowCount.
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+-----------+----------+------- public | UDSession | rowcount | 3 (1 row)
Altering the User-Defined Session Parameter
You can also manually alter the value of RowCount. To do so, enter the following command:
=> ALTER SESSION SET UDPARAMETER FOR UDSession RowCount = 25; ALTER SESSION
Check the value of RowCount:
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+-----------+----------+------- public | UDSession | rowcount | 25 (1 row)
Clearing the User-Defined Session Parameter
From the client:
To clear the current value of RowCount, enter the following command:
=> ALTER SESSION CLEAR UDPARAMETER FOR UDSession RowCount; ALTER SESSION
Verify that RowCount has been cleared:
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+---------+-----+------- (0 rows)
Through the UDx in C++:
You can set the session parameter to clear through the UDx itself. For example, to clear RowCount when its value reaches 10 or greater do the following:
-
Remove the following line from the
destroy()method in RowCount: -
Replace the removed line from the
destroy()method with the following code: -
To see the UDx clear the session parameter, set RowCount to a value of 9:
-
Check the value of RowCount:
-
Invoke RowCount so that its value becomes 10:
-
Check the value of RowCount. Because the value has reached 10, the threshold specified in the UDx, expect that RowCount is cleared:
udParams.getUDSessionParamWriter("library").getStringRef("rowCount").copy(i_as_string);
if (rowCount < 10)
{
udParams.getUDSessionParamWriter("library").getStringRef("rowCount").copy(i_as_string);
}
else
{
udParams.getUDSessionParamWriter("library").clearParameter("rowCount");
}
=> ALTER SESSION SET UDPARAMETER FOR UDSession RowCount = 9; ALTER SESSION
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+-----------+----------+------- public | UDSession | rowcount | 9 (1 row)
=> SELECT RowCount(15,15);
RowCount
----------
30
(1 row)
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+---------+-----+------- (0 rows)
As expected, RowCount is cleared.
Through the UDx in Java:
-
Remove the following lines from the
destroy()method in RowCount: -
Replace the removed lines from the
destroy()method with the following code: -
To see the UDx clear the session parameter, set RowCount to a value of 9:
-
Check the value of RowCount:
-
Invoke RowCount so that its value becomes 10:
-
Check the value of RowCount. Since the value has reached 10, the threshold specified in the UDx, expect that RowCount is cleared:
udParams.getUDSessionParamWriter("library").setString("rowCount", Integer.toString(rowCount));
srvInterface.log("RowNumber processed %d records", count);
if (rowCount < 10)
{
udParams.getUDSessionParamWriter("library").setString("rowCount", Integer.toString(rowCount));
srvInterface.log("RowNumber processed %d records", count);
}
else
{
udParams.getUDSessionParamWriter("library").clearParameter("rowCount");
}
=> ALTER SESSION SET UDPARAMETER FOR UDSession RowCount = 9; ALTER SESSION
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+-----------+----------+------- public | UDSession | rowcount | 9 (1 row)
=> SELECT RowCount(15,15);
RowCount
----------
30
(1 row)
=> SHOW SESSION UDPARAMETER all; schema | library | key | value --------+---------+-----+------- (0 rows)
As expected, RowCount is cleared.
Read-Only and Hidden Session Parameters
If you don't want a parameter to be set anywhere except in the UDx, you can make it read-only. If, additionally, you don't want a parameter to be visible in the client, you can make it hidden.
To make a parameter read-only, meaning that it cannot be set in the client, but can be viewed, add a single underscore before the parameter's name. For example, to make rowCount read-only, change all instances in the UDx of "rowCount" to "_rowCount".
To make a parameter hidden, meaning that it cannot be viewed in the client nor set, add two underscores before the parameter's name. For example, to make rowCount hidden, change all instances in the UDx of "rowCount" to "__rowCount".
