Enhance Your Cloud Application with HANA Search
It’s time to make your cloud application’s content, pages, documents, etc. discoverable e.g. searchable by your application’s users. It will be nice to have internet like search results.Having this in mind you will need some query more powerful than: "...WHERE "COMPANY_NAME" like '%Oil%'...".
Fortunately SAP HANA provides a built-in search capabilities that allows your application’s users “to search tables and views much like they would when searching for information on the Internet.” (for details refer “SAP HANA Developer Guide“). In the following lines, you will find how to setup your Cloud HANA DB table, so you could benefit from HANA search capabilities.
Scenario
We got a HANA Cloud application, that uses two DB tables (SNWD_PD, SNWD_TEXTS). These tables contain products, and texts associated with them. We want to give our application’s users the ability to search for products by name and description. Here are the specific search requirements:
- Make fault tolerant search (fuzzy search in HANA) for the product’s name and description (e.g. if user searches for “mouzePat”, find also products for “Mousepad”)
- Make linguistic search for the product’s name and description (e.g. if user searches for “mouse”, find also products for “mice”)
- Score the results by relevance
- Give higher scores (weight) to the results from linguistic search than the ones from fuzzy search
Prerequisites
- You have an account on SAP HANA Cloud trial landscape
- You have downloaded and installed the SAP HANA Studio Developer Edition in accordance with the SAP HANA Studio Installation Guide
- You have downloaded the latest version of SAP HANA Cloud SDK (Java Web) in accordance with the Installing Cloud SDK Guide.
- In your SAP HANA Studio you have installed SAP development tools for Eclipse
- In your SAP HANA Studio you have setup the cloud SDK location and landscape host
- In your SAP HANA Studio you have setting up the runtime environment
DB Tables Structure
In the used tables below the columns “NAME_GUID”, and “DESC_GUID” refers texts (“PARENT_KEY”) for product’s name, and description respectively.
- SNWD_TEXTS table structure:
- SNWD_PD table structure:
Prepare Text’s (SNWD_TEXTS) DB Table for Search
HANA search capability is enabled per DB table’s column. Such a column has defined fulltext index (existing by default for column types TEXT, SHORTTEXT; for details refer “SAP HANA Developer Guide“).
CREATE FULLTEXT INDEX SNWD_TEXTS_TEXT ON "JP_OPINT_WEBSHOP_WEB"."SNWD_TEXTS"(TEXT) FAST PREPROCESS OFF FUZZY SEARCH INDEX ON;
- “FAST PREPROCESS OFF” – enables HANA linguistic search over a DB column
- “FUZZY SEARCH INDEX ON” – increase performance of fuzzy search
Create DB Procedure for Search Execution
As a result from a search we expect a table with product’s information (from “SNWD_PD” table), name and description (from “SNWD_TEXTS” table). We have created a procedure “findProduct” capsulating SQL queries. Meaning of SQL queries is the following:
- “search” – executes fuzzy, and linguistic search for “searchTerm” over “SNWD_TEXTS” table. The desired weights for both searches is used. Score is used latter to sort the results by relevance
- “name” – join product’s table with name’s search results
- “descr” – join product’s table with description’s search results
- “search_all” – union search results for product’s name and description
- “max_relevance” – leave the best search relevance for a product
- “result” – group search results by products, and order them by relevance
create procedure findProduct(in searchTerm VARCHAR(100), out result "PRODUCT_RESULT") AS
BEGIN
search = SELECT "PARENT_KEY", "TEXT", SCORE() AS RELEVANCE FROM "SNWD_TEXTS"
WHERE client='000' AND
(
CONTAINS (TEXT, :searchTerm, FUZZY(0.4), WEIGHT(0.5)) OR
CONTAINS (TEXT, :searchTerm, LINGUISTIC, WEIGHT(0.6))
);
name = SELECT "PRODUCT".*,
"TEXT"."TEXT" AS NAME,
DESCRIPTION.text AS DESCRIPTION,
RELEVANCE
FROM "SNWD_PD" AS "PRODUCT"
INNER JOIN :search AS "TEXT"
ON "TEXT"."PARENT_KEY" = "PRODUCT"."NAME_GUID"
LEFT OUTER JOIN "SNWD_TEXTS" AS DESCRIPTION
ON "PRODUCT".DESC_GUID=DESCRIPTION.PARENT_KEY
WHERE "PRODUCT".client='000';
descr = SELECT "PRODUCT".*,
"TEXT"."TEXT" AS NAME,
DESCRIPTION.text AS DESCRIPTION,
RELEVANCE
FROM "SNWD_PD" AS "PRODUCT"
INNER JOIN :search AS DESCRIPTION
ON "DESCRIPTION"."PARENT_KEY" = "PRODUCT"."DESC_GUID"
LEFT OUTER JOIN "SNWD_TEXTS" as "TEXT"
ON "PRODUCT".NAME_GUID="TEXT".PARENT_KEY
WHERE "PRODUCT".client='000';
search_all = SELECT * FROM :name UNION SELECT * FROM :descr;
max_relevance = SELECT MAX(TO_DOUBLE(RELEVANCE)) AS RELEVANCE, PRODUCT_ID
FROM :search_all group by PRODUCT_ID;
result = SELECT PRODUCT.*
FROM :max_relevance MAX_RELEVANCE
INNER JOIN :search_all PRODUCT
ON PRODUCT.RELEVANCE=MAX_RELEVANCE.RELEVANCE AND
PRODUCT.PRODUCT_ID=MAX_RELEVANCE.PRODUCT_ID
ORDER BY PRODUCT.RELEVANCE DESC;
END;
Search Result
Here is a search result for “mouzePat”:
Try It Yourself
Get java web application project from GitHub: cloud-hana-search-demo.
Make it works:
- Import the web application cloud-hana-search-demo from github into Eclipse workspace
- From command prompt do run “mvn clean install” in this project
- Deploy the project on SAP HANA Cloud Platform
- Go to SAP HANA Cloud Platform Cockpit, and open URL of deployed application
- The screenshot below should appear
Enjoy the benefits of native HANA search abilities in the SAP HANA Cloud.
Reference
8 Easy Steps to Develop an XS application on the SAP HANA Cloud Platform
Using HANA Modeler in the SAP HANA Cloud
Creating and using HANA native scripted calculation view in SAP HANA Cloud
Click and Try Sample XS Applications on the SAP HANA Cloud Platform
Hi Dimitar,
I am interested in the way that the JDBC connection was used to create the stored procedures in the DBManager/DBUtil class by loading the scripts from the deployed resources. DO you see this as being a fairly standard way to deal with the hassles of deploying stored procedures to HANA?
I'm guessing that you used HANA studio and an instance of a HANA DB to test/build/generate the SQL? Or did you craft that procedure by hand? In which case, hat's off, nice work! (nice work btw regardless! 🙂 )
Did you connect your local "JVM bit of HANA Cloud Platform" to a "local" HANA DB for testing or did you always deploy to cloud to test? I remember doing something similar at a InnoJam last year and the constant need to redeploy to test UI changes (as no local HANA connection was possible) was an absolute killer.
Given recent EclipseLink JPA 2.1 Stored Procedures support, http://www.eclipse.org/eclipselink/releases/2.5.php would you consider re-writing your example to use that tooling? Do you think it would make it more maintainable?
Thanks for sharing this with us all.
So much happening in SAP HANA Cloud Platform space it is hard (even for someone like me who is reasonably well engaged) to keep up!
Cheers,
Chris
Hi Chris,
Thanks for the interest 🙂
Here are my answers:
1. The aim of the search demo application is to give an easy way for a user to try HANA Search functionality. It does not impose any standards for HANA development.
2. We used HANA Studio (connected to our local HANA server) to create/test the SQL code for the search demo application. We wrote procedure by hand in HANA SQL editor.
3. At this point of time we do not intend to rewrite the search demo application using EclipseLink.
Stay tuned 🙂
Best regards,
Dimitar
Just in case you missed it - the source code is now avilable on our github repo:
https://github.com/SAP/cloud-hana-search-demo
Have fun!
Hi Dimitar,
I've imported the sample project to my HANA Virtual machine, comited and activated in my working repository...Everything looks fine and activated, but if I try to execute the step 4. according your documentation "Navigate to SearchDemoServlet URL, and try the search" http://10.66.177.101:8000/SearchDemoServlet
I get an Error 404."This link seems to be broken"...
Do you have any idea how I can solve this problem?
Kind regards,
Inna
Hi Inna,
First, thanks for your interest 🙂
The provided project is a web application which should be deployed on SAP HANA Cloud.
You may create a trial account on SAP HANA Cloud, and deploy the project there.
For more informaton on setting up HANA Studio for Cloud usage please see "Prerequisites" section in blog http://scn.sap.com/community/developer-center/cloud-platform/blog/2013/07/16/using-hana-modeler-in-sap-hana-cloud
Once you are done with "Prerequisites" you may:
1. Import project from git https://github.com/SAP/cloud-hana-search-demo
2. From Eclipse's "Java EE" perspective using mouse alternative button do click on the project "Run As" -> "Run on Server". Server should be trial on SAP HANA Cloud
Hope this helps.
Best regards,
Dimitar
Unless you are asking for clarification/correction of some part of the Document, please create a new Discussion marked as a Question. The Comments section of a Blog (or Document) is not the right vehicle for asking questions as the results are not easily searchable. Once your issue is solved, a Discussion with the solution (and marked with Correct Answer) makes the results visible to others experiencing a similar problem. If a blog or document is related, put in a link. Read the Getting Started documents (link at the top right) including the Rules of Engagement.
NOTE: Getting the link is easy enough for both the author and Blog. Simply MouseOver the item, Right Click, and select Copy Shortcut. Paste it into your Discussion. You can also click on the url after pasting. Click on the A to expand the options and select T (on the right) to Auto-Title the url.
Thanks, Mike (Moderator)
SAP Technology RIG