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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
Related searches
- java code examples hello world
- vba code examples for access
- java code examples pdf
- linux commands and meaning
- independent and dependent variables examples in science
- 1s and 0s code translator
- 1 and 0 code translator
- python commands and functions pdf
- dependent variable and independent variable examples biology
- android terminal commands and hacks
- all python commands and uses
- independent and dependent variables examples for kids