Creating and using HANA native scripted calculation view in SAP HANA Cloud

[former_member214175]

This article describes how to create a simple Web application running at SAP HANA Cloud and consuming HANA scripted calculation view. With a few steps we could create calculation view via HANA Studio SQL Editor and consume it in a standard servlet. The described scenario provides the opportunity to create on demand web application which consumes HANA database artifacts in secured and isolated manner.

Prerequisites

1. You have an account on SAP HANA Cloud trial landscape
2. You have downloaded and installed the SAP HANA Cloud Platform tools for Eclipse IDE in accordance with Installing SAP Development Tools for Eclipse.
3. You have downloaded the latest version of SAP HANA Cloud SDK (Java Web) in accordance with the Installing Cloud SDK Guide.

Procedure

Step 1: Create a Trial Instance

Open the SAP HANA Cloud Cockpit and enter the HANA Instances section. Choose New HANA Instance. Enter the required name and save changes.

Step 2: Create and Consume a Scripted Calculation View in a Web Application

In a Web application, we’ll create a database table called Managers, and a relevant scripted calculation view. The scripted calculated view contains three major artifacts - user-defined type, stored procedure and column view. Script procedure could be defined via SQL or CE as described in SAP HANA SQLScript Reference.

     Using cloud persistence service by plain JDBC, we’ll consume the calculation view in a servlet.
     All this is implemented in the source code of the HANA Scripted View Demo application in GitHub. We’ll use it as an example.

To run the HANA Scripted View Demo application:
     1. From GitHub, clone the https://github.com/SAP/cloud-hana-scriptview-demo repository and import the relevant project.
     2. Using the context menu of the project in the Project Explorer view, choose Run As -> Run on server.
     3. Choose the Trial landscape and deploy. For more details, see Deploying on the Cloud from Eclipse IDE.
     4. In the Cockpit, go to Databases & Schemas. Check the DB schema ID associated with your scriptview application. It should be something like this:      <your trial user>trial.scriptview.web.

5. In your Eclipse IDE, open the SAP HANA Development perspective.

6. In Systems, choose Add Cloud System.

7. Connect to the Trial landscape using your Trial account information. Choose the DB schema associated with the scriptview application.

8. Get the ID of the DB schema

9. Run a search & replace operation over the scriptview project to replace all occurrences of NEO_<YOUR_SCHEMA_ID> with the actual DB schema ID that you got in the previous step.

10. Republish the scriptview application and observe the table data visualized.

NOTE: ScriptViewDemoServlet is using persistence service via JDBC to consume scripted calculation view created via database tunneled HANA studio.

NOTE: Application web.xml is containing the additional configurations needed for the plain JDBC.

The application contains the following SQL scripts:

• cloud-hana-scriptview-demo\src\main\resources\sql\create_procedure.sql– the procedure that will be called when using the scripted view
• cloud-hana-scriptview-demo\src\main\resources\sql\create_tables.sql – creates
  the type used by the procedure
• cloud-hana-scriptview-demo\src\main\resources\sql\create_view.sql -  creates the scripted view

public class ScriptViewDemoServlet extends HttpServlet {

    .....

    @Override

    public void init() throws ServletException {

        try {

            DBUtil dbUtil = new DBUtil((DataSource) new InitialContext().lookup("java:comp/env/jdbc/DefaultDB"));

            dbManager = new DBManager(dbUtil);

            dbManager.initDB();

        } catch (NamingException e) {

            throw new RuntimeException("Failed to lookup default datasource", e);

        } catch (IOException ioe) {

            throw new RuntimeException("Failed to load resource", ioe);

        }

    }

    @Override

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        PrintWriter writer = response.getWriter();

        writer.print(getFormattedString(dbManager.getManagers()));

        writer.flush();

    }

    ....

}

public class DBManager {

    ....

    private static final String SELECT_ALL_MANAGERS_SCRIPTED_VIEW =

          "SELECT * FROM \"NEO<YOUR_SCHEMA_ID>\".\"manager/SCRIPTVIEW\"";

    public DBManager(DBUtil dbAccess) {

        this.dbAccess = dbAccess;

    }

    public List<ManagerEntity> getManagers() throws IOException {

        Connection connection = dbAccess.getConnection();

        List<ManagerEntity> managerList = new ArrayList<ManagerEntity>();

             ResultSet result = connection.prepareStatement(SELECT_ALL_MANAGERS_SCRIPTED_VIEW).executeQuery();

                while (result.next()) {

                    ManagerEntity manager = new ManagerEntity();

                    manager.setId(result.getInt("ID"));

                    manager.setName(result.getString("NAME"));

                    manager.setRegion(result.getString("REGION"));

                    manager.setBudget(result.getDouble("BUDGET"));

                    manager.setBudgetPercentageShare(result.getDouble("BUDGET_PERCENTAGE_SHARE"));

                    managerList.add(manager);

                }

        } catch (SQLException e) {

            throw new RuntimeException("Error while reading customer data", e);

        } finally {

            dbAccess.closeConnection(connection);

        }

        return managerList;

    }

    ....

}

Conclusion

This article demonstrates how HANA Cloud users could combine on-demand with calculation engine features. It is not intended to cover all scripted view, SQL/CE features and don't cover HANA Modeler. It is very basic example which provides overview of HANA Studio/HANA Cloud integration and usage of database artifacts from standard cloud web application.

Contributor:

Dobrinka Stefanova

Reference

8 Easy Steps to Develop an XS application on the SAP HANA Cloud Platform

Using HANA Modeler in the SAP HANA Cloud