Seamlessly Integrating EMQX Cloud With the New Timescale Service

Fan Wang -

The need for efficient and scalable database solutions has become paramount in a world where data is growing unprecedentedly.

Timescale, a well-known player in the database industry, has recently made a significant announcement, transforming itself into a cloud company with a robust database product.

This blog will explore how EMQX Cloud, a leading MQTT cloud service, integrates seamlessly with the new Timescale service, providing a powerful and comprehensive solution for managing time-series data.

Timescale Cloud : A Shift in Focus

Timescale has been recognized for its database expertise, particularly with its open-source PostgreSQL extension, TimescaleDB.

However, with their recent transformation, Timescale has emerged as a cloud company, placing their cloud product, formerly known as Timescale Cloud, at the forefront of their offering. By embracing the cloud-first approach, Timescale aims to empower businesses with a scalable, reliable, and flexible infrastructure for managing their data.

EMQX Cloud: Seamless Integration for Timescale

EMQX Cloud, on the other hand, is a reputable MQTT cloud service that specializes in handling large-scale, real-time data streams. EMQX Cloud provides a one-stop operations and maintenance (O&M) colocation and a unique isolated environment for MQTT services.

In the Internet of Everything era, EMQX Cloud can help you quickly build industry applications and easily realize the collection, transmission, computation, and persistence of IoT data.

The integration between EMQX Cloud and the new Timescale service brings together the strengths of both platforms, providing a comprehensive solution for managing time-series data. With EMQX Cloud's robust MQTT connectivity and Timescale’s scalable and high-performance database capabilities, businesses can unlock the full potential of their time-series data.

5 Steps to Integrate EMQX Cloud With Timescale

Now, let’s see how to get data from EMQX Cloud to the new Timescale service.

Step 1: Create a Timescale service

Login to the Timescale and click Create a service page, select a Region, and then click the Create service button:

Now, a new Timescale service has been created.

Initiate your Timescale deployment

You can download the cheatsheet to initiate your Timescale deployment.

Step 2: Create an EMQX deployment

Creating a dedicated MQTT broker on EMQX Cloud is as easy as a few clicks.

Get an EMQX Cloud account

Go to EMQX Cloud and click Start Free to register an account if you are new to EMQX Cloud.

Create an EMQX Cloud dedicated professional deployment

Once logged in, click Cloud Console under the account menu, and you will see the green button to create a new deployment.

Select Dedicated edition：

Select the Profession, choose the US East (N.Virginia) region, and click the Create Now button.

In just a few minutes, you will get a fully managed MQTT broker:

Step 3: Set up NAT gateway for your EMQX Cloud deployment

Before we can start setting up the Timescale integration, we need to enable the NAT gateway. By default, the MQTT broker is deployed in a private VPC, which can not send data to third-party systems over the public network.

There are two ways to resolve this. One is to enable the NAT gateway and allow the broker to send data to Timescale through that gateway. The other solution is to set up VPC peering, which depends on whether the target cloud service supports VPC peering. In this tutorial, we will use the first solution.

Go to the VAS page and scroll down to the bottom of the page, where you will see the NAT Gateway widget.

Click the Subscribe Now button and follow the instructions to create a NAT Gateway.

When the status of the NAT gateway is running, the deployment can access public network resources.

Step 4: Set up EMQX Cloud data integration with Timescale

EMQX Cloud offers more than 30 native data integrations with popular data systems. Timescale is one of them.

Create Timescale resource

Click Data Integrations on the left menu and View All Resources. You will find the TimescaleDB in the list.

Click the TimescaleDB card to create a new resource; you will go to the resource configuration page:

  1. Server: this is the address of your Timescale service. Remember, don’t forget the port.
  2. Database: The database name we created in the previous steps, and the default is tsdb.
  3. User: the username for connecting to your Timescale service, and the default is tsdbadmin.
  4. Password: the password for the connection, which you can find in the cheatsheet file.
  5. Click the Test button to ensure the database can be connected.

Create a new rule

