Tutorial: Using the Sense Tecnic InfluxDB platform with FRED (Cloud Node-RED)

with No Comments

InfluxDB is an open-source timeseries database that is optimized for fast, high-availability storage. It is widely used in fields such as operations monitoring, application metrics, IoT sensor data storage and real-time analytics. The new Sense Tecnic InfluxDB service (STS-InfluxDB) provides FRED users an easy way to setup and to manage InfluxDB data on FRED. This tutorial shows you the basic steps to setup and use the STS-InfluxDB service with Node-RED on FRED.

About STS-InfluxDB

InfluxDB is optimized for timeseries data structures composed of measurements, series, and points. STS-InfluxDB is a cloud-hosted InfluxDB service that not only provides a reliable InfluxDB database but also enhances the user experience with a simple, clean and efficient user interface for managing database users and privileges, as well as a quick and easy debugging query tool.

 

About Sense Tecnic

Sense Tecnic Systems Inc have been building IoT applications and services since 2010. We provide FRED, cloud hosted Node-RED as a service to the community. We also offer a commercial version to our customers, as well as professional services. Learn more.

 

Create an STS service account

If you don’t already have an account on FRED, set one up now. You can visit https://fred.sensetecnic.com or https://influxdb.sensetecnic.com to create your STS account and check out the subscription options. You will need to be a paid subscriber to access the InfluxDB service.

After registering, make sure to activate your account by email. You will not be able to log in until you validate your account.

Getting Started

So, here are the steps in the tutorial:

  1. Login to STS-InfluxDB platform
  2. A basic tour of STS-InfluxDB platform
  3. Create database
  4. Create database users
  5. Insert data from FRED
  6. Use the Query tool
  7. Query data from FRED
  8. Advanced tips on using STS-InfluxDB
  9. Troubleshooting

Login to STS-InfluxDB platform

To log in to STS-InfluxDB, make sure you have already registered your STS service account. Since the accounts are unified on STS service platform, you can use your FRED account to log in on STS-InfluxDB platform. Go to https://influxdb.sensetecnic.com/, if you already have signed in before, you will be automatically logged in, otherwise, you will be prompted to log in with your STS service account.

A basic tour of STS-InfluxDB platform

Once you’ve signed in to the STS-InfluxDB platform, as you can see from the left side navigation bar, there are three main function tabs: Databases, Users and Privileges, and Query Tool.

Screen Shot 2017-05-19 at 3.17.41 PM

  • Databases

The Databases tab shows all the databases you’ve created on the service.  You can add new databases, edit existing databases or delete databases here.

  • Users and Privileges

The Users and Privileges tab shows all the database users and their privileges here. You can create new database users, edit passwords, edit privileges and delete database users here.

  • Query Tool

The Query Tool allows users to quickly query a database on STS-InfluxDB. It is very handy for debugging.


Create database

Let’s begin with creating a database. We will need to create a database so we can start storing data.

We go to the Databases tab, and click Screen Shot 2017-02-28 at 3.55.55 PM

This will bring up a dialog for you to create your database as shown.

Screen Shot 2017-05-19 at 4.13.29 PM

All the database names will be in the format of “{YOUR USERNAME}_{DATABASENAME}”. After you click the “Confirm and Create” button, you should see the newly created database shown on the list.

Screen Shot 2017-05-19 at 4.13.49 PM


Create database users

Now we can create a database user for this database. Let’s go to the Users and Privileges tab, and click Screen Shot 2017-02-28 at 3.55.55 PM

This will bring up a dialog for you to create your database users as shown.

Screen Shot 2017-05-19 at 4.42.57 PM

First, you need to enter a unique database username for you to manage the database users on STS-InfluxDB platform.

You also need to add new privileges to this database user. You may choose either “Read only”, “Write only” or “Read & Write” for an existing database.

Once you are ready, you can click Submit button to add the database user.

Note: You will need to have a valid database user in order to connect to the database.

Note: You should save the password somewhere secure as you will not be able to view the password later on. If by any chance you lose your password, you will need to reset the password for the database user.

Once you are done, you should see the new database user show up in your Users and Privileges tab.

Screen Shot 2017-05-19 at 4.49.59 PM

 


Insert data from FRED

Now we can start inserting data from FRED into the STS-InfluxDB platform. We will first insert a single value to show how to connect your FRED instance with your STS-InfluxDB database. We will need to drag an inject node, a function node, and an influxdb out node to the editor. We will connect them and config the influxdb out node as below.

Screen Shot 2017-05-20 at 11.02.38 AM

In the influxdb out node, we will create a server connection that connects to the InfluxDB database which you just created.

Screen Shot 2017-05-20 at 11.07.29 AM

NOTE: The host address in this case would be “idb0.sensetecnic.com“. In this case, you will put in the database user credential in the “username” and “password” fields.

NOTE: You will need a database user with WRITE permission.

