Athena: Queries of this type are not supported

Question

I have the current query in athena.

SELECT col1,
       col_2,
       A.col_3
    FROM 
        (SELECT col_1,
                col_3
        FROM table_1
        JOIN col_3
        WHERE col_1 IN 
            (SELECT DISTINCT col_1
            FROM table_2
            JOIN table_1
                ON table_1.col_1 = table_2.col_1
            ) 
        ) AS A
            LEFT JOIN 
                (SELECT col_2,
                        col_3
                FROM table_3
                JOIN col_3
                WHERE col_2 IN 
                    (SELECT DISTINCT col_2
                    FROM table_2
                    JOIN table_4
                        ON table_2.col_1 = table_4.col_1
                    JOIN table_3
                        ON table_4.col_2 = table_3.col_2
                    ) 
                ) AS B
                        ON B.col_3 = A.col_3

Which works in SQLite. But when I run it in AWS Athena I got the following error:

Queries of this type are not supported (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: some_id)

I assume that some part of this query is not supported by AWS Athena, but I am new to the Framework.

What is the purpose of the query? Sample data and desired results would also help. — GMB, Aug 18 '20 at 18:51
Did you break it down to determine what portion of the query caused the error? For example, was it the use of sub-selects, or `IN DISTINCT`? Often, an `IN ... DISTINCT` can be avoided by using a `LEFT OUTER JOIN` and checking for `NULL` values. Worst case, you might need to create some 'in between' tables using `CREATE TABLE AS`. — John Rotenstein, Aug 18 '20 at 22:18
There is an error in your query: "FROM table_1 JOIN col_3 WHERE col_1" is not valid SQL. There are also ambiguities introduced by column name obfuscation (e.g. col_1 is ambiguous). If I remove those problems, current Presto (340) can handle this query without problem. Did not check with Athena. — Piotr Findeisen, Aug 19 '20 at 07:17
Query `select foo, from bar` produces the same error (as of engine version 2) — darw, Jun 10 '22 at 10:23

score 19 · Answer 1 · answered Aug 26 '20 at 14:11

19

"Queries of this type are not supported" is Athena's generic way of saying that it doesn't understand your SQL, but that it's not a simple syntax error. You're using SQL that Athena does not support, in other words.

Run the innermost part of the query by itself, and if you don't get the error, add the SQL that wraps it, and so on until you find the fragment that causes the error. If you don't know how to fix it ask a new question focused on that.

answered Aug 26 '20 at 14:11

Theo

131,503
21
160
205

1

That's a good tip! I got the error when a column name was a reserved word. – John Rotenstein May 06 '21 at 06:59

score 0 · Answer 2 · answered May 19 '23 at 13:42

If you're running into this issue and still haven't figured it out, it might be because your lambda or user doesn't have permissions to run select statements.

You'll need something like this in your policy:

    - Effect: 'Allow'
      Resource: '*'
      Action: lakeformation:*

Note: you should restrict these down to specific resource and action. The wildcard is not best practices.

Athena: Queries of this type are not supported

2 Answers2