Beginner8 min read

SQL for DynamoDB and the Limits of PartiQL

DynamoDB answers key-shaped SQL through PartiQL and nothing relational: no JOIN, GROUP BY or aggregates. This page maps what PartiQL covers and the ways to run the queries it can't express.

Can you query DynamoDB with SQL?

Partly. AWS describes as "a SQL-compatible query language, to select, insert, update, and delete data in Amazon DynamoDB", and says "Amazon DynamoDB supports a subset of the PartiQL query language."

So SELECT * FROM "tickets" WHERE ticketId = 'T-1001' works, while JOIN, GROUP BY and COUNT(*) are out. For those you need a SQL engine on top: Athena, zero-ETL to Redshift, Hive on EMR, or DynoTable's SQL Workbench.

DynamoDB itself is a NoSQL key-value and document store, with no schema beyond the primary key and no query planner. PartiQL vs SQL has the feature-by-feature comparison.

PartiQL compiles to native operations

PartiQL maps SQL-looking statements onto the data-plane operations the SDK exposes. A SELECT with a equality compiles to a keyed read; without one, it's a Scan. Per the AWS SELECT reference:

Using the SELECT statement can result in a full table scan if an equality or IN condition with a partition key is not provided in the WHERE clause.

The access-pattern rules that govern Query and Scan still apply; PartiQL hides them behind familiar syntax. The statement-by-statement mapping is in PartiQL vs SQL.

A full scan meters every item it reads. On on-demand, an eventually consistent scan of a 500 MB table costs about 64,000 read request units (512,000 KB ÷ 4 KB × 0.5), whatever the WHERE keeps. Price yours in the pricing calculator.

What PartiQL covers

DynamoDB's PartiQL supports four DML/query statements:

  • SELECT: read items (a keyed read or a Scan)
  • INSERT: add one item (PutItem). It never overwrites: an existing key fails with DuplicateItemException.
  • UPDATE: modify one existing item (UpdateItem). It never creates: a missing key fails with ConditionalCheckFailedException.
  • DELETE: remove one item (DeleteItem)

There's no upsert, so for overwrite-or-create use the native PutItem or UpdateItem. PartiQL also supports transactions and batch operations. A well-formed read targets the partition key with an equality or IN:

SELECT ticketId, minutesSpent
FROM "tickets"
WHERE ticketId IN ['T-1001', 'T-1002', 'T-1003'] ORDER BY ticketId DESC

ORDER BY is allowed, but the AWS reference restricts the ordering key to "a hash key or a sort key": the partition or , not arbitrary columns.

To order by anything else, sort in your code, or model that order as a sort key or GSI sort key. For copy-paste-ready statements, see PartiQL examples.

What PartiQL can't do

These are the things developers most often expect from "SQL", and PartiQL supports none of them:

  • No JOIN. The PartiQL SELECT syntax is a single FROM {{table}}[.{{index}}]: one table or one index. Query each side by key and merge in code, or design so one read returns both (JOIN guide).
  • No GROUP BY. It isn't in the grammar. Pre-compute the groups on write, as the GROUP BY guide shows.
  • No aggregate functions. The PartiQL functions reference lists exactly one function under "Aggregate functions": SIZE, which returns an attribute's size in bytes for a single item. AWS states: "Any SQL functions that are not included in this list are not currently supported in DynamoDB." For counts and sums, see COUNT and SUM.
  • No LIKE, no subqueries, no UNION, no window functions. Pattern matching uses contains / begins_with. For a subquery, run the inner SELECT first and pass its keys in an IN [...] list (50 partition-key values at most); for UNION, run each SELECT and merge in code. Window functions need one of the SQL engines below.

So "support minutes per account last month", one line of GROUP BY elsewhere, can't be written in PartiQL. Natively you keep a running total per account as tickets are worked, or read the month's tickets and add them up in code.

Ways to run real SQL over DynamoDB

To get JOIN, GROUP BY and aggregates you need a SQL engine on top of the table. Three are documented by AWS, and DynoTable adds a desktop option:

OptionWhere the SQL runsPick it for
Athena DynamoDB connectorAthena, reading live tables via LambdaAd-hoc analysis and dashboards, no cluster to run
Zero-ETL to Amazon RedshiftRedshift, on a copy updated every 15–30 minRecurring reports, without load on the live table
Hive on Amazon EMRHiveQL on an EMR cluster you runLarge batch jobs and copies to S3 or HDFS
DynoTable SQL WorkbenchYour desktop, over rows read from DynamoDBQuick joins and rollups while you explore tables

How to query DynamoDB with real SQL via Amazon Athena

