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 PartiQL 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 partition-key equality compiles to a keyed read;
without one, it's a Scan. Per the
AWS SELECT reference:
Using the
SELECTstatement 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 withDuplicateItemException. - UPDATE: modify one existing item (
UpdateItem). It never creates: a missing key fails withConditionalCheckFailedException. - 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 DESCORDER BY is allowed, but the AWS reference restricts the ordering key to "a hash key
or a sort key": the partition or sort key, 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 PartiQLSELECTsyntax is a singleFROM {{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, noUNION, no window functions. Pattern matching usescontains/begins_with. For a subquery, run the innerSELECTfirst and pass its keys in anIN [...]list (50 partition-key values at most); forUNION, run eachSELECTand 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:
| Option | Where the SQL runs | Pick it for |
|---|---|---|
| Athena DynamoDB connector | Athena, reading live tables via Lambda | Ad-hoc analysis and dashboards, no cluster to run |
| Zero-ETL to Amazon Redshift | Redshift, on a copy updated every 15–30 min | Recurring reports, without load on the live table |
| Hive on Amazon EMR | HiveQL on an EMR cluster you run | Large batch jobs and copies to S3 or HDFS |
| DynoTable SQL Workbench | Your desktop, over rows read from DynamoDB | Quick 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:
- In the Athena console, open Data sources, choose Create data source, then
Amazon DynamoDB, and give it a name such as
ddb. - Create the connector's Lambda function when prompted. Its template asks for a spill bucket: an S3 bucket for results that exceed Lambda limits.
- In the query editor, pick the
ddbdata source. Your tables appear in itsdefaultdatabase. - Query them by catalog, database and table name, joins and
GROUP BYincluded:
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 DESCUse 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
QueryandScan. AWS warns that "queries that use scans can consume a large number of read capacity units (RCUs)." A partition-key predicate is pushed down asQuerycalls; anything else scans, so check the pricing calculator before you join big tables. - It reads only:
INSERT INTOisn'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 DESCBecause 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 JOINandLEFT JOINonly. The joined (to-) side must be keyed on theONattribute: 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. NoRIGHT/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.