CODEWITHSUNDEEP
amazon-athenaprestoamazon-quicksight
Hazzamataza
Hazzamataza

Reputation: 993

Quick sight adding nesting to SQL query which causes errors in Athena

I'm trying to create a very simple visualisation in Quicksight, and to do this I'm using an SQL query in Quicksight,

SELECT COUNT(distinct uuid), day
FROM analytics.myTable
GROUP BY day

Unfortunately, whenever I run this query in Quicksight it fails due to the following error

from the AWS Athena client. SYNTAX_ERROR: line 2:8: Column '_col0' cannot be resolved

When I look in Athena, I can see that Quicksight is "nesting" the SQL query... this is what's causing the error in Athena,

/* QuickSight 4da449cf-ffc6-11e8-92ea-9ffafcc3adb3 */
SELECT "_col0"
FROM (SELECT COUNT(distinct uuid)
FROM pregnancy_analytics.final_test_parquet) AS "DAU"

What I don't understand is: a) why this is flagging an error? b) why Quicksight is nesting the SQL?

If I simply run the command directly in Athena,

SELECT COUNT(distinct uuid) FROM analytics.myTable

It does indeed show the column name "_col0", 

    _col0
1   1699174

so the fact that Quicksight is raising an error shouldn't actually be a problem.

Can someone offer some advice on how to resolve this issue?

Thanks

Upvotes: 1

Views: 1592

Answers (2)

vinod kumar
vinod kumar

Reputation: 21

You can modify the query to explicitly name the aggregated column and then the query will work.

Example:

SELECT COUNT(distinct uuid) as "distinct_uuid", day
FROM analytics.myTable
GROUP BY day

Upvotes: 2

Blake
Blake

Reputation: 134

Often in visualization software you will need to explicitly name your aggregate/function-wrapped columns as they default to things like _col0 which the software doesn't parse well, so it throws that error.

Specifically, I see this all the time in Superset using Presto.

For your problem explicitly, you should just do what Piotr recommended which is just adding a name after COUNT(distinct uuid) - I'm partial to freq, but it looks like you'll want something like uuid or unique_uuid :)

Upvotes: 1

Related Questions