VoltDB Commands and SP Code Examples CIS 612 Sunnie …

VoltDB Commands and SP Code Examples

CIS 612 Sunnie Chung

Tested with VoltDB Enterprise version 4.2 for Mac

VoltDB is not like traditional database products because there is no generic database. Instead, each VoltDB database is optimized for a specific application by compiling the schema, stored procedure, and partitioning information into VoltDB application catalog. The catalog then will be loaded on or more lost machines to create a distributed database.

Example: a schema is saved in a text file towns.sql

CREATE TABLE towns ( town VARCHAR(128), county VARCHAR(64), state VARCHAR(2)

);

Compiling the Application Catalog:

$ voltdb compile towns.sql -----------------------------------------Successfully created catalog.jar Includes schema: towns.sql [MP][WRITE] TOWNS.insert

INSERT INTO TOWNS VALUES (?, ?, ?); -----------------------------------------Catalog contains 1 built-in CRUD procedures.

Simple insert, update, delete and select procedures are created automatically for convenience. -----------------------------------------Full catalog report can be found at or can be viewed at "" when the server is running. ------------------------------------------

or to name the catalog (default is catalog.jar)

1

$ voltdb compile ?o towns.jar towns.sql

Starting the Database:

$ voltdb create towns.jar Initializing VoltDB... Build: 4.2 voltdb-4.2-0-gc9751d3-local Enterprise Edition Connecting to VoltDB cluster as the leader... Host id of this node is: 0 Starting VoltDB with trial license. License expires on May 17, 2014. Initializing the database and command logs. This may take a moment... WARN: This is not a highly available cluster. K-Safety is set to 0. Server completed initialization.

Check report, schema, procedure, etc. at

1. Command Line interface VoltDB provides a SQL shell interpreter that allows users to execute VoltDB SQL and Stored Procedure interactively as well as non-interactively via scripts. VOLTDB provides a command line interface, which can be accessed through sqlcmd

$ sqlcmd SQL Command :: localhost:21212 1>

Three key options at the sqlcmd prompt: - SQL queries: for ad hoc SQL queries - Procedure calls: execute stored procedures

2

- Exit: to exit interactive session

2. VoltDB Query/Syntax

VoltDB supports a subset of ANSI-standard SQL 99, including CREATE INDEX, CREATE TABLE, and CREATE VIEW for schema definition and SELECT, INSERT, UPDATE, and DELETE for data manipulation.

Insert statement:

1> insert into towns values ('Billerica','Middlesex','MA'); (1 row(s) affected) 2> insert into towns values ('Buffalo','Erie','NY'); (1 row(s) affected) 3> insert into towns values ('Bay View','Erie','OH'); (1 row(s) affected)

Select statement:

4> select count(*) as total from towns; TOTAL ------

3

(1 row(s) affected)

5> select town, state from towns ORDER BY town; TOWN STATE ---------- -----Bay View OH Billerica MA Buffalo NY

(3 row(s) affected)

Exit:

6> exit

3. Input

CSV and TXT files are standard input files to be loaded into VoltDB database. VoltDB provides a simplified CSV loader through shell script csvloader. Command:

csvloader tableName < dataFile.csv csvloader tableName ?f dataFile.csv

Example:

3

Create a database with two tables towns and people from a schema saved in towns.sql

$ voltdb compile -o towns.jar towns.sql $ voltdb create towns.jar

Prepare input files

$ cut -d'|' -f2,4-7,16 POP_PLACES_20140401.txt | grep -v '|$' | grep -v '||' > towns.txt

Loading the data:

$ csvloader --separator "|" --skip 1 --file towns.txt towns Read 194465 rows from file and successfully inserted 194465 rows (final) Elapsed time: 4.989 seconds Invalid row file:

/Users/nqt289/Desktop/voltdb/csvloader_TOWNS_insert_invalidrows.csv Log file: /Users/nqt289/Desktop/voltdb/csvloader_TOWNS_insert_log.log Report file: /Users/nqt289/Desktop/voltdb/csvloader_TOWNS_insert_report.log

Querying the Database:

1> SELECT town,state,elevation from towns order by elevation desc limit 5;

TOWN

STATE ELEVATION

------------------------- ------ ----------

Corona (historical) CO

3573

Quartzville (historical) CO

3527

Logtown (historical) CO

3524

Tomboy (historical) CO

3508

Rexford (historical) CO

3484

(5 row(s) affected)

2> select town, count(town) as duplicates from towns 3> group by town order by duplicates desc limit 5;

TOWN DUPLICATES

------------ -----------

Midway

214

Fairview

211

Oak Grove

167

Five Points

150

Riverside

130

(5 row(s) affected)

Load another file: people.txt

$ csvloader --file people.txt --skip 1 people

4

Read 3143 rows from file and successfully inserted 1802 rows (final) Elapsed time: 0.467 seconds

Check "people" table

1> select * from people order by population desc limit 5;

STATE_NUM COUNTY_NUM STATE COUNTY

---------- ----------- ----------- ------------------- -----------

6

37 California Los Angeles County 9818605

17

31 Illinois Cook County

5194675

4

13 Arizona Maricopa County 3817117

6

73 California San Diego County 3095313

6

59 California Orange County

3010232

POPULATION

(5 row(s) affected)

Perform join tables

2> select top 5 min(t.elevation) as height, 3> t.state,t.county, max(p.population) 4> from towns as t, people as p 5> where t.state_num=p.state_num and t.county_num=p.county_num 6> group by t.state, t.county order by height desc; HEIGHT STATE COUNTY C4 ------- ------ --------- ------

2754 CO Lake 7310 2640 CO Hinsdale 843 2609 CO Mineral 712 2523 CO San Juan 699 2452 CO Summit 27994

(5 row(s) affected)

4. Save and Recover As VoltDB uses memory for operational storage unit, it provides tool to save database snapshots. Snapshots are a complete disk-based representation of a VoltDB database, including everything needed to reproduce the database after a shutdown. Save:

$ voltadmin save /Users/nqt289/Desktop/voltdb/voltdbroot/snapshots/ "townsandpeople"

-- Snapshot Save Results --

HOST_ID HOSTNAME

TABLE RESULT ERR_MSG

------- ------------------------ ------ ------- -------

0 Thuats-MacBook-Pro.local PEOPLE SUCCESS

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download