Geek Answers Handbook

HiveQL. How to find a column value is numeric or does not use any UDF?

Basically, I would like return rowsbased on one column value.

If the column contains values non numeric, return these rows from the hive table.

Anyone UDFavailable at Hive?

+7
source share
2 answers

I believe that Hive supports rlike(regular expressions). So you can do:

where col rlike '[^0-9]'

It searches for any character without numbers. You can expand this if your numerical values ​​can have decimal points or commas.

+14
source

Use cast(expr as <type>). nullreturns if the conversion failed.

case when cast(col as double) is null then 'N' else 'Y' end as isNumber

WHERE: cast(col as double) is not null

isNumber:

create temporary macro isNumber(s string)
       cast(s as double) is not null;

:

hive> select isNumber('100.100'), isNumber('100'), isNumber('.0'), isNumber('abc');
OK
_c0     _c1     _c2     _c3
true    true    true    false

, cast(s as Int)

.

+8

More articles:


All Articles