random tech
random tech

Reputation: 11

Hive str_to_map

I have a sample data as below Id(string), delimProperties(string)
== ==============

1, foo.prop=foo value|bar.prop=bar value
2, foo2.prop=foo2 value|bar2.prop=bar2 value

Here is my dml
1)

CREATE TABLE IF NOT EXISTS test_data(Id STRING, delimProperties STRING)<br>
row format delimited <br>
fields terminated by ","<br>
COLLECTION ITEMS TERMINATED BY '|' <br>
map keys terminated by '='<br>
STORED AS textfile;<br>

2)

CREATE TABLE IF NOT EXISTS final_data(Id STRING, delimProperties STRING)
row format delimited
fields terminated by ","
COLLECTION ITEMS TERMINATED BY '|'
map keys terminated by '=';

3)

alter table final_data add columns (properties map<string,string>);

4)

load data local inpath 'file:/c:/data/sample.csv' into table test_data;

5)

insert into table final_data select Id,
str_to_map(delimproperties,'|','=')
from test_data;

6)

Select * from final_data limit 1;

1 {"":"", " ":null, ".":null}

Any idea what I must be doing wrong?

Upvotes: 1

Views: 7617

Answers (2)

Ankur Goel
Ankur Goel

Reputation: 21

You need to escape the '|' character as it has special meaning in Java regular expression, which is used by string.split(..) here

https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFStringToMap.java.

This should work:

insert into table final_data select Id,
str_to_map(delimproperties,'\\|','=')
from test_data;

Upvotes: 2

codghost
codghost

Reputation: 11

You can try this:

str_to_map(delimproperties,'[|]','=')

Upvotes: 1

Related Questions