Creation and Dropping of Tag table

The user must explicitly create TAG table.

Note that there is no TAG table when the database is first installed.

Since the TAG table is, in basic, intended to store sensor data, the following three essential items must be included.

  • Tag name
  • Input time
  • Sensor value

However, the Machbase TAG table is accompanied by keywords for the above required columns, as it allows input of the above three and additional columns.

  • Tag name : PRIMARY KEY
  • Input time : BASETIME
  • Sensor value : SUMMARIZED

This tag name is used as tag meta information described in the next section.


Index


Creation of Tag table


The simplest tag table is generated as follows.

Mach> create tagdata table TAG (name varchar(20) primary key, time datetime, value double);
[ERR-02253: Mandatory column definition (PRIMARY KEY / BASETIME / SUMMARIZED) is missing.]
==> If you omit some keywords on creating tag table, error occurs.

Mach> create tagdata table TAG (name varchar(20) primary key, time datetime basetime, value double summarized);
Executed successfully.

Mach> desc tag;
[ COLUMN ]               
----------------------------------------------------------------
NAME      TYPE        LENGTH
----------------------------------------------------------------
NAME      varchar         20
TIME      datetime       31
VALUE    double          17

A table named TAG was created.


Additional sensor columns


In reality, it is sometimes difficult to solve a given problem with just three columns when using the TAG table..

In particular, since the information of the sensor data to be input may be a specific group or an Internet address as well as a name, a time, and a value, the following can be added.

Mach> create tagdata table TAG (name varchar(20) primary key, time datetime basetime, value double summarized, grpid short, myip ipv4) ;
Executed successfully.

Mach> desc tag;
[ COLUMN ]               
----------------------------------------------------------------
NAME             TYPE        LENGTH
----------------------------------------------------------------
NAME             varchar         20
TIME             datetime        31
VALUE            double          17
GRPID            short            6       <=== added column
MYIP             ipv4            15       <=== added column


Note, however, that in older versions, including 5.5, values of type VARCHAR can not fit into the supplementary column.

Mach> create tagdata table TAG (name varchar(20) primary key, time datetime basetime, value double summarized, myname varchar(100)) ;
[ERR-01851: Variable length columns are not allowed in tag table.]

In the case of string type, the above error occurs. In versions 5.6 and later, VARCHAR is also supported for additional columns in the TAG table.


Additional metadata columns


It is not only possible to add sensor columns to the TAG table, but also to input information dependent on each tag name.

Since this information does not need to be redundantly stored in the sensor data, it is necessary to add a separate column definition syntax METADATA (...) for efficient management.

Mach> create tagdata table TAG (name varchar(20) primary key, time datetime basetime, value double summarized)
   2  metadata (room_no integer, tag_description varchar(100));

Here, room_no and tag_description are information dependent on name. For example, you can input this information.

nameroom_notag_description
temp_0011Reads current temperature as Celsius
humid_0011

Reads current humidity as percentage


After input, you can query with TAG table through SELECT.

Mach> SELECT name, time, value, tag_description FROM tag LIMIT 1;
name                  time                            value
--------------------------------------------------------------------------------------
tag_description
------------------------------------------------------------------------------------
temp_001              2019-03-01 09:52:17 000:000:000 25.3
It reads current temperature as Celsius


Specify the numberof partitions

Although the number of partitions is set to four by default, you can specify this number to control memory and CPU usage as follows.

Create the table by specifying its value in the table property tag_partition_count.

If you increase this value, the higher the parallelism and the better the performance, but the memory usage increases and the CPU usage also increases.

The following example shows setting tag_partition_count to 1 for the purpose of minimizing memory and CPU usage.

Mach> create tagdata table TAG (name varchar(20) primary key, time datetime basetime, value double summarized) tag_partition_count=1;
Executed successfully.


Dropping tag table

If you need to recreate the generated tag table, or if you need to free up disk space, you can use the following DROP command to drop it.

Note that all data related to the TAG table, ie tag data, metadata, and ROLLUP tables are deleted.

Mach> drop table tag;
Dropped successfully.

Mach> desc tag;
tag does not exist.