We are running the Hive Query for conducting a wild card name search for a list of potential matches of customer info for one of our banking client. We are executing this query on Hue UI and this query is basically use like operation with OR condition. Query having 2K plus OR condition with like operator as mentioned below. This query compilation taking too much time and causing Hive Server Master node hung. Can you please advice if we can use any alternate approach or how we can optimize this query performance to avoid server down issue. This query runs fine in oracle DB.
Select * from customer where partition_dt='2017-01' and full_name like 'test %'
full_name like ' ravi %'
or full_name like 'rajeev %'
Any help on this issue will be really helpful. Please let me know if needed any additional details.Thanks
Select * from customer where partition_dt='2017-01' and full_name like '%test%' limit 100;
In an alternative you can try this as well -
Select * from customer where partition_dt='2017-01'
and full_name RLIKE '*ravi*|*rajeev*'