---
title: Configure tables for SQL connectors
slug: bhdo/configure-tables-for-SQL-connectors
createdAt: 2023-02-27T17:59:00.000Z
---

Belden Horizon Data Operations (BHDO) writes each message a SQL connector receives as a row in a database table. You control both the structure of that table and which value goes in each column.

This applies to the following connectors:

- [DB - Microsoft SQL Server](docId\:H83KRlogNesNUOynmHgOL)
- [DB - Microsoft SQL Server SSL](docId\:KI8p3-_0f-6C0tHqusnRO)
- [DB - MySQL](docId\:REo4zZaCjl1Ze4XlD82fd)
- [DB - MySQL SSL](docId\:FVaralGRYAQ8yNuHdIYky)
- [DB - PostgreSQL](docId\:r8VbCCvPMAoWswosU18y0)
- [DB - PostgreSQL SSL](docId\:vv5OiUBLj1irxXjtbta29)

Select one of two options. **Create table** uses a fixed table that BHDO defines. **Show Mapping** uses a table you define yourself, with columns you fill from the payload.

## Use the default table

Select the **Create table** checkbox so that BHDO creates the destination table when it does not exist. If a table with that name already exists, BHDO writes to it. You need permission to create tables in the target database.

The default table stores one reading per row, with the device, tag, and value in separate columns. The schema depends on the database.

### Microsoft SQL Server

```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

```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

```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)
);
```

## Use a custom table

Select the **Show Mapping** checkbox to send data to a table you create yourself, with whatever structure you need. You then pair each column in that table with the value that fills it.

Mapping does not create or alter tables, so create the table before you configure the connector. Mapping also places no restriction on data types. Any column type works as long as it can store the values you send to it.

### How BHDO reads a mapping value

Each mapping entry has a key and a value. The key is a column in your table. The value tells BHDO what to put in that column:

- A payload field name, such as `deviceId`, writes the value of that field.
- A column name from the default schema, such as `device_id`, writes the value BHDO would have written to that column.
- Text prefixed with `@`, such as `@plant-a`, writes that text into every row as a static value.
- An empty value uses the default schema.

### Map nested fields with JSONPath

Mapping values match first-level fields in the payload. If your data arrives as nested JSON, select **Enable JSONPath** so that BHDO reads any value beginning with `$` as a JSONPath expression instead of a field name. Dotted paths, quoted keys, and array indexes all work.

Values that do not begin with `$` keep their usual meanings, so a single mapping can combine expressions, field names, and static text. An expression that matches nothing writes `NULL`.

:::hint{type="info"}
**Note:** **Enable JSONPath** is available on Gen 2 PostgreSQL, MySQL, and Microsoft SQL Server connectors. The checkbox is clear by default, and while it is clear, mapping values match first-level fields.
:::

## Before you begin

Before you map a custom table, make sure you have:

- A table created in the target database
- Column types that can store the values you intend to send
- Enough SQL knowledge to match payload fields to your schema

An error in the mapping stops data reaching the database.

## Map columns to payload fields

Complete steps 1 to 3 of one of the following guides, then return here to configure the connector in step 4:

- [Microsoft SQL Server Integration Guide](#)
- [MySQL Integration Guide](#)
- [PostgreSQL Integration Guide](#)

These guides use DeviceHub devices and tags to produce the data. You can also produce it with [Analytics](docId\:AtWhYuuQVQvqLnxc_4vHn) or [Flows Manager](docId\:DnmYuzxTFXkY03jWRMRoJ). 

**To map columns to payload fields:**

1. In the *Add a connector* or *Edit connector* dialog box, enter the name of your table in the **Table** field.
2. Select the **Show Mapping** checkbox. Confirm that the **Create table** checkbox is clear. The *Key/Value* section appears.

   ::Image[]{src="https://api.archbee.com/api/optimize/vAqK-046vdh4I0e1y9cfX/35JBLFUf1gOzAW_fIvYPS_image.png" size="80" isUploading="false" width="798" height="678" darkWidth="798" darkHeight="678" position="flex-start" showCaption="false"}
3. In the **Key** field, enter the name of a column in your table.
4. In the **Value** field, enter the payload field that supplies the value.
5. Click **+Add**.
   The pair is added to the mapping.
6. Repeat steps 3 to 5 for each remaining column.

### Mapping example

Your connector receives the following messages.

```json
{"deviceName": "PLC1", "timestamp": 1239423823, "value": 123}
{"deviceName": "PLC1", "timestamp": 1247382948, "value": 545}
{"deviceName": "PLC1", "timestamp": 1294859324, "value": 787}
```

Your table has three columns.

| Device Name | Timestamp | Data Value |
| ----------- | --------- | ---------- |
|             |           |            |

You map each column to the field that fills it.

::Image[]{src="https://api.archbee.com/api/optimize/vAqK-046vdh4I0e1y9cfX/QeJggecJch_MHAAfTUbWZ_image.png" size="80" isUploading="false" width="774" height="423" darkWidth="774" darkHeight="423" position="flex-start" showCaption="false"}

The connector then writes the following rows.

| Device Name | Timestamp  | Data Value |
| ----------- | ---------- | ---------- |
| PLC1        | 1239423823 | 123        |
| PLC1        | 1247382948 | 545        |
| PLC1        | 1294859324 | 787        |

## Map columns to nested payload fields

Use this procedure when the values you want are nested below the first level of the payload, for example inside a `machine` or `sensors` object.

**To map columns to nested payload fields:**

1. In the *Add a connector* or *Edit connector* dialog box, enter the name of your table in the **Table** field.
2. Select the **Show Mapping** checkbox. Confirm that the **Create table** checkbox is clear.
3. Select the **Enable JSONPath** checkbox.
4. In the **Key** field, enter the name of a column in your table.
5. In the **Value** field, enter a JSONPath expression that points to the field you want, starting with `$`.
6. Click **+Add**.
   The pair is added to the mapping.
7. Repeat steps 4 to 6 for each remaining column.

### JSONPath example

Your connector receives the following payload.

```json
{
  "factory": {
    "name": "Plant A",
    "location": "Detroit",
    "line": {
      "id": "L1",
      "product": "Engine Block",
      "shift": "morning",
      "machine": {
        "id": "M101",
        "type": "CNC Mill",
        "status": "running",
        "operator": "John",
        "sensors": {
          "temperature": 72.4,
          "vibration": 0.3,
          "rpm": 3200
        }
      }
    }
  }
}
```

With **Enable JSONPath** selected, you map each column to an expression.

| Key                 | Value                                        |
| ------------------- | -------------------------------------------- |
| product             | `$.factory.line.product`                     |
| status              | `$.factory.line.machine.status`              |
| sensor\_temperature | `$.factory.line.machine.sensors.temperature` |

The connector then writes the following row.

| product      | status  | sensor\_temperature |
| ------------ | ------- | ------------------- |
| Engine Block | running | 72.4                |

## Change a mapping or a table

The connector works from the schema it reads when it starts.

:::hint{type="info"}
**Caution:** Disable and enable the connector after you change a data type or the destination table in the database. If you do not restart it, the data transfer can fail.
:::