Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from http://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html below:

Functions - AWS IoT Core

You can use the following built-in functions in the SELECT or WHERE clauses of your SQL expressions.

Returns the absolute value of a number. Supported by SQL version 2015-10-08 and later.

Example: abs(-5) returns 5.

Returns the ID of the account that owns this rule as a String. Supported by SQL version 2015-10-08 and later.

Example:

accountid() = "123456789012"

Returns the inverse cosine of a number in radians. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: acos(0) = 1.5707963267948966

Returns the inverse sine of a number in radians. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: asin(0) = 0.0

Returns the inverse tangent of a number in radians. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: atan(0) = 0.0

Returns the angle, in radians, between the positive x-axis and the (x, y) point defined in the two arguments.  The angle is positive for counter-clockwise angles (upper half-plane, y > 0), and negative for clockwise angles (lower half-plane, y < 0). Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: atan2(1, 0) = 1.5707963267948966

Calls the specified Lambda function passing inputJson to the Lambda function and returns the JSON generated by the Lambda function.

The ARN of the Lambda function to call. The Lambda function must return JSON data.

The JSON input passed to the Lambda function. To pass nested object queries and literals, you must use SQL version 2016-03-23.

You must grant AWS IoT lambda:InvokeFunction permissions to invoke the specified Lambda function. The following example shows how to grant the lambda:InvokeFunction permission using the AWS CLI:

aws lambda add-permission --function-name "function_name"
--region "region"
--principal iot.amazonaws.com 
--source-arn arn:aws:iot:us-east-1:account_id:rule/rule_name
--source-account "account_id"
--statement-id "unique_id" 
--action "lambda:InvokeFunction"

The following are the arguments for the add-permission command:

--function-name

Name of the Lambda function. You add a new permission to update the function's resource policy.

--region

The AWS Region of your account.

--principal

The principal who is getting the permission. This should be iot.amazonaws.com to allow AWS IoT permission to call a Lambda function.

--source-arn

The ARN of the rule. You can use the get-topic-rule AWS CLI command to get the ARN of a rule.

--source-account

The AWS account where the rule is defined.

--statement-id

A unique statement identifier.

--action

The Lambda action that you want to allow in this statement. To allow AWS IoT to invoke a Lambda function, specify lambda:InvokeFunction.

If you add a permission for an AWS IoT principal without providing the source-arn or source-account, any AWS account that creates a rule with your Lambda action can trigger rules to invoke your Lambda function from AWS IoT. For more information, see Lambda Permission Model.

Given a JSON message payload like:

{
    "attribute1": 21,
    "attribute2": "value"
}

The aws_lambda function can be used to call Lambda function as follows.

SELECT
aws_lambda("arn:aws:lambda:us-east-1:account_id:function:lambda_function", {"payload":attribute1}) as output FROM 'topic-filter'

If you want to pass the full MQTT message payload, you can specify the JSON payload using '*', such as the following example.

SELECT
aws_lambda("arn:aws:lambda:us-east-1:account_id:function:lambda_function", *) as output FROM 'topic-filter'

payload.inner.element selects data from messages published on topic 'topic/subtopic'.

some.value selects data from the output that's generated by the Lambda function.

The rules engine limits the execution duration of Lambda functions. Lambda function calls from rules should be completed within 2000 milliseconds.

Performs a bitwise AND on the bit representations of the two Int(-converted) arguments. Supported by SQL version 2015-10-08 and later.

Example: bitand(13, 5) = 5

Performs a bitwise OR of the bit representations of the two arguments. Supported by SQL version 2015-10-08 and later.

Example: bitor(8, 5) = 13

Performs a bitwise XOR on the bit representations of the two Int(-converted) arguments. Supported by SQL version 2015-10-08 and later.

Example:bitor(13, 5) = 8

Performs a bitwise NOT on the bit representations of the Int(-converted) argument. Supported by SQL version 2015-10-08 and later.

Example: bitnot(13) = 2

Converts a value from one data type to another. Cast behaves mostly like the standard conversions, with the addition of the ability to cast numbers to or from Booleans. If AWS IoT cannot determine how to cast one type to another, the result is Undefined. Supported by SQL version 2015-10-08 and later. Format: cast(value as type).

Example:

cast(true as Int) = 1

The following keywords might appear after "as" when calling cast:

Casting rules:

The source value.

With SQL V2 (2016-03-23), numeric values that are whole numbers, such as 10.0, return an Int value (10) instead of the expected Decimal value (10.0). To reliably cast whole number numeric values as Decimal values, use SQL V1 (2015-10-08) for the rule query statement.

Rounds the given Decimal up to the nearest Int. Supported by SQL version 2015-10-08 and later.

Examples:

ceil(1.2) = 2

ceil(-1.2) = -1

Returns the ASCII character that corresponds to the given Int argument. Supported by SQL version 2015-10-08 and later.

Examples:

chr(65) = "A".

chr(49) = "1".

Returns the ID of the MQTT client sending the message, or n/a if the message wasn't sent over MQTT. Supported by SQL version 2015-10-08 and later.

