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. I happen to be commenting to make you know of the
    really good encounter my wife’s princess developed viewing your blog.
    She even learned a lot of pieces, not to mention what it is like
    to possess an awesome coaching heart to make folks very easily fully understand certain very
    confusing subject areas. You really did more than readers’ desires.
    Many thanks for coming up with such interesting, dependable, educational not to mention unique tips on your topic to Emily.

    my website; Ketokor Side Effects

    Like

    Reply

  3. Very good website you have here but I was wanting
    to know if you knew of any discussion boards that cover
    the same topics talked about in this article? I’d really love to be
    a part of community where I can get advice from other experienced individuals that share the
    same interest. If you have any recommendations, please let me know.

    Cheers!

    Look at my blog; Captive Skin Cream Review

    Like

    Reply

  4. I loved as much as you’ll obtain performed proper here.
    The cartoon is tasteful, your authored material stylish.
    nevertheless, you command get got an nervousness over that you want be delivering
    the following. ill certainly come further earlier once
    more since precisely the similar just about a lot often inside of case you defend this hike.

    Also visit my web site – Imarais Beauty Review

    Like

    Reply

  6. We are a group of volunteers and opening a new scheme in our community.
    Your web site offered us with valuable information to work
    on. You have done a formidable job and our whole
    community will be grateful to you.

    Stop by my website … Semzia Pills

    Like

    Reply

  8. Having read this I believed it was extremely informative.

    I appreciate you finding the time and energy to put this
    article together. I once again find myself personally spending a significant amount of time both reading and leaving comments.
    But so what, it was still worth it!

    Like

    Reply

  10. wonderful put up, very informative. I ponder why the opposite experts of this sector do not realize
    this. You should proceed your writing. I’m confident,
    you have a huge readers’ base already!

    Take a look at my website … Maxatin Pills

    Like

    Reply

  12. This design is spectacular! You certainly know how
    to keep a reader amused. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job.
    I really enjoyed what you had to say, and more than that, how you presented it.
    Too cool!

    Also visit my website: Maxatin Review

    Like

    Reply

  14. I would like to thank you for the efforts you’ve put in writing this blog.
    I’m hoping the same high-grade web site post from you in the upcoming as
    well. In fact your creative writing abilities has encouraged me to
    get my own website now. Really the blogging is spreading its wings rapidly.
    Your write up is a great example of it.

    Check out my site; Keto Incinerate

    Like

    Reply

  16. Yesterday, while I was at work, my cousin stole my apple ipad and tested to see if it can survive a 30 foot drop,
    just so she can be a youtube sensation. My iPad is now destroyed and she has 83 views.
    I know this is totally off topic but I had to
    share it with someone!

    Here is my webpage; Infinuity CBD

    Like

    Reply

  17. Hey I am so grateful I found your blog, I really found you
    by mistake, while I was searching on Askjeeve for something
    else, Anyways I am here now and would just like to say cheers for a fantastic 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 minute but I
    have bookmarked it and also added your RSS feeds,
    so when I have time I will be back to read a lot more, Please do keep up the fantastic job.

    Review my web blog – Infinity CBD Review

    Like

    Reply

  19. Greetings from Los angeles! I’m bored to tears at
    work so I decided to check out your website on my iphone during lunch break.
    I really like the info you present here and can’t wait
    to take a look when I get home. I’m shocked at how fast your blog loaded on my phone ..
    I’m not even using WIFI, just 3G .. Anyhow, awesome blog!

    My web site: lifeafter.neteasegamer.jp

    Like

    Reply

  23. Nice post. I used to be checking constantly this blog and I’m inspired!

    Extremely helpful info specifically the final part 🙂 I deal with such information much.
    I used to be looking for this particular information for a very long time.
    Thanks and good luck.

    my blog – Yec Keto

    Like

    Reply

  29. I as well as my buddies have been taking note of the excellent helpful hints on your web blog and at once I got
    a horrible suspicion I had not expressed respect to the site owner for those tips.
    The boys came consequently glad to see them and have now surely been having fun with them.

    Many thanks for indeed being simply thoughtful and for opting
    for certain marvelous subjects most people are really needing to understand about.
    My honest regret for not expressing gratitude to you earlier.

    My blog Max BHB Review

    Like

    Reply

  31. Hiya, I’m really glad I have found this information. Today bloggers publish only about gossips and
    internet and this is really annoying. A good website with exciting content, that’s
    what I need. Thank you for keeping this web-site, I will be visiting it.
    Do you do newsletters? Can not find it.

    Take a look at my homepage 23.95.102.216

    Like

    Reply

  33. Hi there very nice website!! Guy .. Beautiful .. Superb ..
    I’ll bookmark your web site and take the feeds also…I’m glad to search out
    so many useful info right here in the submit, we want work out extra strategies in this regard, thanks for
    sharing.

    Here is my site … Clean Cut Keto Pills

    Like

    Reply

  34. Hmm it appears like your website ate my first comment (it was super 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 everything.
    Do you have any tips and hints for beginner blog writers?
    I’d really appreciate it.

    my web-site – Anavale Skin Cream (https://bbs.cnction.com)

    Like

    Reply

  38. Hello my family member! I wish to say that this article is awesome, great written and include almost
    all significant infos. I’d like to look extra posts like this.

    Feel free to surf to my blog post: Semzia

    Like

    Reply

  47. I actually still cannot quite assume that I could often be
    one of those reading through the important tips found on your website.
    My family and I are really thankful for the generosity and for providing me the
    possibility to pursue our chosen profession path. Appreciate your sharing the important information I managed to
    get from your web site.

    Here is my homepage – smartcalltaxi.kr

    Like

    Reply

Leave a reply to Semzia Pills Cancel reply