Will elastic search work on fully exposed industry standard external tables ?
We have a business requirement, where in we need to query different external table (i.e.Fully exposed industry standard tables without having pxInsName/pzInsKey/BLOB columns) specific columns (These specific columns are minimum of 3 & maximum of 6 columns from each tables) which don’t have any keys defined on them to get the records based on search operator i.e. contains/starts with/ ends with/equals. Each table will have minimum of 2 lakh records & maximum is unbounded as it’s ever growing in nature.
We tried exploring elastic search feature, but it’s not solving our use case. The problem with this search are:
- Even though we have provided custom search properties as specific columns, it’s searching all the columns in that table
- At any point of time, we will be getting only one result as pxInsName is null as it’s an fully exposed external table.
- Class joins, sub reports, or SQL functions are not supported in RD
For now, we are using RD’s to get the information from the tables including joins & adding filter criteria. It was taking long time to get the information for certain use cases where we have contains as search operator.
We are using PRPC 7.2.2 version.
Is there any other approach/search pattern to get the results without impacting the performance?
***Updated by Moderator: Marissa to move from Applications to Product Support Community; update categories***
Instellars
IN
Hi,
I am not sure about your use case if without any keys defined, elastic search can work as per your requirement or not. However, you can use custom SQL functions in RD as shown in the image where a sql alias function which takes a parameter has been used for one of the filters.
Pegasystems Inc.
IN
Hi,
In context of elastic search, RD's won't support SQL functions. However in normal use cases, we can use custom SQL functions & at the same time those functions have to be optimised for better performance.
Pegasystems Inc.
IN
Even though we have provided custom search properties as specific columns, it’s searching all the columns in that table
Custom search properties are specified to retrieve them back in search results;
For your use-case where the search needs to be restricted to a few columns, you might need to add filters in the RD that you pass on to pxRetrieveSearchData;
At any point of time, we will be getting only one result as pxInsName is null as it’s an fully exposed external table
pxInsName will be internally populated for external tables provided you specify keys in your class definition; (Only specific to search) Could you verify if the class associated with your external table has keys defined ?
Class joins, sub reports, or SQL functions are not supported in RD
Yes, these features are not supported by search within the platform.