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
-bash-3.2# g++ stock_gen.cpp -o stock_gen
-bash-3.2# chmod +x stock_gen
-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;
[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 !
