amazon-web-servicesamazon-dynamodbpartiql

Is it possible to use JOIN in PartiQL in Dynamo?

From the AWS guide here: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/EMRforDynamoDB.Querying.html

SELECT ecs.state_name, f.feature_name, f.feature_class 
FROM s3_east_coast_states ecs 
JOIN ddb_features f ON ecs.state_alpha = f.state_alpha 
WHERE ecs.state_name LIKE 'New%';

That's definitely a JOIN. But when I run a join:

SELECT * FROM "division-allocations-dev" da JOIN "branch-division-dev" bd ON bd.divisionID = da.divisionID where da.divisionID = 499;"

I get this error:

Only select from a single table or index is supported.

Now those docs are specific to EMR for Dynamo, so is a JOIN only allowed in the EMR tool? PartiQL definitely has JOINs so is Dynamo only supporting a subset of PartiQL? If so, where do I find a list of what Dynamo supports?

Solution

  • Is it possible to use JOIN in PartiQL in Dynamo?

    Short answer: no.

    Dynamo only supporting a subset of PartiQL?

    Yes. The DynamoDB PartiQL subset provides familiar syntax consistent with the (no-join) core API.

    where do I find a list of what Dynamo supports?

    See the docs. You get SELECT (no joins), UPDATE, INSERT and DELETE.