Difference between revisions of "AVA Database Management Reference"

Revision as of 15:06, 15 May 2013

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 METHODs 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.