Command line

machbase-neo serve

Start machbase-neo server process.

Flags

General flags

flag	default	desc
`--host`	`127.0.0.1`	listening network addr ex) `--host 0.0.0.0`
`-c`, `--config`		config file location ex) `--config /data/machbase-neo.conf`
`--pid`		file path to save pid ex) `--pid /data/machbase-neo.pid`
`--data`	`./machbase_home`	path to database ex) `--data /data/machbase`
`--file`	`.`	path to files ex) `--file /data/files`
`--backup-dir`	`./backups`	path to the backup dir ex) `--backup-dir /data/backups` Since v8.0.26
`--pref`	`~/.config/machbase`	path to preference directory path
`--preset`	`auto`	database preset `auto`, `fog`, `edge` ex) `--preset edge`

Log flags

flag	default	desc
`--log-filename`	`-` (stdout)	log file path ex) `--log-filename /data/logs/machbase-neo.log`
`--log-level`	`INFO`	log level. TRACE, DEBUG, INFO, WARN, ERROR ex) `--log-level INFO`
`--log-append`	`true`	append existing log file. Since v8.0.13
`--log-rotate-schedule`	`@midnight`	time scheduled log file rotation Since v8.0.13
`--log-max-size`	`10`	file max size in MB Since v8.0.13
`--log-max-backups`	`1`	maximum log file backups Since v8.0.13
`--log-max-age`	`7`	maximum days in backup files Since v8.0.13
`--log-compress`	`false`	gzip compress the backup files Since v8.0.13
`--log-time-utc`	`false`	use UTC time for logging Since v8.0.13

Listener flags

flag	default	desc
`--shell-port`	`5652`	ssh listen port
`--mqtt-port`	`5653`	mqtt listen port
`--http-port`	`5654`	http listen port
`--grpc-port`	`5655`	grpc listen port
`--grpc-sock`	`mach-grpc.sock`	grpc unix domain socket
`--grpc-insecure`	`false`	set `true` to use plain tcp socket, disable TLS Since v8.0.18
`--mach-port`	`5656`	machbase native listen port

📌

IMPORTANT
Since the default of --host is the loopback address, it is not allowed to access machbase-neo from the remote hosts.
Set --host <host-address> or --host 0.0.0.0 for accepting the network connections from remote clients.

If execute machbase-neo serve with no flags,

$ machbase-neo serve

it is equivalent with

$ machbase-neo serve --host 127.0.0.1 --data ./machbase_home --file . --preset auto

machbase-neo shell

Start machbase-neo shell. It will start interactive mode shell if there are no other arguments.

Flags

flag (long)	default	desc
`-s`, `--server`	`tcp://127.0.0.1:5655`	machbase-neo’s gRPC address. e.g. `-s unix://./mach-grpc.sock` e.g. `--server tcp://127.0.0.1:5655`
`--user`	`sys`	user name. env: `NEOSHELL_USER` Since v8.0.4
`--password`	`manager`	password. env: `NEOSHELL_PASSWORD` Since v8.0.4

When machbase-neo shell starts, it is looking for the user name and password from OS’s environment variables NEOSHELL_USER and NEOSHELL_PASSWORD. Then if the flags --user and --password are provided, it will override the provided values instead of the environment variables.

Precedence of username and password

Command line flags

If --user, --password is provided? Use the given values

Environment variables

If $NEOSHELL_USER (on windows %NEOSHELL_USER%) is set? Use the value as the user name.

If $NEOSHELL_PASSWORD (on windows %NEOSHELL_PASSWORD%) is set? Use the value as the password.

Default

None of those are provided? Use default value sys and manager.

Practical usage

For the security, use instant environment variables as below example.

$ NEOSHELL_PASSWORD='my-secret' machbase-neo shell --user sys

Be aware when you use --password flag, the secret can be exposed by simple ps command as like an example below.

$ machbase-neo shell --user sys --password manager

