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. Pingback: Hive SerDe – RegEx – Example9 – Big Data and SQL
  2. Pingback: Apache Hive Functions – Big Data and SQL
  3. Pingback: Apache Hive Course Contents – Big Data and SQL

  4. I found your blog site on google and verify just a few of your early posts. Continue to maintain up the excellent operate. I simply extra up your RSS feed to my MSN Information Reader. Looking for ahead to reading extra from you in a while!…

    Like

    Reply

    1. I spend a lot of time writing blog posts and frequently forget to express gratitude to my readers and followers.
      Your feedback is really valuable to me.
      Thanks a lot.

      Like

      Reply

  5. I do accept as true with all the concepts you have presented for your post.
    They are really convincing and will definitely work. Nonetheless, the posts are
    very brief for starters. May just you please prolong them a little from next time?
    Thanks for the post.

    Like

    Reply

  7. Thank you a lot for sharing this with all of us you actually recognise what
    you are talking approximately! Bookmarked. Please additionally talk
    over with my web site =). We will have a link trade contract between us!

    Look at my web site – MaxBHB

    Like

    Reply

  8. Hmm it appears like your website ate my first comment (it was extremely long) so I guess I’ll
    just sum it up what I wrote 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 suggestions for inexperienced blog writers?
    I’d really appreciate it.

    my web blog :: Imarais Beauty Skin Care

    Like

    Reply

  11. Woah! I’m reallү enjⲟying the tеmplate/themе of this website.
    It’s simpⅼe, yet effective. A ⅼоtt of times it’s haгd to get tthat “perfect balance” bstween superb usability and visual appearance.
    I mmust say you’ve done a great job witһ this.
    Also, the blog loads super quick ffor me on Firefox.

    Outstanding Blog!

    Like

    Reply

  13. I want to show thanks to the writer for bailing me out of this particular circumstance.
    Right after surfing throughout the internet and obtaining things that were not pleasant, I assumed my entire
    life was gone. Living without the presence of strategies to the issues you’ve solved by means of your
    entire review is a crucial case, as well as those which might have adversely damaged my career if I had not noticed your web site.
    Your actual skills and kindness in playing with all areas was useful.
    I’m not sure what I would have done if I hadn’t come across such a thing like this.

    I’m able to at this point look ahead to my future. Thank you so much for this high quality and result oriented guide.
    I will not think twice to suggest your web blog to
    anyone who should get counselling about this issue.

    My page :: Fenitra (hypnotronstudios.com)

    Like

    Reply

  18. Good – I should definitely pronounce, impressed with your web site.
    I had no trouble navigating through all the tabs and related information ended up being truly simple to do to access.
    I recently found what I hoped for before you know it at all.
    Quite unusual. Is likely to appreciate it for those who add forums or something, web site theme .
    a tones way for your customer to communicate.
    Nice task.

    Here is my blog – http://211.159.150.117

    Like

    Reply

  25. Very good website you have here but I was wanting to
    know if you knew of any user discussion forums that cover the same topics discussed here?
    I’d really love to be a part of community where I can get suggestions from other experienced individuals that share
    the same interest. If you have any recommendations, please let me know.

    Many thanks!

    My blog post :: http://www.alotofsex.com

    Like

    Reply

  27. I was just searching for this information for a while. After 6 hours of continuous Googleing, finally
    I got it in your site. I wonder what is the lack of Google strategy that do not rank this kind of informative web
    sites in top of the list. Normally the top websites are
    full of garbage.

    Also visit my blog post: Tundra Breeze Air Conditioner
    Price (ebmelectronics.com)

    Like

    Reply

  36. I tend not to write a comment, however I read a great deal of responses here Regular
    Expression Extract (Regexp_Extract) In Apache Hive –
    Big Data and SQL. I actually do have 2 questions for you if it’s allright.
    Could it be only me or does it look like like a few of these remarks appear like they are written by
    brain dead individuals? 😛 And, if you are writing on other places, I’d
    like to keep up with everything new you have to post.
    Could you make a list of every one of all your social pages like your
    linkedin profile, Facebook page or twitter feed?

    my blog post – Oracle Leaf Gold CBD

    Like

    Reply

  37. Wow, marvelous blog format! How long have you ever been running
    a blog for? you made blogging look easy. The total
    look of your web site is magnificent, let alone the content material![X-N-E-W-L-I-N-S-P-I-N-X]I just couldn’t leave your website prior to suggesting that I extremely enjoyed the standard info an individual supply in your guests?
    Is gonna be back steadily to check out new posts.

    My web page :: http://www.aniene.net

    Like

    Reply

  38. Thank you for your blog post. Johnson and I have already been saving for a new publication on this subject and your post
    has made people like us to save our own money. Your opinions really responded all our problems.
    In fact, greater than what we had acknowledged in advance of the time we came upon your great blog.

    We no longer nurture doubts including a troubled mind because you have actually attended to our own needs in this post.
    Thanks

    My site: http://www.goldenanapa.ru

    Like

    Reply

  39. My spouse and I absolutely love your blog and find most of your post’s to
    be just what I’m looking for. can you offer guest writers to write content in your case?
    I wouldn’t mind producing a post or elaborating on a number of the subjects you
    write concerning here. Again, awesome web site!

    Here is my website Max Ketosis Fuel

    Like

    Reply

  40. This is the perfect web site for anybody who wishes to understand this
    topic. You know so much its almost tough to argue with you (not that I actually would want to?HaHa).
    You definitely put a brand new spin on a topic that has been discussed for a long time.

    Great stuff, just wonderful!

    Feel free to surf to my blog duna-anapa.net.ru

    Like

    Reply

  42. Definitely believe that which you stated. Your favorite justification appeared to be on the internet the easiest
    thing to be aware of. I say to you, I definitely get irked while people consider worries
    that they plainly do not know about. You managed to hit the nail upon the top and defined out the
    whole thing without having side-effects , people
    can take a signal. Will probably be back to get more.
    Thanks

    Like

    Reply

  44. Беттеры смогут на сто процентов
    легально сделать ставку на киберспортивные
    дисциплины в GG Бет, в кратчайшие сроки обналичивать
    выигрыш. Данная букмекерская контора устанавливает достаточно высокие коэффициенты, раздаёт своим
    пользователям бонусы. Другие важные
    преимущества данного ресурса:

    Like

    Reply

  48. Thank you for your website post. Thomas and I are
    saving for just a new publication on this matter and your short article has made
    us all to save our money. Your ideas really answered all our problems.
    In fact, above what we had recognized prior to when we came across your excellent blog.

    My spouse and i no longer have doubts as well as a troubled mind because you
    have attended to the needs above. Thanks

    Here is my website; https://jpgsoft.co.kr/index.php?mid=picknotice&m=0&page=1&document_srl=958324

    Like

    Reply

Leave a reply to SHAFI SHAIK Cancel reply