I used this question earlier on the wrong list; posting on apache phoenix user group on the advise of James Taylor:
We have a use case of migration from legacy database to hbase/phoenix. I'd want to hear your thoughts on how to manage the given multi-valued field:
Field name : namesList
Contains: comma separated list of strings.
e.g. "Jack, Richard, Fred"
The field does not have an inherent limit on the number of strings, however for successful migration we need to support unto 100 strings in the field
This field is currently indexed in our legacy DB technology where the user is able to query by any of the contained strings.
select * from myTable where namesList LIKE '%Fred%';
Is there any way Phoenix supports generating multiple index values from a single column in a row ? We are looking for a method where we don't explicitly specify individual offsets for indexing, (nameList, nameList etc) , rather say a custom functional index equivalent generates the indexes as a list and each of the index in the list is persisted individually.
Effectively our goal is to avoid full table scans when doing queries that match values from multi valued fields such as example above. We do want to avoid exploding the data however possible.
Any comments / suggestions are welcome.
Thank you !