$ ps -aef |grep machbase-neo
  501 13551  3598   0  9:33AM ttys000    0:00.07 machbase-neo shell --user sys --password manager

Run Query

machbase-neo» select binary_signature from v$version;
┌────────┬─────────────────────────────────────────────┐
│ ROWNUM │ BINARY_SIGNATURE                            │
├────────┼─────────────────────────────────────────────┤
│      1 │ 8.0.2.develop-LINUX-X86-64-release-standard │
└────────┴─────────────────────────────────────────────┘
a row fetched.

Create Table

machbase-neo» create tag table if not exists example (name varchar(20) primary key, time datetime basetime, value double summarized);
executed.

Schema Table

machbase-neo» desc example;
┌────────┬───────┬──────────┬────────┐
│ ROWNUM │ NAME  │ TYPE     │ LENGTH │
├────────┼───────┼──────────┼────────┤
│      1 │ NAME  │ varchar  │     20 │
│      2 │ TIME  │ datetime │      8 │
│      3 │ VALUE │ double   │      8 │
└────────┴───────┴──────────┴────────┘

Insert Table

machbase-neo» insert into example values('tag0', to_date('2021-08-12'), 100);
a row inserted.

Select Table

machbase-neo» select * from example;
┌────────┬──────┬─────────────────────┬───────┐
│ ROWNUM │ NAME │ TIME(LOCAL)         │ VALUE │
├────────┼──────┼─────────────────────┼───────┤
│      1 │ tag0 │ 2021-08-12 00:00:00 │ 100   │
└────────┴──────┴─────────────────────┴───────┘
a row fetched.

Drop Table

machbase-neo» drop table example;
executed.

Sub commands

explain

Syntax explain [--full] <sql>

Shows the execution plan of the sql.

machbase-neo» explain select * from example where name = 'tag.1';
 PROJECT
  TAG READ (RAW)
   KEYVALUE INDEX SCAN (_EXAMPLE_DATA_0)
    [KEY RANGE]
     * IN ()
   VOLATILE INDEX SCAN (_EXAMPLE_META)
    [KEY RANGE]
     *

export

  export [options] <table>
  arguments:
    table                    table name to read
  options:
    -o,--output <file>       output file (default:'-' stdout)
    -f,--format <format>     output format
                csv          csv format (default)
                json         json format
       --compress <method>   compression method [gzip] (default is not compressed)
       --[no-]heading        print header message (default:false)
       --[no-]footer         print footer message (default:false)
    -d,--delimiter           csv delimiter (default:',')
       --tz                  timezone for handling datetime
    -t,--timeformat          time format [ns|ms|s|<timeformat>] (default:'ns')
                             consult "help timeformat"
    -p,--precision <int>     set precision of float value to force round

import

  import [options] <table>
  arguments:
    table                 table name to write
  options:
    -i,--input <file>     input file, (default: '-' stdin)
    -f,--format <fmt>     file format [csv] (default:'csv')
       --compress <alg>   input data is compressed in <alg> (support:gzip)
       --no-header        there is no header, do not skip first line (default)
	   --charset          set character encoding, if input is not UTF-8
       --header           first line is header, skip it
       --method           write method [insert|append] (default:'insert')
       --create-table     create table if it doesn't exist (default:false)
       --truncate-table   truncate table ahead importing new data (default:false)
    -d,--delimiter        csv delimiter (default:',')
       --tz               timezone for handling datetime
    -t,--timeformat       time format [ns|ms|s|<timeformat>] (default:'ns')
                          consult "help timeformat"
       --eof <string>     specify eof line, use any string matches [a-zA-Z0-9]+ (default: '.')

show info

Display the server information.

