You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by "Ryabin, Thomas" <To...@McKesson.com> on 2012/04/23 15:43:57 UTC
Possible to use regex column specification with WHERE clause?
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
Re: Possible to use regex column specification with WHERE clause?
Posted by Nitin Pawar <ni...@gmail.com>.
Shashwat,
i think he wanted to put a regex in the where clause to derive a column
name instead of select clause
On Tue, Apr 24, 2012 at 4:45 PM, shashwat shriparv <
dwivedishashwat@gmail.com> wrote:
> 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 B strings NULL 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 B strings NULL 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 TRUE A REGEXP B strings Same 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 <ni...@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 <To...@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
>
>
>
--
Nitin Pawar
Re: Possible to use regex column specification with WHERE clause?
Posted by shashwat shriparv <dw...@gmail.com>.
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 <ni...@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 <To...@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
Re: Possible to use regex column specification with WHERE clause?
Posted by Nitin Pawar <ni...@gmail.com>.
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 <To...@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