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

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 docid\ yuomqivegeh1q3gyhxuzk docid\ fu1pawy6macn0l re3uzg docid\ zmi5hc hruvw b2cq2mm8 docid 4vrxfx2u5rphtycznt83n docid 2c3hxslpl0jziok7etjyx docid\ j0ekx4kbatkxaokip8odf docid\ orvqznqjqz4hio0ynstmj docid\ cadbftb3efgef1mqymfn6 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 microsoft sql server default table if you set up a connection with the docid\ yuomqivegeh1q3gyhxuzk or docid\ fu1pawy6macn0l re3uzg and select create table , an existing table will be used or a new one will be created using the following commands 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 docid\ zmi5hc hruvw b2cq2mm8 or docid 4vrxfx2u5rphtycznt83n and select create table , an existing table will be used or a new one will be created using the following commands 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 docid 2c3hxslpl0jziok7etjyx or docid\ j0ekx4kbatkxaokip8odf and select create table , an existing table will be used or a new one will be created using the following commands 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 docid 4 xdgx46cfd2q hmpjaiw docid\ rwdwibwdt2ajmrx f4bac docid\ ehprsonckw9rvywaf byc 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 docid\ thwaae9fa23y3jt 1zovp or the docid\ ofgzt1vvy ryxjmhk0rsm to create this data when you get to step 4 to add the connector in belden horizon data operations, 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 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 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