---
title: Create a Custom Table in a MySQL Database
slug: litmusedge-v1/how-to-guides/integration-guides/create-a-custom-table
description: Learn how to create a custom table in a MySQL database. 
docTags: 
createdAt: 2024-03-04T20:54:21.591Z
---

You can use the *Show Mapping* option in SQL-connector integrations to create a custom table. 

# User Scenario

In your manufacturing plant there is one PLC that tracks the temperature. You want to store the temperature value in a database with a timestamp, but don't need the additional data provided in the payload of DeviceHub devices. You also want to convert the values that are calculated in Celsius to Fahrenheit as integer values. 

For this guide, you will do the following:

- Simulate temperature values coming from a PLC
- Convert the values to Fahrenheit
- Deploy a MySQL container in Litmus Edge
- Create a custom table in the MySQL database
- Configure a connection to the database through the MySQL connector
- Send the data through the connector into the custom table

## Step 1: Add a Device

Follow the steps to [Connect a Device](docId:3eyAfPpweuVmBLCEy17sq) and configure the following parameters. 

- **Device Type**: Simulator
- **Driver Name**: Generator
- **Enable Alias Topics**: Select the checkbox. 

## Step 2: Add Tag

After connecting the device, [Add a Tag](docId:8sE7z3PmRfwL1nmzcwaLX) to the device.  

### Tag 1

- **Name**: Select **S - Random value generator**.
- **Value Type**: Select **int64**. 
- **Polling Interval**: Enter **5**.
- **Tag Name**: Enter **Tag1**. 
- **Min\_value**: Enter **100**.
- **Max\_value**: Enter **129**.

## Step 3: Create Analytics Flow

You can now create the analytics flow that will convert the temperature values into Celsius. 

**To create the analytics flow:**

1. Navigate to **Analytics **> **Instances**. 
2. Click **Add Flow**. 
   The *Create Flow* dialog displays. 
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/ygXFT_hIplmYcbdrL1MEU_flow-icon.png)
3. For* Processor Input*, select **DataHub Subscribe**. 
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/1ZZHYykbyQ_JtG7AkJyjy_datahubsubscribe.png)
4. Click the **Search **icon and select the device and tag you previously created. 
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/cFOc0_s7WsvPgF7PIYEV-_topicsearch.png)
5. Click **Next**. 
6. For *Processor Function*, select **Conversion**. Then, select the **Celsius > Fahrenheit** conversion. 
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/aJUEEQSq1QHN2pO_WyCry_conversion2.png)
7. Click **Next**. 
8. For *Processor Output*, select **DataHub Publish**, Then, copy the topic name and store it somewhere securely for reference later. 
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/Ia9U9gayCgmhQl7FNgBH8_datahubpublish.png)
9. Click **Create Flow**. 
10. Click **Save **to save the analytics flow on the canvas. 

## Step 4: Create Flow

Since the only data needed from the device is the converted temperature value and the timestamp, you will create the flow that will parse the data and only provide the required data in the payload. 

**To create the flow:**

1. Navigate to **Flows Manager **and create a new flow. See [Create a Flow](docId:7x0n7C4DxswTD61vpIBza) to learn more. 
2. Connect the following nodes together:
   DataHub Subscribe
   json
   change
   json
   DataHub Publish
   debug (connected to second json node)
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/6ALwpDjFvMQK0YkpDmvBq_flowfull.png)
3. Configure the nodes. 

### DataHub Subscribe

The* DataHub Subscribe* node will be configured to subscribe to the topic that publishes the converted temperature values from the analytics flow. 

**To configure the DataHub Subscribe node:**

1. Double-click the **DataHub Subscribe** node. 
2. In the *Topic *field paste the topic name copied from the DataHub Publish processor in the analytics flow. 
3. If needed, configure the Datahub Subscribe connection. See the *Step 3: Configure Connector Nodes* section in [Create a Flow](docId:7x0n7C4DxswTD61vpIBza) to learn more. 
4. Optionally, enter a name for the node. 
5. Click **Done**. 

### JSON Nodes

The two json nodes will do necessary parsing of data between JSON string and object. No configurations are needed for these nodes. 

