Installing and connecting to the VMart example database

• 1: Quick installation using a script
• 2: Advanced installation
• 2.1: Step 1: setting up the example environment
• 2.2: Step 2: creating the example database
• 2.3: Step 3: connecting to the database
• 2.4: Step 4: defining the database schema
• 2.5: Step 5: loading data

1 - Quick installation using a script

The script you need to perform a quick installation is located in /opt/vertica/sbin and is called install_example. This script creates a database on the default port (5433), generates data, creates the schema and a default superprojection, and loads the data. The folder also contains a delete_example script, which stops and drops the database.

1. In a terminal window, log in as the database administrator.

   $ su dbadmin

   Password: (your password)

2. Change to the /examples directory.

   $ cd /opt/vertica/examples

3. Run the install script:

   $ /opt/vertica/sbin/install_example VMart

After installation, you should see the following:

[dbadmin@localhost examples]$ /opt/vertica/sbin/install_example VMart
Installing VMart example example database
Mon Jul 22 06:57:40 PDT 2013
Creating Database
Completed
Generating Data. This may take a few minutes.
Completed
Creating schema
Completed
Loading 5 million rows of data. Please stand by.
Completed
Removing generated data files
Example data

The example database log files, ExampleInstall.txt and ExampleDelete.txt, are written to /opt/vertica/examples/log.

To start using your database, continue to Connecting to the Database in this guide. To drop the example database, see Restoring the Status of Your Host in this guide.

2 - Advanced installation

To perform an advanced-but-simple installation, set up the VMart example database environment and then create the database using the Administration Tools or Management Console.

The advanced installation requires the following steps:

2.1 - Step 1: setting up the example environment

1. Stop all databases running on the same host on which you plan to install your example database.

   If you are unsure if other databases are running, run the Administration Tools and select View Cluster State. The State column should show DOWN values on pre-existing databases.

   If databases are running, click Stop Database in the Main Menu of the Administration Tools interface and click OK.

2. In a terminal window, log in as the database administrator:

   $ su dbadmin
   Password: 
   

3. Change to the /VMart_Schema directory.

   $ cd /opt/vertica/examples/VMart_Schema
   

   Do not change directories while following this tutorial. Some steps depend on being in a specific directory.

4. Run the sample data generator.

   
   $ ./vmart_gen
   

5. Let the program run with the default parameters, which you can review in the README file.

   Using default parameters
   datadirectory = ./
   numfiles = 1
   seed = 2
   null = ' '
   timefile = Time.txt
   numfactsalesrows = 5000000
   numfactorderrows = 300000
   numprodkeys = 60000
   numstorekeys = 250
   numpromokeys = 1000
   numvendkeys = 50
   numcustkeys = 50000
   numempkeys = 10000
   numwarehousekeys = 100
   numshippingkeys = 100
   numonlinepagekeys = 1000
   numcallcenterkeys = 200
   numfactonlinesalesrows = 5000000
   numinventoryfactrows = 300000
   gen_load_script = false
   Data Generated successfully !
   Using default parameters
   datadirectory = ./
   numfiles = 1
   seed = 2
   null = ' '
   timefile = Time.txt
   numfactsalesrows = 5000000
   numfactorderrows = 300000
   numprodkeys = 60000
   numstorekeys = 250
   numpromokeys = 1000
   numvendkeys = 50
   numcustkeys = 50000
   numempkeys = 10000
   numwarehousekeys = 100
   numshippingkeys = 100
   numonlinepagekeys = 1000
   numcallcenterkeys = 200
   numfactonlinesalesrows = 5000000
   numinventoryfactrows = 300000
   gen_load_script = false
   Data Generated successfully !
   

6. If the vmart_gen executable does not work correctly, recompile it as follows, and run the sample data generator script again.

   $ g++ vmart_gen.cpp -o vmart_gen
   $ chmod +x vmart_gen
   $ ./vmart_gen
   

2.2 - Step 2: creating the example database

To create the example database: use the Administration Tools or Management Console, as described in this section.

Creating the example database using the administration tools

In this procedure, you create the example database using the Administration Tools. To use the Management Console, go to the next section.

1. Run the Administration Tools.

   $ /opt/vertica/bin/admintools
   

   or simply type admintools

