Vertica Stock Schema Example Database
Here is a copy of the scripts that will generate the data and the metadata to create a TickStore_Schema in Vertica.
- i hope HP won't mind me sharing this with the community so others can play with it and learn how Vertica works.
- the bundle comes also with various scripts that uses the generated data to create reports and do analisis on top of it.
- Download the zipped files from here TickStore_Schema
- Create an new folder and unzip it inside.
bash-3.2# pwd
/tmp/schemas/TickStore_Schema- To build:
-bash-3.2# g++ stock_gen.cpp -o stock_gen
-bash-3.2# chmod +x stock_gen- Generate the data:
[dbadmin@DCG023 TickStore_Schema]$ ./tickstore_gen
--datadirectory /tmp/tickstore_schema
--trades_days 5
--quotes_days 20
--symbols_file symbols.dat
--trades_start 12202007
--quotes_start 12012001
Data Directory: /tmp/tickstore_schema
Average Trades Per Day: 1000000
Average Quotes Per Day: 1000000
Total Number of Days of Trades: 5
Total Number of Days of Quotes: 20
Start Day (Trades): 12202007
Start Day (Quotes): 12012001
Random Seed: 20177
Print Format: '|' delimited
Loaded 8673 Symbols
Record Type: Trades
Adjusted First Day (Trades): 12202007
Start Day (Trades): Thursday
Record Type: Quotes
Adjusted First Day (Quotes): 12032001
Start Day (Quotes): Monday- A new directory will be create in /tmp/tickstore_schema
[dbadmin@DCG023 TickStore_Schema]$ cd /tmp/tickstore_schema/
[dbadmin@DCG023 tickstore_schema]$ ll
total 3058512
-rw-r--r-- 1 dbadmin verticadba 2553422996 Apr 30 15:32 Quotes_Fact.tbl
-rw-r--r-- 1 dbadmin verticadba 575419022 Apr 30 15:30 Trades_Fact.tbl- Create the schema and the tables on your database
[dbadmin@DCG023 tickstore_schema]$ cd /tmp/schemas/TickStore_Schema/
[dbadmin@DCG023 TickStore_Schema]$ ll
total 272
-rw-r--r-- 1 dbadmin verticadba 1259 Jul 3 2014 README
-rw-r--r-- 1 dbadmin verticadba 71682 Jul 3 2014 symbols.dat
-rw-r--r-- 1 dbadmin verticadba 68 Jul 3 2014 tickstore_count_data.sql
-rw-r--r-- 1 dbadmin verticadba 834 Jul 3 2014 tickstore_define_schema.sql
-rwxr-xr-x 1 dbadmin verticadba 26558 Apr 30 15:28 tickstore_gen
-rw-r--r-- 1 dbadmin verticadba 21604 Jul 3 2014 tickstore_gen.cpp
-rw-r--r-- 1 dbadmin verticadba 266 Apr 28 14:36 tickstore_load_data_datadir.sql
-rw-r--r-- 1 dbadmin verticadba 250 Jul 3 2014 tickstore_load_data.sql
-rw-r--r-- 1 dbadmin verticadba 14043 Jul 3 2014 tickstore_queries.sql
-rw-r--r-- 1 dbadmin verticadba 390 Jul 3 2014 tickstore_query_01.sql
-rw-r--r-- 1 dbadmin verticadba 558 Jul 3 2014 tickstore_query_02.sql
-rw-r--r-- 1 dbadmin verticadba 510 Jul 3 2014 tickstore_query_03.sql
-rw-r--r-- 1 dbadmin verticadba 573 Jul 3 2014 tickstore_query_04.sql
-rw-r--r-- 1 dbadmin verticadba 468 Jul 3 2014 tickstore_query_05.sql
-rw-r--r-- 1 dbadmin verticadba 468 Jul 3 2014 tickstore_query_06.sql
-rw-r--r-- 1 dbadmin verticadba 414 Jul 3 2014 tickstore_query_07.sql
-rw-r--r-- 1 dbadmin verticadba 396 Jul 3 2014 tickstore_query_08.sql
-rw-r--r-- 1 dbadmin verticadba 541 Jul 3 2014 tickstore_query_09.sql
-rw-r--r-- 1 dbadmin verticadba 533 Jul 3 2014 tickstore_query_10.sql
-rw-r--r-- 1 dbadmin verticadba 797 Jul 3 2014 tickstore_query_11.sql
-rw-r--r-- 1 dbadmin verticadba 522 Jul 3 2014 tickstore_query_12.sql
-rw-r--r-- 1 dbadmin verticadba 709 Jul 3 2014 tickstore_query_13.sql
-rw-r--r-- 1 dbadmin verticadba 594 Jul 3 2014 tickstore_query_14.sql
-rw-r--r-- 1 dbadmin verticadba 377 Jul 3 2014 tickstore_query_15.sql
-rw-r--r-- 1 dbadmin verticadba 506 Jul 3 2014 tickstore_query_16.sql
-rw-r--r-- 1 dbadmin verticadba 515 Jul 3 2014 tickstore_query_17.sql
-rw-r--r-- 1 dbadmin verticadba 497 Jul 3 2014 tickstore_query_18.sql
-rw-r--r-- 1 dbadmin verticadba 489 Jul 3 2014 tickstore_query_19.sql
-rw-r--r-- 1 dbadmin verticadba 639 Jul 3 2014 tickstore_query_20.sql
-rw-r--r-- 1 dbadmin verticadba 383 Jul 3 2014 tickstore_query_21.sql
-rw-r--r-- 1 dbadmin verticadba 455 Jul 3 2014 tickstore_query_22.sql
-rw-r--r-- 1 dbadmin verticadba 571 Jul 3 2014 tickstore_query_23.sql
-rw-r--r-- 1 dbadmin verticadba 598 Jul 3 2014 tickstore_query_24.sql
-rw-r--r-- 1 dbadmin verticadba 759 Jul 3 2014 tickstore_query_25.sql
-rw-r--r-- 1 dbadmin verticadba 832 Jul 3 2014 tickstore_query_26.sql
-rw-r--r-- 1 dbadmin verticadba 85 Jul 3 2014 tickstore_schema_drop.sql
[dbadmin@DCG023 TickStore_Schema]$ vsql -f tickstore_define_schema.sql
Password:
CREATE TABLE
CREATE TABLE- Load the generated data into your tables:
- you just need to alter the tickstore_load_data.sql by adding the full path to the directory where we generated the data in.
set t_pwd /tmp/tickstore_schema
set input_file '''':t_pwd'/Trades_Fact.tbl'''
COPY Trades_Fact FROM :input_file DELIMITER '|' NULL E'\n' DIRECT;- Run the load script
[dbadmin@DCG023 TickStore_Schema]$ vsql -f tickstore_load_data.sql
Password:
Timing is on.
Rows Loaded
-------------
4831788
(1 row)
Time: First fetch (1 row): 23103.882 ms. All rows formatted: 23103.999 ms
Rows Loaded
-------------
19779249
(1 row)
Time: First fetch (1 row): 89251.802 ms. All rows formatted: 89251.873 ms- quite fast for VBox single node Vertica Cluster with 1 Gb RAM + 1 Cpu :).