The Amazon Athena DynamoDB connector "enables Amazon Athena to communicate with DynamoDB so that you can query your tables with SQL." AWS's walkthrough is titled "Access, query, and join Amazon DynamoDB tables using Athena." Setting it up:

  1. In the Athena console, open Data sources, choose Create data source, then Amazon DynamoDB, and give it a name such as ddb.
  2. Create the connector's Lambda function when prompted. Its template asks for a spill bucket: an S3 bucket for results that exceed Lambda limits.
  3. In the query editor, pick the ddb data source. Your tables appear in its default database.
  4. Query them by catalog, database and table name, joins and GROUP BY included:
SELECT a.country, COUNT(*) AS tickets, SUM(t.minutesspent) AS minutes
FROM ddb.default.tickets t
JOIN ddb.default.accounts a ON t.accountid = a.accountid
GROUP BY a.country
ORDER BY minutes DESC

Use lowercase names in Athena; for mixed-case DynamoDB tables and attributes, the connector docs cover the sourceTable and columnMapping properties you set in AWS Glue.

Setup and cost:

  • It's a Lambda-based federated connector you deploy into your account (via the Athena console or the Serverless Application Repository), with a Glue connection for its configuration (and optionally Glue tables for schema) and an S3 bucket for spills.
  • It still reads through DynamoDB's Query and Scan. AWS warns that "queries that use scans can consume a large number of read capacity units (RCUs)." A partition-key predicate is pushed down as Query calls; anything else scans, so check the pricing calculator before you join big tables.
  • It reads only: INSERT INTO isn't supported, so writes still go through PartiQL or the SDK.

Once the connector is deployed, Athena suits ad-hoc analysis and BI dashboards. For a quick join while you browse tables, standing up a Lambda, Glue and a spill bucket is a lot of setup; that's the case the next section covers.

DynoTable SQL Workbench: SQL within DynamoDB's access-pattern rules

DynoTable's SQL Workbench runs JOIN, GROUP BY and COUNT/SUM/AVG against your live tables from a desktop client, with no Lambda, Glue or S3 to stand up. It reads the base table with Query or Scan and fetches the joined side by key, then runs one SELECT over the rows on your desktop:

-- Runs in the DynoTable Workbench (NOT in PartiQL).
-- accounts: partition key accountId, no sort key
SELECT a.country, COUNT(*) AS tickets, SUM(t.minutesSpent) AS minutes
FROM tickets t
INNER JOIN accounts a ON t.accountId = a.accountId
GROUP BY a.country
ORDER BY minutes DESC

Because every read still goes through DynamoDB's own operations, the editor shows an estimate of the base read before you run, and it enforces DynamoDB's access model:

  • INNER JOIN and LEFT JOIN only. The joined (to-) side must be keyed on the ON attribute: the partition key of a table with no sort key, or the partition key of exactly one GSI. To join to a table that has a sort key, flip the join or join on a GSI partition key. No RIGHT / FULL / CROSS / comma-join.
  • No self-joins (the same table on both sides), no subqueries, no derived tables, no CTEs or UNION, no window functions.
  • Joins and projections operate on scalar attributes.

If you only need the conditions and key expressions for the raw API, not a full SQL statement, the DynamoDB Expression Builder generates the FilterExpression / KeyConditionExpression without PartiQL at all.

If you want a DynamoDB SQL client for exploring, debugging and analyzing tables, the Workbench sits inside a full DynamoDB GUI. Try DynoTable to run SQL against your own tables.

FAQ

Can you run SQL on DynamoDB? You can run PartiQL, a SQL-compatible subset (SELECT/INSERT/UPDATE/DELETE by key). For JOIN, GROUP BY and aggregates you need a SQL engine on top: the Amazon Athena DynamoDB connector, zero-ETL to Amazon Redshift, Hive on Amazon EMR, or DynoTable's SQL Workbench (a single SELECT dialect with INNER/LEFT JOIN, no CTEs, unions or subqueries).

Does DynamoDB PartiQL support JOIN? No. The PartiQL SELECT syntax has a single FROM table or index and no join grammar. Query each side by key and merge in code, or run the join in a SQL engine such as Athena.

Does PartiQL support GROUP BY or aggregates like COUNT and SUM? No. There's no GROUP BY clause, and the only "aggregate" function is SIZE (an attribute's byte size for one item). For counts, use Select: 'COUNT' on a Query or Scan; for totals, maintain them on write or use one of the SQL engines above.

Is DynamoDB SQL or NoSQL? NoSQL: a key-value and document store. PartiQL adds a SQL-compatible query language on top, but DynamoDB has no relational engine, joins, or aggregates.

Is PartiQL good for ad-hoc queries? For key-based lookups, yes. For analytical ad-hoc queries (counts, rollups, joins), no: PartiQL can't express them, and unconstrained SELECT statements silently become full table scans. Use Athena or the Workbench for those.

Is there a DynamoDB SQL client that handles JOIN and GROUP BY? Yes. Amazon Athena does it through a federated connector you deploy in your AWS account, and DynoTable's SQL Workbench runs JOIN/GROUP BY/aggregates against live tables from the desktop.

By Dmitriy · Updated