Hi,

Do you have the equivalent of that operation in pure SQL. Also have you tried Spark query tool with Hive table.

I gather you are doing this through Java?


On 5 May 2016 at 13:10, Amey Barve <ameybarve15@gmail.com> wrote:
Thanks Mich,

It does work, but with operators other than '=', '!=', '<', '<=', '>' and '>=' , my code with custom storage handler gets null expression.

final String expression = conf.get(TableScanDesc.FILTER_EXPR_CONF_STR); // expression is null for hive query that has like operator

Why does above API return null for hive query that has like operator? I need to pass like operator for predicate push down to my custom storage handler.

Regards,
Amey

On Thu, May 5, 2016 at 5:30 PM, Mich Talebzadeh <mich.talebzadeh@gmail.com> wrote:
On a normal query using sql in Hive 2 LIKE predicate works fine. Case in point in a 1 billion rows table with the column random_string of varchar(50) I have one row that satisfies the followinh@

+-----------+------------------+------------------+-------------------+-----------------------------------------------------+-----------------+----------------+--+
| dummy.id  | dummy.clustered  | dummy.scattered  | dummy.randomised  |                 dummy.random_string                 | dummy.small_vc  | dummy.padding  |
+-----------+------------------+------------------+-------------------+-----------------------------------------------------+-----------------+----------------+--+
| 1         | 0                | 0                | 63                | rMLTDXxxqXOZnqYRJwInlGfGBTxNkAszBGEUGELqTSRnFjRGbi  |          1      | xxxxxxxxxx     |
| 2         | 0                | 1                | 926               | UEDJsfIgoYqwreSuuvjIcPZarpxMdCthpDCsgPlJfvIiylLiBS  |          2      | xxxxxxxxxx     |

Now let us try to select that row with LIKE predicate:

0: jdbc:hive2://rhes564:10010/default> select count(1) from dummy where random_string like 'rMLTDXxxqXOZnqYRJ%';

INFO  :
Query Hive on Spark job[0] stages:
INFO  : 0
INFO  : 1
INFO  :
Status: Running (Hive on Spark job[0])

INFO  : Completed executing command(queryId=hduser_20160505125700_cbc415b6-91bb-4ed6-95e4-d177e12988f6); Time taken: 153.544 seconds
INFO  : OK
+-----+--+
| c0  |
+-----+--+
| 1   |
+-----+--+
1 row selected (153.959 seconds)

So it does work

HTH



On 5 May 2016 at 11:53, Amey Barve <ameybarve15@gmail.com> wrote:
Hi All,

I have implemented custom storage-handler and able to get predicates from hive for '=', '!=', '<', '<=', '>' and '>=' operators.
But I cannot get predicates from hive for 'like', 'between' operators.

Here's my code:

final String expression = conf.get(TableScanDesc.FILTER_EXPR_CONF_STR);

here expression remains null for like and between operators but not null for above operators.

Does hive not give predicates for 'like' and 'between' operators to custom storage handler ?
Is there some other mechanism to get predicates for 'like' operator?

I tested with hive version 1.2 and 0.14.

Thanks and Regards,
Amey