question Re: Find a keyword in a column in Hive table in Archives of Support Questions (Read Only)

Find a keyword in a column in Hive table

jgarrigan — Sun, 27 Mar 2016 07:25:11 GMT

I have a Hive table with a number of columns where column X contains a large string of text with many spaces between each word, all delimiters have been removed and all that remains are 0-9 a-z and A-Z characters. I would like to query Column X for a keyword Y and Y^ (eg Java and Javascript) and count the number of unique users from column Z that have mentioned these two words. Either of these words need to be mentioned once in column X for it to be counted. I would then like to count the total number of users that mentioned either of these two words.

Re: Find a keyword in a column in Hive table

pminovic — Sun, 27 Mar 2016 11:56:31 GMT

Hi @John Garrigan, your specs are a little imprecise, but can you try this:

select count(distinct Z) from t1 where locate(Y, X)>0;   -- for example:
select count(distinct Z) from t1 where locate("Java", X)>0;

Note that, as stated, your condition on "Y^" (usually expressed as "Y*") is superfluous, since if Y is there then any superstring of Y will be there too.

Re: Find a keyword in a column in Hive table

jgarrigan — Sun, 27 Mar 2016 18:15:06 GMT

Hi Predrag, many thanks for your help this worked perfectly.

Re: Find a keyword in a column in Hive table

evaloschin — Thu, 19 Apr 2018 21:36:42 GMT

hi @Predrag Minovic

how can i locate some word but in a table, no in a column?

select count(distinct Z)from t1 where locate("Java", X)>0;

thank you.

Re: Find a keyword in a column in Hive table

sow — Wed, 27 May 2020 12:12:59 GMT

hi,

Were you able to find a solution for this ?