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

1. You have an account on SAP HANA Cloud trial landscape
2. You have downloaded and installed the SAP HANA Studio Developer Edition in accordance with the SAP HANA Studio Installation Guide
3. You have downloaded the latest version of SAP HANA Cloud SDK (Java Web) in accordance with the Installing Cloud SDK Guide.
4. In your SAP HANA Studio you have installed SAP development tools for Eclipse
5. In your SAP HANA Studio you have setup the cloud SDK location and landscape host
6. 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:

1. Import the web application cloud-hana-search-demo from github into Eclipse workspace
2. From command prompt do run “mvn clean install” in this project
3. Deploy the project on SAP HANA Cloud Platform
4. Go to SAP HANA Cloud Platform Cockpit, and open URL of deployed application
5. 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