EMQX Cloud provides a powerful rule engine that can transform and enrich the raw MQTT message before sending it to third-party systems. You can learn more info about the usage of the rule engine in our documentation.

You will see a popup during the resource creation. Clicking on New will lead you to the rule creation page.

The SQL defines how to select data from specific MQTT topics and payload:

SELECT
timestamp div 1000 AS up_timestamp, clientid AS client_id, payload.temp AS temp, payload.hum AS hum
FROM
"temp_hum/emqx"
You can also test your SQL to see if anything is incorrect.
Then we create an Action with the following script:
INSERT INTO temp_hum(up_timestamp, client_id, temp, hum) VALUES (to_timestamp(${up_timestamp}), ${client_id}, ${temp}, ${hum})

This defines how we insert data into the Timescale database; now click the NEXT button to finish creating the rule.

View rules details

Now, you can see a rule of Timescale data integration that defines how to insert data from the MQTT topic temp_hum/emqx into your Timescale database.

Step 5: Verify your integration

Simulate an MQTT client to connect to EMQX Cloud via MQTTX

You can use any MQTT client or SDK to publish the message. In this tutorial, we will use MQTTX, a powerful MQTT client application provided by EMQ.

Click New Connection on MQTTX and fill out the connection form:

  1. Name: connection name. Use whatever name you want.
  2. Host: the MQTT broker connection address. You can get it from the EMQX Cloud overview page.
  3. Port: MQTT broker connection port. You can get it from the EMQX Cloud overview page.
  4. Username/Password: the username/password we defined in EMQX Cloud Authentication settings.

Publish MQTT messages to EMQX Cloud

  1. Set payload format to JSON.
  2. Set to topic: temp_hum/emqx (the topic we just set in the rule)
  3. JSON body:
{
     "temp": "45.5",
     "hum": "27.5"
}


Click the Send button on the right. You can change the temperature value and send more data to the EMQX Cloud.

Check rule status in EMQX Cloud

The data sent to EMQX Cloud should be processed by the rule engine and inserted into Timescale automatically. We can check it from the EMQX Cloud Data Integration dashboard:

Check the data persisted in Timescale

Now, it’s time to take a look at the data on the Timescale. Ideally, the data you send from MQTTX will go to the EMQX Cloud and persist to the Timescale database with data integration.

You can connect to the SQL console on the Timescale console or use any client tool to fetch data from your timescale database.

Run SQL to query the specific table we defined in the EMQX Cloud data integration rule, and you can see the data has been inserted correctly.

Key Benefits of Integrating EMQX Cloud With Timescale

  1. Scalability and performance: Timescale's architecture enables horizontal scalability, allowing businesses to handle ever-growing data volumes effortlessly. EMQX Cloud complements this by efficiently processing and managing real-time data streams, ensuring optimal performance.
  2. Advanced time-series capabilities: By leveraging this powerful database technology, businesses can perform complex analytics and queries on their time-series data. EMQX Cloud seamlessly integrates with TimescaleDB, providing a unified solution for storing and analyzing MQTT data.
  3. Simplified data management: Integrating EMQX Cloud and Timescale simplifies data management processes. With EMQX Cloud securely collecting and processing MQTT data and Timescale storing and organizing this data, businesses can focus on extracting valuable insights rather than worrying about infrastructure management.
  4. Enhanced reliability and security: EMQX Cloud and Timescale prioritize reliability and security. EMQX Cloud ensures reliable message delivery, even in challenging network conditions, while Timescale provides robust security measures to protect sensitive time-series data. Together, they offer a highly reliable and secure environment for critical IoT applications.

Conclusion

The integration of EMQX Cloud with the new Timescale service brings together the power of real-time data streams and scalable time-series database technology.

Businesses can efficiently collect, store, and analyze their time-series data by seamlessly connecting these two platforms, unlocking valuable insights, and accelerating digital transformation initiatives.

The collaboration between EMQX Cloud and Timescale represents a significant step towards building a comprehensive ecosystem for managing and leveraging time-series data in the cloud.

You can test this powerful duo by signing up for a free EMQX Cloud and Timescale trial—start unlocking valuable insights today.