Example:

clientid() = "123456789012"

Concatenates arrays or strings. This function accepts any number of arguments and returns a String or an Array. Supported by SQL version 2015-10-08 and later.

Examples:

concat() = Undefined.

concat(1) = "1".

concat([1, 2, 3], 4) = [1, 2, 3, 4].

concat([1, 2, 3], "hello") = [1, 2, 3, "hello"]

concat("con", "cat") = "concat"

concat(1, "hello") = "1hello"

concat("he","is","man") = "heisman"

concat([1, 2, 3], "hello", [4, 5, 6]) = [1, 2, 3, "hello", 4, 5, 6]

If any argument is an Array, the result is a single array containing all of the arguments. If no arguments are arrays, and at least one argument is a String, the result is the concatenation of the String representations of all the arguments. Arguments are converted to strings using the standard conversions previously listed.

Returns the cosine of a number in radians. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example:

cos(0) = 1.

Returns the hyperbolic cosine of a number in radians. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: cosh(2.3) = 5.037220649268761.

Use the decode function to decode an encoded value. If the decoded string is a JSON document, an addressable object is returned. Otherwise, the decoded string is returned as a string. The function returns NULL if the string cannot be decoded. This function supports decoding base64-encoded strings and Protocol Buffer (protobuf) message format.

Supported by SQL version 2016-03-23 and later.

value

A string value or any of the valid expressions, as defined in AWS IoT SQL reference, that return a string.

decodingScheme

A literal string representing the scheme used to decode the value. Currently, only 'base64' and 'proto' are supported.

In this example, the message payload includes an encoded value.

{
    encoded_temp: "eyAidGVtcGVyYXR1cmUiOiAzMyB9Cg=="
}

The decode function in this SQL statement decodes the value in the message payload.

SELECT decode(encoded_temp,"base64").temperature AS temp from 'topic/subtopic'

Decoding the encoded_temp value results in the following valid JSON document, which allows the SELECT statement to read the temperature value.

{ "temperature": 33 }

The result of the SELECT statement in this example is shown here.

{ "temp": 33 }

If the decoded value was not a valid JSON document, the decoded value would be returned as a string.

You can use the decode SQL function to configure a Rule that can decode your protobuf message payload. For more information, see Decoding protobuf message payloads.

If you omit the source‐arn or source‐account when setting permissions for an AWS IoT principal, any AWS account can invoke your Decode function through other AWS IoT rules. To secure your function, see Bucket policies in the Amazon Simple Storage Service User Guide.

The function signature looks like the following:

decode(<ENCODED DATA>, 'proto', '<S3 BUCKET NAME>', '<S3 OBJECT KEY>', '<PROTO NAME>', '<MESSAGE TYPE>')            

ENCODED DATA

Specifies the protobuf-encoded data to be decoded. If the entire message sent to the Rule is protobuf-encoded data, you can reference the raw binary incoming payload using *. Otherwise, this field must be a base-64 encoded JSON string and a reference to the string can be passed in directly.

1) To decode a raw binary protobuf incoming payload:

decode(*, 'proto', ...)

2) To decode a protobuf-encoded message represented by a base64-encoded string 'a.b':

decode(a.b, 'proto', ...)

proto

Specifies the data to be decoded in a protobuf message format. If you specify base64 instead of proto, this function will decode base64-encoded strings as JSON.

S3 BUCKET NAME

The name of the Amazon S3 bucket where you’ve uploaded your FileDescriptorSet file.

S3 OBJECT KEY

The object key that specifies the FileDescriptorSet file within the Amazon S3 bucket.

PROTO NAME

The name of the .proto file (excluding the extension) from which the FileDescriptorSet file was generated.

MESSAGE TYPE

The name of the protobuf message structure within the FileDescriptorSet file, to which the data to be decoded should conform.

An example SQL expression using the decode SQL function can look like the following:

SELECT VALUE decode(*, 'proto', 's3-bucket', 'messageformat.desc', 'myproto', 'messagetype') FROM 'some/topic'

• *

  Represents a binary incoming payload, which conforms to the protobuf message type called mymessagetype.

• messageformat.desc

  The FileDescriptorSet file stored in an Amazon S3 bucket named s3-bucket.

• myproto

  The original .proto file used to generate the FileDescriptorSet file named myproto.proto.

• messagetype

  The message type called messagetype (along with any imported dependencies) as defined in myproto.proto.

Use the encode function to encode the payload, which potentially might be non-JSON data, into its string representation based on the encoding scheme. Supported by SQL version 2016-03-23 and later.

value

Any of the valid expressions, as defined in AWS IoT SQL reference. You can specify * to encode the entire payload, regardless of whether it's in JSON format. If you supply an expression, the result of the evaluation is converted to a string before it is encoded.

encodingScheme

A literal string representing the encoding scheme you want to use. Currently, only 'base64' is supported.

Returns a Boolean indicating whether the first String argument ends with the second String argument. If either argument is Null or Undefined, the result is Undefined. Supported by SQL version 2015-10-08 and later.

