配置单元如何查询转换后的变量；失败: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屋！