How to Use DQL to Subquery Multiple DynamoDB Tables

Hi, I’m using getting started using DQL to query my DynamoDB tables. I’m wanting to take the result of a query on one table and use that result to query another table. Anybody know how to do this? I’ve got something set up like

SELECT firstName FROM UserTable where id = (SELECT id FROM SubscriptionTable where receiptId = ‘xxxxxx’)

but I’m getting an error at column 58 which is “where” keyword. Would appreciate any help with this. Thanks!

Unfortunately, DQL is no longer maintained by its author. At Redash, we continue to include it for legacy reasons.

But DQL never supported this syntax and the future doesn’t look promising. So there are two approaches:

  1. Use QRDS to perform your aggregations.
  2. Link your Dynamo instance with a true integrator like Rockset which introduces full relational capability. Then point Redash at Rockset (this is very common)

Thanks for your reply. Are you guys planning to update your DynamoDB integration then? At this stage it’s not worth it for my team to pay for Rockset and Redash QRDS might work for now.

It’s not on our roadmap. Our official guidance is to use an intermediary like Rockset or Stitch. However we would certainly consider a pull request implementing a nicer integration.

The biggest question is what would such an integration look like?

DynamoDB is not a relational database so it’s not as easy as wrapping SQL query text in a call to a vanilla driver. DQL does it. And it’s impressive that it’s as feature-rich considering Dynamo’s document-oriented design. But this isn’t a winning-strategy since it involves completely re-implementing ANSI SQL in Python.

Most queries to DynamoDB are chained method calls which are harder to replicate in a SQL Box environment like Redash. The way we did this with MongoDB was by using a JSON document that implements a key-value-pair for each “step” in the method chain. An implementation like this for DynamoDB could work. With Mongo we pass everything to a collections.find() call. Is there a similar method available in Amazon’s boto3?