This article describes how to integrate the thingsHub with a MySQL database. This enables you to send data to the database.


Using the New Integration wizard to set up a MySQL database integration

Step 1) Go to Data > Integrations and select the New Integration button.

Step 2) In the Select Integration Type step of the Add Integration wizard, enter the configuration information as described in the below parameters table. Then press the Next button to continue to the next step, Configure selected Integration.

Parameter

Description

Name

Enter a name for the integration.

Description

Optional field used to describe the integration.

Data Selection Label

Select labels that are assigned to the devices from which you would like acquire data. For more information on device labels, see Using labels to group and sort devices.

Integration type

Select MySQL

Step 3) In the Configure selected Integration step, enter the configuration information as described in the below parameters table. Then press the Create button to complete the process and return to the Integrations page.

Parameter

Description

Host

MySQL host (required)

Port

MySQL port. (optional, default: 3306)

Database

MySQL database (required)

User Name

MySQL user (required)

The user requires the following grants on the database:
ALTER, CREATE, DELETE, INDEX, INSERT, SELECT, UPDATE

Password

MySQL password (required)


Data format

Device mapping

This table will hold information of the device sending uplinks to this integration. The table name is device_mapping, but in cases where the table_prefix config is used, then the table name is prefixed with table_prefix. (eg: th_device_mapping).

Field

Description

id

Database auto incremented id (integer)

device_id

Things hub device id (string)

driver

Driver of the device (string)

data_table

Name of the table where the data from device is stored at the time of this uplink.

updated_at

When this row has been last updated (datetime)

created_at

When this row has been inserted. (datetime)

Device data

The table that will hold the current data of the devices mapped in device_mapping table. This table name is determined base on the driver that a device has at the time of uplink. Based on that driver, the device_mapping.data_table field contains the name of this table with the table_prefix in case this exists.

  • Examples:

    • smartmakers_nke_ino_1_1_0 - this is a table name where the prefix is not set and the device uses the smartmakers/nke-ino:1.1.0 driver

    • th_smartmakers_nke_s0_2_0 - this is a table name where table prefix is used and the driver is smartmakers/nke-s0:2.0

Field

Description

id

Database auto incremented id (integer)

device_mapping_id

Relational id of the device_mapping table. (integer)

Variable columns

The rest of the table will contain columns based on the data returned from the device driver.