### Change

The *change *node will delete the unnecessary data in the payload from the subscribed topic. 

**To configure the change node: **

1. Double-click the **change **node. 
2. Update **Set **to **Delete**. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/VFB1sZ9iyo_AUakxMiFQY_changedelete.png" size="70" width="528" height="334" position="center" showCaption="false"}
3. In the msg. field, enter payload.metadata. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/SuXYg91fPSPq1Fo1W4qWf_firstchange.png" size="70" width="517" height="299" position="center" showCaption="false"}
4. Click **+add**. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/qK27Mi71wur2D2t1w1KF__addchange.png" size="70" width="526" height="875" position="center" showCaption="false"}
5. Continue adding the following deletions. 
   - payload.registerId
   - payload.tagName
   - payload.success
   - payload.description
   - payload.datatype
   - payload.deviceName
   - payload.deviceID
6. Click **Done**. 

### DataHub Publish

The DataHub Publish node will be used by the MySQL connector to write data into the database table. 

1. Double-click the **DataHub Publish** node. 
2. In the *Topic *field enter a topic name. You will refer to this name later. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/k5cS60IelFXdZfOgz3ysi_publishname.png" size="70" width="506" height="306" position="center" showCaption="false"}
3. Click **Done**. 

### Debug

The debug node will be used to review the updated payload and ensure the output is correct. 

**To review the payload:**

1. On the flow canvas, click **Deploy **to save the changes to the flow. 
2. Expand the message window beneath the flow.
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/woDAMmFgulRO1sZTdx3NK_expanddebug-window.png)
3. Click the **Debug **icon to view the data output.
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/LQSeWVyAJIbwiJomRgIXI_debugicon.png)
4. Confirm that the output is only including two items in the payload: *value *and *timestamp*. 
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/F6Dmj1IpoblHz4L4PQeuk_output.png)
5. Click **Deploly **to save the flow. 

## Step 5: Add MySQL Marketplace Application

You can now add the MySQL application to Litmus Edge. You can customize the parameters to your own preferred configurations. 

:::hint{type="info"}
**Note**: The version used in this guide for MySQL version is 8.0. See the [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/) for more information. 
:::

**To add the MySQL Marketplace Application:**

1. In Litmus Edge, navigate to **Applications **> **Marketplace**.
2. Click **Marketplace List** and select **Default Marketplace Catalog**.
3. Click the **MySQL** application tile. 
4. From the **Installation script version** drop-down list, select **latest**. 
5. Configure the following parameters.
   - **Name**: Enter **MySQL**. 
   - **Database**: Enter **sample**. 
   - **User**: Enter **user**. 
   - **Password**: Enter the user password. 
   - **MySQL password**: Enter the same value as the *Password *parameter. 
6. Click **Launch**. 
7. Navigate to **Applications **> **Catalog Apps**. 
   The MySQL application appears, pulls the image, and then starts the application. The MySQL application shows as running.
8. Navigate to **Applications **> **Containers**. 
   The MySQL application Container is running.
9. Copy the IP address for the MySQL application.

## Step 6: Create Custom Database Table

You will need to create the custom table in the database before data can be written into it. 

Update the credentials, database name, and table name to your own specific configurations. 

**To create the custom table:**

1. In Litmus Edge, navigate to **Applications **>** Containers**.
2. Click the **Terminal **icon next to the MySQL container.
   The MySQL shell opens.
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/9t2LYeH-mHRMjiVEnpZrv_terminalicon.png)
3. From the MySQL container terminal, enter `mysql -u user -p` and press **ENTER**.
   *Enter password: *appears.
4. Enter your user password, and then press **ENTER**.
   *Welcome to the MySQL monitor *appears.
5. Enter `show databases;`**, **and then press **ENTER**.
   The database names appears.
6. Enter `use sample;`, and then press **ENTER**.
   *Reading table information for completion of table and column names *appears in the console. This allows you to use the database. 
7. Enter `create table conversion_values( timestamp bigint null, value int null );`,  and press **ENTER**. 

The custom table is created with the following:

- The table name *conversion\_values*
- The column names *timestamp *and *value*
- The column timestamp has the data type *bigint *and value has the data type *int*
- Both columns allow null values

