AthenaQueryError: Athena query failed: "NOT_SUPPORTED: Unsupported Hive type

Question

I recently ran into the following error "AthenaQueryError: Athena query failed: "NOT_SUPPORTED: Unsupported Hive type", and for this I followed this stack overflow link: converting to timestamp with time zone failed on Athena

error:

The weird part of the whole issue is the sql query that is generated as I use an internal python plugin is working fine as I run it manually in Athena but the same doesn't work in a jupyter notebook

Marrowbone

It'd be great to know what causes this error, to me the same thing has happened. Keep us posted here if you find out something about this. – Savagery

Answer 1

Not sure if this is the same as yours, But I ran into the same error when I was extracting some JSON data using json_extract, it works fine with Athena, but fails inside a Jupyter Notebook, throwing the same error as yours.

Putting json_format before json_extract solved it for me. casting json into an array also solved it.

Here is a example SQL code using json_format:

SELECT col1,col2 
json_format(json_extract(col3, '$JSPath')) AS some_alias 
FROM some_database

Here is the other one using CAST

SELECT col1,col2 
CAST ((json_extract(col3, '$JSPath')) AS ARRAY(JSON)) AS some_alias 
FROM some_database

Saxe

I'm using the AWS Wrangler package and had the same problem. Using json_format to convert the output to string fixed my issue. Thanks for this solution 💪 – Iapetus

Answer 2

In my case, the issue was that I was doing a CROSS JOIN on json, so far so good, but forgot to NOT select it in the SELECT statement.

This was my query:

SELECT
    *,
    JSON_EXTRACT_SCALAR(experiments, '$.feature_flag_name') AS experiment_name,
    JSON_EXTRACT_SCALAR(experiments, '$.feature_flag_variant') AS experiment_variant
FROM
    actions AS a
CROSS JOIN
    UNNEST(CAST(JSON_EXTRACT(payload, '$.feature_flags') AS ARRAY(JSON))) AS t(experiments)

As you can see, I was casting alright, but due to SELECT * I ended up selecting from the joined table as well, which contained JSON and which in turn produced the error in question.

Restricting the SELECT statement to SELECT a.* ... of course solved the issue.

Fye