Creating and Dropping Tag table

The user must write ‘TAG’ as the table type. By manipulating this table, sensor data can be utilized in various ways.

Unlike the previous version, tag table name does not need to be “TAG”, and can be freely specified.

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.

Starting from version 7.5, the SUMMARIZED keyword in the tag value is optional.

Tag name : PRIMARY KEY
Input time : BASETIME

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

Creation of Tag table

The simplest tag table is generated as follows.

Mach> CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME, value DOUBLE);
[ERR-02253: Mandatory column definition (PRIMARY KEY / BASETIME) is missing.]
==> If you omit some keywords on creating tag table, error occurs.
 
Mach> CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE SUMMARIZED);
Executed successfully.
==> To use statistical information, the SUMMARIZED keyword must be added to the tag value.
 
Mach> desc tag;
[ COLUMN ]              
----------------------------------------------------------------
NAME      TYPE        LENGTH
----------------------------------------------------------------
NAME      varchar         20
TIME      datetime       31
VALUE    double          17
 
Mach> CREATE TAG TABLE other_tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE);
Executed successfully.
 
Mach> desc OTHER_TAG;
[ COLUMN ]              
----------------------------------------------------------------
NAME      TYPE        LENGTH
----------------------------------------------------------------
NAME      varchar         20
TIME      datetime       31
VALUE    double          17

==> If you omit some keywords on creating tag table, error occurs. To improve performance, an internal table is created which divided into four partitions.

Additional Sensor Column

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 tag table TAG (name varchar(20) primary key, time datetime basetime, value double, 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 tag 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 tag table TAG (name varchar(20) primary key, time datetime basetime, value double)
   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.

name	room_no	tag_description
temp_001	1	It reads current temperature as Celsius
humid_001	1	It 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

Setting Table Property

When creating tag table, user can set 3 types of property.

NAME	EXPLANATION	VALUE
TAG_PARTITION_COUNT	User can specify the number of partition to control memory and CPU usage.	- Default: 4 - Min: 1 - Max: 1024
TAG_DATA_PART_SIZE	User can specify data size to control memory and CPU usage for each partition. User can specify in BYTE, can ALIGN in to MB.	- Default: 16MB (16 * 1024 * 1024) - Min: 1MB (1024 * 1024) - Max: 1GB (1024 * 1024 * 1024)
TAG_STAT_ENABLE	User can specify it to activate the function that save statistical information for each TAG ID.	- Default : 1 - Min: 0 (disable) - Max: 1 (enable)

Mach> CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE) TAG_PARTITION_COUNT=1;
Executed successfully.
 
Mach> CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE) TAG_DATA_PART_SIZE=1048576;
Executed successfully.
 
Mach> CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE) TAG_STAT_ENABLE=0;
Executed successfully.
 
Mach> CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE SUMMARIZED) TAG_PARTITION_COUNT=2, TAG_STAT_ENABLE=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 tables are also dropped.

Mach> DROP TABLE tag;
Dropped successfully.
 
Mach> DESC tag;
tag does not exist.

Last updated on Sep 25, 2023

Managing tag meta (tag name)