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
- SAP Managed Tags:
6 Comments
