Save MQTT Data from EMQX Cloud on GCP to Timescale Cloud through the public network

In this article, we will simulate temperature and humidity data and report it to the EMQX cloud on the GCP platform via the MQTT protocol, after which we will use the EMQX Cloud data integration to enable NAT gateway and save the data to the Timescale Cloud over the public network.

EMQX Cloud

EMQX Cloud is the world's first fully managed MQTT 5.0 public cloud service for IoT from EMQ. It provides a one-stop O&M colocation and a unique isolated environment for MQTT services. In the era of Internet of Everything, EMQX Cloud can help you quickly build industry applications and easily realize the collection, transmission, computation and persistence of IoT data.

With the infrastructure provided by cloud providers, EMQX Cloud is available in dozens of countries and regions around the world, providing low-cost, secure, and reliable cloud services for 5G and Internet of Everything applications.

For more information, please go to the EMQX Cloud website or see the EMQX Cloud documentation.

Timescale Cloud

Timescale Cloud is a hosted, cloud-native TimescaleDB service that allows you to quickly spin up new TimescaleDB instances. Powered by TimescaleDB, Timescale Cloud is an innovative and cost-effective way to store and analyze your time-series data. Get started super fast with demo data, or your own dataset, and enjoy the security of automated upgrades and backups.

Create Deployments

Create EMQX Cluster

Create a GCP deployment of EMQX Cloud, other options default.

When the status is Running, the creation of the deployment is complete.

Create Timescale Cloud instances

If you are creating a Timescale Cloud instance for the first time, you can refer to the help document. A service in Timescale Cloud is a cloud instance which contains your database. Each service contains a single database, named tsdb.

When you have a service up and running, you can connect to it from your local system using the psql command-line utility. If you've used PostgreSQL before, you might already have psql installed. If not, check out the installing psql section.

# install psql
sudo apt-get update
sudo apt-get install postgresql-client

# connect to your service
psql -x "postgres://{YOUR_USERNAME_HERE}:{YOUR_PASSWORD_HERE}@{YOUR_HOSTNAME_HERE}:{YOUR_PORT_HERE}/{YOUR_DB_HERE}"

To create a new table. Use the following SQL statement to create temp_hum table. This table will be used to save the temperature and humidity data reported by devices.

CREATE TABLE temp_hum (
    up_timestamp   TIMESTAMPTZ       NOT NULL,
    client_id      TEXT              NOT NULL,
    temp           DOUBLE PRECISION  NULL,
    hum            DOUBLE PRECISION  NULL
);

SELECT create_hypertable('temp_hum', 'up_timestamp');

Insert test data and view it.

INSERT INTO temp_hum(up_timestamp, client_id, temp, hum) values (to_timestamp(1603963414), 'temp_hum-001', 19.1, 55);

select * from temp_hum;

In the Timescale Cloud database instance, the default max_connections is 25, which needs to be changed to 100 to facilitate EMQX Cloud connections.

Enable NAT Gateway

NAT gateways can provide network address translation services to provide Professional deployments with the ability to access public network resources without the need for VPC peering connections.

Data Integrations

1. Create TimescaleDB resources

   Go to the Data Integrations page. On the data integration page, Click on TimescaleDB under the Data Persistence.

   

   Fill in the timescaledb database information you have just created and click Test. If there is an error, you should check if the database configuration is correct. Then click on New to create TimescaleDB resource.

   

2. Create a new rule

   Choose the TimescaleDB resource under Configured Resources, click on New Rule and enter the following rule to match the SQL statement.

   In the following rule we read the time up_timestamp when the message is reported, the client ID, the message body (Payload) from the temp_hum/emqx topic and the temperature and humidity from the message body respectively.

   SELECT
       payload.location as location, 
       payload.temp as temp, 
       payload.hum as hum
   FROM "temp_hum/emqx"
   

   

   You can click SQL Test under the SQL input box to fill in the data:

   • topic: temp_hum/emqx
   • payload:

   {
     "temp": 24.3,
     "hum":35.4
   }
   

   Click Test to view the obtained data results. If the settings are correct, the test output box should get the complete JSON data as follows:

   

   If the test fails, please check whether the SQL is compliant and whether the topic in the test is consistent with the SQL filled in.

3. Add Action to Rule

   Click on the Next button in the bottom to enter action view. Select the resource created in the first step and enter the following data to insert into the SQL template.

   INSERT INTO temp_hum(up_timestamp, client_id, temp, hum) VALUES (to_timestamp(${up_timestamp}), ${client_id}, ${temp}, ${hum})
   

   

4. View Resource Detail

   Click on the resource to see the detail.

   

5. Check Rule Monitoring

   Click the monitor icon of rule to see the metrics.

   

Verification

1. Use MQTTX to simulate temperature and humidity data reporting

   We recommend you to use MQTTX, an elegant cross-platform MQTT 5.0 desktop client to subscribe/publish messages.

   Click on the add button and fill in the deployment information to connect to the deployment. You need to replace broker.emqx.io with the created deployment connection address, add client authentication information to the EMQX Cloud console. Enter the topic name and payload message to publish the message.

   

2. View rules monitoring

   Check the rule monitoring and add one to the number of success.

   

3. View data dump results

   select * from temp_hum order by up_timestamp desc limit 10;
   

   

So far, we have used EMQX Cloud data integration based on the GCP platform to save the entire process of data to the Timescale Cloud over the public network.