WHITE PAPER
The Most Trusted MQTT Platform for loV and Connected Cars →

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

EMQX Cloud Team
Oct 25, 2022
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.

MQTT Cloud

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.

Create a GCP deployment

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

Running status

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.

Create Timescale Cloud instances

Create Timescale Cloud instances

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}"

connect to your service

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');

create_hypertable

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;

Insert test data and view it

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

Timescale Cloud

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.

Enable NAT Gateway

Data Integrations

  1. Create TimescaleDB resources

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

    Create TimescaleDB resources

    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.

    New 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"
    

    Create a new rule

    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:

    JSON data

    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})
    

    Add Action to Rule

  4. View Resource Detail

    Click on the resource to see the detail.

    View Resource Detail

  5. Check Rule Monitoring

    Click the monitor icon of rule to see the metrics.

    Check Rule Monitoring

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.

    MQTT Client

  2. View rules monitoring

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

    View rules monitoring

  3. View data dump results

    select * from temp_hum order by up_timestamp desc limit 10;
    

    View data dump results

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.

Try EMQX Cloud for Free
No credit card required
Get Started →

Related Posts