Example: endswith("cat","at") = true.

Returns e raised to the Decimal argument. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: exp(1) = e.

Rounds the given Decimal down to the nearest Int. Supported by SQL version 2015-10-08 and later.

Examples:

floor(1.2) = 1

floor(-1.2) = -2

Extracts a value from a collection-like type (Array, String, Object). No conversion is applied to the first argument. Conversion applies as documented in the table to the second argument. Supported by SQL version 2015-10-08 and later.

Examples:

get(["a", "b", "c"], 1) = "b"

get({"a":"b"}, "a") = "b"

get("abc", 0) = "a"

Retrieves data from a DynamoDB table. get_dynamodb() allows you to query a DynamoDB table while a rule is evaluated. You can filter or augment message payloads using data retrieved from DynamoDB. Supported by SQL version 2016-03-23 and later.

get_dynamodb() takes the following parameters:

tableName

The name of the DynamoDB table to query.

partitionKeyName

The name of the partition key. For more information, see DynamoDB Keys.

partitionKeyValue

The value of the partition key used to identify a record. For more information, see DynamoDB Keys.

sortKeyName

(Optional) The name of the sort key. This parameter is required only if the DynamoDB table queried uses a composite key. For more information, see DynamoDB Keys.

sortKeyValue

(Optional) The value of the sort key. This parameter is required only if the DynamoDB table queried uses a composite key. For more information, see DynamoDB Keys.

roleArn

The ARN of an IAM role that grants access to the DynamoDB table. The rules engine assumes this role to access the DynamoDB table on your behalf. Avoid using an overly permissive role. Grant the role only those permissions required by the rule. The following is an example policy that grants access to one DynamoDB table.

JSON

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dynamodb:GetItem",
            "Resource": "arn:aws:dynamodb:us-east-1:123456789012:table/table-name"
        }
    ]
}

As an example of how to use get_dynamodb(), say you have a DynamoDB table that contains device ID and location information for all of your devices connected to AWS IoT. The following SELECT statement uses the get_dynamodb() function to retrieve the location for the specified device ID:

SELECT *, get_dynamodb("InServiceDevices", "deviceId", id, "arn:aws:iam::12345678910:role/getdynamo").location AS location FROM 'some/topic'

• You can call get_dynamodb() a maximum of one time per SQL statement. Calling get_dynamodb() multiple times in a single SQL statement causes the rule to terminate without invoking any actions.

• If get_dynamodb() returns more than 8 KB of data, the rule's action may not be invoked.

References any of the following MQTT5 headers: contentType, payLoadFormatIndicator, responseTopic, and correlationData. This function takes any of the following literal strings as an argument: content_type, format_indicator, response_topic, and correlation_data. For more information, see the following Function arguments table.

contentType

String: A UTF-8 encoded string that describes the content of the publishing message.

payLoadFormatIndicator

String: An Enum string value that indicates whether the payload is formatted as UTF-8. Valid values are UNSPECIFIED_BYTES and UTF8_DATA.

responseTopic

String: A UTF-8 encoded string that's used as the topic name for a response message. The response topic is used to describe the topic that the receiver should publish to as part of the request-response flow. The topic must not contain wildcard characters.

correlationData

String: The base64-encoded binary data used by the sender of the Request Message to identify which request the Response Message is for when it's received.

The following table shows the acceptable function arguments and their associated return types for the get_mqtt_property function:

The following example Rules SQL references any of the following MQTT5 headers: contentType, payLoadFormatIndicator, responseTopic, and correlationData.

SELECT *, get_mqtt_property('content_type') as contentType,
          get_mqtt_property('format_indicator') as payloadFormatIndicator,
          get_mqtt_property('response_topic') as responseTopic,
          get_mqtt_property('correlation_data') as correlationData
FROM 'some/topic'

Retrieves the value of the encrypted SecretString or SecretBinary field of the current version of a secret in AWS Secrets Manager. For more information about creating and maintaining secrets, see CreateSecret, UpdateSecret, and PutSecretValue.

get_secret() takes the following parameters:

secretId

String: The Amazon Resource Name (ARN) or the friendly name of the secret to retrieve.

secretType

String: The secret type. Valid values: SecretString | SecretBinary.

SecretString

• For secrets that you create as JSON objects by using the APIs, the AWS CLI, or the AWS Secrets Manager console:

  • If you specify a value for the key parameter, this function returns the value of the specified key.

  • If you don't specify a value for the key parameter, this function returns the entire JSON object.

• For secrets that you create as non-JSON objects by using the APIs or the AWS CLI:

  • If you specify a value for the key parameter, this function fails with an exception.

  • If you don't specify a value for the key parameter, this function returns the contents of the secret.

SecretBinary

• If you specify a value for the key parameter, this function fails with an exception.

• If you don't specify a value for the key parameter, this function returns the secret value as a base64-encoded UTF-8 string.

key

(Optional) String: The key name inside a JSON object stored in the SecretString field of a secret. Use this value when you want to retrieve only the value of a key stored in a secret instead of the entire JSON object.

