配置单元如何查询转换后的变量;失败:SemanticException [错误10004] [英] Hive how query over a transformed variable; FAILED: SemanticException [Error 10004]
问题描述
我正在尝试查询特定的格式化日期:
I'm trying to query over a specific formatted date:
我有这个查询:
SELECT
REGEXP_REPLACE(datewithoutdash,
'^(\\d{2})(\\d{2})(\\d{2})(.*)$','20\\1-\\2-\\3')
datewithdash
FROM table1 WHERE datewithdash < "2016-11-10";
为什么我不能在新变量上使用 where 子句?
Why I can't use where clause over the new variable?
我收到此错误:
失败:SemanticException [错误10004]:行26:14无效的表 别名或列引用"datewithdash" :(可能的列名称为: ...)
FAILED: SemanticException [Error 10004]: Line 26:14 Invalid table alias or column reference 'datewithdash': (possible column names are: ...)
推荐答案
Hive在同一查询中评估where子句时,不知道select子句中的别名列名称.不幸的是,您必须嵌套它,或将转换函数复制到where子句中:
Hive doesn't know about the aliased column names in a select clause when its evaluating a where clause in the same query. Unfortunately you either have to nest it, or duplicate the transformation function into the where clause:
SELECT
REGEXP_REPLACE(datewithoutdash,
'^(\\d{2})(\\d{2})(\\d{2})(.*)$','20\\1-\\2-\\3') as datewithdash
FROM
table1
WHERE
REGEXP_REPLACE(datewithoutdash,
'^(\\d{2})(\\d{2})(\\d{2})(.*)$','20\\1-\\2-\\3') < "2016-11-10";
OR
select * from (
SELECT
REGEXP_REPLACE(datewithoutdash,
'^(\\d{2})(\\d{2})(\\d{2})(.*)$','20\\1-\\2-\\3') as datewithdash
FROM
table1
) a
WHERE
datewithdash < "2016-11-10";
另一个说明-该函数非常讨厌-您可能会使用内置的蜂巢函数,例如:
Another note - that function is pretty nasty - you could probably use a build in hive function like:
to_date(unix_timestamp(datewithoutdash,'yyMMdd'))
相反-可能会更清楚.
这篇关于配置单元如何查询转换后的变量;失败:SemanticException [错误10004]的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!