Multiple CTE query works in Athena but not in custom SQL

Q&A
, , ,
1

Hi,

I have a query with multiple CTEs and it works in Athena. But when I use the same query in quicksight dataset custom SQL, the new column “extracted_value3” from the final CTE "Finalcte4 " is not visible.

WITH cte1 AS (
    SELECT
        column1,
        split(column2,',') as array_column
    FROM
        my_table
),

cte2 AS (
    SELECT
        column1,
        REGEXP_SUBSTR(value, 'pattern1') AS extracted_value1
    FROM
        cte1
    GROUP BY
        column1
),

cte3 AS (
    SELECT
        column1,
        split(extracted_value1, ':') AS extracted_value2
    FROM
        cte2
),

Finalcte4 AS (
    SELECT
        column1,
        split(extracted_value2, '\') AS extracted_value3
    FROM
        cte3
),

SELECT
    column1,
    extracted_value3
FROM
    Finalcte4

The output in custom SQL only has column1 but no extracted_value3.

In Athena,

Column1 extracted_value3
value1 extracted_val1
value2 extracted_val2

In Custom SQL,

Column1
value1
value2

I wonder if the Athena query and Custom SQL query is different? Can anyone also help me to make the Finalcte4 extracted_value3 column visible in the custom SQL? Thank you!

2

Hi @emyatsuna

Could you please check the Athena Recent Queries log and check if you are seeing any issue with query send by Quick Sight to Athena.

image
image1297×394 54.1 KB

Thanks
VInod

3

Thanks for your reply. I just checked the Athena recent queries and there were no log issues.

4

Hi @emyatsuna,
It’s been awhile since last communication on this thread, did you have any additional questions regarding your initial topic?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

5

Hi, I still have the same problem in custom SQL. The query works in Athena but not in custom SQL. Thank you

6

@emyatsuna ,

1/ Validate the CTE SQL example you have provided
2/ REGEXP_SUBSTR is not supported in Amazon Athena.
2/ Screenshots of the results from Athena using REGEXP_SUBSTR , similar to my example

SELECT REGEXP_SUBSTR (‘AmazonQuick Sight Gen BI’, ‘(\S*)(\s)’)

image
image1073×354 29.4 KB

Kind regards,
Koushik

7

Hi @emyatsuna,
It’s been awhile since we last heard from you; did you have any additional questions regarding your initial topic?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

8

Hi @emyatsuna,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!