Work with Tables in SQL Connectors (Create Table and Show Mapping)

10min
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 - MySQL﻿﻿
﻿DB - MySQL SSL﻿﻿
﻿DB - PostgreSQL﻿﻿
﻿DB - PostgreSQL SSL﻿﻿
﻿DB - MongoDB﻿﻿
﻿DB - Mongo v4+﻿﻿
When configuring the connector, make sure to select only one of the following options.
Option 1: Create Table
If you select the Create table check box, a default table in JSON 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 check box, make sure to unselect the Show Mapping check box. 
MySQL Default Table
If you set up a connection with DB - MySQL﻿ or DB - MySQL SSL﻿ 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)
);
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﻿ or DB - PostgreSQL SSL﻿ 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)
);
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 check box, you can send data to a custom table in the database that is already created. This table can be configured in your preferred format and structure. 
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 Integration guides:
﻿MySQL Integration Guide﻿﻿
﻿PostgreSQL Integration Guide﻿﻿
These use cases use DeviceHub data from devices and tags to create the data that will be sent to the database. Alternatively, you can also use Analytics﻿ or the Flows Manager﻿ to create this data. 
When you get to Step 4 to add the connector in Manufacturing Connect 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 check box. See the Configure Key/Value Pairs section below to learn how to format key/value pairs. 
Create Table: Make sure to unselect this check box. 
Configure Key/Value Pairs
Once you select the Show Mapping check box, you'll be able to map the key/value pairs for the custom table. 
To configure key/value pairs:
From the Add a connector or Edit connector dialog box, select the Show Mapping check box. Make sure the Create table check box is not selected. 
The Key/Value section appears. 
Show Mapping check box
﻿
In the Key field, enter the name of the first column in the table. 
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. 
Click +Add. 
The key/value pair is added to the mapping for the connector. 
Continue adding columns and corresponding values for your table. 
Mapping Example
You have the following data in JSON format. 
{“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. 
﻿
Show Mapping formatting example
﻿
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
﻿
Device Name	Timestamp	Data Value
PLC1	1239423823	123
PLC1	1247382948	545
PLC1	1294859324	787
Updated 27 Jun 2024
Set Up Edge Cascading with the NATS Connector
Set Up the OSI Agent