Import of Historical (Logged) Data

Ignition
1

I have an Ignition system up and running and storing historical data to the database.
I have a number of offline systems which generate data in CSV format and I want to import this data into the Ignition Historical data tables in the database.
This can be achieved using the SQL BULK INSERT command, so no problems there.
However, the problem is that a lot of the data pre-dates the Ignition system, so there are no entries in the sqlth_partitions table that correspond to where this data should be stored.

The entries are as follows:

sqlt_data_1_2010_09,1,1283939117220,1285926318947
sqlt_data_1_2010_10,1,1285926323155,1288608324436
sqlt_data_1_2010_11,1,1288608334240,1291200334829
sqlt_data_1_2010_12,1,1291200335771,1293878736469
sqlt_data_1_2011_01,1,1293878744155,1296557145355

Can I create entries in this table corresponding to each historical month and then store the data in the appropriate tables, or is there more to it than that? such as the sqlth_sce or sqlth_te tables?

2

In order to get this working you need to do the following:

  1. If your data was logged all at the same rate make sure you have a row in the sqlth_scinfo for that scan class. This scan class corresponds to one in the Scan Classes editor for SQLTags.

  2. Next, add each tag to the sqlth_te table and associate it with the right scan class from step 1.

  3. Add each partition to the sqlth_partitions table with the appropriate start and end times. Make sure each partition table exists.

  4. Lastly, add a row for each scan class in sqlth_sce with the appropriate start time, end time and rate. You can just have one row for each and make your start time the first partition start time and the end time set to the current time.

  5. Restart Ignition to make sure we see the changes.

If you just add one tag and historize it you will see how Ignition creates these tables and how the data is formatted. If you have any more questions let us know.

3

Thanks Travis; I’ll try it out and see how it goes.