DT2DB User Manual

User Manual for DT2DB (DataTurbine to Database).

This program, “dt2db” inserts the streaming data from DataTurbine into a relational database using JDBC.
Before running the program, please specify the parameters in the xml configuration file. There are two sample configuration files in the edu/ucsd/osdt/db. The two sample configuration files represent two different data models: row model vs. Entity Attribute Value (EAV) model. Please refer to Wikipedia for further information on the two.

Installation:

dt2db is a Java program that can be run from the terminal

java -classpath=<$classpath>;<JDBCDriver> edu.ucsd.osdt.db.dt2db    <path to an xml configuration file>

The user must provide JDBC driver, supplied from the database.   It is assumed that database server is running and the tables are defined. In addition, the DataTurbine servers are currently collecting the data.

Overview of the parameters:

There are four main types of parameters.

  1. DT/RBNB related parameters
  2. DB related parameters
  3. DT2DB error handling parameters
  4. mapping parameters between DB and DT

The detailed explanations for the parameters are given in the following section.

Configuration file:

The user must configure the value field in the param entries, and mapping parameters. Each parameter (namely, <param>) entry in xml file contains two parts: <name> part and <value> part. The name part should not be changed, and the user should supply the appropriate text in the value field.

DataTurbine RBNB related parameters:

Name of the server that hosts DataTurbine (RBNB)

<param name=”rbnbServerAddress” value=”niagara-dev.sdsc.edu”/>

Port number for the host server.

<param name=”rbnbServerPort” value=”3333″/>

Name of the server that will log the events of dt2db in its syslog file.

<param name=”sysLoggerServerName” value=”niagara-dev.sdsc.edu”/>

Database related parameters:

Name of the server that hosts database

<param name=”dbServerName” value = “localhost” />

Name of the JDBC driver

<param name=”jdbcDriverName” value = “org.postgresql.Driver” />

Name of the database instance

<param name=”dbName” value = “testdb” />

Name of the user name for the database

<param name=”dbUserName” value = “postgres” />

Name of the password for the database:

<param name=”dbPassword” value = “something” />

DT2DB error handling parameters
These parameters are optional. Please use default values if you are in doubt. On the other hand, if you’d like to know the details, please refer to SinkClientManger.java to understand how they are used.

Path of the file that contains the start timestamp value for dt2db

<param name=”startTimeFilePath” value = “/Users/petershin/Documents/workspace/startTime.txt” />

The interval between the updates to the database in seconds

<param name=”durationSeconds” value = “60.0” />

The switch for stopping at error automatically or manually

<param name=”stopAtError” value = “NO”/>

Path of the file that specifies (YES or NO) to continue storing the data – at error, the file will contain “NO” if “stopAtError” is specified as “YES”

<param name=”continueFlagFile” value =”/Users/petershin/Documents/workspace/continue.txt” />

Email address of the administrator in case the error recovery is not automated.

<param name=”emailContact” value = “pshin@sdsc.edu” />

Parameters related to mapping between DataTurbine and database:
Option 1 (RowModel):

The syntax will be different for two models: RowModel and EAVModel.

<param name=”dataModel” value=”RowModel” />

Table Name

<table name=”ss.fake_daq”>

Column name and the corresponding DataTurbine channel name (TimeStamp is defined by DataTurbine)

<column name=”time_tag” channelMapping=”TimeStamp”/>

Column name and the corresponding DataTurbine channel name

<column name=”ch0″ channelMapping=”FakeDAQ/0″ />

Column name and the corresponding DataTurbine channel name

<column name=”ch1″ channelMapping=”FakeDAQ/1″/>

Close the table tag.

</table>

Option 2 (EAVModel):

The syntax will be different for two models: RowModel and EAVModel.

<param name=”dataModel” value=”EAVModel” />

Mapping parameter that specifies both DB table name/column name and corresponding channel name

<mapping tableName =”[RBNB].[dbo].[DataValues]” colName =”valueID” value = “temp” rbnbChannel=”/FakeDAQ/ch0″ >

Each channel will have various metadata fields. These values are associated with the column, and they tend to be static.

<value valueName=”siteID” value=”2″ type=”INT” />

Another value associated with the map.

<value valueName = “sensorID” value = “10” type= “STRING”/>
</mapping>

A similar mapping must be supplied for each channel.

<mapping tableName =”[RBNB].[dbo].[DataValues]” colName =”valueID” value = “hum” rbnbChannel=”/FakeDAQ/ch1″ >
<value valueName = “siteID” value=”3″ type = “INT”/>
<value valueName = “sensorID” value = “8” type=”DOUBLE” />
</mapping>

Close the config tag.

</config>

To do list / current limitations:

  • Assistant UI for creating xml configuration file
  • Sanity checks for server connection to db and dt.
  • Getting the list of channel name
  • Getting the list of column names
  • Finding the values for the EAV models
  • Which UI interface??? – jsp? Applet? Etc. homogeneous look preferred.
  • Testing parent-child mode over several months.
  • Fine grained logging abilities (logger from sun java).
  • Web service interface for continuing decision.
  • How to convert arrays or special java objects into db objects.
  • Multiple RBNB Servers into multiple DBs?

Known bug/problem/limitations:

If the database does not allow less than 1 millisecond interval while the data is sampled at faster than 1 millisecond interval, then the sampled data will be inserted twice with the same timestamp.

Millisecond can be the smallest scale for some DB or schema, and if the smaller scale is supported and configured, this needs to be addressed in the new version.

Open Source DataTurbine Initiative © 2017 Frontier Theme