SQL data connector configuration object consists of the following attributes:

AttributeDescription
typeConnector type; must be always set to sql.
idApp wide unique connector id.
serverProvide only the domain part of the URL without scheme.
portService port number.
connectionParametersOptional Map of connection parameters - up to 10.
credentialsOptional Username and password to connect to the broker.
databaseFlavourOne of postgresql, mysql, and sqlserver.
databaseNameName of the database.
tableName of the table.
columnsNames of the columns that will be modified.
dataProcessingMethodOptional. Id or name of the App method that will be used for filtering and/or transforming incoming data. See Overview page for details.
disabledOptional. When true data connector stops streaming.
POST .../v3/apps/:ref/dataconnectors/test

{
  "type": "sql",
  "id": "dc.sql.1",
  "server": "mysql-265fae22-connio-c43e.aivencloud.com",
  "port": 29540,
  "connectionParameters": {
    "ssl-mode": "REQUIRED"
  },
  "credentials": {
    "user": "avnadmin",
    "password": "ys2q2esmvd3vay94"
   },
   "databaseFlavour": "mysql",
   "databaseName": "defaultdb",
   "table": "test",
   "columns": ["feedId", "temperature"],
   "dataProcessingMethod": "processData",  
   "disabled": false
}
POST .../v3/apps/:ref/dataconnectors

{
  "type": "sql",
  "id": "dc.sql.1",
  "server": "mysql-265fae22-connio-c43e.aivencloud.com",
  "port": 29540,
  "credentials": {
    "user": "avnadmin",
    "password": "ys2q2esmvd3vay94"
   },
   "connectionParameters": {
    "ssl-mode": "REQUIRED"
   },
   "databaseFlavour": "mysql",
   "databaseName": "defaultdb",
   "table": "test",
   "columns": ["feedId", "temperature"],
   "dataProcessingMethod": "processData",  
   "disabled": false
}
{
    "type": "sql",
    "id": "dc.postgresql.1",
    "server": "pg-f18d550-connio-c43e.aivencloud.com",
    "port": 29540,
    "credentials": {
    	"user": "avnadmin",
    	"password": "degs21gc1yeweto2"
    },
    "connectionParameters": {
      "sslmode": "require"
    },
    "databaseFlavour": "postgresql",
    "databaseName": "defaultdb",
    "table": "test",
    "columns": ["feedId", "temeprature", "time"],
    "disabled": false
}
PUT .../v3/apps/:ref/dataconnectors/dc.sql.1

{
  "databaseName": "testDB",
  "table": "test",
  "columns": ["feedId", "temperature", "time"],
  "dataProcessingMethod": "processData"
}

The data connector's data processing method should return a map where Key is the column name, and Value is the value that will be inserted into that column.

By default if no data processing method is provided, a data value will be mapped like the following:

feed -> data value feed
account -> account id
app -> app id
device -> device id. If it is an app data value this is not assigned
datavalue -> complete data value in JSON stringified

Example default expected table:

CREATE TABLE public.datavalues (
    feed varchar(100) not null,
    account varchar(100) not null,
    app varchar(100) not null,
    device varchar(100) null,
    datavalue varchar(10000) not null
)

For a data connector that will populate feedId and temperature columns, you can return your own map as shown below:

{
  "type": "sql",
  "id": "dc.sql.1",
  "server": "mysql-265fae22-connio-c43e.aivencloud.com",
  "port": 29540,
  "credentials": {
    "user": "avnadmin",
    "password": "ys2q2esmvd3vay94"
   },
   "connectionParameters": {
    "ssl-mode": "REQUIRED"
   },
   "databaseFlavour": "mysql",
   "databaseName": "defaultdb",
   "table": "test",
   "columns": ["feedId", "temperature"],
   "disabled": false
}
function mymethod(value) {
  // ignore all properties other than `temperature`
  if (value.destination.property.name === "temperature") {
    return { 
        feedId: value.feedId,
        temperature: value.value,
      //time: value.value.sourceTime
    };
  }
  else {
    return null;
  }  
}

What’s Next