Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SET clause

Use the SET clause to define variables that store expression results. You can reuse these variables in SELECT and WHERE clauses, and in substitution templates. This helps you avoid duplicating complex expressions and reduce the number of function calls in your SQL statement.

The SET clause supports Data types, Operators, Functions, Literals, Case statements, JSON extensions, Variables, and Nested object queries.

SET clause syntax

The SET clause must appear before the SELECT clause in your SQL statement. Use the following syntax:

SET @variable_name = expression [, @variable_name2 = expression2]

Syntax rules:

Using variables

After you define variables, you can use them in:

Variables are referenced using the same @variable_name syntax used in the SET clause. You can also use JSON extension syntax to access properties of variables that contain objects, such as @variable_name.property.

SET clause examples

Basic variable usage

The following example shows a payload published on topic device/data: {"temp_fahrenheit": 75, "humidity": 60}

SQL statement:

SET @temp_celsius = (temp_fahrenheit - 32) * 5 / 9
SELECT @temp_celsius AS celsius, humidity FROM 'device/data'

Outgoing payload: {"celsius": 23.89, "humidity": 60}

Access members in embedded JSON objects

The following example shows a payload published on topic device/data: {"device1": {"deviceId":"weather_sensor", "deviceData": {"sensors": {"temp_fahrenheit": 75, "humidity": 60}, "location": [47.606,-122.332]}}}

SQL statement:

SET @device_sensor_data = device1.deviceData.sensors
SELECT @device_sensor_data.temp_fahrenheit AS temp_fahrenheit, @device_sensor_data.humidity as humidity, device1.deviceId as deviceId FROM 'device/data'

Outgoing payload: {"temp_fahrenheit":75,"humidity":60,"deviceId":"weather_sensor"}

for more information on how to work with JSON extensions, reference JSON extensions

Avoiding duplicate function calls

SET variables help avoid duplicating complex decode operations:

SET @decoded_data = decode(encode(*, 'base64'), 'proto', 'schema', 'schema.desc', 'message.proto', 'Message')
SELECT @decoded_data.sensor_id, @decoded_data.reading FROM 'device/protobuf' 
WHERE @decoded_data.reading > 100

Without SET variables, you would need to repeat the decode function three times, which exceeds the function call limits.

Multiple variables

You can define multiple variables in a single SET clause by separating them with commas:

SET @user_data = get_user_properties(device_id), @threshold = 50
SELECT @user_data.name, temp_fahrenheit FROM 'sensors/+'
WHERE temp_fahrenheit > @threshold AND @user_data.active = true

Using variables in substitution templates

Variables can also be used in action substitution templates, allowing you to reuse computed values across both the SQL statement and rule actions.

SQL statement:

SET @temp_celsius = (temp_fahrenheit - 32) * 5 / 9
SELECT @temp_celsius AS celsius, humidity FROM 'device/data'

Action configuration:

{
  "s3": {
    "roleArn": "arn:aws:iam::123456789012:role/testRuleRole",
    "bucketName": "bucket",
    "key": "temperature-data/${device_id}/temp-${@temp_celsius}C.json"
  }
}

In this example, the SET variable @temp_celsius is used in a substitution template to construct the key field of the S3 action.

Non-JSON payload usage

SET variables does not support non-JSON payloads directly, so the payload must be encoded or decoded first:

SET @encoded_payload = encode(*, 'base64')
SELECT @encoded_payload AS raw_data FROM 'device/binary'

for more information on how to work with non-JSON payloads, reference Working with binary payloads

SET clause limits

The following limits apply to SET variables: