hive to separate columns based on a pattern

user_17139186 0
Viewed 37

in one column data is like this

':9a:abcd efgh ijkl :12a: mnop qr :52b: stuv w :63a: xyz......'

I have to separate this data based on these tags :9a:, :12a:, :52b: and these tags keeps changing like not all tags are present for every record, so if a particular tag is not present it should have null value and the data is not fixed length and some times each tag value is multi-line

column :9a:   |column :12a:| column :52b:| column :63a:|    
abcd efgh ijkl| mnop qr.   | stuv w.     |xyz......    |
hue hive find sql
2 Answers

I would write a User Defined Function to do this work. This really is a programing problem more than a SQL query issue. A user defined function is written in Java and can be called from SQL to do work. They are slower than normal HIve SQL but they are good for this exact type of complicated logic you are asking about.

You can do some work with regexp_extract in hive but this seems to be a little too complicated for that.

I would think that Spark would do a better job of parsing the data for this table if that an option.

user_135351200

Use regexp_extract.

Demo:

with mydata as(
select ':9a:abcd efgh ijkl :12a: mnop qr :52b: stuv w :63a: xyz......' as col 
)

select regexp_extract(col,':9a:\\s?(.*?)(:|$)',1) as `column :9a:`,
       regexp_extract(col,':12a:\\s?(.*?)(:|$)',1) as `column :12a:`,
       regexp_extract(col,':52b:\\s?(.*?)(:|$)',1) as `column :52b:`,
       regexp_extract(col,':63a:\\s?(.*?)(:|$)',1) as `column :63a:`
  from mydata

Result:

column :9a:      column :12a:   column :52b:    column :63a:    
abcd efgh ijkl   mnop qr        stuv w          xyz......

Regexp ':63a:\\s?(.*?)(:|$)' meaning:

:63a: - tag

\\s - optional space - Remove this if the space after tag should be included in value

(.*?)(:|$) - value (.*?) to extract till : or the end of the string $

If values can contain : you may need more complex regex, for example like this:

':12a:\\s?(.*?)(:\\d+[a-z]:|$)' - first group (.*?) is the value to be extracted.

Second group is used to determine the end of value (:\\d+[a-z]:|$) it has two alternatives: tag pattern or the end of the string $. Tag pattern :\\d+[a-z]: consist of :, 1+ digits and one a-z character. If your tags can have different pattern, adjust the regex accordingly.

user_27003440
Related