Skip to Content

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:

/wp-content/uploads/2013/05/snwd_texts_218095.jpg

  • SNWD_PD table structure:

/wp-content/uploads/2013/05/snwd_pd_218099.jpg

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”:

/wp-content/uploads/2013/05/search_result_218442.jpg

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

/wp-content/uploads/2013/05/search_demo_servlet_220792.jpg

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

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Chris Paine

    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

    (0) 
    1. Dimitar Tenev Post author

      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

      (0) 
  2. Inna Nickel

    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 searchhttp://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

    (0) 
    1. Dimitar Tenev Post author

      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

      (0) 
  3. Michael Appleby

    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

    (0) 

Leave a Reply