An example of tag table
Data conversion flowchart
As you can see in the figure above, we will load the raw CSV file into Machbase's log table and convert it into a tag table.
Tag table creation and tag meta loading
Create TAG table as shown below and load the tag names (tag meta) stored in the CSV file at once using a tool called tagmetaimport.
Mach> create tagdata table tag (name varchar(32) primary key, time datetime basetime, value double summarized); Executed successfully. Elapsed time: 3.032 $ cat tag_meta.csv MTAG_V00 MTAG_V01 MTAG_C00 MTAG_C01 MTAG_C02 MTAG_C03 MTAG_C04 MTAG_C05 MTAG_C06 MTAG_C07 MTAG_C08 MTAG_C09 MTAG_C10 MTAG_C11 MTAG_C12 MTAG_C13 MTAG_C14 MTAG_C15 $ tagmetaimport -d tag_meta.csv Import time : 0 hour 0 min 0.340 sec Load success count : 18
As shown above, 18 tag meta information were loaded successfully.
Create table for PLC data loading
Execute the following query to create the log table.
create table plc_tag_table( tm datetime, V0 DOUBLE , V1 DOUBLE , C0 DOUBLE , C1 DOUBLE , C2 DOUBLE , C3 DOUBLE , C4 DOUBLE , C5 DOUBLE, C6 DOUBLE , C7 DOUBLE , C8 DOUBLE , C9 DOUBLE , C10 DOUBLE , C11 DOUBLE , C12 DOUBLE , C13 DOUBLE , C14 DOUBLE , C15 DOUBLE );
Note that this table is a log type of table (do not get confused by file names). In Machbase, if you do not specify a separate table type, the default type of table is log.
Loading PLC data
Input the plc_tag.csv file, which contains 2 million original PLC data, using the machloader as PLC input in the log table plc_tag_table created above.
In the plc_tag.csv file, the first column is time, then V0, V1, ... Columns are divided up to C15.
$ machloader -t plc_tag_table -i -d plc_tag.csv -F "tm YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn" ----------------------------------------------------------------- Machbase Data Import/Export Utility. Release Version 5.5.0.official Copyright 2014, MACHBASE Corporation or its subsidiaries. All Rights Reserved. ----------------------------------------------------------------- NLS : US7ASCII EXECUTE MODE : IMPORT TARGET TABLE : plc_tag_table DATA FILE : 4_plc_tag.csv IMPORT MODE : APPEND FIELD TERM : , ROW TERM : \n ENCLOSURE : " ESCAPE : \ ARRIVAL_TIME : FALSE ENCODING : NONE HEADER : FALSE CREATE TABLE : FALSE Progress bar Imported records Error records ============================== 2000000 0 Import time : 0 hour 0 min 26.544 sec Load success count : 2000000 Load fail count : 0
Tag meta name generation rules
Now you must re-insert data in log table into the tag table in order to see the data through the Tag Analyzer.
For this, the insert-select statement will duplicate each record in the log table into the TAG table.
The name of each tag is determined as follows.
Column name of log table | Tag name values of tag table |
---|---|
V0 | MTAG_V00 |
V1 | MTAG_V01 |
C0 | MTAG_C00 |
C1 | MTAG_C01 |
... | |
C15 | MTAG_C15 |
Loading Tag table data
It's time to load the actual data into the tag table.
The following query will generate TAG data based on TAG names.
Mach> insert into tag select 'MTAG_V00', tm, v0 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 4.898 Mach> insert into tag select 'MTAG_V01', tm, v1 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 5.577 Mach> insert into tag select 'MTAG_C00', tm, c0 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 6.327 Mach> insert into tag select 'MTAG_C01', tm, c1 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 7.445 Mach> insert into tag select 'MTAG_C02', tm, c2 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 6.898 Mach> insert into tag select 'MTAG_C03', tm, c3 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 7.078 Mach> insert into tag select 'MTAG_C04', tm, c4 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 6.799 Mach> insert into tag select 'MTAG_C05', tm, c5 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 7.210 Mach> insert into tag select 'MTAG_C06', tm, c6 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 9.232 Mach> insert into tag select 'MTAG_C07', tm, c7 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 6.398 Mach> insert into tag select 'MTAG_C08', tm, c8 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 6.432 Mach> insert into tag select 'MTAG_C09', tm, c9 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 6.734 Mach> insert into tag select 'MTAG_C10', tm, c10 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 7.692 Mach> insert into tag select 'MTAG_C11', tm, c11 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 8.628 Mach> insert into tag select 'MTAG_C12', tm, c12 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 8.229 Mach> insert into tag select 'MTAG_C13', tm, c13 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 9.517 Mach> insert into tag select 'MTAG_C14', tm, c14 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 7.231 Mach> insert into tag select 'MTAG_C15', tm, c15 from plc_tag_table; 2000000 row(s) inserted. Elapsed time: 7.830
A total of 36 millions of records are created.