SAP HANA Cloud Platform: Download and Store Data

In the previous article, we talked about how to create a test instance of a cloud platform (hereinafter referred to as HCP - HANA Cloud Platform ) and how to connect the Eclipse development environment with the cloud using the simplest XS application (XS - eXtended Services) as an example.

Now we would like to go further and show how you can load data into the database from an external service. In this case, some subtleties will be shown when working with the trial cloud related to the requirements of isolating users within a single trial system.

So what we have. The first is access to our cloud control panel at https://account.hanatrial.ondemand.com/cockpit

The second is a created instance of the platform on SAP HANA called helloworldtrial .

')
The third is an installed Eclipse development environment with a plug-in for the SAP HANA Cloud and with a created connection to the helloworldtrial:


What we want to do is create a table in the SAP HANA database and load data from an external source there. For our purposes, we will store information about the current air temperature in several cities. For example, we will take data from openweathermap.org.

Let's start by creating a new project. Launch our Eclipse, open the SAP HANA Development perspective ( Window> Open Perspective> Other ... ):


Create a new project XS application, specifying its name (File> New> Project ...> SAP HANA Cloud):


Next, we need to perform the standard steps for choosing a workspace and a package. Attention! For our example, it is important to choose a package that matches our instance of the cloud system, of the form pXXXXXXXXtrial.helloworldtrial. Where pXXXXXXXXtrial is the username in the cloud system:


And we indicate our main script file, which will do all the work - get_weather.xsjs:


Package Access Control. .Xsaccess file
Using the .xsaccess file, we specify the properties of our XS application (for example, the required authorization to execute the script, etc.). Replace the contents of this file with:

{ "exposed" : true, "default_file" : "get_weather.xsjs" } 

The exposed property indicates that this XS application can be executed on the server from a URL. The default_file property specifies the main executable file of the application; This will allow us to launch it directly from the control panel (cockpit), without appending the file name.

Now we activate our application to check that at this stage all the steps have been executed correctly. To do this, right-click on our project in the Project Explorer tab and select Team> Activate in the context menu.

Restart the control panel in the web browser by clicking update (F5); Otherwise, our XS application does not appear in the list:


Click on the URL of our XS application:


A new window should open with the contents of “Wrong content type request use application / json”. Having made sure that our stocking is started, we can move on.

Creating a table in the database
Create a table in the database, which will contain columns: date and time, name of the settlement and the current air temperature.
There are several ways to create a table in the system - using an interactive designer, SQL script or. Hdbtable file.

The fastest way is to run a script by substituting the name of our schema. Go to the Systems tab and run the SQL console for our cloud:


Run such a script, substituting the name of our NEO_ scheme:

 create column table "NEO_<<<>>>"."T_WEATHER"( "ID" INTEGER GENERATED ALWAYS AS IDENTITY (MINVALUE 0 START WITH 0 INCREMENT BY 1), "FORECAST_TMSTMP" TIMESTAMP NOT NULL , "LOCATION" NVARCHAR(50) NOT NULL, "TEMPERATURE" REAL NOT NULL, "TIMESTMP" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY ("ID")); 

Note that we are creating a table with column storage. And also implicitly create a so-called. sequence, which generates a sequence of integers and, when creating records in a table, stores them in the ID attribute.

Updating the NEO_ catalog, we will see our table:


DB user role. .Hdbrole file
In the free trial system, the user has a very limited set of rights. This concerns, in particular, authorization to change objects in the directory and management of user accounts. Therefore, the trial system has a set of procedures in the “HCP” scheme that allow the user to manage the rights to the objects created by him.

To write and read data from the T_WEATHER table, we need a new role. Add a file of type .hdbrole to our project (in the main menu File> New> Other ... ):


call it weather_access:


Replace the contents of this file with the following fragment, and then activate the file:

 role p<<<>>>trial.helloworldtrial.TryWeather::weather_access { catalog sql object "NEO_<<<>>>>"."T_WEATHER": SELECT, UPDATE, INSERT; } 

Replace p <<< >>> trial with the name of your user. Instead of the NEO _ <<< >>>> scheme, you need to insert the name of your scheme, which we saved when creating the table. And now the magic begins, connected with the fact that our cloud in trial mode creates many users on one system. And for the separation of access rights for these users are very limited privileges.

In order for our system user to get the weather_access role, we must perform a system procedure.
Run the SQL console and execute the following script in it:

 call "HCP"."HCP_GRANT_ROLE_TO_USER"('p<<<>>>trial .helloworldtrial.TryWeather::weather_access', 'p<<<>>>'); 

The main script. .Xsjs file
So, we have a blank XS script and a table in the database for which we added permission to write and read data. In this table we will write data about the current weather.

We will retrieve the weather data and write it in a table.

This code uses the so-called. destination to call external API.
And access to the database from Javascript is carried out through the connection object in the $ .db namespace:

 var conn = $.db.getConnection(); var stmt = conn.prepareStatement("INSERT INTO <<table>> (FORECAST_TMSTMP,LOCATION,TEMPERATURE) VALUES (?,?,?)"); 

Access to external URL. .Xshttpdest file
Add a new object of type .xshttpdest with the name o_weather to our project:


And replace the contents of this file with:

 host = "api.openweathermap.org"; port = 80; description = "current weather"; useSSL = false; pathPrefix = "/data/2.5/weather"; authType = none; useProxy = true; proxyHost = "proxy-trial"; proxyPort = 8080; timeout = 5000; // in milliseconds 

Save and activate this object.

Now in the HCP control panel on the tab with XS applications, we will see a new element in the Destinations panel:


Clicking on the URL of our application, run the script and get approximately the result on the screen:


Now in Eclipse, check that the data has been saved in the table. To do this, right-click on the T_WEATHER table and select Open Data Preview:


And make sure that a new record in the database has been created:


Total
So, in this article, we created an XS application that can receive data from an external source (in our case, a web service) and store it in a table in the HANA database. In addition, we created two special objects: the definition of roles (weather_access.hdbrole) and the definition of a channel for accessing an external web service (o_weather.xshttpdest). They also called stored procedures in order to give permissions to access the database table in the trial system.