The other day, I was porting an Athena query to run it on Hive, and I realized that the regexp_like function does not exist in Hive. Fortunately, there is a nice replacement that offers precisely the same behavior.
First, lets take a look at the regexp_like function in Presto/Athena:
regexp_like(column_name, 'regexp')
For example, we can use it like this to return the value of another column when a value in the row matches the regexp and return 0 when it doesn’t:
CASE WHEN (regexp_like(column_name, '^[0-9]+$')) THEN cast(other_column as double) ELSE 0 END
To get the same behavior in Hive, I have to use the RLIKE keyword. Instead of WHEN CASE with only two branches, I can use the IF function:
IF(column_name RLIKE '^[0-9]+$', cast (other_column as double), 0)
Want to build AI systems that actually work?
Download my expert-crafted GenAI Transformation Guide for Data Teams and discover how to properly measure AI performance, set up guardrails, and continuously improve your AI solutions like the pros.