If you specify a value for this parameter and the secret doesn't contain a JSON object inside its SecretString field, this function fails with an exception.

roleArn

String: A role ARN with secretsmanager:GetSecretValue and secretsmanager:DescribeSecret permissions.

This function always returns the current version of the secret (the version with the AWSCURRENT tag). The AWS IoT rules engine caches each secret for up to 15 minutes. As a result, the rules engine can take up to 15 minutes to update a secret. This means that if you retrieve a secret up to 15 minutes after an update with AWS Secrets Manager, this function might return the previous version.

This function is not metered, but AWS Secrets Manager charges apply. Because of the secret caching mechanism, the rules engine occasionally calls AWS Secrets Manager. Because the rules engine is a fully distributed service, you might see multiple Secrets Manager API calls from the rules engine during the 15-minute caching window.

Examples:

You can use the get_secret function in an authentication header in an HTTPS rule action, as in the following API key authentication example.

"API_KEY": "${get_secret('API_KEY', 'SecretString', 'API_KEY_VALUE', 'arn:aws:iam::12345678910:role/getsecret')}"
            

For more information about the HTTPS rule action, see HTTP.

Returns the specified shadow of the specified thing. Supported by SQL version 2016-03-23 and later.

thingName

String: The name of the thing whose shadow you want to retrieve.

shadowName

(Optional) String: The name of the shadow. This parameter is required only when referencing named shadows.

roleArn

String: A role ARN with iot:GetThingShadow permission.

Examples:

When used with a named shadow, provide the shadowName parameter.

SELECT * from 'topic/subtopic'
WHERE
    get_thing_shadow("MyThing","MyThingShadow","arn:aws:iam::123456789012:role/AllowsThingShadowAccess")
    .state.reported.alarm = 'ON'

When used with an unnamed shadow, omit the shadowName parameter.

SELECT * from 'topic/subtopic'
WHERE
    get_thing_shadow("MyThing","arn:aws:iam::123456789012:role/AllowsThingShadowAccess")
    .state.reported.alarm = 'ON'

References User Properties, which is one type of property headers supported in MQTT5.

userProperty

String: A user property is a key-value pair. This function takes the key as an argument and returns an array of all values that match the associated key.

Function arguments

For the following User Properties in the message headers:

The following table shows the expected SQL behavior:

The following example Rules SQL references User Properties (a type of MQTT5 property header) into the payload:

SELECT *, get_user_properties('user defined property key') as userProperty
FROM 'some/topic'

AWS IoT provides the following hashing functions:

• md2

• md5

• sha1

• sha224

• sha256

• sha384

• sha512

All hash functions expect one string argument. The result is the hashed value of that string. Standard string conversions apply to non-string arguments. All hash functions are supported by SQL version 2015-10-08 and later.

Examples:

md2("hello") = "a9046c73e00331af68917d3804f70655"

md5("hello") = "5d41402abc4b2a76b9719d911017c592"

Returns the first index (0-based) of the second argument as a substring in the first argument. Both arguments are expected as strings. Arguments that are not strings are subjected to standard string conversion rules. This function does not apply to arrays, only to strings. Supported by SQL version 2016-03-23 and later.

Examples:

indexof("abcd", "bc") = 1

Returns true if the argument is the Null value. Supported by SQL version 2015-10-08 and later.

Examples:

isNull(5) = false.

isNull(Null) = true.

Returns true if the argument is Undefined. Supported by SQL version 2016-03-23 and later.

Examples:

isUndefined(5) = false.

isUndefined(floor([1,2,3]))) = true.

Returns the number of characters in the provided string. Standard conversion rules apply to non-String arguments. Supported by SQL version 2016-03-23 and later.

Examples:

length("hi") = 2

length(false) = 5

Returns the natural logarithm of the argument. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: ln(e) = 1.

Returns the base 10 logarithm of the argument. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: log(100) = 2.0.

Returns the lowercase version of the given String. Non-string arguments are converted to strings using the standard conversion rules. Supported by SQL version 2015-10-08 and later.

Examples:

lower("HELLO") = "hello".

lower(["HELLO"]) = "[\"hello\"]".

Returns the String argument, padded on the left side with the number of spaces specified by the second argument. The Int argument must be between 0 and 1000. If the provided value is outside of this valid range, the argument is set to the nearest valid value (0 or 1000). Supported by SQL version 2015-10-08 and later.

Examples:

lpad("hello", 2) = "  hello".

lpad(1, 3) = "   1"

Removes all leading white space (tabs and spaces) from the provided String. Supported by SQL version 2015-10-08 and later.

Example:

Ltrim(" h i ") = "hi ".

Use the machinelearning_predict function to make predictions using the data from an MQTT message based on an Amazon SageMaker AI model. Supported by SQL version 2015-10-08 and later. The arguments for the machinelearning_predict function are:

modelId

The ID of the model against which to run the prediction. The real-time endpoint of the model must be enabled.

roleArn

The IAM role that has a policy with machinelearning:Predict and machinelearning:GetMLModel permissions and allows access to the model against which the prediction is run.

