Introducing NuoDB - An Elastically Scalable Cloud Database

curator's note: this is the first in a series of three articles that explore nuodb. you can learn more about nuodb, as well as access a free trial, by heading over to the nuodb website .

introduction

traditional relational databases are built upon a synchronous, client-server architecture that is often limited in terms of scalability requirements that are posed by distributed computing systems. as a result, various sharding, caching, and replication techniques emerged to cope with these demands. on the other hand, nosql solutions have emerged on the ground of the cap theorem. data management systems like bigtable, hbase, mongodb, cassandra, and dynamo offer different capabilities depending on how they balance consistency, availability, and partition tolerance. however, they gave up supporting sql and acid properties, which are critical in the relational database world.

nuodb is a complete re-think of relational databases that is built on a new foundation: partial, on-demand replication. under the hood, nuodb is an asynchronous, decentralized, peer-to-peer database. it uses the concept of atoms, which are objects that are being replicated. in nuodb, everything is an atom; database, schema, sequence, table, index, records, blobs, data. nuodb holds a patent on this peer-to-peer object replication.

nuodb architecture

nuodb architecture has three layers: management, sql, and data. the management layer is comprised of an agent that manages the nuodb processes running on a particular computer. it starts and stops them, and it also collects statistics from the transaction and storage engines. certain agents are configured to be a broker . brokers communicate with the client initially and then introduce the client to the transaction engine. from then on, the client can communicate directly with the transaction engines. nuodb’s management layer also offers a command line and a web-based management tool to manage the databases, as well as a command line loader for exporting and importing data.

at the sql layer , transaction engines provide access to a single database.the transaction engine parses, compiles, optimizes, and executes the sql statements on behalf of the clients.

at the data layer, storage managers provide persistence of the data. a storage manager uses key/value pairs to store information, but it can also use more sophisticated stores e.g. hdfs.

in the case of a minimal configuration, we can run every component (broker, transaction engine, and storage manager) on the same machine. nuodb can be easily scaled out and can be made redundant by adding multiple brokers, transaction engines, and storage managers. in more complex scenarios, we can run nuodb in the aws cloud or across multiple corporate datacenters in order to provide geo-redundancy. below is an example of a redundant architecture with two brokers, two transaction engines, and two storage managers.

getting started

nuodb is available on multiple platforms, including windows 32 and 64-bit, linux 64-bit (ubuntu, rhel, suse), mac osx 10.7, and solaris 11 (inter 64-bit).  for this tutorial, we used an ubuntu 12.04 lts virtual machine.

first, we need to start up the components as discussed above, beginning with the broker/agent. then, from the command line management tool, we can start up the transaction engine and the storage manager. we also need to configure the properties file to contain the settings for the domain.

$ vi ./etc/stock.properties
# a flag specifying whether this agent should be run as a connection broker
broker = true
# the name used to identify the domain that this agent is a part of 
domain = stock
# the default administrative password, and the secret used by agents to
# setup and maintain the domain securely
domainpassword = stock
=================================================================
# start agent
$ java -dpropertiesurl=file:///home/notroot/nuodb/etc/stock.properties -jar jar/nuoagent.jar --verbose &>/tmp/stock.log &
# start command line manager
$ java -jar jar/nuodbmanager.jar --broker localhost --password stock
nuodb [stock] > show domain summary
hosts:
[broker] localhost/127.0.0.1:48004
## create a new domain administrator user
nuodb [stock] > create domain administrator user istvan password istvan
## start storage manager
nuodb [stock] > start process sm
database: stock
host: localhost
process command-line options: --dba-user stock --dba-password stock
archive directory: /home/notroot/nuodb/data
initialize archive: true
started: [sm] ubuntu/127.0.1.1:59914 [ pid = 3467 ] active
## ps -ef | grep nuodb
## notroot   3467  3396  0 12:01 pts/0    00:00:00 /home/notroot/nuodb-1.0.1.128.linux.x86_64/bin/nuodb --connect-key 7124934669079864995
## start transaction engine
nuodb [stock/stock] > start process te
host: localhost
process command-line options: --dba-user stock --dba-password stock
started: [te] ubuntu/127.0.1.1:60545 [ pid = 3494 ] active

## ps -ef| grep nuodb
## notroot   3494  3396  0 12:06 pts/0    00:00:00 /home/notroot/nuodb-1.0.1.128.linux.x86_64/bin/nuodb --connect-key 8587006928753483386

note that we started the storage manager with the initialize yes option. this is only for the first time; any subsequent startup shall use the initialize no option, otherwise the data will be overwritten.

next, we can connect to the database using the nuosql client – the first argument is the name of the database (stock), and we need to specify the database admin username/password. after login, we can set the schema with the use command to stock:

$ bin/nuosql stock --user stock --password stock
sql> use stock
sql> show
	autocommit state is on
	semicolon completion is required
	current schema is stock
sql> show tables
	no tables found in schema stock
