Regular Expression Extract (Regexp_Extract) In Apache Hive

Apache Hive 164 comments

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;

movies_regexp_extract

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;

sort1
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.

164 comments

  1. Terrific article! This is the kind of info that are supposed to be shared around the net.
    Shame on the seek engines for no longer positioning this publish upper!
    Come on over and discuss with my site . Thank you =)

    Like

    Reply

  3. I’m curious to find out what blog system you’re working
    with? I’m experiencing some small security
    problems with my latest blog and I would like to find
    something more safeguarded. Do you have any recommendations?

    Visit my web-site Primal Testo XL

    Like

    Reply

  4. Hey there! Would you mind if I share your blog with my twitter group?
    There’s a lot of people that I think would really appreciate your content.

    Please let me know. Thanks

    Like

    Reply

  5. Currently it looks like BlogEngine is the top blogging platform out there right now.
    (from what I’ve read) Is that what you’re using on your blog?

    Like

    Reply

  7. Whats up very nice website!! Guy .. Beautiful .. Wonderful ..

    I’ll bookmark your blog and take the feeds additionally…I’m
    glad to search out so many helpful info right here in the post, we need work out more strategies on this regard, thank you for sharing.

    Also visit my site Tyree

    Like

    Reply

  12. Hi there I am so delighted I found your blog, I really found you
    by accident, while I was researching on Aol for something
    else, Anyhow I am here now and would just like to say many thanks for a marvelous post and a all round exciting
    blog (I also love the theme/design), I don’t have time to read through
    it all at the moment but I have bookmarked it and also added your RSS feeds, so when I have time I
    will be back to read much more, Please do keep up the excellent work.

    Like

    Reply

  17. For hottest information you have to go to see internet
    and on world-wide-web I found this web page as a most excellent website for most recent updates.

    Like

    Reply

  19. I believe this is one of the such a lot vital information for me.
    And i am satisfied reading your article. But wanna observation on few common issues, The website
    style is ideal, the articles is in reality nice : D.

    Excellent job, cheers

    Feel free to visit my web blog :: TestonoX

    Like

    Reply

  20. Wonderful goods from you, man. I have understand your
    stuff previous to and you are just extremely magnificent.
    I really like what you have acquired here, really like what you’re
    saying and the way in which you say it. You make it entertaining and you still take
    care of to keep it sensible. I can not wait to read much more
    from you. This is actually a terrific site.

    Like

    Reply

  21. I believe this is one of the such a lot vital information for me.
    And i am glad studying your article. However should remark on some basic
    issues, The site style is great, the articles is
    in reality excellent : D. Just right task, cheers

    My page :: TestonoX

    Like

    Reply

  22. Heʏa are using WordPress for your site platform?

    I’m new to the blog world but I’m trying to get startedd
    annⅾ sett up my own. Do you require any html coding expertise to make your own bⅼog?

    Any help wouuⅼd be greɑtly aрpreciateԀ!

    Like

    Reply

  25. hey there and thank you for your info – I’ve definitely picked up something new from right here.
    I did however expertise several technical points using this site,
    since I experienced to reload the site lots of times previous to I could
    get it to load correctly. I had been wondering if your hosting is OK?
    Not that I’m complaining, but sluggish loading instances times will sometimes affect your placement
    in google and could damage your high quality score if ads and marketing with Adwords.

    Well I am adding this RSS to my email and can look out for a lot more of your respective interesting content.
    Make sure you update this again soon..

    My site; Clean Cut Keto Ingredients

    Like

    Reply

  26. Hello superb blog! Does running a blog similar to this take a large amount of work?

    I’ve absolutely no expertise in programming but I was hoping to start my
    own blog soon. Anyways, should you have any suggestions or techniques for new blog owners please share.
    I understand this is off subject nevertheless I just wanted to ask.

    Thanks!

    my webpage :: shihan.com.ru

    Like

    Reply

  32. I still cannot quite think that I could be one of those reading the important points found on your site.
    My family and I are seriously thankful for the generosity and for presenting me the potential to pursue my
    chosen career path. Thank you for the important information I got from your web-site.

    Feel free to visit my homepage :: smartcalltaxi.kr

    Like

    Reply

  33. It is really a great and useful piece of info. I am happy that you shared this useful info with us.

    Please stay us up to date like this. Thanks for sharing.

    Like

    Reply

  40. Fasсinating blog! Is your theme custom made or
    did you download itt from somewhere? A theme ⅼike yours wiіth
    a feew sіmple tweekks would really make my blog stand out.
    Pleassе let me know where you got your theme.

    Thanks

    Like

    Reply

  42. I know this if off topic but I’m looking into starting my
    own weblog and was curious what all is needed to get set up?
    I’m assuming having a blog like yours would cost a
    pretty penny? I’m not very internet smart so I’m not 100% sure.
    Any tips or advice would be greatly appreciated. Thanks

    Like

    Reply

  48. A fascinating discussion is worth comment. I do believe
    that you should publish more about this subject matter,
    it may not be a taboo subject but typically folks don’t speak about these subjects.
    To the next! Many thanks!!

    Like

    Reply

  49. Hmm it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what
    I had written and say, I’m thoroughly enjoying your blog.
    I as well am an aspiring blog writer but I’m still new to the whole thing.
    Do you have any recommendations for rookie blog writers?
    I’d definitely appreciate it.

    Like

    Reply

Leave a reply to Genie Cancel reply