Migrate the historical data in SQL Server

If the historical data of data log models is stored in SQL Server, use InfluxDB commands and follow the database schema to migrate the historical data. To plot the data with FactoryTalk View SE controls, such as TrendPro or XY Plot, you must add the list of tags to DataLogPro first.
Before you begin, you must understand:
  • SQL Server storage format.
  • InfluxDB storage format.
  • How data entries are constructed in InfluxDB.
SQL Server storage format
Three tables are created in SQL Server for storing the data log model's historical data: Tag table, Float table, and String table. The string type data is stored in the String table while other types of data is stored in the Float table. Some of the tables' columns will be needed to establish the mapping with the InfluxDB storage format.
For more information on SQL Server database format, see ODBC format databases.
InfluxDB storage format
The InfluxDB database organizes data into buckets and measurements. The bucket name is the name of your HMI server. The InfluxDB's measurement is
TagData
, which includes
Tag
 and
Field
 elements. TagData, Tag and Field, and Tag and Field's keys will be needed to establish the data entry in InfluxDB.
For more information on the InfluxDB storage format, see InfluxDB storage format and schemas.
How data entries are constructed in InfluxDB
The construction of data entries follows the line protocol element parsing standard:
  • Measurement
  • Tag set
  • Field set
  • Timestamp
  • Lines are separated by the newline character (
    \n
    ). Line protocol is space sensitive.
For more information on the InfluxDB line protocol, see InfluxDB OSS documentation Line protocol and Load data from sources in the InfluxDB user interface (UI) or refer to your own InfluxDB version's documentation.
where
  • TagData
    : The measurement in InfluxDB.
  • TagName
    : The tag key in InfluxDB. In SQL Server, it's a column of the Tag table.
  • <tag>
    : A tag's name. In SQL Server, it shows in the
    TagName
     column of the Tag table.
  • S
    : The tag key in InfluxDB. It represents the
    Status
     column of the Float table or String table in SQL Server.
  • status
    : The status (
    C
    ,
    D
    ,
    E
    ,
    S
    , or
    U
    ) in the
    Status
     column of the Float table or String table in SQL Server.
  • <FieldKey>
    : The field key that you define for InfluxDB, such as
    V_B
    ,
    V_I
    ,
    V_D
    , or
    V_S
    , which represents Boolean, Integer, Double, or String value respectively.
  • <value>
    : A tag's value. In SQL Server, it shows in the
    Val
     column of the Float table or String table in SQL Server. If <FieldKey> is V_I, meaning the value is integer, then the value should follow this format
    <value>i
    . For example,
    V_I=8i
    .
  • M
    : The marker. In SQL Server, it's a column of the Float table or String table. If there is no marker, M and <MarkerValue> can be omitted.
  • <MarkerValue>
    : The value (
    B
    ,
    E
    ,
    S
    , or blank) in the
    Marker
     column of the Float table or String table in SQL Server.
  • <Timestamp>
    : The timestamp for a data point in Unix time in InfluxDB. To construct the timestamp, use the
    DateAndTime
     and
    Millitm
     columns of the Float table or String table in SQL Server.
Example
TagData,TagName="system\Second",S="C" V_I=8i,M="B" 1465839830100400200
Migrate historical data to InfluxDB
With the understanding of SQL Server and InfluxDB storage formats, use the
sql.from()
 Influx function and script in the InfluxDB UI. To do so, visit
ComputerName:8086
 in your browser, navigate to
Data Explorer
, enter your script (see example below), and then click
Run
. The script will read the data from SQL Server, transform the data to match the InfluxDB schema, and then write the data to InfluxDB. For more information, see
sql.from()
 function.
