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.
Once the installation is done, set the environment variables using the Source command which points to the sa_config.sh file (as shown below)
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.
Click on the database server to list the database under it.
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.
Connect Sensors to Raspberry PI
I am using a TI sensor which has 10 low-power MEMS sensors including support for light, digital microphone, magnetic sensor, humidity, pressure, accelerometer, gyroscope, magnetometer, object temperature, and ambient temperature. I have configured my Raspberry PI to talk to it over Bluetooth using open source libraries and extract the temperature/light sensor readings.
I have also used Nodejs library for SQL Anywhere DB to allow my nodejs script to communicate with SQL Anywhere. I have incorporated the logic within my nodejs script which reads the sensor values from the TI Sensor tag and updates the SENSOR_TABLE in the SQL Anywhere DB (called mysqlDB01 which resides within the Raspberry PI).
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.
- 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.
Once you get a "Synchronization completed" message in the console, navigate to the HCP Database and view the SENSOR_TABLE to check if the records have been 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)
- SAP Managed Tags:
1 Comment
