hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From shashwat shriparv <dwivedishash...@gmail.com>
Subject Re: Possible to use regex column specification with WHERE clause?
Date Tue, 24 Apr 2012 11:15:42 GMT
Use this to generate probable strings  Some examples are here :

regexp_extract(s, '^([a-zA-Z0-9]{2}\.)?(a-zA-Z0-9]{3}-?){3}')

select regexp_extract(request, ' (\\S*) HTTP', 1) from logfile;

select regexp_extract('junk:text:ua123','ua[0-9]+',0) from dual

and pass in your query with or condition. just read a bit about
regexp_extract

Cheer :)

Check out these threads also :
https://issues.apache.org/jira/browse/HIVE-420

This is from  :
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
A LIKE BstringsNULL if A or B is NULL, TRUE if string A matches the SQL
simple regular expression B, otherwise FALSE. The comparison is done
character by character. The _ character in B matches any character in
A(similar to . in posix regular expressions) while the % character in B
matches an arbitrary number of characters in A(similar to .* in posix
regular expressions) e.g. 'foobar' like 'foo' evaluates to FALSE where as
'foobar' like 'foo_ _ _' evaluates to TRUE and so does 'foobar' like 'foo%'A
RLIKE BstringsNULL if A or B is NULL, TRUE if string A matches the Java
regular expression B(See Java regular expressions syntax), otherwise FALSE
e.g. 'foobar' rlike 'foo' evaluates to FALSE where as 'foobar' rlike
'^f.*r$' evaluates to TRUEA REGEXP BstringsSame as RLIKE


>From :
http://karmasphere.com/ksc/hive-queries-on-tables.html#regex_column_spec
REGEX Column Specification

A SELECT statement can take regex-based column specification.

   -

   We use java regex syntax. Try http://www.fileformat.info/tool/regex.htm for
   testing purposes.
   - The following query select all columns except ds and hr.

SELECT `(ds|hr)?+.+` FROM sales



Shashwat Shripar


On Tue, Apr 24, 2012 at 1:00 PM, Nitin Pawar <nitinpawar432@gmail.com>wrote:

> you may want to have a programmatic approach for doing this and
> provide hive with a final query.
>
> You can solve this with either solving your regular expression outside
> hive paradigm and then provide the query to hive
>
>
> On 4/23/12, Ryabin, Thomas <Tom.Ryabin@mckesson.com> wrote:
> > Hi,
> >
> >
> >
> > I know that it is possible to use regex column specification with the
> > SELECT clause like so:
> >
> > SELECT `employee.*` FROM employees;
> >
> >
> >
> > I was wondering if it is possible to use it with the WHERE clause also.
> > For example I want to create the query:
> >
> > SELECT `employee.*` FROM employees WHERE $1 = 'Steve';
> >
> >
> >
> > where $1 represents a column that was matched by the regex. Any ideas?
> >
> >
> >
> > Thanks,
> >
> > Thomas Ryabin
> >
> >
>
>
> --
> Nitin Pawar
>



-- 


∞
Shashwat Shriparv

Mime
View raw message