Today, I am building a Node.js application on top of an SAP HANA Data lake IQ instance (HDL). My goal with this application is to accomplish the following three items.
- Make a connection to my HDL instance
- Query the database and receive data
- Upload files to my HDL file store from my own webpage
These three simple goals should give me the power to build any application I can imagine on my HDL instance with Node.
Setting Up the ODBC Configuration
The first step to being able to make a connection to an HDL instance is having the proper ODBC driver. The ODBC driver for HDL can be found in the SAP HANA Data Lake Client. Once the data lake client is installed, the “SAP HANA data lake IQ” ODBC driver will be installed on your system as well. However, the history of the technology behind the data lake and IQ begins with a company known as Sybase and the old naming conventions still exist today. So, the ODBC driver you’ll find on your system will be called “Sybase IQ”.
After Identifying the driver, I created a DSN. On my windows machine it is configured in the ODBC Data Source Administrator and on a Unix machine the DSN can be configured in the .odbc.ini file. The driver only requires the host, port, user id, and password to make a connection to HDL. The host and port information can be found in the SAP BTP cockpit or SAP HANA Cloud Central.
Creating a Node App
Once a DSN had been set up, I could begin creating my Node application. The first task was to initialize my app directory. After creating a directory called “nodeapp” I ran npm init and installed node-gyp, odbc, nodemon, express, and formidable. Here is my final package.json which can be copied and used to install the packages I just mentioned if you’re following along at home.
{
"name": "nodeappblog",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"start": "nodemon index.js"
},
"author": "",
"license": "ISC",
"dependencies": {
"express": "^4.17.1",
"formidable": "^1.2.2",
"node-gyp": "^8.1.0",
"nodemon": "^2.0.12",
"odbc": "^2.3.6"
}
}
With the packages I needed installed, I began hacking away at my HDL application. I created an “index.js” for my main server-side code. In my index.js I imported all the packages I would need to create my application and wrote the basic code for making an ODBC connection with my DSN.
'use strict';
const express = require('express');
const fs = require('fs');
const formidable = require('formidable');
const odbc = require('odbc');
const https = require('https');
// Constants
const PORT = 8012;
const HOST = '0.0.0.0';
// App
const app = express();
app.get('/', (req, res) => {
let conn = connectToDB()
.then(conn => res.send('Database connection was successful!'))
.catch(err => {
res.send('Database connection failed.');
console.log(err);
});
});
app.listen(PORT, HOST);
console.log(`Running on http://${HOST}:${PORT}`);
async function connectToDB(){
const cnxConfig = {
connectionString: 'DSN=HDLDSN',
connectionTimeout: 10,
loginTimeout: 10,
}
return odbc.connect(cnxConfig);
}
The core functionality is handled in the async connectToDB function. The function uses the odbc module to make a connection to my HDL instance. It uses the cnxConfig variable which specifies which DSN to use for my connection and some other parameters like connection and login timeouts.
With the connectToDB function written, I could test out my code, both the positive and negative test cases. To do this I headed to http://localhost:8012/. I first tested the application with the DSN I had set up earlier and got a response indicating I’ve made a successful connection.
Then, I tested the code with an invalid DSN to ensure that any errors would be caught and got the expected response again.
Perfect! Now I wanted to try to get some data from my database.
Querying the SAP HANA Data Lake IQ
The first thing I did was create a new endpoint and sent a query to a table in my data lake which holds some IoT sensor data.
app.get('/sensor-data', (req, res) => {
let conn = connectToDB()
.then(conn => conn.query("SELECT * FROM GENERATED_SENSOR_DATA;")
.then(data => res.send(data))
.catch(err => res.send('Query failed!')))
.catch(err => res.send('Database connection failed.'));
});
This endpoint’s functionality is a simple extension of the last one. This time I used the conn object returned from the connectToDB function and sent a query. Again, I added the catch clause to both the query and connection function for easy debugging. Now it was time again to test my code. Luckily, the fails were graceful and my query returns my data! (Click the image to enlarge)
With the ability to get data from the data lake discovered and functional, it was time to build something I’ve always wanted. An interface for uploading files to my data lake file store.
Building an Interface for the SAP HANA Data Lake IQ File Store
This section assumes some prior setup, specifically the HANA Data Lake File Store Command Line Interface (HDLFSCLI). This requires having some certificates generated and configured to be trusted by the data lake. Here is a great blog outlining how the HDLFSCLI can be setup with self-signed certificates. The reason why the HDLFSCLI setup is important is that I will be using the HDLFS REST API for interacting with the data lake file store which can use the same certificates for making a connection. Anyhow, the next step would be to serve a form to upload files to. In my index.js I added the following endpoint.
app.get('/upload-files', (req, res) => {
res.sendFile('file_upload.html', {root: __dirname + '/html'});
});
With that, I created a folder called html in my app directory and a file inside that folder called file_upload.html. Then I wrote the following contents in file_upload.html.
<!DOCTYPE html>
<html>
<body>
<p>Click on the "Choose File" button to upload a file:</p>
<form method="POST" action="/hdlfs-upload" enctype="multipart/form-data">
<input type="file" id="myFile" name="filename">
<input type="submit">
</form>
</body>
</html>
This endpoint I just created will serve the static HTML page which will act as my user interface for uploading files into my data lake file store. I navigated to http://localhost:8012/upload-files to see my interface.
Its definitely not an artistic masterpiece, but it will get the job done. This is how all great things start, isn’t it? After verifying the user interface, it was time to hack away at the code that will handle uploading my file to the data lake file store. I added the following line to my index.js.
app.post('/hdlfs-upload', uploadFiles);
The uploadFiles function is where all the heavy lifting will be done. Now, this function ended up being quite large, but its relatively straight forward once its broken down. First, I am using the formidable module to parse the form when it is submitted. This gave me convenient access to the file name and absolute path for the path and name variables.
function uploadFiles(req, res){
var form = new formidable.IncomingForm();
form.parse(req, function (err, fields, files) {
let path = files.filename.path;
let name = files.filename.name;
});
}
Next, I began creating the payload for the request I plan to make to the file store REST API. The key parameters are seen in the options variable below. For those trying this at home, the hostname, x-sap-filecontainer, cert, and key values will need to reflect your setup.
function uploadFiles(req, res){
var form = new formidable.IncomingForm();
form.parse(req, function (err, fields, files) {
let path = files.filename.path;
let name = files.filename.name;
let options = {
hostname: '<YOUR_HOST_NAME>',
port: 443,
path: `/webhdfs/v1/${name}?op=CREATE&data=true&overwrite=true`,
method: 'PUT',
headers: {
'Content-Type': 'application/octet-stream',
'x-sap-filecontainer': '<YOUR_FILE_CONTAINER>'
},
connection: 'keep-alive',
cert: fs.readFileSync( __dirname + '/client.crt' ),
key: fs.readFileSync( __dirname + '/client.key' )
}
var fs_req = https.request(options, res => {
console.log('statusCode:', res.statusCode);
console.log('headers:', res.headers);
res.on('data', (d) => {
process.stdout.write(d);
});
});
fs_req.on('error', (e) => {
console.log(e);
});
fs_req.write(fs.readFileSync(path));
fs_req.end();
});
}
Then I encountered an issue with my certificate being self-signed. Setting process.env["NODE_TLS_REJECT_UNAUTHORIZED"] = 0; was a requirement for my specific setup. The reason being, Node.js did not like that I was using a self-signed certificate in the request. This line can be omitted if you’re trying this at home and are using certificates with a verified certificate authority.
In the second half of the function I created the http request to the file store REST API with the options I specify above. The fs_req variable is specific to the file store API request and the line fs_req.write(fs.readFileSync(path)); sends the file stream to the destination for creating the file. The last thing to do is test the code. I navigated to http://localhost:8012/upload-files and proceeded to upload a file.
Upon hitting submit, I could see in the Node.js console that my file has been uploaded.
Another way to verify that the file was successfully uploaded is via the REST API or HDLFSCLI. I wanted to verify the file’s upload status with my Node app, so I added one more endpoint. This endpoint would use the list command of the HDL file store API to list the files I have stored in my file store.
app.get('/list-fs-status', (req, res) => {
let options = {
hostname: '<YOUR_HOST_NAME>',
port: 443,
path: `/webhdfs/v1?op=LISTSTATUS`,
method: 'GET',
headers: {
'Content-Type': 'application/octet-stream',
'x-sap-filecontainer': '<YOUR_FILE_CONTAINER>'
},
connection: 'keep-alive',
cert: fs.readFileSync( __dirname + '/client.crt' ),
key: fs.readFileSync( __dirname + '/client.key' )
}
var fs_req = https.request(options, fs_res => {
console.log('statusCode:', res.statusCode);
console.log('headers:', res.headers);
fs_res.on('data', (d) => {
process.stdout.write(d);
res.write(d);
res.end();
});
});
fs_req.on('error', (e) => {
console.log(e);
});
fs_req.end();
});
And then, I navigated to the endpoint URL to verify that my file has been uploaded. (Click image to enlarge)
Voila, there it is! With a successful file upload, I can conclude this blog.
Summary
We looked at how a Node.js app can be built on top of an SAP HANA Data Lake IQ instance. We made a direct connection to the data lake IQ data base, queried the data base and received the results, uploaded a file to the data lake file store, and lastly used the list command to view the files in the data lake file store.
I’ve demonstrated how the capabilities of an SAP HANA data lake can be leveraged within a Node.js application and now it’s your turn to try it out! Although I did not build an application for a specific business problem, the above prototype is an excellent starting point for those interested.
Are you interested in building an application on top of a HANA data lake instance? Or are you interested in learning more about a specific SAP HANA data lake topic? Comment down below what you would like to see or learn more about.
If you’ve found this blog post useful feel free to give it a like! Questions are welcome in the comment section below or on the community questions page. For more information on SAP HANA Cloud, data lake there are detailed step-by-step tutorials here.
- SAP Managed Tags:
