separator within primitive fields

["kulkarni.swarnim@gmail.com" <ku...@gmail.com>]

Hello,

Suppose I have a very simple struct that I want to query in hive.

struct test {

  string field1;
  int field2;
  boolean field3;
}

Now if my field has multiple values delimited by a separator. So, for
example

field1 = "test1 | test2 | test3"
field2 = 23
field3 = true

Is it possible for me to run a query like,

select test.field2,test.field3 from my_table where field1="test1";

which would return:

23 true

Any help is much appreciated.

Thanks,
-- 
Swarnim

Re: separator within primitive fields

[Jan Dolinár <do...@gmail.com>]

Hi,

You can use something like

where instr(field1, 'test1')>=0

It is simple but not very reliable. Better solution is probably

where array_contains(split(field1, ' \| '),'test1')

I have not tested these, so you might need to tweak it a bit, but it is
well described in manual:
https://cwiki.apache.org/Hive/languagemanual-udf.html

Best regards
Jan


On Thu, Jun 28, 2012 at 4:17 PM, kulkarni.swarnim@gmail.com <
kulkarni.swarnim@gmail.com> wrote:

> Hello,
>
> Suppose I have a very simple struct that I want to query in hive.
>
> struct test {
>
>   string field1;
>   int field2;
>   boolean field3;
> }
>
> Now if my field has multiple values delimited by a separator. So, for
> example
>
> field1 = "test1 | test2 | test3"
> field2 = 23
> field3 = true
>
> Is it possible for me to run a query like,
>
> select test.field2,test.field3 from my_table where field1="test1";
>
> which would return:
>
> 23 true
>
> Any help is much appreciated.
>
> Thanks,
> --
> Swarnim
>