2. From the Administration Tools Main Menu, click Configuration Menu and click OK.

3. Click Create Database and click OK.

4. Name the database VMart and click OK.

   

5. Click OK to bypass the password and click Yes to confirm.

   There is no need for a database administrator password in this tutorial. When you create a production database, however, always specify an administrator password. Otherwise, the database is permanently set to trust authentication (no passwords).

6. Select the hosts you want to include from your Vertica cluster and click OK.

   This example creates the database on a one-host cluster. Vertica recommends a minimum of three hosts in the cluster. If you are using the Vertica Community Edition, you are limited to three nodes.

   

7. Click OK to select the default paths for the data and catalog directories.

   

   • Catalog and data paths must contain only alphanumeric characters and cannot have leading space characters. Failure to comply with these restrictions could result in database creation failure.

   • When you create a production database, you’ll likely specify other locations than the default. See Prepare Disk Storage Locations in the Administrator’s Guide for more information.

8. Since this tutorial uses a one-host cluster, a K-safety warning appears. Click OK.

   

9. Click Yes to create the database.

   

   During database creation, Vertica automatically creates a set of node definitions based on the database name and the names of the hosts you selected and returns a success message.

10. Click OK to close the Database VMart created successfully message.

    

Creating the example database using Management Console

In this procedure, you create the example database using Management Console. To use the Administration Tools, follow the steps in the preceding section.

1. Connect to Management Console and log in.

2. On the Home page, click Infrastructure to go to the Databases and Clusters page.

3. Click to select the appropriate existing cluster and click Create Database.

4. Follow the on-screen wizard, which prompts you to provide the following information:

   • Database name, which must be between 3–25 characters, starting with a letter, and followed by any combination of letters, numbers, or underscores.

   • (Optional) database administrator password for the database you want to create and connect to.

   • IP address of a node in your database cluster, typically the IP address of the administration host.

5. Click Next.

2.3 - Step 3: connecting to the database

Regardless of the installation method you used, follow these steps to connect to the database.

1. As dbadmin, run the Administration Tools.

   $ /opt/vertica/bin/admintools

   or simply type admintools.

2. If you are already in the Administration Tools, navigate to the Main Menu page.

3. Select Connect to Database, click OK.

   

   To configure and load data into the VMart database, complete the following steps:

   • Step 4: defining the database schema
   • Step 5: loading data

   If you installed the VMart database using the Quick Installation method, the schema, tables, and data are already defined. You can choose to drop the example database (see Restoring the Status of Your Host in this guide) and perform the Advanced Installation, or continue straight to Querying Your Data in this guide.

2.4 - Step 4: defining the database schema

The VMart database installs with sample scripts with SQL commands that are intended to represent queries that might be used in a real business. The vmart_define_schema.sql script runs a script that defines the VMart schema and creates tables. You must run this script before you load data into the VMart database.

This script performs the following tasks:

• Defines two schemas in the VMart database schema: online_sales and store.

• Defines tables in both schemas.

• Defines constraints on those tables.

Vmart=> \i vmart_define_schema.sql
CREATE SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE

2.5 - Step 5: loading data

Now that you have created the schemas and tables, you can load data into a table by running the vmart_load_data.sql script. This script loads data from the 15 .tbl text files in /opt/vertica/examples/VMart_Schema into the tables that vmart_design_schema.sql created.

It might take several minutes to load the data on a typical hardware cluster. Check the load status by monitoring the vertica.log file, as described in Monitoring Log Files in the Administrator’s Guide.

VMart=> \i vmart_load_data.sql
Rows Loaded
-------------
1826
(1 row)
Rows Loaded
-------------
60000
(1 row)
Rows Loaded
-------------
250
(1 row)
Rows Loaded
-------------
1000
(1 row)
Rows Loaded
-------------
50
(1 row)
Rows Loaded
-------------
50000
(1 row)
Rows Loaded
-------------
10000
(1 row)
Rows Loaded
-------------
100
(1 row)
Rows Loaded
-------------
100
(1 row)
Rows Loaded
-------------
1000
(1 row)
Rows Loaded
-------------
200
(1 row)
Rows Loaded
-------------
5000000
(1 row)
Rows Loaded
-------------
300000
(1 row)
VMart=>