Regular Expression Extract (Regexp_Extract) In Apache Hive

Regular expression functions identify precise patterns of characters in the given string and returns the matching text item from the string/data.

Syntax: REGEXP_EXTRACT(STRING subject, STRING pattern, INT index)

Let’s see practically how it works.

Below is the sample records from the dataset:
ReleaseYear MovieTitle
1969 Downhill Racer
1970 M*A*S*H
1970 The Party at Kitty and Stud’s
1970 Lovers and Other Strangers
1970 The Sidelong Glances of a Pigeon Kicker
1970 Hercules in New York
1971 Bananas
1971 Klute
1972 What’s Up, Doc?
1973 No Place to Hide

If you look at the data, there are two columns however it is difficult to convert these into structured format due to the delimitation. It can only be done using regular expressions. Let’s convert this data into columns using REGEXP_EXTRACT function. 

–Creating a table to hold the entire data in a single column.
–Let’s create dummy table to hold the entire data.
CREATE TABLE movies(data STRING);
— Loading the data
LOAD DATA LOCAL INPATH ‘Desktop/movies.csv’ INTO TABLE movies;
–Converting the data into meaningful columns.
SELECT REGEXP_EXTRACT(data, ‘^\\d+’, 0),
              REGEXP_EXTRACT(data, ‘\\s+(\\S.*)’, 1)
FROM movies;

Let me explain the meaning of regular expression provided for regexp_extract function:

^\\d – matches the digit at the start of the string (^). + symbol represents one or more number of characters. Thus, it captures the first string from the beginning that has any number of digits.

\\s – Helps in skipping the space character. + symbol helps in skipping more than one space character.

\\S – Captures any non-whitespace char. And the dot and asterisk (.*) matches any zero or more chars other than line break chars. Index argument is 1 so that REGEXP_EXTRACT returns the captured group group without the initial whitespace.

With this function, let’s see what else we can do and what else we can add.

SORTING:
SELECT REGEXP_EXTRACT(data,’\\s+(\\S.*)’,1)x FROM movies ORDER BY x LIMIT 20;


SEARCHING:
Find out the names of the employees whose last-name starts with ‘S’

Data:
Will Smith
Black Smith
Scott James
James Miller
Allen Jackson
Some Name

SELECT REGEXP_EXTRACT(name,'(.*) (S.*)’,0) from someNames;

Result:
Will Smith
Black Smith

Find out the names whose first-name or last-name starts with ‘S’

SELECT REGEXP_EXTRACT(name,'(.*)(S.*)’,0) from someNames;

Result:
Some Name
Will Smith
Black Smith
Scott James


Hope you find this article. Please do follow for more interesting updates.