Menu
Basically i would like to return rows based on one column value.
If the column contains non numeric values, then return those rows from a hive table.
Any UDF is available in Hive?
476
Apache Hive is numeric User Defined Function You can create user defined function to check if string is numeric. Below is the sample python script to identify the numeric values. Use Hive TRANSFORM function to execute isnumeric script.
This is the way: SELECT * FROM TABLE_NAME WHERE NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$ '); This also excludes values which contain decimals.
SQL Server ISNUMERIC() Function The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
Integer type data can be specified using integral data types, INT. When the data range exceeds the range of INT, you need to use BIGINT and if the data range is smaller than the INT, you use SMALLINT. TINYINT is smaller than SMALLINT.
Use cast(expr as <type>). A null is returned if the conversion does not succeed.
case when cast(col as double) is null then 'N' else 'Y' end as isNumber
or simply use Boolean expression in the WHERE: cast(col as double) is not null
Also you can create isNumber macro:
create temporary macro isNumber(s string)
cast(s as double) is not null;
And use it in your queries:
hive> select isNumber('100.100'), isNumber('100'), isNumber('.0'), isNumber('abc');
OK
_c0 _c1 _c2 _c3
true true true false
If you need to check for Integer then use cast(s as Int)
This approach works correctly with negative and fractional numbers.
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
