AWS IoT - Fails to deploy rule containing SQL get function?

Serverless Framework
1

Hi guys. I’m having trouble deploying a AWS IoT rule that contains the SQL function get. The SQL works fine if I create the rule manually through the AWS console, so my guess is that it triggers something in CloudFormation. Any idea what the problem might be? Can I “escape” the function somehow, or disable validation?

–Yaml code–
functions:
updateComponentState:
handler: updateComponentState.handler
events:
- iot:
name: “catchStateDeviceEvents”
sql: “SELECT topic(3) as stateDeviceId, timestamp as stateTime, version as stateVersion, case state.reported.activeUUID = get(state.reported.sensorUIIDs, 0).uuid when true then ‘Closed’ else ‘Open’ end as stateCurrent FROM ‘$aws/things/+/shadow/update/accepted’ WHERE regexp_matches(topic(3), ‘magnetSensor’) = true”

If I remove the get part, or put single quotes around it in the form of ‘get(state.reported.sensorUIIDs, 0).uuid’, it deploys, but obviously putting single quotes around it makes the rule defect.

–Error–
An error occurred: UpdateComponentStateIotTopicRule1 - Errors encountered while validating query.
ERROR: 1-indexed number is out of bounds: 0.

Any ideas? I’ve spent quite a bit of time trying to figure this out, but am none the wiser.

2

i was having issues with serverless on IOT too, so I just avoided it and wrote direct cloud formation calls in my yaml file.

If this doesn’t work, enter your SQL via the WebUI and make sure it is valid.

  resources:
    Resources:
      PresenceIotTopicRule1:
        Type: AWS::IoT::TopicRule
        Properties:
          TopicRulePayload:
            RuleDisabled: false
            Sql: "SELECT * FROM '$aws/events/presence/#'"
            Actions:
              - Lambda:
                  FunctionArn:
                    'Fn::GetAtt': [ ClipLambdaFunction,Arn ]
      PresenceLambdaPermissionIotTopicRule1:
        Type: AWS::Lambda::Permission
        Properties:
          FunctionName:
            "Fn::GetAtt": [ ClipLambdaFunction,Arn ]
          Action: lambda:InvokeFunction
          Principal: iot.amazonaws.com
          SourceArn:
            Fn::Join:
            - ""
            - - "arn:aws:iot:"
              - Ref: AWS::Region
              - ":"
              - Ref: AWS::AccountId
              - ":rule/"
              - Ref: PresenceIotTopicRule1
3

I found a solution, and as usual it turned out to be my own stupidity. Serverless deploy seems to default to sql version 2015-10-08, and it doesn’t support the get function. When I specified sql version 2016-03-23 in serverless.yml, I could deploy just fine.

If you deploy with: Type: “AWS::IoT::TopicRule”
You need to specify: AwsIotSqlVersion: “2016-03-23”

I haven’t tested the serverless default syntax, but it is documented here: https://serverless.com/framework/docs/providers/aws/events/iot/

sqlVersion: “2016-03-23”

1 Like
4

This solved my issue too.
Thanks heaps. I think the problem is with AWS’s cfn documentation. They should mention the difference between IOTSQL parser versions, and the default version is the old one before saying it’s not required.