Run SQL on DynamoDB Tables (Using a Visual Editor)
Written by Rafal Wilinski
Published on November 24th, 2020
Time to 10x your DynamoDB productivity with Dynobase [learn more]
DynamoDB and SQL
One of the most popular complaints about DynamoDB is its learning curve. Not only the way it works is radically different from most of the SQL-based databases we know, but also, the syntax used to query and manipulate data is hard to grasp.
The same fact was also causing migrations to be much harder to conduct. If you're going from, let's say, MySQL engine, it's not a regular "heterogenous" migration. It's a massive effort in which you need to rethink and reimplement your database structure and querying language, often effectively leading to extensive rewrites.
This is about to get a bit easier because DynamoDB now supports PartiQL - an SQL-like language for querying and manipulating data.
Dynobase and SQL / PartiQL
We're excited to announce that Dynobase supports querying and modifying data using PartiQL / SQL too!
Moreover, with Dynobase you can now also bookmark, stream, and browse the history of your PartiQL/SQL queries that ran on your DynamoDB tables.
Learn more about using PartiQL for querying DynamoDB here.
What is PartiQL?
PartiQL is SQL-like query language for accessing your data from multiple data sources like S3, Redshift, QLDB, and now - DynamoDB. PartiQL is compatible with SQL in most basic cases.
What are the limitations of PartiQL / SQL in DynamoDB?
The limitations of querying and modifying the data remain the same. You still cannot update or delete records in batches, each update or delete operation must be performed on a singular record. In order to delete or update multiple items, you need to run multiple queries or use our built-in Terminal feature which allows running conditional and mass updates.
How expensive is using SQL with DynamoDB?
Costs of running queries and modifying data in DynamoDB using PartiQL and using standard syntax is exactly the same.
Should I use PartiQL or learn DynamoDB syntax?
While PartiQL allows you to use SQL-like syntax you're already familiar with, it abstracts away the details of how DynamoDB works. This may lead to confusion and suboptimal performance in some cases. PartiQL may transform certain queries into scan operations which are slow and generally speaking discouraged unless used with caution.
How can I use PartiQL/SQL to query my table?
There are four ways to start using SQL to query your tables:
- Using Dynobase
- Using new AWS Console
- Using AWS Athena
- Using SDK/CLI
Are there any prerequisites before I can start using SQL to query DynamoDB?
Make sure that you have
dynamodb:PartiQL IAM actions added to your IAM policies.
Learn more about PartiQL here.
© 2022 Dynobase