record

The data to be passed into the SageMaker AI Predict API. This should be represented as a single layer JSON object. If the record is a multi-level JSON object, the record is flattened by serializing its values. For example, the following JSON:

{ "key1": {"innerKey1": "value1"}, "key2": 0}

would become:

{ "key1": "{\"innerKey1\": \"value1\"}", "key2": 0}

The function returns a JSON object with the following fields:

predictedLabel

The classification of the input based on the model.

details

Contains the following attributes:

PredictiveModelType

The model type. Valid values are REGRESSION, BINARY, MULTICLASS.

Algorithm

The algorithm used by SageMaker AI to make predictions. The value must be SGD.

predictedScores

Contains the raw classification score corresponding to each label.

predictedValue

The value predicted by SageMaker AI.

Returns the remainder of the division of the first argument by the second argument. Equivalent to remainder(Decimal, Decimal). You can also use "%" as an infix operator for the same modulo functionality. Supported by SQL version 2015-10-08 and later.

Example: mod(8, 3) = 2.

Returns the first argument if it is a valid Decimal. Otherwise, the second argument is returned. Supported by SQL version 2015-10-08 and later.

Example: Nanvl(8, 3) = 8.

Returns a random 16-byte UUID. Supported by SQL version 2015-10-08 and later.

Example: newuuid() = 123a4567-b89c-12d3-e456-789012345000

Returns the number of bytes in the UTF-8 encoding of the provided string. Standard conversion rules apply to non-String arguments. Supported by SQL version 2016-03-23 and later.

Examples:

numbytes("hi") = 2

numbytes("€") = 3

Use the parse_time function to format a timestamp into a human-readable date/time format. Supported by SQL version 2016-03-23 and later. To convert a timestamp string into milliseconds, see time_to_epoch(String, String).

The parse_time function expects the following arguments:

pattern

(String) A date/time pattern that follows Joda-Time formats.

timestamp

(Long) The time to be formatted in milliseconds since Unix epoch. See function timestamp().

timezone

(String) The time zone of the formatted date/time. The default is "UTC". The function supports Joda-Time time zones. This argument is optional.

Examples:

When this message is published to the topic 'A/B', the payload {"ts": "1970.01.01 AD at 21:46:40 CST"} is sent to the S3 bucket:

{
    "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME",
    "topicRulePayload": {
        "sql": "SELECT parse_time(\"yyyy.MM.dd G 'at' HH:mm:ss z\", 100000000, 'America/Belize' ) as ts FROM 'A/B'",

        "ruleDisabled": false,
        "awsIotSqlVersion": "2016-03-23",
        "actions": [
            {
                "s3": {
                    "roleArn": "arn:aws:iam::ACCOUNT_ID:rule:role/ROLE_NAME",
                    "bucketName": "BUCKET_NAME",
                    "key": "KEY_NAME"
                }
            }
        ],
        "ruleName": "RULE_NAME"
    }
} 

When this message is published to the topic 'A/B', a payload similar to {"ts": "2017.06.09 AD at 17:19:46 UTC"} (but with the current date/time) is sent to the S3 bucket:

{
    "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME",
    "topicRulePayload": {
        "sql": "SELECT parse_time(\"yyyy.MM.dd G 'at' HH:mm:ss z\", timestamp() ) as ts FROM 'A/B'",
        "awsIotSqlVersion": "2016-03-23",
        "ruleDisabled": false,
        "actions": [
            {
                "s3": {
                    "roleArn": "arn:aws:iam::ACCOUNT_ID:rule:role/ROLE_NAME",
                    "bucketName": "BUCKET_NAME",
                    "key": "KEY_NAME"
                }
            }
        ],
        "ruleName": "RULE_NAME"
    }
} 

parse_time() can also be used as a substitution template. For example, when this message is published to the topic 'A/B', the payload is sent to the S3 bucket with key = "2017":

{
    "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME",
    "topicRulePayload": {
        "sql": "SELECT * FROM 'A/B'",
        "awsIotSqlVersion": "2016-03-23",
        "ruleDisabled": false,
        "actions": [{
            "s3": {
                "roleArn": "arn:aws:iam::ACCOUNT_ID:rule:role/ROLE_NAME",
                "bucketName": "BUCKET_NAME",
                "key": "${parse_time('yyyy', timestamp(), 'UTC')}"
            }
        }],
        "ruleName": "RULE_NAME"
    }
}

Returns the first argument raised to the second argument. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Supported by SQL version 2015-10-08 and later.

Example: power(2, 5) = 32.0.

Returns the principal that the device uses for authentication, based on how the triggering message was published. The following table describes the principal returned for each publishing method and protocol.

The following examples show the different types of values that principal() can return:

• X.509 certificate thumbprint: ba67293af50bf2506f5f93469686da660c7c844e7b3950bfb16813e0d31e9373

• IAM role ID and session name: ABCD1EFG3HIJK2LMNOP5:my-session-name

• Returns a user ID: ABCD1EFG3HIJK2LMNOP5