NOTE: On our STS service platform, we use the secure connection to protect our user’s data. To connect to our STS-InfluxDB service, you will need to check “Enable secure (SSL/TLS) connection“. You can then create an empty TLS configuration by unchecking the “Verify server certificate” option.

Screen Shot 2017-05-20 at 11.14.07 AM

You will need to specify the measurement that you are writing data to. You can specify that in the Measurement field or in the incoming msg.measurement property. In this case, we are writing data to the “test” measurement.

Screen Shot 2017-05-20 at 11.16.30 AM

Now in the function node, we will inject a random number in msg.payload with these two lines:


1
2
msg.payload = Math.random()*10;
return msg;

Once you deploy your flow, you should be able to insert a random value into your new database.


Use the Query tool

Now we can verify the data on STS-InfluxDB using the Query Tool. Let’s head to the Query Tool tab on STS-InfluxDB.

Screen Shot 2017-05-20 at 11.22.04 AM

First, we will need to select the right database. In this case, we will be using the “testrobot1_testdb” database as shown in the drop-down menu. Then, we can enter the query to execute.

Note: since STS-InfluxDB is a cloud-based InfluxDB management tool, and the query tool is merely a debug tool. Only queries requiring only “READ” privileges can be executed in the query tool.

Let’s check if the measurement was created properly. Let’s send the query “SHOW MEASUREMENTS” and click the “Execute” button.

Screen Shot 2017-05-20 at 11.47.54 AM

So we see the return result contains the measurement name we set in the previous section. We can also query the injected values by:

Screen Shot 2017-05-20 at 3.09.03 PM

For more query options, please refer to our STS-InfluxDB document and the official InfluxDB document.


Query data from FRED

Now we are ready to query data from STS-InfluxDB on FRED. We will need an inject node, function node, influxdb out node and a debug node.

Screen Shot 2017-05-20 at 3.14.50 PM

Let’s config the influxdb out node first. In this tutorial, we will use the previous server config from the Influxdb in node. You can use any server config as long the database user exists on STS-InfluxDB with the READ permission. You can set the query command in the query field, or send the query in msg.query. In this sample, we will set the query in the function node as:

msg.query="select * from test;";
return msg;

Now, if you deploy the flow and everything works fine, then you should see an array of record objects in the debug tab. Simple huh?


Advanced tips on using STS-InfluxDB

The above example is the most basic use case for the STS-InfluxDB. In reality, you can use it for much more complex tasks. If you check out the README page of the node-red-contrib-influxdb which we wrote and maintain, you will see some more complex examples of the node where you can send multiple data points with tags.

You can also make use of the query command to query more complex conditions. You can refer to the official InfluxDB query docs and our STS-InfluxDB docs on query language.

Note: that the STS-InfluxDB platform only allows “Read” and/or “Write” privileges to its database users. Any query command that requires “Admin” privileges sent in via shell, the web query tool and the influxdb out node are not permitted. Our STS-InfluxDB docs explain which query commands are allowed.


Troubleshooting

  • I cannot log into the STS-InfluxDB platform.

Your STS service accounts are unified which means that your account can be used on our FRED system and STS-InfluxDB.

If you have forgotten your password, simply reset your password at the login page.

If you have not yet activated your account via our confirmation email, you can request to resend the activation email at the login page.

  • Can I reuse database user

Yes, you can reuse the database user to access a database in different connections.

  • I forgot my database user password

Database user credentials are required when you connect to a STS-InfluxDB database. The database user password is only visible when the database user was created. If you lost your database user password, you will need to reset the database user password.

To do so, simply go to the User and Privileges tab, click the edit button of the database user. In the pop-up dialog, enter your own database user password or click regenerate password. Ensure you copy the new database user password and then click the Update button.

  • What do I put in for the password field in the InfluxDB node on Node-RED/FRED?

You will enter your database user password in the password field of the InfluxDB node.

  • I sent in a query but I got a forbidden response

Ensure you query only requires “READ” and/or “WRITE” privileges. Any query requires “Admin” privileges that are sent via shell, the InfluxDB node and the when query tool will receive the “FORBIDDEN” error.

Also, ensure your database user has correct permission to “READ” or “WRITE”.

  • I sent in a query but I got an “ERROR PARSING” error.

Make sure you are using SSL connection by checking the “Use SSL/TLS connection”. In the configuration of SSL connection, you should uncheck “Verify server certificate”.

Conclusion

In this tutorial, we outlined the basic setup of using STS-InfluxDB with FRED to insert and query time-series structured data. We also pointed to how to write and query more complex queries to meet production use cases. Since STS-InfluxDB is a cloud host database tool, only the queries that require “READ” and/or “WRITE” are allowed.

If you have questions about this tutorial, or you have questions that are not listed in the troubleshooting section, please feel free to contact us at info@sensetecnic.com

About Sense Tecnic

Sense Tecnic Systems Inc have been building IoT applications and services since 2010. We provide FRED, cloud hosted Node-RED as a service to the community. We also offer a commercial version to our customers, as well as professional services. Learn more.