Shell script for writing data

CREATE TAG TABLE IF NOT EXISTS EXAMPLE  (
    NAME VARCHAR(20) PRIMARY KEY,
    TIME DATETIME BASETIME,
    VALUE DOUBLE SUMMARIZED
);

The simplest way writing data into machbase-neo is using its command line tool machbase-neo shell. We can import/export/read table with it.

Step 1. Data generation

For demonstration, we prepares simple shell script that prints out sine/cosine values per a second.

Copy script below and save it as gen_wave.sh

#!/bin/bash
angle=0
step_angle=24
sinval=0
cosval=0
PI=3.14159

while [ 1 ]
do
    ts=`date +"%s"`
    sinval=$(awk "BEGIN{ printf \"%.6f\", (sin($angle*($PI/180)))}")
    cosval=$(awk "BEGIN{ printf \"%.6f\", (cos($angle*($PI/180)))}")
    echo "wave.sin,$ts,$sinval"
    echo "wave.cos,$ts,$cosval"
    sleep 1
    angle=$((angle+step_angle))
done

Step 2. Run script

Let’s run this script for testing.

sh ./gen_wave.sh

It periodically prints sin/cos values with name (wave.sin, wave.cos), UNIX epoch time and value per a second as below. The output is in csv and it is intended to be utilized by machbase-neo shell command.

Press ^C to stop shell script.

Why the output csv should be in this order? It’s depends on the table scheme.

Run command below to “describe” the table.

machbase-neo shell desc EXAMPLE

Subcommand desc <table> shows some details of the table.

TABLE    EXAMPLE
TYPE     Tag Table
TAGS     wave.cos, wave.sin
┌───┬───────┬──────────┬────────┐
│ # │ NAME  │ TYPE     │ LENGTH │
├───┼───────┼──────────┼────────┤
│ 1 │ NAME  │ varchar  │    100 │
│ 2 │ TIME  │ datetime │      8 │
│ 3 │ VALUE │ double   │      8 │
└───┴───────┴──────────┴────────┘

When we import the csv data into a table, it is important to make fields of csv arranged in order of columns in the table and its type.

Step 3. Combine script and command

Now we can use the output of the script for input of machbase-neo shell.

sh gen_wave.sh | machbase-neo shell import --timeformat=s EXAMPLE

A each line of CSV that are generated by shell script is processed in machbase-neo shell import then “import” into EXAMPLE table.

This means also we can write data manually as an exmaple below.

echo "wave.pi,1674860125,3.141592" | machbase-neo shell import -t s EXAMPLE

or

echo "wave.pi,`date +%s`,3.141592" | machbase-neo shell import -t s EXAMPLE

Then let’s query the latest value.

machbase-neo shell "select * from EXAMPLE where NAME='wave.pi' order by time desc limit 1"