如何在 hive 中使用强制转换列作为 where 子句条件？

发布于 2025-01-12 21:36:44 字数 435 浏览 2 评论 0原文

例如，“dt”是一个字符串，我将其转换为日期类型，然后我想将其用作 WHERE 子句中的条件，但失败：

hive> select mid,  cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) as date) from message_use_tags where date<2021-11-08 and date >2021-11-01 limit 100;
FAILED: SemanticException Line 0:-1 Invalid table alias or column reference 'date': (possible column names are: mid, type, content, dt)

我已经将“dt”转换为“日期”，然后如何在 WHERE 子句中使用它？

原文

For example, the 'dt' is a string and I cast it to a date type, then I want to use it as a condition in WHERE clause, but it failed:

hive> select mid,  cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) as date) from message_use_tags where date<2021-11-08 and date >2021-11-01 limit 100;
FAILED: SemanticException Line 0:-1 Invalid table alias or column reference 'date': (possible column names are: mid, type, content, dt)

I already convert 'dt' to 'date', and then how to use it in the WHERE clause?

分享到QQ

分享到微博

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

发布评论

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

将军与妓 2025-01-19 21:36:44

您在 select 中所做的转换不适用于 where 子句，否则您必须在子查询中设置转换并在外部查询中应用过滤，或者您可以尝试这个

hive> select mid,  cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) as date) from message_use_tags where cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))) < 2021-11-08 and cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))) >2021-11-01 limit 100;

the casting you did in the select wouldnt be applicable it the where clause else you would have to set the casting in a subquery and apply the filtering in the outer query, or you could try this

hive> select mid,  cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) as date) from message_use_tags where cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))) < 2021-11-08 and cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))) >2021-11-01 limit 100;

回复收藏 0 原文

鹿港小镇 2025-01-19 21:36:44

请尝试下面的代码。有时自动转换对于某些工具不起作用。更好地转换和信任代码而不是工具。

select mid,   to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))  dt_new --you dont have to cast to date. to_date will reove time part
from message_use_tags 
where 
to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) < to_date(from_unixtime(unix_timestamp('2021-11-08', 'yyyy-MM-dd')))  -- convert hardcode dates properly as well
and 
to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) > to_date(from_unixtime(unix_timestamp('2021-11-01', 'yyyy-MM-dd')))   -- convert hardcode dates properly as well
limit 100;

请注意，我假设列 dt 是格式为 yyyyMMdd 的字符串日期。

Please try below code. Sometime auto conversion doesnt work for some tools. Better convert and trust the code and not the tool.

select mid,   to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))  dt_new --you dont have to cast to date. to_date will reove time part
from message_use_tags 
where 
to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) < to_date(from_unixtime(unix_timestamp('2021-11-08', 'yyyy-MM-dd')))  -- convert hardcode dates properly as well
and 
to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) > to_date(from_unixtime(unix_timestamp('2021-11-01', 'yyyy-MM-dd')))   -- convert hardcode dates properly as well
limit 100;

Pls note, i assumed column dt to be a string date in the format yyyyMMdd.

回复收藏 0 原文

~没有更多了~