## Step 7: Configure MySQL Connector

Follow the steps to [Add a Connector](docId\:b7eMDh8AO2-NOqDHwPD7Y) and select the **DB - MySQL **provider. 

Configure the following required parameters. 

- **Name**: Enter a name for the connector. 
- **Hostname**: Paste the IP address you copied in Step 5.  
- **Port**: Confirm the MySQL Server port. The default value is **3306**. 
- **Username**: Enter **user **or the username configured in Step 5. 
- **Password **(Optional): Enter the password configured in Step 5. 
- **Database**: Enter **sample **or the database configured in Step 5. 
- **Table**: Enter **conversion\_values **or the table name configured in Step 6. 
- **Show Mapping**: Enter the following key/value pairs and click **+ Add**. 
  - **Key 1**: timestamp
  - **Value 1**: \{\{.timestamp}}
  - **Key 2**: value
  - **Value 2**: \{\{.value}}
- **Create table**: Make sure the checkbox is not selected. 

Configure the following optional parameters as needed. 

- **Commit timeout**: Enter the transaction commit timeout in (ms).
- **Max transaction size**: Enter the maximum number of messages before a transaction is committed, regardless of timeout parameter. 
- **Bulk insert count**: (Available for Litmus Edge version 3.11.1 and later) To enable this option, enter the number of messages to group together and send as one bulk insert statement. Enabling this option can improve how quickly data is processed when dealing with high volumes of tags. 
- **Throttling limit**: The maximum number of messages per second to be processed. The default value is zero, which means that there is no limit. 
- **Persistent storage**: When enabled, this will cause messages to undergo a store-and-forward procedure. Messages will be stored within Litmus Edge when cloud providers are online.  
- **Queue Mode**: Select the queue mode as **lifo **(last in first out) or **fifo **(first in first out). Selecting **lifo **means that the last data entry is processed first, and selecting **fifo **means the first data entry is processed first. 

## Step 8: Add Outbound Topic

You will need to add the DataHub Publish topic you configured on the flow canvas as an outbound topic in the MySQL connector. 

**To add the outbound topic:**

1. Navigate to Integration. 
2. Click the MySQL connector tile. 
3. Click the **Topics **tab.
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/odNo8M5KEK95osYZnnQ8z_topicstab.png)
4. Click the **Add **icon and select **Add a new subscription**. 
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/RpmJjmFmSqptxQ56ThNVa_addnewsubscription.png)
5. Configure the parameters for the topic. 
   - **Data Direction**: Select **Local to Remote - Outbound**. 
   - **Local Data Topic**: Paste or enter the DataHub Publish topic name configured in the flow canvas in Step 4. In our example, enter **conversion\_values**. 
   - **Remote Data Topic**: Optionally, enter your desired topic name that will be used to write the data into the database table. 
   - **Description**: Optionally, enter a description. 
   - **Enable**: Click the toggle to enable the topic. 
6. Click **OK**. 
   The new topic appears on the *Topics *pane and is enabled. This topic will write data to the MySQL database table. 

![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/JqEg2_onpKXdX37AhedLs_image.png)

## Step 9: Verify Connection

Use the command line in the MySQL application to confirm data is successfully being written into the database. 

Update the credentials, database name, and table name to your own specific configurations. 

**To verify the connection:**

1. In Litmus Edge, navigate to **Applications **>** Containers**.
2. Click the **Terminal **icon next to the MySQL container.
   The MySQL shell opens.
   ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/9t2LYeH-mHRMjiVEnpZrv_terminalicon.png)
3. From the MySQL container terminal, enter `mysql -u user -p` and press **ENTER**.
   *Enter password: *appears.
4. Enter your user password, and then press **ENTER**.
   *Welcome to the MySQL monitor *appears.
5. Enter `use sample;`, and then press **ENTER**. 
   *Reading table information for completion of table and column names *appears in the console.
6. Enter `select * from conversion_values;`,  and press **ENTER**. 

The data in the custom table displays. 

::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/g71z0s3b92tKVDjcyb1t7_image.png" size="40" width="288" height="280" position="center" showCaption="false"}