Difference between revisions of "Quick Start Guide"

Revision as of 19:06, 31 January 2020

Command Line Interface Conventions

AVA uses both SQL and its own proprietary command line database management languages.

Non-Standard SQL

Command starts with ".", for example:

 .load 'a'
 .save a as 'a'
 .schema a

Database Global Parameter Setting

Command starts with "/", for example:

 /v false
 /nr 100

Standard SQL

Command of SQL '92 standard and other AVA proprietary query language ( No prefix or special character is needed ). For example:

select * from a
update a: c - 1 from a

Loading and Saving Data Table

AVA database can launched from command line. To launch a data table from disk ( data table name "a" ):

ava> .load 'a'
0.28 seconds elapsed.

The load command will automatically decompress the binary file if the table was previously compressed.

To quit the instance without saving the table, simple exit:

ava> exit
Goodbye!

To save the table ( to commit all the changes to the data table for eventual persistence ):

ava> .save a as 'new_table'
3.187 seconds elapsed.

SYNTAX

• ava>　.load 'FILE_NAME' (as TABLE_NAME)
• ava>　.save TABLE_NAME as 'FILE_NAME'

Load an AVA data table ( binary )

AVA data table is a binary table in proprietary AVA format stored on disk. To load an AVA table:

ava> .load 'a'
0.291 seconds elapsed.
ava> select * from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |       open |       high |        low |      close |     volume |  adj_close |
---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- |
2000/10/16 |   ss600008 |      20.40 |      20.58 |      20.18 |      20.58 |     797800 |      16.87 |
2000/10/16 |   ss600018 |      20.16 |      20.50 |      20.15 |      20.20 |     346400 |      17.60 |
2000/10/16 |   ss600130 |      28.08 |      28.29 |      27.16 |      27.23 |     438300 |      24.89 |
2000/10/16 |   ss600158 |      20.25 |      20.30 |      19.60 |      19.70 |     571400 |      18.73 |
2000/10/16 |   ss600208 |      14.50 |      14.61 |      14.44 |      14.58 |     138900 |      14.02 |
0 seconds elapsed.

The "as" clause is optional. If not specified, the table name will be derived from the internal table name contained within the binary.

Save an AVA data table ( binary )

Save to a file without specifying the suffix of the file name:

ava> .save a as 'new_table'
3.187 seconds elapsed.

Load a csv data table

ava> .load 'a.csv' as a
Loading a.csv... 3252871 records loaded.
4.086 seconds elapsed.
ava> select * from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |       open |       high |        low |      close |     volume |  adj_close |
---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- |
2000-10-16 |   ss600008 |      20.40 |      20.58 |      20.18 |      20.58 | 797,800.00 |      16.87 |
2000-10-16 |   ss600018 |      20.16 |      20.50 |      20.15 |      20.20 | 346,400.00 |      17.60 |
2000-10-16 |   ss600130 |      28.08 |      28.29 |      27.16 |      27.23 | 438,300.00 |      24.89 |
2000-10-16 |   ss600158 |      20.25 |      20.30 |      19.60 |      19.70 | 571,400.00 |      18.73 |
2000-10-16 |   ss600208 |      14.50 |      14.61 |      14.44 |      14.58 | 138,900.00 |      14.02 |
0.001 seconds elapsed.

The "as" clause is optional. If not specified, the table name will be called "temp".

While loading data table from a csv file, all numerical columns will be converted into FLOAT, and all non-numerical data will be converted into VARCHAR.

Save a csv data table

Save to a file specifying that the suffix of the file name is ".csv":

ava> .save a as 'new_table.csv'
3.187 seconds elapsed.