machbase-neo» show info;
┌────────────────────┬─────────────────────────────┐
│ NAME               │ VALUE                       │
├────────────────────┼─────────────────────────────┤
│ build.version      │ v2.0.0                      │
│ build.hash         │ #c953293f                   │
│ build.timestamp    │ 2023-08-29T08:08:00         │
│ build.engine       │ static_standard_linux_amd64 │
│ runtime.os         │ linux                       │
│ runtime.arch       │ amd64                       │
│ runtime.pid        │ 57814                       │
│ runtime.uptime     │ 2h 30m 57s                  │
│ runtime.goroutines │ 45                          │
│ mem.sys            │ 32.6 MB                     │
│ mem.heap.sys       │ 19.0 MB                     │
│ mem.heap.alloc     │ 9.7 MB                      │
│ mem.heap.in-use    │ 13.0 MB                     │
│ mem.stack.sys      │ 1,024.0 KB                  │
│ mem.stack.in-use   │ 1,024.0 KB                  │
└────────────────────┴─────────────────────────────┘

show ports

Display the server’s interface ports

machbase-neo» show ports;
┌─────────┬────────────────────────────────────────┐
│ SERVICE │ PORT                                   │
├─────────┼────────────────────────────────────────┤
│ grpc    │ tcp://127.0.0.1:5655                   │
│ grpc    │ unix:///database/mach-grpc.sock        │
│ http    │ tcp://127.0.0.1:5654                   │
│ mach    │ tcp://127.0.0.1:5656                   │
│ mqtt    │ tcp://127.0.0.1:5653                   │
│ shell   │ tcp://127.0.0.1:5652                   │
└─────────┴────────────────────────────────────────┘

show tables

Syntax: show tables [-a]

Display the table list. If flag -a is specified, the result includes the hidden tables.

machbase-neo» show tables;
┌────────┬────────────┬──────┬─────────────┬───────────┐
│ ROWNUM │ DB         │ USER │ NAME        │ TYPE      │
├────────┼────────────┼──────┼─────────────┼───────────┤
│      1 │ MACHBASEDB │ SYS  │ EXAMPLE     │ Tag Table │
│      2 │ MACHBASEDB │ SYS  │ TAG         │ Tag Table │
│      3 │ MACHBASEDB │ SYS  │ TAGDATA     │ Tag Table │
└────────┴────────────┴──────┴─────────────┴───────────┘

show table

Syntax show table [-a] <table>

Display the column list of the table. If flag -a is specified, the result includes the hidden columns.

machbase-neo» show table example -a;
┌────────┬───────┬──────────┬────────┬──────────┐
│ ROWNUM │ NAME  │ TYPE     │ LENGTH │ DESC     │
├────────┼───────┼──────────┼────────┼──────────┤
│      1 │ NAME  │ varchar  │    100 │ tag name │
│      2 │ TIME  │ datetime │     31 │ basetime │
│      3 │ VALUE │ double   │     17 │          │
│      4 │ _RID  │ long     │     20 │          │
└────────┴───────┴──────────┴────────┴──────────┘

show meta-tables

machbase-neo» show meta-tables;
┌────────┬─────────┬────────────────────────┬─────────────┐
│ ROWNUM │      ID │ NAME                   │ TYPE        │
├────────┼─────────┼────────────────────────┼─────────────┤
│      1 │ 1000020 │ M$SYS_TABLESPACES      │ Fixed Table │
│      2 │ 1000024 │ M$SYS_TABLESPACE_DISKS │ Fixed Table │
│      3 │ 1000049 │ M$SYS_TABLES           │ Fixed Table │
│      4 │ 1000051 │ M$TABLES               │ Fixed Table │
│      5 │ 1000053 │ M$SYS_COLUMNS          │ Fixed Table │
│      6 │ 1000054 │ M$COLUMNS              │ Fixed Table │
......

show virtual-tables