Returns a pseudorandom, uniformly distributed double between 0.0 and 1.0. Supported by SQL version 2015-10-08 and later.

Example:

rand() = 0.8231909191640703

Returns true if the string (first argument) contains a match for the regular expression (second argument). If you use | in the regular expression, use it with ().

Examples:

regexp_matches("aaaa", "a{2,}") = true.

regexp_matches("aaaa", "b") = false.

regexp_matches("aaa", "(aaa|bbb)") = true.

regexp_matches("bbb", "(aaa|bbb)") = true.

regexp_matches("ccc", "(aaa|bbb)") = false.

Second argument:

Must be a valid regex expression. Non-string types are converted to String using the standard conversion rules. Depending on the type, the resultant string might not be a valid regular expression. If the (converted) argument is not valid regex, the result is Undefined.

Replaces all occurrences of the second argument (regular expression) in the first argument with the third argument. Reference capture groups with "$". Supported by SQL version 2015-10-08 and later.

Example:

regexp_replace("abcd", "bc", "x") = "axd".

regexp_replace("abcd", "b(.*)d", "$1") = "ac".

Second argument:

Must be a valid regex expression. Non-string types are converted to String using the standard conversion rules. Depending on the type, the resultant string might not be a valid regular expression. If the (converted) argument is not a valid regex expression, the result is Undefined.

Third argument:

Must be a valid regex replacement string. (Can reference capture groups.) Non-string types are converted to String using the standard conversion rules. If the (converted) argument is not a valid regex replacement string, the result is Undefined.

Finds the first match of the second parameter (regex) in the first parameter. Reference capture groups with "$". Supported by SQL version 2015-10-08 and later.

Example:

regexp_substr("hihihello", "hi") = "hi"

regexp_substr("hihihello", "(hi)*") = "hihi"

Second argument:

Must be a valid regex expression. Non-string types are converted to String using the standard conversion rules. Depending on the type, the resultant string might not be a valid regular expression. If the (converted) argument is not a valid regex expression, the result is Undefined.

Returns the remainder of the division of the first argument by the second argument. Equivalent to mod(Decimal, Decimal). You can also use "%" as an infix operator for the same modulo functionality. Supported by SQL version 2015-10-08 and later.

Example: remainder(8, 3) = 2.

Replaces all occurrences of the second argument in the first argument with the third argument. Supported by SQL version 2015-10-08 and later.

Example:

replace("abcd", "bc", "x") = "axd".

replace("abcdabcd", "b", "x") = "axcdaxcd".

Returns the string argument, padded on the right side with the number of spaces specified in the second argument. The Int argument must be between 0 and 1000. If the provided value is outside of this valid range, the argument is set to the nearest valid value (0 or 1000). Supported by SQL version 2015-10-08 and later.

Examples:

rpad("hello", 2) = "hello  ".

rpad(1, 3) = "1   ".

Rounds the given Decimal to the nearest Int. If the Decimal is equidistant from two Int values (for example, 0.5), the Decimal is rounded up. Supported by SQL version 2015-10-08 and later.

Example: Round(1.2) = 1.

Round(1.5) = 2.

Round(1.7) = 2.

Round(-1.1) = -1.

Round(-1.5) = -2.

Removes all trailing white space (tabs and spaces) from the provided String. Supported by SQL version 2015-10-08 and later.

Examples:

rtrim(" h i ") = " h i"

Returns the sign of the given number. When the sign of the argument is positive, 1 is returned. When the sign of the argument is negative, -1 is returned. If the argument is 0, 0 is returned. Supported by SQL version 2015-10-08 and later.

Examples:

sign(-7) = -1.

sign(0) = 0.

sign(13) = 1.

Returns the sine of a number in radians. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: sin(0) = 0.0

Returns the hyperbolic sine of a number. Decimal values are rounded to double precision before function application. The result is a Decimal value of double precision. Supported by SQL version 2015-10-08 and later.

Example: sinh(2.3) = 4.936961805545957

Retrieves the IP address of a device or the router that connects to it. If your device is connected to the internet directly, the function will return the source IP address of the device. If your device is connected to a router that connects to the internet, the function will return the source IP address of the router. Supported by SQL version 2016-03-23. sourceip() doesn't take any parameters.

A device's public source IP address is often the IP address of the last Network Address Translation (NAT) Gateway such as your internet service provider's router or cable modem.

Examples:

sourceip()="192.158.1.38"

sourceip()="1.102.103.104"

sourceip()="2001:db8:ff00::12ab:34cd"

SQL example:

SELECT *, sourceip() as deviceIp FROM 'some/topic'

Examples of how to use the sourceip() function in AWS IoT Core rule actions:

Example 1

The following example shows how to call the () function as a substitution template in a DynamoDB action.

