Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Possible to use regex column specification with WHERE clause?


Copy link to this message
-
Re: Possible to use regex column specification with WHERE clause?
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 <
[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]> 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