Only users with administrative permissions to InfluxDB can perform the migration. For more information, see InfluxDB OSS documentation, Create a token or refer to your own InfluxDB version's documentation.
Overall, the migration of historical data includes migrating the tags' data and migrating the marker, which you can perform separately or together. You can migrate the data of multiple tags using one script as long as they are of the same type, which is Boolean (V_B), Integer (V_I), Double (V_D), or String (V_S). Also, you can migrate all the markers from one table, which is the Float table or the String table.
InfluxDB data type (field key)
Corresponding SQL Server table
Boolean (V_B), Integer (V_I), and Double (V_D)
Float table
String (V_S)
String table
The following example of migrating historical data stored in SQL Server to InfluxDB serves as a reference only; it's not intended to cover all ODBC databases or be a production-ready solution. The example migrates the data and marker together. Some of the parameters are necessary while others are editable. You can copy the script and modify the editable parameters per your own needs.
Example: Migrate data log models' historical data stored in SQL Server to InfluxDB
import "sql"
sql.from(
    driverName: "sqlserver", 
    dataSourceName: "sqlserver://sa:password123@10.224.1.1:1433?database=mydb", 
    query: "Select 'TagData' as _measurement, FORMAT(DATEADD(SECOND,DATEDIFF(SECOND,GETDATE(),GETUTCDATE()),DATEADD(MILLISECOND,Millitm,CAST(DateAndTime as datetime2))), 'yyyy-MM-ddTHH:mm:ss.fffZ') as _time, dbo.TagTable.TagName as TagName, COALESCE(NULLIF(dbo.FloatTable.Status,''),'C') as S,'V_I' as _field, dbo.FloatTable.Val as _value
            from dbo.TagTable
            Join dbo.FloatTable on  dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex
            where dbo.TagTable.TagName = 'system\\Second'")
|> map(fn: (r) =>({r with _time: time(v:r._time)}))
|> map(fn: (r) =>({r with _value: int(v:r._value)}))
|>to(
       bucket: "HMIserver",
       org:"Rockwell")                
sql.from(
    driverName: "sqlserver", 
    dataSourceName: "sqlserver://sa:password123@10.224.1.1:1433?database=mydb", 
    query: "Select 'TagData' as _measurement, FORMAT(DATEADD(SECOND,DATEDIFF(SECOND,GETDATE(),GETUTCDATE()),DATEADD(MILLISECOND,Millitm,CAST(DateAndTime as datetime2))), 'yyyy-MM-ddTHH:mm:ss.fffZ') as _time, dbo.TagTable.TagName as TagName, COALESCE(NULLIF(dbo.FloatTable.Status,''),'C') as S,'M' as _field, dbo.FloatTable.Marker as _value
            from dbo.TagTable
            Join dbo.FloatTable on  dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex
            where dbo.FloatTable.Marker <> ''")
|> map(fn: (r) =>({r with _time: time(v:r._time)}))
|>to(
       bucket: "HMIserver",
       org:"MyOrganization")
Explanation of the example
The first part of the script migrates the integer data (
system\\Second
) in SQL Server database's
Float
 table and the second part migrates all the markers in the
Float
 table.
  • import "sql"
    : Imports the
    sql
     package. The
    sql
     package provides tools for working with data in SQL databases.
  • sql.from()
    : Retrieves data from a SQL data source. It has the following parameters:
    • driverName
      : Driver to use to connect to the SQL database. For SQL Server, it's
      sqlserver
      .
    • dataSourceName
      : Data source name (DNS) or connection string used to connect to the SQL database. It includes SQL Server's user name, password, host address, port, and database name, all of which should be edited according to your own needs.
    • query
      : Query executed on the SQL Server database.
      The query in the first part of the script selects several fields from the
      Tag
       table and
      Float
       table where the
      TagName
       is
      system\Second
       and the field is
      V_I
      . The selected fields are then renamed to match the InfluxDB schema.
      You should edit the sample script's Tag table name, Float or String table name, field key (
      V_B
      ,
      V_I
      ,
      V_D
      , or
      V_S
      ), and tag name according to your own needs. You can add multiple tags of the same type separated with "
      or
      ", for example:
      dbo.TagTable.TagName = 'tag1' or dbo.TagTable.TagName = 'tag2'
      .
      The query in the second part of the scripts is similar to the first one, except that it selects the field
      M
       from the
      Float
       table.
      You should edit the sample script's table type (Float table or String table) according to your own needs.
  • map
    : Function to transform the
    _time
     field in each record from a string to a time value or transform the
    _value
     field in each record from a string to an integer.
    You should edit the sample script's value type (
    int
    ,
    bool
    ,
    float
    , or
    string
    ) according to your own needs.
  • to
    : Function to write the data to an InfluxDB bucket
    HMIserver
     in the organization
    MyOrganization
    .
    You should edit the sample script by entering your own bucket and organization. To migrate to the built-in InfluxDB, the organization name must be
    Rockwell
    ; to migrate to an external InfluxDB, the organization name can be customized.
Provide Feedback
Have questions or feedback about this documentation? Please submit your feedback here.
Normal