{
	"topicRulePayload": {
		"sql": "SELECT * AS message FROM 'some/topic'",
		"ruleDisabled": false,
		"awsIotSqlVersion": "2016-03-23",
		"actions": [
			{
				"dynamoDB": {
					"tableName": "my_ddb_table",
					"hashKeyField": "key",
					"hashKeyValue": "${sourceip()}",
					"rangeKeyField": "timestamp",
					"rangeKeyValue": "${timestamp()}",
					"roleArn": "arn:aws:iam::123456789012:role/aws_iot_dynamoDB"
				}
			}
		]
	}
}

Example 2

The following example shows how to add the sourceip() function as an MQTT user property using substitution templates.

{
	"topicRulePayload": {
		"sql": "SELECT * FROM 'some/topic'",
		"ruleDisabled": false,
		"awsIotSqlVersion": "2016-03-23",
		"actions": [
			{
				"republish": {
					"topic": "${topic()}/republish",
					"roleArn": "arn:aws:iam::123456789012:role/aws_iot_republish",
					"headers": {
						"payloadFormatIndicator": "UTF8_DATA",
						"contentType": "rule/contentType",
						"correlationData": "cnVsZSBjb3JyZWxhdGlvbiBkYXRh",
						"userProperties": [
							{
								"key": "ruleKey1",
								"value": "ruleValue1"
							},
							{
								"key": "sourceip",
								"value": "${sourceip()}"
							}
						]
					}
				}
			}
		]
	}
}            

You can retrieve the source IP address from messages passing to AWS IoT Core rules from both Message Broker and Basic Ingest pathways. You can also retrieve the source IP for both IPv4 and IPv6 messages. The source IP will be displayed like the following:

IPv6: yyyy:yyyy:yyyy::yyyy:yyyy

IPv4: xxx.xxx.xxx.xxx

Expects a String followed by one or two Int values. For a String and a single Int argument, this function returns the substring of the provided String from the provided Int index (0-based, inclusive) to the end of the String. For a String and two Int arguments, this function returns the substring of the provided String from the first Int index argument (0-based, inclusive) to the second Int index argument (0-based, exclusive). Indices that are less than zero are set to zero. Indices that are greater than the String length are set to the String length. For the three argument version, if the first index is greater than (or equal to) the second index, the result is the empty String.

 If the arguments provided are not (String, Int), or (String, Int, Int), the standard conversions are applied to the arguments to attempt to convert them into the correct types. If the types cannot be converted, the result of the function is Undefined. Supported by SQL version 2015-10-08 and later.

Examples:

substring("012345", 0) = "012345".

substring("012345", 2) = "2345".

substring("012345", 2.745) = "2345".

substring(123, 2) = "3".

substring("012345", -1) = "012345".

substring(true, 1.2) = "rue".

substring(false, -2.411E247) = "false".

substring("012345", 1, 3) = "12".

substring("012345", -50, 50) = "012345".

substring("012345", 3, 1) = "".

Returns the SQL version specified in this rule. Supported by SQL version 2015-10-08 and later.

Example:

sql_version() = "2016-03-23"

Returns the square root of a number. Decimal arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: sqrt(9) = 3.0.

Returns Boolean, whether the first string argument starts with the second string argument. If either argument is Null or Undefined, the result is Undefined. Supported by SQL version 2015-10-08 and later.

Example:

startswith("ranger","ran") = true

Returns the tangent of a number in radians. Decimal values are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: tan(3) = -0.1425465430742778

Returns the hyperbolic tangent of a number in radians. Decimal values are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later.

Example: tanh(2.3) = 0.9800963962661914

Use the time_to_epoch function to convert a timestamp string into a number of milliseconds in Unix epoch time. Supported by SQL version 2016-03-23 and later. To convert milliseconds to a formatted timestamp string, see parse_time(String, Long[, String]).

The time_to_epoch function expects the following arguments:

timestamp

(String) The timestamp string to be converted to milliseconds since Unix epoch. If the timestamp string doesn't specify a timezone, the function uses the UTC timezone.

pattern

(String) A date/time pattern that follows JDK11 Time Formats.

Examples:

time_to_epoch("2020-04-03 09:45:18 UTC+01:00", "yyyy-MM-dd HH:mm:ss VV") = 1585903518000

time_to_epoch("18 December 2015", "dd MMMM yyyy") = 1450396800000

time_to_epoch("2007-12-03 10:15:30.592 America/Los_Angeles", "yyyy-MM-dd HH:mm:ss.SSS z") = 1196705730592

Returns the current timestamp in milliseconds from 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, as observed by the AWS IoT rules engine. Supported by SQL version 2015-10-08 and later.

Example: timestamp() = 1481825251155

Returns the topic to which the message that triggered the rule was sent. If no parameter is specified, the entire topic is returned. The Decimal parameter is used to specify a specific topic segment, with 1 designating the first segment. For the topic foo/bar/baz, topic(1) returns foo, topic(2) returns bar, and so on. Supported by SQL version 2015-10-08 and later.

Examples:

topic() = "things/myThings/thingOne"

topic(1) = "things"

When Basic Ingest is used, the initial prefix of the topic ($aws/rules/rule-name) is not available to the topic() function. For example, given the topic:

$aws/rules/BuildingManager/Buildings/Building5/Floor2/Room201/Lights

