AVA Database Management Reference

SQL Extension for Database Management

All the database management commands starts with ".". For example:

ava>.load 'a'
ava>.save a as 'a'

These functions include saving, loading, compressing tables, displaying schema, database status, etc.

Loading data table

SYNTAX: .load 'LOCATION' <AS> TABLE_NAME

ava> .load 'a'
0.31 seconds elapsed.

For more details, please refer to Quick Start Guide .

Saving data table

SYNTAX: .save TABLE_NAME as "LOCATION"

ava> .save a as 'a2'
0.31 seconds elapsed.

For more details, please refer to Quick Start Guide .

List tables

SYNTAX: .tables

ava> .tables
Number of tables: 1
----------------------
a
0 seconds elapsed.

List table schema

SYNTAX: .schema TABLE_NAME

ava> .schema a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
         date |         sec_id |           open |           high |            low |          close |         volume |      adj_close |
   ---------- |     ---------- |     ---------- |     ---------- |     ---------- |     ---------- |     ---------- |     ---------- |
      DATE(1) |     VARCHAR(8) |       FLOAT(1) |       FLOAT(1) |       FLOAT(1) |       FLOAT(1) |        LONG(1) |       FLOAT(1) |

0 seconds elapsed.

Sort a table by column(s)

SYNTAX: .sort TABLE_NAME order by COL1 ASC|DESC, COL2 ASC|DESC

ava> .sort a order by date asc, sec_id desc
0.683 seconds elapsed.
ava> select date, sec_id, open from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |       open |
---------- | ---------- | ---------- |
2000/10/16 |   sz399001 |   4,478.15 |
2000/10/16 |   sz200992 |       2.77 |
2000/10/16 |   sz200986 |       1.99 |
2000/10/16 |   sz200160 |       2.49 |
2000/10/16 |   sz200152 |       1.89 |
2000/10/16 |   sz000999 |      19.10 |
2000/10/16 |   sz000997 |      33.00 |
2000/10/16 |   sz000996 |      18.80 |
2000/10/16 |   sz000995 |      15.35 |
2000/10/16 |   sz000993 |      13.40 |
0.001 seconds elapsed.

Compress a table

The compression and decompression methods are all lost-less date compression.

SYNTAX: .compress TABLE_NAME <by COMPRESSION METHOD>

The "COMPRESSION METHOD"s are LZF, LZ4, LZMA. If the compression commands are repeated with different method, the later command will always convert previous the compression to the new compression. All methods are inline, in-memory. The LZF and LZ4 methods are high-speed methods with lower compression ratio. The LZMA method produces higher compression ratio, but the speed is much lower.

ava> .compress a
0.412 seconds elapsed.

Deompress a table

SYNTAX: .compress TABLE_NAME

ava> .decompress a
0.143 seconds elapsed.

Global Variables

Global Variables stays as long as an AVA instance (process). All global variables starts with the forward slash "/". For example:

ava>/v 
[v] verbose mode: On
0 seconds elapsed.

The variable command without value will return the current variable being set. Otherwise, The variable will be replaced with the new value. For example:

ava>/v 
[v] verbose mode: On
0 seconds elapsed.

ava>/v false

verbose mode

If turned on, the runtime will be reported at the end of the returned screen. For example:

ava> .load 'a'
0.284 seconds elapsed.
ava> /v false
ava> .load 'a'
ava>

number of rows returning to screen

Specify the number of rows being reported on the screen. For example:

ava> /nr 5
ava> select date, sec_id from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |
---------- | ---------- |
2000/10/16 |   ss600008 |
2000/10/16 |   ss600018 |
2000/10/16 |   ss600130 |
2000/10/16 |   ss600158 |
2000/10/16 |   ss600208 |

precision of the floating numbers reported to screen

ava> /p
[p] precision: 5
ava> /p 7
ava> select date, sec_id, open from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |       open |
---------- | ---------- | ---------- |
2000/10/16 |   ss600008 | 20.4000000 |
2000/10/16 |   ss600018 | 20.1600000 |
2000/10/16 |   ss600130 | 28.0800000 |
2000/10/16 |   ss600158 | 20.2500000 |
2000/10/16 |   ss600208 | 14.5000000 |

whether to report the rid on screen

The default value is "false".

ava> /rid true
ava> select date, sec_id, open from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
    row_id |       date |     sec_id |       open |
---------- | ---------- | ---------- | ---------- |
         0 | 2000/10/16 |   ss600008 |   20.40000 |
         1 | 2000/10/16 |   ss600018 |   20.16000 |
         2 | 2000/10/16 |   ss600130 |   28.08000 |
         3 | 2000/10/16 |   ss600158 |   20.25000 |
         4 | 2000/10/16 |   ss600208 |   14.50000 |

Group method

The method/algorithm to perform the "group by" clause. The choices are hash, sort or auto. Defaults to auto. The auto is a smart algorithm to determine the proper method internally.

ava>/gm hash

Table Join method

The method/algorithm to perform the table joins. The choices are hash, sort or auto. Defaults to auto. The auto is a smart algorithm to determine the proper method internally.

ava>/jm hash