Combine the Simplicity of SQL with the Power of NoSQL (Pt-1)

This is part one of a two-part blog series. It has been written with the assumption that the readers of this blog have some foundation knowledge about DynamoDB, its data structure, and the typical access patterns i.e. query v/s scan, etc. Now, Let’s get started.

Image 1

Introduction

DynamoDB offers multiple ways to interact with the service and access the underlying table data. Commonly used methods are —

1. AWS management console— The most preferred way for beginners to get started with
2. NoSQL workbench — Nice intuitive way to perform CRUD (create, read, update & delete) operation using a GUI tool installed on your laptop
3. CLI & SDKs — Preferred way for automating stuff and programming web/mobile or any other application backends

Image 2: DynamoDB API Interface

As illustrated in this image, irrespective of the method you chose, they all interact with the same API interface. These tools only take away the complexity of creating standardized request DynamoDB API expects and leaves us with only filling up a few required fields. Does it mean programming a DynamoDB application is simple? The short answer is YES. The long answer is — It depends on the level of familiarity with the service. For starters (especially from an SQL background), it can be a slow start to adapt to DynamoDB syntaxes. On the surface, it looks effortless to interact with tables and query/manipulate data. Most of the time, we (in this context “we” means “I”) need to look through the developer’s guide to revive our memory about syntaxes and nitty-gritty details.

In this blog, I will explain a relatively new feature in DynamoDB i.e. PartiQL takes away this small, although noticable effort. We will learn how PartiQL simplifies DynamoDB table operations even further and thereby enhances our productivity.

What is PartiQL?

It is a SQL-compatible query language to access structured, semi-structured, and nested data. It has a wide variety of use cases, although, in the context of DynamoDB, it is used to perform CRUD operations on the table data using SQL. PartiQL is supported in all the methods I mentioned before i.e. console, CLI/SDK, and NoSQL workbench

Why do we need it?

Below are a few reasons one would choose PartiQL over DynamoDB DSL.

1. The simplicity of SQL

DynamoDB has a wide range of programming language support. Then why do we need another language i.e. SQL on top of it?
Let’s understand it with an example — Below is a simple table with 4 attributes —

Image 3: Movie table data structure

Imagine you have a mobile app where users can rate the movies by a star rating ((1- 5) system. Now, let’s see a python code snippet that updates the number of votes and stars when a user submits a rating.

Terms like UpdateExpression, ExpressionAttributeValues, ExpressionAttributeNames (in case you are using reserved DynamoDB keyword) are purely DynamoDB specific. It may not sound familiar or make a lot of sense when a newbie on DynamoDB looks at it.

Now let's see the PartiQL version of the same scenario —

The UPDATE SQL statement looks quite familiar already. It does not involve any DynamoDB-specific keywords. It uses an SQL statement to update the item in a NoSQL table. As the title of this blog says — Now you can harness NoSQL power by SQL’s simplicity.

2. Easy to learn — Although there is a learning curve to understand PartQL, it is relatively less compared to traditional DynamoDB DSL. SQL is a very well-known language among most software engineers. Taking this assumption into account, it would be easier to switch to a NoSQL database like DynamoDB.

3. Faster Development —As compared to the older approach, PartiQL allows one to increase productivity since less time is spent looking at AWS documents. Writing SQL code feels quite natural without memorizing a lot of DynamoDB terms.

Key Considerations while using PartiQL

Things you can perform with PariQL —

1. CRUD operations on a DynamoDB table are supported
2. Actions like query & scan can be replaced with SELECT statements with appropriate predicates in the “WHERE” clause
3. You can also use it on table indexes i.e. global secondary index (GSI) & local secondary index (LSI)

Things to keep in mind while using PartiQL —

1. Now that you can use SQL statements to access a DynamoDB table, it does not mean that you can run COUNT() function. The LIMIT clause is not supported either.
2. The main purpose is to complement the existing methods by adding SQL simplicity
3. PartiQL on DynamoDB is strictly for OLTP use cases. In part 2 of this blog, I will explain how you can run OLAP queries on DynamoDB
4. You might wonder whether PartiQL is faster/slower than traditional methods GetItem/PutItem etc. And what about Query v/s Scan? Does it vanish with the introduction of PartQL? The answer is — they are all there under the hood. PartiQL queries are translated internally to make it DynamoDB API-supported format; hence it is transparent to the end users.
5. It is free of cost and AWS does not charge any extra cents. However, it is quite easy to make a mistake while writing a SELECT statement. If the “WHERE” clause involves non-key attributes (other than PK & SK), it will result in a table scan. It can cause performance and cost penalties. You can restrict tables scan by putting an additional condition in the IAM policy.

Sample Code

Here are a few samples of CLI commands using PartiQL to perform CRUD operations on the “Movies” (Ref — Image 2)table.

1. Create an item

aws dynamodb execute-statement --statement "INSERT INTO movies VALUE 
{'year':1996,'title':'mission impossible', 'num_votes':90, 'total_stars':360}"

2. Query/Read an item (Note — You can also use operators such as IS, LIKE, IN, NOT IN, CONTAINS, BEGINS WITH, etc.)

aws dynamodb execute-statement --statement "select * from movies where year=1996 and title = 'mission impossible'"

3. Update an item

ws dynamodb execute-statement --statement "update movies set total_stars=400 where year=1996 and title='mission impossible'"

4. Delete an item

aws dynamodb execute-statement --statement "delete from movies where year=1996 and title = 'mission impossible'"

Conclusion

In this blog, We learned how PartiQL is capable of simplifying app programming on DynamoDB. The main takeaway is that — if you are already proficient at accessing tables using the traditional approach, then you will not benefit a lot from PartiQL except to have one more option. For those just getting started with DynamoDB, PartiQL can provide a great headstart without requiring too much effort. We should also keep in mind that — PartiQL is strictly for OLTP use cases. In the second part of this blog, I will explain a way to run “OLAP” workload on DynamoDB. Stay tuned!