--- title: Work with Tables in SQL Connectors (Create Table and Show Mapping) slug: litmusedge/how-to-guides/integration-guides/work-with-tables-in-sql-connectors description: When you set up and configure a connection with SQL connectors, you have the option of selecting how the data will be transferred to database tables. docTags: createdAt: 2023-02-21T13:38:26.000Z --- When you set up and configure a connection with the following SQL connectors, you have the option of selecting how the data will be transferred to database tables. - [DB - Microsoft SQL Server](docId\:r9uQ3rJob_FqtPHEwTMzl) - [DB - Microsoft SQL Server SSL](docId\:haJd_22slYV1_GJOFoW3k) - [DB - MySQL](docId\:fb9osMf0Bd16XTe4zwR85) - [DB - MySQL SSL](docId\:irqU87UcpqJzZD63hFnLV) - [DB - PostgreSQL](docId\:RRiM1Z9G24jAWbcQa7bKj) - [DB - PostgreSQL SSL](docId\:lyekItpH135pxQPCvaLDg) - [DB - MongoDB](docId\:W6-7xdz33hlkX-hjmqudW) - [DB - Mongo v4+](docId\:OcyFolgl7TGrJRGSLwFBT) When configuring the connector, make sure to select only one of the following options. # Option 1: Create Table If you select the **Create table** checkbox, a default table will be created if one doesn't already exist. The table will be used to store data sent through the connector. If you select this checkbox, make sure to unselect the **Show Mapping** checkbox. ## Microsoft SQL Server Default Table If you set up a connection with [DB - Microsoft SQL Server](docId\:r9uQ3rJob_FqtPHEwTMzl) or [DB - Microsoft SQL Server SSL](docId\:haJd_22slYV1_GJOFoW3k) and select **Create table**, an existing table will be used or a new one will be created using the following commands. ```sql IF NOT EXISTS(SELECT * FROM sysobjects WHERE NAME = '"TABLE_NAME"' AND xtype = 'U') CREATE TABLE "TABLE_NAME" ( id BIGINT IDENTITY(1,1) NOT NULL, record_uuid CHAR(36) NOT NULL, arrived_at DATETIME NOT NULL, device_id VARCHAR(64) NULL, register_id VARCHAR(64) NULL, tag_name VARCHAR(64) NULL, datatype VARCHAR(32) NULL, value TEXT NULL, success BIT NULL, PRIMARY KEY (id) ) ``` ## MySQL Default Table If you set up a connection with [DB - MySQL](docId\:fb9osMf0Bd16XTe4zwR85) or [DB - MySQL SSL](docId\:irqU87UcpqJzZD63hFnLV) and select **Create table**, an existing table will be used or a new one will be created using the following commands. ```mysql CREATE TABLE IF NOT EXISTS `TABLE_NAME` ( id BIGINT AUTO_INCREMENT NOT NULL, record_uuid CHAR(36) NOT NULL, arrived_at DATETIME NOT NULL, device_id VARCHAR(64) NULL, register_id VARCHAR(64) NULL, tag_name VARCHAR(64) NULL, datatype VARCHAR(32) NULL, value TEXT NULL, success TINYINT NULL, PRIMARY KEY (id) ); ``` ## PostgreSQL Default Table If you set up a connection with [DB - PostgreSQL](docId\:RRiM1Z9G24jAWbcQa7bKj) or [DB - PostgreSQL SSL](docId\:lyekItpH135pxQPCvaLDg) and select **Create table**, an existing table will be used or a new one will be created using the following commands. ```pgsql CREATE TABLE IF NOT EXISTS "TABLE_NAME" ( id BIGSERIAL NOT NULL, record_uuid UUID NOT NULL, arrived_at TIMESTAMP WITH TIME ZONE NOT NULL, device_id VARCHAR(64) NULL, register_id VARCHAR(64) NULL, tag_name VARCHAR(64) NULL, datatype VARCHAR(32) NULL, value VARCHAR NULL, success BOOLEAN NULL, PRIMARY KEY (id) ); ``` # Option 2: Show Mapping If you select the **Show Mapping** checkbox, you can send data to a custom table in the database. This table can be configured in your preferred format and structure. :::hint{type="warning"} **Important**: - If you make any changes to the data type or destination table in the database, make sure to disable and enable the connector. If the connector isn't restarted, Litmus Edge will not have access to the latest table schema. This may affect the data transfer. - When configuring the table, there are no limitations on what data types are supported. You can put any field type into the custom table as long as this type can store data from the respective field. ::: ## Before You Begin - Make sure you have sufficient knowledge of SQL when configuring the mapping for the custom table. Any errors in the format will cause failures in sending data to the database. - The database table that will store the data from this connection will be need to be created before completing these steps. This task only maps the data that will be sent to the pre-existing table. ## Configure Custom Table Mapping To configure custom table mapping, begin by following Step 1, Step 2, and Step 3 for one of the following guides: - [Microsoft SQL Server Integration Guide](docId\:ZV0hxkfygUXtt3nXwXPOk) - [MySQL Integration Guide](docId\:dG7MaI7cE3XkC4HJzAzht) - [PostgreSQL Integration Guide](docId\:Zf_BaOyvCb2fM6IVUXrU0) These guides use DeviceHub data from devices and tags to create the data that will be sent to the database. Alternatively, you can also use [Analytics](docId:8alOe_5_c9uBqJsW0PpCX) or the [Flows Manager](docId\:JGhNQYhxbIk8x2nhH5Vte) to create this data. When you get to Step 4 to add the connector in Litmus Edge, configure the following parameters for the custom table: - **Table**: This is the name of the pre-existing custom table that will store the transferred data. - **Show Mapping**: Select this checkbox. See the *Configure Key/Value Pairs *section below to learn how to format key/value pairs. - **Create Table**: Make sure to unselect this checkbox. ### Configure Key/Value Pairs Once you select the *Show Mapping* checkbox, you'll be able to map the key/value pairs for the custom table. **To configure key/value pairs:** 1. From the *Add a connector* or* Edit connector *dialog box, select the **Show Mapping** checkbox. Make sure the *Create table* checkbox is not selected. The Key/Value section appears. ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/5WNE83rVoi-LUO5K3O0tU_show-mapping-check-box.png" size="80" width="1170" height="751" position="center" caption="Show Mapping checkbox" showCaption="true"} 2. In the **Key **field, enter the name of the first column in the table. 3. In the **Value **field, enter the value name that will be stored in the first column in the following format: `{{.value_name}}`. Replace *value\_name* with the corresponding key value of the JSON. See the example below. 4. Click **+Add**. The key/value pair is added to the mapping for the connector. 5. Continue adding columns and corresponding values for your table. ### Mapping Example You have the following data in JSON format. :::BlockQuote \{“deviceName”: “PLC1”, “timestamp”: 1239423823, "value": 123}, \{“deviceName”: “PLC1”, “timestamp”: 1247382948, "value": 545}, \{“deviceName”: “PLC1”, “timestamp”: 1294859324, "value": 787} ::: You have created the following database table. | Device Name | Timestamp | Data Value | | ----------- | --------- | ---------- | | | | | The Show Mapping section would be formatted as shown below. ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/JHQ2iAcrCggVPAvjvOo1J_image.png" size="80" width="1165" height="500" caption="Show Mapping formatting example" position="center" showCaption="true"} The database table would be updated with the data as shown below. | Device Name | Timestamp | Data Value | | ----------- | ---------- | ---------- | | PLC1 | 1239423823 | 123 | | PLC1 | 1247382948 | 545 | | PLC1 | 1294859324 | 787 |