topic() = "Buildings/Building5/Floor2/Room201/Lights"

topic(3) = "Floor2"

Returns the trace ID (UUID) of the MQTT message, or Undefined if the message wasn't sent over MQTT. Supported by SQL version 2015-10-08 and later.

Example:

traceid() = "12345678-1234-1234-1234-123456789012"

Returns an array of objects that contains the result of the specified transformation of the Object parameter on the Array parameter.

Supported by SQL version 2016-03-23 and later.

String

The transformation mode to use. Refer to the following table for the supported transformation modes and how they create the Result from the Object and Array parameters.

Object

An object that contains the attributes to apply to each element of the Array.

Array

An array of objects into which the attributes of Object are applied.

Each object in this Array corresponds to an object in the function's response. Each object in the function's response contains the attributes present in the original object and the attributes provided by Object as determined by the transformation mode specified in String.

String parameter

Object parameter

Array parameter

Result

enrichArray

Object

Array of objects

An Array of objects in which each object contains the attributes of an element from the Array parameter and the attributes of the Object parameter.

Any other value

Any value

Any value

Undefined

The array returned by this function is limited to 128 KiB.

This example shows how the transform() function produces a single array of objects from a data object and an array.

In this example, the following message is published to the MQTT topic A/B.

{
    "attributes": {
        "data1": 1,
        "data2": 2
    },
    "values": [
        {
            "a": 3
        },
        {
            "b": 4
        },
        {
            "c": 5
        }
    ]
}

This SQL statement for a topic rule action uses the transform() function with a String value of enrichArray. In this example, Object is the attributes property from the message payload and Array is the values array, which contains three objects.

select value transform("enrichArray", attributes, values) from 'A/B'

Upon receiving the message payload, the SQL statement evaluates to the following response.

[
  {
    "a": 3,
    "data1": 1,
    "data2": 2
  },
  {
    "b": 4,
    "data1": 1,
    "data2": 2
  },
  {
    "c": 5,
    "data1": 1,
    "data2": 2
  }
]

This example shows how the transform() function can use literal values to include and rename individual attributes from the message payload.

In this example, the following message is published to the MQTT topic A/B. This is the same message that was used in Transform function example 1.

{
    "attributes": {
        "data1": 1,
        "data2": 2
    },
    "values": [
        {
            "a": 3
        },
        {
            "b": 4
        },
        {
            "c": 5
        }
    ]
}

This SQL statement for a topic rule action uses the transform() function with a String value of enrichArray. The Object in the transform() function has a single attribute named key with the value of attributes.data1 in the message payload and Array is the values array, which contains the same three objects used in the previous example.

select value transform("enrichArray", {"key": attributes.data1}, values) from 'A/B'

Upon receiving the message payload, this SQL statement evaluates to the following response. Notice how the data1 property is named key in the response.

[
  {
    "a": 3,
    "key": 1
  },
  {
    "b": 4,
    "key": 1
  },
  {
    "c": 5,
    "key": 1
  }
]

This example shows how the transform() function can be used in nested SELECT clauses to select multiple attributes and create new objects for subsequent processing.

In this example, the following message is published to the MQTT topic A/B.

{
  "data1": "example",
  "data2": {
    "a": "first attribute",
    "b": "second attribute",
    "c": [
      {
        "x": {
          "someInt": 5,
          "someString": "hello"
        },
        "y": true
      },
      {
        "x": {
          "someInt": 10,
          "someString": "world"
        },
        "y": false
      }
    ]
  }
}

The Object for this transform function is the object returned by the SELECT statement, which contains the a and b elements of the message's data2 object. The Array parameter consists of the two objects from the data2.c array in the original message.

select value transform('enrichArray', (select a, b from data2), (select value c from data2)) from 'A/B'

With the preceding message, the SQL statement evaluates to the following response.

[
  {
    "x": {
      "someInt": 5,
      "someString": "hello"
    },
    "y": true,
    "a": "first attribute",
    "b": "second attribute"
  },
  {
    "x": {
      "someInt": 10,
      "someString": "world"
    },
    "y": false,
    "a": "first attribute",
    "b": "second attribute"
  }
]

The array returned in this response could be used with topic rule actions that support batchMode.

Removes all leading and trailing white space from the provided String. Supported by SQL version 2015-10-08 and later.

Example:

Trim(" hi ") = "hi"

Truncates the first argument to the number of Decimal places specified by the second argument. If the second argument is less than zero, it is set to zero. If the second argument is greater than 34, it is set to 34. Trailing zeroes are stripped from the result. Supported by SQL version 2015-10-08 and later.

Examples:

trunc(2.3, 0) = 2.

trunc(2.3123, 2) = 2.31.

trunc(2.888, 2) = 2.88.

trunc(2.00, 5) = 2.

Returns the uppercase version of the given String. Non-String arguments are converted to String using the standard conversion rules. Supported by SQL version 2015-10-08 and later.

Examples:

upper("hello") = "HELLO"

upper(["hello"]) = "[\"HELLO\"]"

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4