Quick Sight pie chart SQL fails against ClickHouse due to $RANK_N alias in GROUP BY

Q&A
,
1

We are using Amazon Quick Sight connected to a ClickHouse cluster (version 25.6.2.5).

When creating a pie chart in an analysis, Quick Sight generates SQL that includes DENSE_RANK() for Top-N logic and then references the result ($RANK_4) in the outer GROUP BY.

ClickHouse rejects this query with the following error:

Code: 215. DB::Exception: Column t1.`$RANK_4` is not under aggregate function and not in GROUP BY keys.

Can Quick Sight adjust SQL generation for ClickHouse connectors to avoid referencing window function aliases in GROUP BY?

2

Hi @richa.jha

Welcome to the community!

Since you have already identified the issue; you can create a ticket with AWS for this. I don’t recollect any previous posts in the community for ClickHouse Cluster.

Regards,
Giri

3

Hi @richa.jha,

Hope everything is well! Just wanted to check in since this thread hasn’t received a response in a while. Were you able to get further elaboration from AWS Support or able to find a workaround in general for your use case? If we do not receive a response in the next 3 business days, I’ll close out of this topic.

Thank you!

4

Hi @richa.jha,

Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, please feel free to post again in the community and link this discussion for any relevant information if necessary.

Thank you.