Direct Query disabled for Cross-Source Join (Athena + Postgres) - How to use SQL Parameters?

Q&A
, , ,
1

Hi everyone,

I am hitting a blocker while trying to create a dataset that joins two different data sources using a Right Join:

  1. Athena (S3): Using Custom SQL with a dataset parameter (id).

  2. PostgreSQL: Using Custom SQL with dataset parameters (id and id2).

I am injecting these parameters directly into the Custom SQL queries to fetch specific files/rows.

The Issue: When I try to save this dataset, I encounter the following error: sourceErrorCode: UNSUPPORTED_CUSTOMSQL_SPICE_DATASET_WITH_PARAMETERS sourceErrorMessage: Parameterized CustomSQL query not supported for SPICE

Normally I would switch to Direct Query mode to fix this, but because I am joining two different data sources, the “Direct Query” option is grayed out/disabled. QuickSight seems to force SPICE for cross-source joins.

My Questions:

  1. Is there any way to use dynamic parameters in a Cross-Source join without being forced into SPICE?

  2. Since I cannot use Direct Query here, is the best workaround to use Athena Federated Queries (connecting Athena to Postgres) so QuickSight sees a “Single Source” and allows Direct Query?

  3. Are there any other known workarounds to filter data at the source level in this scenario?

2

Hi @ashishsingla045 and welcome to the Quick Community!

You seem to be right on track with your breakdown;

  • Using dataset parameters in your customSQL requires direct query mode; since it must wait for a parameter’s selection to be made before running
  • However a cross-source join requires the use of SPICE

As these two can’t be worked around, yes I would agree that the best workaround in your scenario would most likely be to use Athena Federated Queries

3

Hi @ashishsingla045,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you

4

Hi @ashishsingla045,

Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.

Thank you