sql> create table stock
   > (
   >    id             integer not null generated always as identity primary key,
   >    stockdate      date,
   >    stockopen      decimal(8,2),
   >    stockhigh      decimal(8,2),
   >    stocklow       decimal(8,2),
   >    stockclose     decimal(8,2),
   >    stockvolume    integer,
   >    stockadjclose  decimal(8,2)
   > );
sql> show tables
	tables in schema stock
		stock

we can then load the data stored in .csv file format into the database table. the csv file – google.csv for stock information – was downloaded from http://finance.yahoo.com

$ bin/nuoloader --schema stock --user stock --password stock --import "/home/notroot/nuodb/samples/stock/google.csv",skip --to "insert into stock values(default,?,?,?,?,?,?,?)" stock &> /tmp/nuoloader.log

imported 2163 rows, failed 0 rows, size 101897 bytes from /home/notroot/nuodb/samples/stock/google.csv

next, we can login again using nuosql and run a regular sql query to retrieve the top 10 stock values and the corresponding date (ordered by adj close value):

notroot@ubuntu:~/nuodb$ bin/nuosql stock --user stock --password stocksql> use stock
sql> select count(*) from stock;
 count  
 ------ 
  2163  

sql> select stockdate, stockopen,stockclose, stockvolume, stockadjclose from stock order by stockadjclose desc limit 10;
 stockdate  stockopen  stockclose  stockvolume  stockadjclose  
 ---------- ---------- ----------- ------------ -------------- 
 2013-03-05   828.93     838.60      4044100        838.60     
 2013-03-11   831.69     834.82      1594700        834.82     
 2013-03-07   834.06     832.60      2052700        832.60     
 2013-03-08   834.50     831.52      2911900        831.52     
 2013-03-06   841.03     831.38      2873000        831.38     
 2013-03-12   830.71     827.61      2008300        827.61     
 2013-03-13   827.90     825.31      1641300        825.31     
 2013-03-14   826.99     821.54      1651200        821.54     
 2013-03-04   805.30     821.50      2775600        821.50     
 2013-03-20   816.83     814.71      1463800        814.71

java client - jdbc for nuodb

nuodb supports various programming languages for client applications such as java, .net, php, ruby and node.js. in this section we demonstrate that nuodb supports jdbc in the same way that it is available for traditional relational databases. the java program needs to add nuodbjdbc.jar to its classpath.

below is an example java code (stockdb.java) to retrieve the highest stock value ever (ordered by adj close) and the related date:

$ cat stockdb.java
import java.sql.connection;
import java.sql.drivermanager;
import java.sql.preparedstatement;
import java.sql.resultset;
import java.sql.sqlexception;
import java.sql.statement;
import java.util.properties;

public class stockdb {

    /** the driver class provided by nimbusdb. */
    public static final string driver_class =
	"com.nuodb.jdbc.driver";

    /** the base url for connecting to a local database server. */
    public static final string database_url =
	"jdbc:com.nuodb://localhost/";

    // the established connection to a local server
    private final connection dbconnection;

    /**
     * creates an instance of db and connects to a local server,
     * as the given user, to work with the given named database
     *
     * @param user the user name for the connection
     * @param password the password for the given user
     * @param dbname the name of the database at the server to use
     */
    public stockdb(string user, string password, string dbname)
	throws sqlexception
    {
	properties properties = new properties();
	properties.put("user", user);
	properties.put("password", password);
	properties.put("schema", "stock");

	dbconnection =
	    drivermanager.getconnection(database_url + dbname, properties);
    }

    /** closes the connection to the server. */
    public void close() throws sqlexception {
	dbconnection.close();
    }

    /**
     * gets the name for the given id, or null if no name exists.
     *
     * @param an identifier
     * @return the name associate with the identifier, or null
     */
    public string getdateandadjclose() throws sqlexception {
	statement stmt = dbconnection.createstatement();
	resultset rs = stmt.
	    executequery("select stockdate, stockadjclose from stock order by stockadjclose desc limit 1");
	try {
	    if (rs.next())
		return rs.getstring(1) + ", " + rs.getstring(2);
	    return null;
	} finally {
	    rs.close();
	    stmt.close();
	}
    }

    /** main-line for this example. */
    public static void main(string [] args) throws exception {
	class.forname(driver_class);

	stockdb stockdb = new stockdb("stock", "stock", "stock");
	system.out.println("date and adjclose: "  + stockdb.getdateandadjclose());

	stockdb.close();
    }

}

then we can run the java program as follows:

notroot@ubuntu:~/nuodb/samples/java$ javac stockdb.java 
notroot@ubuntu:~/nuodb/samples/java$ java -classpath .:../../jar/nuodbjdbc.jar stockdb
date and adjclose: 2013-03-05, 838.60

conclusion

nuodb takes a new approach to address the challenges of data growth and scalability issues. traditional relational databases have limitations due to their architecture and require fairly complex techniques such as sharding and master/slave replication to cope with scale out demands. nosql databases take another route to support scalability at the cost of dropping sql and transaction management that requires new programming methods. noudb promises the ability to combine the good things from the relational database world (sql support and acid properties) and from nosql methodologies  (easy to scale out, asynchronous nature with on-demand replication)  and offers a great choice to deal with the modern, 21 ^st century database requirements.