machbase-neo» show virtual-tables;
┌────────┬─────────┬─────────────────────────────────────────┬────────────────────┐
│ ROWNUM │      ID │ NAME                                    │ TYPE               │
├────────┼─────────┼─────────────────────────────────────────┼────────────────────┤
│      1 │      65 │ V$HOME_STAT                             │ Fixed Table (stat) │
│      2 │      93 │ V$DEMO_STAT                             │ Fixed Table (stat) │
│      3 │     227 │ V$SAMPLEBENCH_STAT                      │ Fixed Table (stat) │
│      4 │     319 │ V$TAGDATA_STAT                          │ Fixed Table (stat) │
│      5 │     382 │ V$EXAMPLE_STAT                          │ Fixed Table (stat) │
│      6 │     517 │ V$TAG_STAT                              │ Fixed Table (stat) │
......

show users

machbase-neo» show users;
┌────────┬───────────┐
│ ROWNUM │ USER_NAME │
├────────┼───────────┤
│      1 │ SYS       │
└────────┴───────────┘
a row fetched.

show license

 machbase-neo» show license;
┌────────┬──────────┬──────────────┬──────────┬────────────┬──────────────┬─────────────────────┐
│ ROWNUM │ ID       │ TYPE         │ CUSTOMER │ PROJECT    │ COUNTRY_CODE │ INSTALL_DATE        │
├────────┼──────────┼──────────────┼──────────┼────────────┼──────────────┼─────────────────────┤
│      1 │ 00000023 │ FOGUNLIMITED │ VUTECH   │ FORESTFIRE │ KR           │ 2024-04-22 15:56:14 │
└────────┴──────────┴──────────────┴──────────┴────────────┴──────────────┴─────────────────────┘
a row fetched.

session list

Syntax: session list Since v8.0.17

 machbase-neo» session list;
┌────┬───────────┬─────────┬────────────┬─────────┬─────────┬──────────┐
│ ID │ USER_NAME │ USER_ID │ STMT_COUNT │ CREATED │ LAST    │ LAST SQL │
├────┼───────────┼─────────┼────────────┼─────────┼─────────┼──────────┤
│ 25 │ SYS       │ 1       │          1 │ 1.667ms │ 1.657ms │ CONNECT  │
└────┴───────────┴─────────┴────────────┴─────────┴─────────┴──────────┘

session kill

Syntax session kill <ID> Since v8.0.17

session stat

Syntax: session stat Since v8.0.17

machbase-neo» session stat;
┌────────────────┬───────┐
│ NAME           │ VALUE │
├────────────────┼───────┤
│ CONNS          │ 1     │
│ CONNS_USED     │ 17    │
│ STMTS          │ 0     │
│ STMTS_USED     │ 20    │
│ APPENDERS      │ 0     │
│ APPENDERS_USED │ 0     │
│ RAW_CONNS      │ 1     │
└────────────────┴───────┘

desc

Syntax desc [-a] <table>

Describe table structure.

machbase-neo» desc example;
┌────────┬───────┬──────────┬────────┬──────────┐
│ ROWNUM │ NAME  │ TYPE     │ LENGTH │ DESC     │
├────────┼───────┼──────────┼────────┼──────────┤
│      1 │ NAME  │ varchar  │    100 │ tag name │
│      2 │ TIME  │ datetime │     31 │ basetime │
│      3 │ VALUE │ double   │     17 │          │
└────────┴───────┴──────────┴────────┴──────────┘

machbase-neo restore

Syntax machbase-neo restore --data <machbase_home_dir> <backup_dir> Since v8.0.17

Restore database from backup.

$ machbase-neo restore --data <machbase home dir>  <backup dir>

machbase-neo version

Show version and engine info.

machbase-neo_version

machbase-neo gen-config

Prints out default config template.

$ machbase-neo gen-config ↵

define DEF {
    LISTEN_HOST       = flag("--host", "127.0.0.1")
    SHELL_PORT        = flag("--shell-port", "5652")
    MQTT_PORT         = flag("--mqtt-port", "5653")
    HTTP_PORT         = flag("--http-port", "5654")
    GRPC_PORT         = flag("--grpc-port", "5655")
......

Last updated on Aug 13, 2024

Config file