Amazon DynamoDB supports PartiQL, a SQL-compatible query language, to select, insert, update, and delete data in Amazon DynamoDB. Using PartiQL, you can easily interact with DynamoDB tables and run ad hoc queries using the AWS Management Console. In this exercise, we will hands-on a few access patterns using PartiQL statements.
SELECT * FROM "movies"
WHERE "mpkey" = 'tt0309377' and begins_with("mskey",'DETL|')
SELECT * FROM "movies"
WHERE "mpkey" = 'tt0309377' and begins_with("mskey",'DETL|actor')
SELECT * FROM "movies"
WHERE "mpkey" = 'tt0309377' and begins_with("mskey",'DETL|') and "ordering" = '1'
SELECT * FROM "movies"
WHERE "mpkey" = 'tt0309377' and begins_with("mskey",'REGN|')
SELECT * FROM "movies"
WHERE "mpkey" = 'tt0309377' and begins_with("mskey",'REGN|NZ')
SELECT * FROM "movies"
WHERE "mpkey" = 'tt0309377' and begins_with("mskey",'REGN|') and "types" = 'original'
To access information at the crew member level (#6 in the access pattern), we need to create an additional Global Secondary Index (GSI) with a new partition key nconst (unique for crew member). This will allow querying on the new partition key for GSI vs scan on the base table.
Parameter | Value |
---|---|
Partition key | nconst |
Data type | string |
Sort key - optional | startYear |
Data type | Number |
Attribute projections | All |
SELECT * FROM "movies"."nconst-startYear-index"
WHERE "nconst" = 'nm0000142'
SELECT * FROM "movies"."nconst-startYear-index"
WHERE "nconst" = 'nm0000142' and "startYear" >= '2002'
ORDER BY "startYear"
Congratulations! you have completed the RDBMS migration exercise.