Load Sample Data Using the Vector CLI

Note: To create a database and load it with data in Vector Community Edition, which has a 100 GB size limit, you must first delete the provided ontimedb database. For more information, see Remove the Sample Databases.

So far, you have been able to query data that was made available to you in the ontimedb database. In this exercise, you will create a database, create tables in that database, load data into Vector, and generate statistics. At the end of the exercise, you will have loaded the same amount of data (approximately 175 million rows) that you ran your queries against earlier.

In this exercise, you will create two tables in the demodb database: ontime and carriers. You will create the tables using copy-and-paste query commands.

1. If it it not already started, start the sql tool by entering the following command at the $ prompt:

create table ontime(

create table carriers(ccode char(2) collate ucs_basic, carrier char(25) collate ucs_basic )\g

INSERT INTO carriers VALUES ('AS','Alaska Airlines (AS)'), ('AA','American Airlines (AA)'), ('DL','Delta Air Lines (DL)'), ('EV','ExpressJet Airlines (EV)'), ('F9','Frontier Airlines (F9)'), ('HA','Hawaiian Airlines (HA)'), ('B6','JetBlue Airways (B6)'), ('OO','SkyWest Airlines (OO)'), ('WN','Southwest Airlines (WN)'), ('NK','Spirit Airlines (NK)'), ('UA','United Airlines (UA)'), ('VX','Virgin America (VX)')\g

In this exercise, you will load airline flight data into the empty ontime table you created in the previous exercise.

The bulk loader command for Vector is called vwload. This command can load up to 500,000 rows per second, depending on the speed of your hard drive.

This exercise showcases a couple of vwload options to load part of the sample data and then points you to a handy script that will load all the data for you using one command.

The ontimefiles.zip file contains CSV files with airline flight data from 1987 to the present and must be unzipped before loading the data.

1. In the CLI window, change to the directory where ontimefiles.zip is located and unzip the file:

cd /Vector/sample_data

Copy and paste the following command to load a subset of airline data for January 1988 into the ontime table of the demodb database:

Specifies the target table ontime, the demodb database name, and the data filenames

The vwload command is executed, and the data in the file On_Time_On_Time_Performance_1988_1.csv is loaded into the ontime table of demodb.

To speed data loading, engage multiple cores on your machine using the -c option, which enables parallelization. To show how this works, load all data files for the year 1989. Copy and paste the following command to load the data files into the ontime table of the demodb database:

vwload -H -f , -q '"' -I -c -t ontime demodb On_Time_On_Time_Performance_1989_*.csv

The vwload command is executed, and the data in the 12 files for 1989 is loaded into the ontime table of demodb.

4. Delete the data loaded in previous steps to prepare for the rest of this exercise. Enter the following command:

Now that you know how to load single and multiple files, you can use the parallel load option to load the complete 75-GB data set into Vector.

The loadall.sh script is included in ontimefiles.zip that will load all the years of data using the -c option. Run the load script:

Note: This can take up to 15 minutes, depending on your machine type. The more cores you have, the faster the data will load. Generally, when using the -c option, the number of input files to the vwload command should equal the number of physical cores (not virtual cores) on the machine.

echo "select count(*) from ontime\g" | sql demodb

Vector has a sophisticated cost-based optimizer that uses statistics to optimize queries. We recommend that you collect statistics upon initially loading data and after any subsequent changes to the data.

There are several ways to collect statistics, but for this exercise you will use the command-line utility, optimizedb.

Using vector processing, statistics are generated for both the ontime and carriers tables.

After loading and optimizing data, run some of the queries against demodb using Director or the Vector CLI. See More Sample Queries.

Note: Vector will automatically tune itself to the number of cores and memory on the machine and will use more resources where it can to scale. You may shut down the EC2 instance and resize it to a larger number of cores and check the query times again.