# Ingest MQTT Data into EMQX Tables

EMQX Tables is a native, fully managed time-series data storage service built into the EMQX Cloud. It is optimized for high-throughput, low-latency ingestion and analysis of MQTT data, making it ideal for Internet of Things (IoT) use cases.

Powered by [GreptimeDB](https://greptime.com/), EMQX Tables integrates seamlessly with EMQX Broker and supports [InfluxDB Line Protocol](https://docs.influxdata.com/influxdb/v2.5/reference/syntax/line-protocol/), enabling efficient storage, querying, and visualization of telemetry data.

To learn more, see the [EMQX Tables Overview](../emqx_tables/emqx_tables_overview.md).

This guide walks you through ingesting MQTT data into EMQX Tables by:

- Creating a connector
- Creating a rule with a Line Protocol writer
- Testing data ingestion and querying results

## Prerequisites

You have created both of the following deployments:

- [EMQX Dedicated/Dedicated Flex deployment](../create/dedicated.md)
- [EMQX Tables deployment](../emqx_tables/emqx_tables_create_deployment.md)

When both deployments are in the same cloud platform and region and are associated with the same network, private connectivity is available between them. To share a network, select the existing network under **Network Association** when creating the second deployment.

When the Broker and Tables deployments are in different regions or networks, private connectivity is not available. You can still connect them over the public internet, but you must first enable the **NAT Gateway** for your Broker deployment under its **Network Management** settings.

You’re familiar with:

- [EMQX Rules](./rules.md)
- [Data Integration in EMQX](./introduction.md)
- [InfluxDB Line Protocol](https://docs.influxdata.com/influxdb/v2.5/reference/syntax/line-protocol/)

## Create an EMQX Tables Connector

Before you can write data, create a connector to EMQX Tables:

1. Go to your EMQX Broker deployment. Click **Data Integration** from the left menu.

2. If it is the first time for you to create a connector, find **EMQX Tables**. If you have already created connectors, click **+ New Connector** and then select **EMQX Tables**.

3. On the **New Connector** page, the **Connect Name** is automatically generated. Choose from two setup modes:

   :::: tabs type:card

   ::: tab Quick Setup (Recommended)

   Use Quick Setup to automatically populate connection information. The dropdown lists all EMQX Tables deployments in the same project.

   1. Select **Quick Setup** (default).
   2. **Select an EMQX Tables Deployment**: Choose a Tables deployment from the dropdown. If the selected deployment is on a different network or in a different region from the Broker, a notice appears reminding you to enable the **NAT Gateway** on the Broker deployment before the connection can work.
   3. **Database Name**: Use the default `public` database, or specify the name of a custom database. Ensure that the database has already been created in the EMQX Tables deployment. For more information, see [Create a Custom Database](../emqx_tables/emqx_tables_quick_start.md#create-a-custom-database).

   ![connector_quick_setup](./_assets/connector_quick_setup.png)

   :::

   ::: tab Custom Setup

   Use **Custom Setup** if your EMQX Tables deployment is in a different project, or if you need to configure the connection manually.

   > Requires enabling the **NAT Gateway** for public access under the Broker deployment's **Network Management**.

   Fill in the connection details by copying them from the **Connection Info** area on your EMQX Tables deployment's **Deployment Overview** page:

   | Field             | Description                                                  | Example Value                                                |
   | ----------------- | ------------------------------------------------------------ | ------------------------------------------------------------ |
   | **Public Host**   | Host address with port. For public internet connections, use port `5001` (gRPCS) with **Enable TLS** toggled on (default). This is recommended for security. Port `4001` (gRPC) is available if TLS is disabled. | `your-emqx-tables-public-host:5001`                          |
   | **Database Name** | Name of the target database                                  | `public` or your custom database name (Ensure it already exists. See [Create a Custom Database](../emqx_tables/emqx_tables_quick_start.md#create-a-custom-database).) |
   | **Username**      | Username of an EMQX Tables deployment                        | auto-generated                                               |
   | **Password**      | Password of an EMQX Tables deployment                        | auto-generated                                               |

   ![emqx_tables_connector_custom_setup](./_assets/emqx_tables_connector_custom_setup.png)

   :::

   ::::

4. Click **Test** to verify connectivity. If the EMQX Tables service is accessible, a success prompt will be returned.

4. Click **New** to complete the creation. You can now proceed to create a rule using this connector.

## Create a Rule for Data Ingestion into EMQX Tables

Next, you need to create a rule to specify the data to be written and add corresponding actions in the rule to write them to EMQX Tables.

1. In the **Rules** section, click **New Rule**, or use the **Actions** icon next to your connector.

2. Define the SQL rule in the **SQL Editor**. In this demonstration, the goal is to trigger the engine when the client sends a temperature and humidity message to the `temp_hum/emqx` topic. Here you can set the SQL as follows:

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

   ::: tip

   If you are a beginner user, click **Try It Out** to learn and test the SQL rule.

   :::

3. Click **Next** to append an action to the rule.

4. In the **Connector** dropdown, select the connector you just created.

5. Set **Time Precision** to `millisecond` (default).

6. Configure the **Write Syntax** to define the Line Protocol format for data parsing and writing into EMQX Tables.

   Specify a text-based format that provides the measurement, tag set, field set, timestamp of a data point, and supported placeholders. See also [InfluxDB 2.3 Line Protocol](https://docs.influxdata.com/influxdb/v2/reference/syntax/line-protocol/) and [InfluxDB 1.8 Line Protocol](https://docs.influxdata.com/influxdb/v1/write_protocols/line_protocol_tutorial/).

   For example:

   ```
    temp_hum,location=${location} temp=${temp},hum=${hum} ${timestamp}
   ```

   ::: tip

   - To write a signed integer type value, add `i` as the type identifier after the placeholder, for example, `${payload.int}i`. See [InfluxDB 1.8 write integer value](https://docs.influxdata.com/influxdb/v1.8/write_protocols/line_protocol_reference/#write-the-field-value-1-as-an-integer-to-influxdb).
   - To write an unsigned integer type value, add `u` as the type identifier after the placeholder, for example, `${payload.int}u`. See [InfluxDB 1.8 write integer value](https://docs.influxdata.com/influxdb/v1.8/write_protocols/line_protocol_reference/#write-the-field-value-1-as-an-integer-to-influxdb).

   :::

7. Click **Confirm** to save the rule.

8. In the **Successful new rule** pop-up, click **Back to Rules** to complete the rule creation.

![emqx_tables_rule_action](./_assets/emqx_tables_rule_action.png)

## Test the Rule and Query the Data 

You are recommended to use [MQTTX](https://mqttx.app/) or other client tools to simulate temperature and humidity data reporting. For quick demonstration, you can just use built-in diagnostic tool inside your broker deployment by clicking the **Online Test** from the left menu. 

1. In **Online Test**, connect to the deployment using username and password or auto-generated authentication.

2. In the **Messages** section, send a message:

   - **Topic**: `temp_hum/emqx`

   - **Payload**:

     ```json
     {
       "temp": 27.5,
       "hum": 41.8,
       "location": "Prague"
     }
     ```

   ![emqx_tables_online_test](./_assets/emqx_tables_online_test.png)

3. Go to your EMQX Tables deployment. Click **Data Explorer** from the left menu.

4. Run the following SQL to query the data ingested into the table `public`:

   ```sql
   select * from "temp_hum"
   ```

   You should see there is one record in the **Query Result** table.

   ![emqx_tables_query](./_assets/emqx_tables_query.png)

5. View the rule statistics in your EMQX Broker deployment. Click the rule ID in the rule list, and you can see the statistics of the rule and action associated with this rule.

   ![emqx_tables_rule_statistics](./_assets/emqx_tables_rule_statistics.png)