{"id":272,"date":"2019-10-26T18:00:01","date_gmt":"2019-10-26T18:00:01","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=272"},"modified":"2024-10-09T06:21:55","modified_gmt":"2024-10-09T06:21:55","slug":"rlike-in-hive-filtering-with-regular-expressions","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/10\/26\/rlike-in-hive-filtering-with-regular-expressions\/","title":{"rendered":"RLIKE in hive: Filtering with regular expressions"},"content":{"rendered":"\n

As I previously did a blog post on Querying SQL Server with something LIKE a regular expression<\/a> (Using simple regular expressions in a LIKE statement), I thought I would use that as a segue into Apache Hive and HiveQL. Because Hive and therefore HiveQL is built using Java, it has the full power of Java regular expressions in it’s RLIKE statement. <\/p>\n\n\n\n

Regular Expressions<\/h2>\n\n\n\n

Some people will read \u201cregular expression\u201d and react with a knowing nod (feel free to jump ahead), everyone else is about to learn something you never realised you needed to know.<\/p>\n\n\n\n

What is a Regular Expression?<\/h3>\n\n\n\n

A regular expression is a Computer Science tool for implementing what mathematicians know as a finite state machine (sometimes called a finite state automaton), usually on text (alphanumeric characters).<\/p>\n\n\n\n

If you have used grep<\/code> on the unix command line, you have probably seen this in action.<\/p>\n\n\n\n

The actual regular Expression is a string which represents a pattern for matching. There are many cases where there is a pattern which we want to test text against. A common example is to test if a date is correctly formatted how a process expects it (YYYY-MM-DD or DD\/MM\/YY etc\u2026).<\/p>\n\n\n\n

A more advanced case is validating an email address, very quickly you can build up rules in your head (it has to have the \u2018@\u2019, but only once, and there has to be something either side of it\u2026. and so on).<\/p>\n\n\n\n

Further Reading<\/h3>\n\n\n\n

If regular expressions are new to you, then make some time to read (thanks to the web archive): The absolute bare minimum every programmer should know about regular expressions<\/a><\/p>\n\n\n\n

Online Tools<\/h3>\n\n\n\n

I found this interactive site, very useful for testing regular expressions (although it goes beyond the SQL Server syntax): https:\/\/regexr.com\/<\/a> <\/p>\n\n\n\n

RLIKE in Hive<\/h2>\n\n\n\n

Hive has both LIKE (which functions the same as in SQL Server and other environments) and RLIKE, which uses regular expressions. These are mentioned briefly in the LanguageManual UDF<\/a> documentation. <\/p>\n\n\n\n

The best way to understand RLIKE is to see it in action. So, I’ve created some sample data and some examples of regular expressions.<\/p>\n\n\n\n

Sample Data<\/h3>\n\n\n\n
DROP TABLE IF EXISTS sample_data;\n\nCREATE TABLE sample_data\n(\n    sample_column VARCHAR(100)\n)\n;\n\nINSERT\n  INTO sample_data\n      (sample_column)\nVALUES('abc'),\n      ('ABC'),\n      ('XYZ'),\n      ('123'),\n      ('000'),\n      ('789'),\n      ('1.3')\n;\n\nSELECT *\n  FROM sample_data\n;<\/pre>\n\n\n\n
sample_column<\/strong><\/td><\/tr>
abc<\/td><\/tr>
ABC<\/td><\/tr>
XYZ<\/td><\/tr>
123<\/td><\/tr>
000<\/td><\/tr>
789<\/td><\/tr>
1.3<\/td><\/tr><\/tbody><\/table>\n\n\n\n
This sample data gives us some text and numeric characters. If you look a the UDF, most functionality is around text and numbers. But special characters can also be manipulated.<\/p>\n\n\n\n
Contains Numbers<\/h2>\n\n\n\n
SELECT sample_column\n  FROM sample_data\n WHERE sample_column RLIKE '[0-9]+'\n;<\/pre>\n\n\n\n
sample_column<\/strong><\/td><\/tr>
123<\/td><\/tr>
000<\/td><\/tr>
789<\/td><\/tr>
1.3<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Because we are matching on if the string has one or more numeric character, we see the 1.3 value. We would also see a string which contained ‘abc123XYZ’.<\/p>\n\n\n\n
All (and only) Numbers<\/h3>\n\n\n\n
SELECT sample_column\n  FROM sample_data\n WHERE sample_column RLIKE '^[0-9]+$'\n;<\/pre>\n\n\n\n
sample_column<\/strong><\/td><\/tr>
123<\/td><\/tr>
000<\/td><\/tr>
789<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Note that this hasn’t included the decimal point. See the example further down for use of a decimal point in a number. <\/p>\n\n\n\n
Text (alpha) Only<\/h3>\n\n\n\n
SELECT sample_column\n  FROM sample_data\n WHERE sample_column RLIKE '([a-z]|[A-Z])+'\n;<\/pre>\n\n\n\n
sample_column<\/strong><\/td><\/tr>
abc<\/td><\/tr>
ABC<\/td><\/tr>
XYZ<\/td><\/tr><\/tbody><\/table>\n\n\n\n
The pattern will match one or more text characters. Noe that it will allow for other  characters to be returned as long as there is one or more text characters. <\/p>\n\n\n\n
Number with Decimal Point<\/h3>\n\n\n\n
SELECT sample_column\n  FROM sample_data\n WHERE sample_column RLIKE '[0-9]+[.][0-9]+'\n;<\/pre>\n\n\n\n
Note that the dot is encased in square brackets, this makes it literal rather than a wildcard (default behaviour) for any<\/em> character.<\/p>\n\n\n\n
Useful Meta Characters<\/h2>\n\n\n\n
On top of using the square brackets []<\/code> to identify character sets, and regular brackets ()<\/code> to identify groupings, there are a few other common characters which control the behaviour.<\/p>\n\n\n\n