dynamodb PartiQL SELECT query returns ValidationException: Unexpected from source

Question

I am using Amplify to setup a dynamodb with a corresponding lambda using the amplify blueprint for dynamodb.

Accessing the dynamodb the "classic" way with KeyConditionExpression etc works just fine but today I wanted to try and use PartiQL instead with the executeStatement and I am just not able to get it to work.

I have added the "dynamodb:PartiQLSelect" permission to the cloudfront template where all the other dynamodb permissions are so it looks like:

 "Action": [
                "dynamodb:DescribeTable",
                "dynamodb:GetItem",
                "dynamodb:Query",
                "dynamodb:Scan",
                "dynamodb:PutItem",
                "dynamodb:UpdateItem",
                "dynamodb:DeleteItem",
                "dynamodb:PartiQLSelect"
              ],

and I do not get any permission error so I hope that part is ok, it does however return the same error even without that line added.

The error that is always returned is: ValidationException: Unexpected from source"

and no matter what I have tried, it does not help. My code is quite basic so far:

const dynamodb2 = new AWS.DynamoDB();

let tableName = "habits_sensors";
if(process.env.ENV && process.env.ENV !== "NONE") {
  tableName = tableName + '-' + process.env.ENV;
}

app.get(path, function(req, res) {
  let params = {
    Statement: `select * from ${tableName}`
  };

  dynamodb2.executeStatement(params, (err, data) => {
    if (err) {
      res.statusCode = 500;
      res.json({error: `Could not get users from : ${tableName} =>` + err});
    } else {
      res.json(data.Items);
    }
  });
});

The complete error string returned from the lambda is:

{
    "error": "Could not get users from : habits_sensors-playground =>ValidationException: Unexpected from source"
}

and I have the table habits_sensors-playground in my AWS account and I can access it the classic way without problems. That is why the "Unexpected from source" is confusing. I interpret it as referring to that the tableName (in from) in the select query is not correct but the name is matching what I have in AWS and it works using the documentclient.

Any suggestion on what might be wrong is very appreciated.

score 50 · Accepted Answer · answered Jan 27 '21 at 10:37

50

Answering myself in case anyone else ends up here.

I got a reply from AWS that if the table name contains dashes, you need to quote the table name with double quotes when using PartiQL (I had tried single quotes and that did not work).

Maybe this will change in a future release of PartiQL.

answered Jan 27 '21 at 10:37

user1816142

1,199
2
9
16

1

even in noSQL Workbench – NanoNova Mar 23 '22 at 09:29
AWS suggests using full quotes on all table resource names (columns, tables). Don't have a link handy but I did read that somewhere. – jcollum Apr 25 '22 at 16:10
Thanks man. Your reply saved me from a huge disappointment – Shaggie Jun 16 '22 at 10:02
thanks for reporting. THat was unexpected! – Karl Pokus Oct 11 '22 at 13:39

Jaakkonen · Answer 2 · 2022-09-21T18:39:01.117

The exception ValidationException: Unexpected from source (CLI: An error occurred (ValidationException) when calling the ExecuteStatement operation: Unexpected from source) happens when table name contains dashes and is not quoted.

So change

aws dynamodb execute-statement --statement \
  "SELECT * FROM my-table WHERE field='string'"

To:

aws dynamodb execute-statement --statement \
  "SELECT * FROM \"my-table\" WHERE field='string'"

or add the double quotes " around the table name in the SDK you're using to use PartiQL.

Note that the WHERE string='value' uses single quotes ' and the table name requires double quotes ".

score 2 · Answer 3 · answered Sep 16 '21 at 13:26

For anyone else who ends up here as I did, I had a powershell script generating a statement for aws dynamodb execute-statement (aws --version 2.x) and was getting the same error. After far too long, I tried the interactive cli and found that my query worked, so what I ended up needing to do was escape the double quotes for both powershell purposes AND again with \ characters for the AWS CLI.

$statement = "SELECT id FROM \`"${tablename}\`" WHERE source = '990doc'"

This double escaping finally got me where I needed to be, and will hopefully save someone else a great deal of frustration.

score 1 · Answer 4 · answered May 10 '22 at 18:53

Adding this solution for anyone ended up here with this error from AWS SDK(Javascript).


const { Items = [] } = await dynamodbClient.executeStatement({
        Statement: `SELECT * FROM "${tablename}" WHERE "_usedId" IS MISSING` 
    }).promise();

Surround the table name with double quotes as mentioned by the answers above.

dynamodb PartiQL SELECT query returns ValidationException: Unexpected from source

4 Answers4