AthenaQueryError：Athena 查询失败：“NOT_SUPPORTED：不支持的 Hive 类型”

发布于 2025-01-13 00:19:16 字数 387 浏览 5 评论 0原文

我最近遇到以下错误“AthenaQueryError：Athena 查询失败：“NOT_SUPPORTED：不支持的 Hive 类型”，为此我遵循了此堆栈溢出链接：在 Athena 上转换为时区时间戳失败

错误：

整个问题的奇怪部分是我使用内部 python 插件时生成的 sql 查询当我在 Athena 中手动运行它时工作正常，但在 jupyter 笔记本中却不起作用

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

随遇而安 2025-01-20 00:19:16

不确定这是否与您的相同，但是当我使用 json_extract 提取一些 JSON 数据时遇到了同样的错误，它在 Athena 中工作正常，但在 Jupyter Notebook 中失败，抛出相同的错误错误如你的。

将 json_format 放在 json_extract 之前为我解决了这个问题。
将 json 转换为数组也解决了这个问题。

以下是使用 json_format 的 SQL 代码示例：

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

这是使用 CAST 的另一个示例

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

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

回复收藏 0 原文

凉风有信 2025-01-20 00:19:16

就我而言，问题是我在 json 上执行CROSS JOIN，到目前为止一切顺利，但忘记NOT在SELECT声明。

这是我的查询：

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)

如您所见，我进行了正确的转换，但由于 SELECT *，我最终也从连接表中进行选择，其中包含 JSON，进而产生了相关错误。

将 SELECT 语句限制为 SELECT a.* ... 当然解决了这个问题。

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.

回复收藏 0 原文

~没有更多了~