This exercise will demonstrate how to create a project containing tables, a calculation view, and a stored procedure using the guided development tool. Appendix 1 is an optional exercise and walks through the process of importing a completed project.
-
In the SAP Business Application Studio, start the Guided Development tool. To do so, open the command palette View, Command Palette (or Fn F1), and search for SAP HANA: Getting Started.
It may take a moment or two for the contents of the Guided Development wizard to appear. If it does not appear, try reloading the page.
The search bar can be used to search for files or commands. To search for a command preceed the search with >
-
Select Get Started with SAP HANA Cloud.
Select the first sub option Create a New SAP HANA Database Project from a Template.
Press the Create SAP HANA Database Project button to open the project creation wizard and then press Next multiple times to accept the defaults.
When asked to on the third page, provide your Cloud Foundry API endpoint, email address used for the trial account, password, and select login icon.
The API endpoint value can be obtained from the BTP Cockpit subaccount page.
Ensure there is no preceding space in the copied API endpoint.
If 2-factor authentication is enabled, provide your password followed by a 2-factor authentication code.
In the example below, notice that the API endpoint is slightly different (us10-001 instead of us10) and hence the endpoint value in the above wizard would need to be updated to use the correct endpoint value. Also make sure that there is not a space before or after the URL.
It should also be noted that if you are using a non-trial account (trial accounts limit the number of instances to one), you may wish to specify which SAP HANA Cloud instance to deploy the SAP HANA Deployment Infrastructure (HDI) container to.
Select the option to Open Output View when asked.
The output view can also be shown (or hidden) by selecting View, Output.
Above, notice that the HDI container and a service key has been created and that the container has been bound to the SAP HANA Cloud instance.
-
In the project explorer, open the generated project.
-
Examine the created project.
The .env file contains connection or binding details on the HDI container.
The panes in the explorer can be expanded and resized.
Optionally right click on OUTLINE and TIMELINE and choose Hide. Hidden panes can be shown again using the ... menu in the top right of the Explorer.
At this point, an HDI container has been created which can be thought of as an isolated set of schemas within an SAP HANA Cloud database.
HDI provides a service that enables you to deploy database development artifacts to containers. This service includes a family of consistent design-time artifacts for all key SAP HANA database features which describe the target (run-time) state of SAP HANA database artifacts, for example: tables, views, or procedures. These artifacts are modeled, staged (uploaded), built, and deployed into SAP HANA.
Multiple HDI containers can be included within one SAP HANA Cloud database. This enables developers to have their own environment, or for multiple instances of an application, to use one SAP HANA Cloud database each with a separate container.
The HDI container can be seen in the SAP BTP Cockpit.
It is also visible within the add database dialog of the SAP HANA database explorer.
The SAP HANA database explorer can connect to the HDI container without providing credentials as it is able to look up the service key mentioned below to connect and browse the HDI container.
Above, the service key SharedDevKey is created when a service key has been added to the service.
It contains details such as the parameters needed to connect to the HDI container (host, port, certificate) as well as credentials for a DT or design time user and RT or runtime user.
These details could also be used to connect to the HDI container from an application using one of the many SAP HANA client drivers (Java, ODBC, Node.js, Python, Go, .NET etc.) or the command line SQL tool HDBSQL.
An example is shown below of connecting with the RT user. Additional details on client connections can be found at Use Clients to Query an SAP HANA Database. The example shown below requires an installation of the SAP HANA Client (which is not on the TechEd laptops) and is shown for illustrative purposes only.
\al SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY; SELECT SCHEMA_NAME, SCHEMA_OWNER FROM SCHEMAS;
The blog How to recreate a HANA Cloud service key aka password rotation provides some additional details on how these service keys can be recreated or rotated.
-
Initialize the Git repository by selecting the GIT extension, entering in a commit message, and pressing the Commit button.
-
Commit the contents of the project and provide a commit message such as initial commit. Optionally select Always to the question "Would you like to stage all your changes and commit them directly?" to simplify the check in process.
The green U stands for unstaged or files that have not yet been added to the repository.
-
In the guided development tool, select Add Database Artifacts.
-
Complete all the steps to create two tables, deploy them, and to open the SAP HANA database explorer to view the deployed tables.
If asked to open to an external website, select Configure Trusted Domains and trust the URL to the SAP HANA database explorer.
-
Note that the new files are again marked with a green U which means they are unstaged or new files and modified files are marked with an orange M.
Switch to the git extension and commit the changes and continue to do so after subsequent steps.
Note that in this way you will have a record of the changes from each step that can be viewed, or the project can be restored to the state of a previous commit.
For additional details see Working with GIT Within SAP Business Application Studio and Git Source Control.
-
In the guided development tool, select Load Data into your SAP HANA Cloud Application's Database Tables.
-
Complete all the steps to load data into the two previously created tables and to view the deployed data in the SAP HANA database explorer.
Notice also that the metadata of a table can be viewed by selecting Open.
Calculation views allow the developers to express their intent instead of defining with SQL code how the data should be processed. Additional details on calculation views can be found at Developing Data Models with SAP HANA Cloud and Working with Calculation Views in SAP HANA Cloud.
-
In the guided development tool, select Create a Calculation View for your Application.
-
Complete all the steps to create and deploy a calculation view and view its properties in the SAP HANA database explorer. It may take a moment or two for the calculation view editor to load after its creation. Note that the permissions to view its data will be granted in a subsequent step.
Within the SAP HANA database explorer, the deployed calculation view can be seen as well as its metadata.
Note that the data cannot yet be viewed from the SAP HANA database explorer as this calculation view is configured to use analytic privileges which have not yet been created.
From the above drop down for Analytic Privileges, clear the option SQL Analytic Privileges.
Once the calculation view has been re-deployed, its data can be viewed in the SAP HANA database explorer as shown below. The data can be shown in a chart or in columnar form by selecting Analysis or Raw Data.
Notice that the Airline column has been added (via drag and drop) to the label axis and a count of the reservation ids has been added to the value axis.
It is also possible to see a similar preview within the calculation view editor.
-
In the guided development tool, skip the steps Create Analytic Privileges for Your Calculation View and Create a Database Role for the Analytic Privilege. Select the step Create a Database Procedure File and complete the steps.
-
Notice that the changed artifact, the hdbprocedure, is identified as needing to be deployed.
The output view provides details on what was deployed.
-
In the SAP HANA database explorer, call the stored procedure. Provide a value such as 1 for the PASSENGER_ID parameter as shown in the screenshot below.
You now have now created native SAP HANA database artifacts and deployed them into an HDI container. Further details on some of the concepts shown here can be found at SAP HANA Cloud, SAP HANA Database Developer Guide for Cloud Foundry Multitarget Applications. Optionally, view the additional content in the appendix. In the next set of exercises, we will focus on functionality within the SAP HANA database explorer.
Continue to - Exercise 1 - Launch the SAP HANA Database Explorer