Skip to main content

Datometry Documentation

Assign a Workload Classifier Using a Hyper-Q Hint

The session_context={'context_key', 'context_value'} hint allows you to add a key-value pair as a label to a SQL query which you can use to create a workload classifier for better workload management. The classifier you create can be used to assign incoming requests to a workload group based on the parameters specified in a classifier statement definition.

To learn more, see the following articles in the Microsoft Azure Synapse Analytics documentation:

The syntax for session_context is:

session_context={'context_key', 'context_value'}

where:

Parameter

Description

context_key

Specifies the wlm_context value which defines the session context that a request can be mapped to. It can serve as a primary or as a secondary identification mechanism for the username and workload group mapping.

context_value

Specifies the label value that a request can be classified against.

You can also define multiple session contexts per hint:

session_context={"context_key_1":"my_context_1", "context_key_2":"my_context_ 2"}

Supported hint scopes are for request and for session. To learn about Hyper-Q hint scope and precedence, see About Hyper-Q SQL Hints.

Example 70. Specify Session Context With a SQL Query

You must set the hint scope when you execute the target SQL query. For example, the following query specifies a for request hint scope.

SELECT --@dtm_session_context={"wlm_context":"root_context"} for request 1;

The above SQL statement results in the following two queries on Azure Synapse. Note that the SELECT statement executes after setting the given key-value pair in the EXEC statement using the current session context.

EXEC sys.sp_set_session_context @key = 'wlm_context' , @value = 'root_context';
SELECT CAST ('1' AS smallint ) AS [1];


Example 71. Specify Session Context for the First SQL Query But Not the Second

In this example session_context is specified for the first Teradata SQL statement using a for request hint scope, but not for the second statement.

BEGIN TRANSACTION;
INSERT INTO --@dtm_session_context={"wlm_context": "my_context_value"} for request
table_name_1 (5);
INSERT INTO table_name_1 (6);
END TRANSACTION;

HyperQ translates the hint and executes the following query on Azure Synapse. Note that the key-value pair is applied to the first SQL statement, but not to the second statement which unsets session_context with @value = null.

BEGIN TRANSACTION
EXEC sys.sp_set_session_context @key = 'wlm_context' , @value = 'my_context_value'
INSERT INTO table_name_1 (5);
EXEC sys.sp_set_session_context @key = 'wlm_context' , @value = null;
INSERT INTO table_name_1 (6);
COMMIT TRANSACTION


Example 72. Specify Session Context for an Entire Session

You can specify that session context be applied throughout an entire SQL session using the for session hint scope. In the following example, the for session scope extends the session content hint to all SQL queries following the hint.

SELECT 1;
SELECT --@dtm_session_context={"wlm_context":"my_context"} for session2;
SELECT 3;
SELECT 4;

Hyper-Q translates the hint and executes the following query on Azure Synapse. Note that the key-value pair is applied to all SQL statements following the inclusion of the session_content hint .

SELECT CAST ('1' AS smallint ) AS [1]; 
EXEC sys.sp_set_session_context @key = 'wlm_context' , @value = 'my_context'; 
SELECT CAST ('2' AS smallint ) AS [2]; SELECT CAST ('3' AS smallint ) AS [3]; 
SELECT CAST ('4' AS smallint ) AS [4];


Example 73. Specify Multiple Session Context Values in a Single Hint

You can specify multiple session contexts to be set simultaneously.

SELECT 
--@dtm_session_context={"context_a":"my_context1", "context_b":"my_context2"} for request
5;

The resulting SQL statement on Azure Synapse is translated as shown below.

EXEC    sys.sp_set_session_context @key = 'context_a' , @value = 'my_context1';
EXEC    sys.sp_set_session_context @key = 'context_b' , @value = 'my_context2';
SELECT CAST ('5' AS smallint ) AS [5];


Example 74. Specify Session Context With a Macro

You can use session_context within an EXEC command to execute a stored procedure or a SQL macro.

EXEC --@dtm_session_context={"wlm_context": "my_context"} for request
macro_name;

The session_context hint applies to the all statements defined in the stored procedure or macro body. The final EXEC statement unsets session_context with @value = null.

EXEC sys.sp_set_session_context @key = 'wlm_context' , @value = 'my_context' 
...
BEGIN TRANSACTION
  <SQL statements>
COMMIT TRANSACTION
EXEC sys.sp_set_session_context @key = 'wlm_context' , @value = null