Run Queries with Actian Vector CLI
The Vector instance comes pre-installed with the ontimedb demonstration database, which is loaded with actual airline flight data from the U.S. Bureau of Transportation from 1987 to the present—175 million rows.
This first query will show you the year, month, and number of flights per month ordered by year and then month from the ontime table of ontimedb.
Important! The root volume of an Amazon AMI is based on an Elastic Block Storage (EBS) snapshot, a standard practice when creating AMIs. These snapshots are stored on S3 and, as you access blocks on the volume, they are slowly loaded from S3 into EBS and served to the EC2 instance. This means that the first access to blocks of existing files after launching an EC2 instance from an AMI boot can be very slow. However, all subsequent accesses (even between instance reboots) are much faster.
This directly affects the sample queries against the ontimedb database that is included in the AMI, as the disk blocks for the ontimedb database files must be read from the S3 snapshot the first time the query accesses them. Although the sample queries that you will run in the following sections will be slow the first time, all subsequent queries will be fast and will continue to be fast through the EC2 instance restarts. For more information, see the AWS documentation at
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-initialize.html.
To run the sample query
2. Start the sql tool by entering the following command at the $ prompt:
sql ontimedb
3. Specify that the following query be timed. Enter the following command at the * prompt:
\rt
4. Enter the following SQL command (copy and paste):
SELECT count(*) FROM ontime\g
Results are displayed:
5. Check the query execution time at the bottom of the results display.
6. Enter the following query to find the cities with the most flights from 2012:
SELECT TOP 10 origincityname as City, count(*) as Num_of_flights FROM ontime WHERE Year = 2012 GROUP BY origincityname ORDER BY Num_of_flights DESC;
You may run more sample queries from
More Sample Queries.
