IoT scenario using the HCP Remote Data Sync service – II

In the previous blog, I described the steps involved to enable the RDSync service and setup the consolidated database on HANA Cloud Platform. In this part of the blog, the focus will be on setting up the Raspberry PI and enable it to synchronize with the consolidated HANA database on HANA Cloud Platform.

Install SQL Anywhere 17

Click on this link to register yourself an SQL Anywhere database. You will get an email with details as to where to download the software from. For SQL Anywhere 17 you don’t need a developer key.

Switch to a tmp folder and download the sqla17developerlinuxarm.tar.gz file as shown below

Extract the contents of the tar.gz file. Change to sqlany17 folder and execute the setup file as shown below

Follow the prompts to accordingly install SQL Anywhere 17 database.

I selected all the options to have a full blown database.

Switch to your folder where you would like to keep all the database related files and issue the below command to create a database providing a dba user and password.

dbinit myslqaDB.db –dba murali, sql123

Now you have a fresh SQL Anywhere database on your Raspberry PI. You can use dbisqlc command line tool to issue SQL commands to this database. It’s very old fashioned and is not rich in UI. Hence, I installed SQL Central on my laptop and connected it to the SQL Anywhere DB on my Raspberry PI using the Connection Profiles menu.

Once connected, you will be able to see the database server.

You can further drill down to explore various objects under it. I have highlighted the 4 important objects which we are going to use for this process.

You can follow the instruction mentioned in SAP Help Developing Client-Initiated Synchronization to create these objects in SQL Central. However, I am going to open the Interactive SQL and create these objects via SQL Command.

Access the interactive SQL from the context menu of the database

CREATE SYNCHRONIZATION USER "MOBI_USER";
CREATE SEQUENCE mys1 MINVALUE 10   MAXVALUE 100000;
CREATE TABLE SENSOR_TABLE (ID INT PRIMARY KEY DEFAULT (mys1.nextval), TEMP DOUBLE, LIGHT DOUBLE);
CREATE PUBLICATION "murali"."mypublication" (TABLE "murali"."SENSOR_TABLE");
CREATE SYNCHRONIZATION SUBSCRIPTION "mySyncSub" TO "murali"."mypublication" FOR "MOBI_USER" SCRIPT VERSION 'V1';
CREATE SYNCHRONIZATION PROFILE mySyncProfile 's=mySyncSub;e={ctp=https;adr=''host=mobilinkserverc59c97a90.ap1.hana.ondemand.com;port=443;tls_type=rsa''}'
COMMIT;

Explanation for the above:

01 – Creates a mobilink user which will be used for the synchronization process

02 – Sequence is created which will be used to populate the primary key of the sensor table

03 – Sensor Table is created with three fields. It will hold sensor data from temperature and light sensor

04 – Publication object is created referencing a sensor table created above

05 – Synchronization subscription is created reference the above publication & mobilink user

06 – Synchronization profile is created which references the above Synchronization subscription and it also contains details on where the mobilink server is located

I can refresh the database and explore the sensor table which has been created under “Tables”.

Below is the structure of the table.

Below is the sensor device hooked to my Raspberry PI.

I can now query my SENSOR_TABLE and check the updated values.

Test the Synchronization

If you have your SQL Anywhere (remote DB) setup on your laptop, you would be able to run the Synchronization profile straight from SQL Central. There is no additional setup required. However, if you are trying to run the Synchronization profile from a DB located in your Raspberry PI, there are few considerations.

1. You will need to generate a certificate to allow HTTPS communication between Raspberry PI and HCP. Open the HCP account in your browser and download the certificate. This needs to be placed in a folder within your raspberry PI. I have placed my certificate under the folder MySql as shown below

     2.  Manually issue the dbmlsync command. This need to refer to the certificate created above.

This will initiate the synchronization processes between the remote DB (in Raspberry PI) and Consolidated DB (in HCP). The console log will show the number of records synchronized.

We have seen how Remote Data Sync service in HCP can be used to sync data from a device which is on the Edge to a consolidated database which is on the cloud. Hope you found this useful. I have also added another series of blog which focus on Smart Data Streaming/IoT/Predictive services in HCP

Capture event streams from IoT devices and perform predictive analytics using HCP – Part 1

Many thanks to

Graeme Perrow (Github contribution)

Tom Slee (for the support to enable Raspberry PI to Sync)