How to Generate a Stock Schema Example for Vertica Database

Here is a copy of the scripts that will generate the data and the metadata to create a  Stock_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.
To download and install the Stock schema follow the steps bellow:
  • Download the zipped files from here Stock_Schema.
  • Create an new folder and unzip it inside.
bash-3.2# pwd
/tmp/schemas/Stock_Schema
  •  To build:
-bash-3.2# g++ stock_gen.cpp -o stock_gen
-bash-3.2# chmod +x stock_gen
  •  Generate the data:
-bash-3.2# pwd
/tmp/schemas/Stock_Schema
-- run the following command
-bash-3.2# ./stock_gen 
         --datadirectory /tmp/stock_schema 
         --time_file Time.txt 
         --stock_dimension 273 
         --trader_dimension 200 
         --split_dimension 500 
         --stocktransaction_fact 100000
datadirectory = /tep/stock_schema
numfiles = 1
numfactrows = 100000
numstockkeys = 273
numsplitkeys = 500
numtraderkeys = 200
random# = 20177
timefile = Time.txt
Data Generated successfully !
  • A new directory will be create in /tmp/stock_schema
-bash-3.2# cd /tmp/stock_schema/
-bash-3.2# ll
total 7088
-rw-r--r-- 1 root root  204118 Apr 29 16:54 Date_Dimension.tbl
-rw-r--r-- 1 root root     165 Apr 29 16:54 Exchange_Dimension.tbl
-rw-r--r-- 1 root root    6012 Apr 29 16:54 Settlement_Dimension.tbl
-rw-r--r-- 1 root root   14698 Apr 29 16:54 Split_Dimension.tbl
-rw-r--r-- 1 root root   26149 Apr 29 16:54 Stock_Dimension.tbl
-rw-r--r-- 1 root root 6970893 Apr 29 16:54 StockTransaction_Fact.tbl
-rw-r--r-- 1 root root    4837 Apr 29 16:54 Trader_Dimension.tbl
-bash-3.2# head -2 Date_Dimension.tbl
1|01/01/2000|January 1, 2000|Sunday|1|1|1|1|1|0|52|January|1|2000-1|1|2000-Q1|1|2000|Holiday|Weekend
2|01/02/2000|January 2, 2000|Monday|2|2|2|2|0|0|52|January|1|2000-1|1|2000-Q1|1|2000|NonHoliday|Weekday
  • Create the schema and the tables on your database
-bash-3.2# su - dbadmin
[dbadmin@DCG023 ~]$ cd /tmp/schemas/Stock_Schema
[dbadmin@DCG023 Stock_Schema]$ ll
total 188
-rw-r--r-- 1 dbadmin verticadba   928 Jul  3  2014 README
-rw-r--r-- 1 dbadmin verticadba   315 Jul  3  2014 stock_count_data.sql
-rw-r--r-- 1 dbadmin verticadba  3900 Jul  3  2014 stock_define_schema.sql
-rwxr-xr-x 1 root    root       38329 Apr 29 16:51 stock_gen
-rw-r--r-- 1 dbadmin verticadba 33103 Jul  3  2014 stock_gen.cpp
-rw-r--r-- 1 dbadmin verticadba   913 Jul  3  2014 stock_load_data_datadir.sql
-rw-r--r-- 1 dbadmin verticadba   913 Jul  3  2014 stock_load_data.sql
-rw-r--r-- 1 dbadmin verticadba  4793 Jul  3  2014 stock_queries.sql
-rw-r--r-- 1 dbadmin verticadba   652 Jul  3  2014 stock_query_01.sql
-rw-r--r-- 1 dbadmin verticadba   902 Jul  3  2014 stock_query_02.sql
-rw-r--r-- 1 dbadmin verticadba   768 Jul  3  2014 stock_query_03.sql
-rw-r--r-- 1 dbadmin verticadba   831 Jul  3  2014 stock_query_04.sql
-rw-r--r-- 1 dbadmin verticadba   690 Jul  3  2014 stock_query_05.sql
-rw-r--r-- 1 dbadmin verticadba   950 Jul  3  2014 stock_query_06.sql
-rw-r--r-- 1 dbadmin verticadba   294 Jul  3  2014 stock_schema_drop.sql
-rw-r--r-- 1 dbadmin verticadba 50808 Jul  3  2014 Time.txt
[dbadmin@DCG023 Stock_Schema]$ vsql -f stock_define_schema.sql
Password:
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
  •  Load the generated data into your tables:
  • you just need to alter the stock_load_data.sql by adding the full path to the directory where we generated the data in.
set t_pwd /tmp/stock_schema

set input_file '''':t_pwd'/Date_Dimension.tbl'''
COPY Date_Dimension FROM :input_file DELIMITER '|' NULL E'\n' DIRECT;
  •  Run the load script
[dbadmin@DCG023 Stock_Schema]$ vsql -f stock_load_data.sql
Password:
 Rows Loaded
-------------

           0
(1 row)

 Rows Loaded
-------------

         200
(1 row)

 Rows Loaded
-------------

           7
(1 row)

 Rows Loaded
-------------

         180
(1 row)

 Rows Loaded
-------------

         500
(1 row)

 Rows Loaded
-------------

         273
(1 row)

 Rows Loaded
-------------

      100000
(1 row)
Done your Stock Schema is up and ready for you to play with !