Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB