Hadoop, bigdata, cloud computing and mobile BI

Main menu

Category Archives: Cloud database

Introduction

Database.com is Salesforce.com’s multitenant Database as a Service platform that aims to be the cloud database engine for application developers. As opposed to Force.com, the Platform as a Service offering from Salesforce.com, it does not support user interface elements such as page layouts or custom views, there is no support for VisualForce, it has no Visual Workflows capabilities and there are no reports and dashboards available. Database.com is focusing on advanced relational database functionalities and supports Salesforce Object Query Language (SOQL) and Saleforce Object Search Language (SOSL) that proved to be popular in enterprise cloud applications development.

Database.com offers a REST API which makes it ideal for mobile and social applications that require data storage with state-of-the-art security model and identity and access management.

Creating objects in Database.com

If you are familiar with Force.com, using Database.com can naturally be related to those experiences. First of all, the user has to sign up at database.com. Once the registration has been completed, the user can login to the platform. The first webpage is a System Overview providing details about number of objects and data records, API usage, etc.

Then we can create our custom objects. In our example we will create a stockprice object (which is essentially a table in traditional RDBMS speak) that will store stock price information such as open and close price, volume, etc. We need to navigate to Create->Objects and click either on New Custom Object or Schema Builder button. In our example we are going to show how to use Schema Builder.

By clicking on the button Schema Builder will open and that is where we can define the object name and a few other parameters:

Once the object is created, we can then start defining the fields. We can use the palette on the left hand side of the Schema Builder and just drag and drop the appropriate data types such as number or date onto the canvas.

.

We can also define validation rules to ensure that the values in the fields (colums in RDBMS world) fulfill the requirements.

Once we are done, we can check our object, it has seven custom fields: AdjClose, Close, Date, High, Low, Open and Volume.

Loading data into Database.com objects

Now, as we have the object created, the next step is to load data into it. In principle we could insert data using the Workbench tool but in this example we are going to use Salesforce.com’s bulk tool called Data Loader that can be very helpful for uploading massive amount of data from our computer. Data Loader is a Windows application that can be downloaded under Data Management->Data Loader menu:

Once it is installed, we can use it to load data in our StockPrice object. The financial data was retrieved from http://finance.yahoo.com.

When we start up the Data Loader, we need to login first. The username is that same that we have used to login to Database.com, whilst the password is the concatenated string of the password for Database.com and the security token that can be generated under My Personal Information menu within Database.com.

From the Data Loader then we can select which object we want to use and we can also the specify the file to be uploaded – it has to be in CSV format with a header. In our case the file format was as follows:

We can also define mapping between file column headers and the field names if we want to.

When the data has been uploaded, we can open Developer Console from Database.com to validate whether all the data are successfully inserted. We need to go to Query Editor, enter our SOQL query like SELECT Close__c, Volume__c from StockPrice__c and click on the Excute button:

Please, note that the API name for the custom fields and the custom table is Close__c, Volume__c and StockPrice__c, indicating that they are custom entities.

Remote access for Database.com

Now, that we have our data loaded into our custom object, the last step is to configure remote access for the remote applications (e.g. our imaginary mobile applications) who wish to run SOQL queries against our object using REST API. The authentication is based on OAuth standard. More details about the Database.com authentication concepts can be read here.

In order to enable remote access we need to go to Develop->Remote Access menu and configure the required parameters. In the Integration section the callback URL is mandatory, in our example we set it to http://localhost:5000/_auth. That is needed for Web Server flow which is the standard authentication method used within the Java template provided by Salesforce.com as a boilerplate application for remote Database.com access.

Our first example is based on username-password authentication flow. In this case the user already has credentials (username/password) and it is sent as part of the request, togethr with the customer key and customer secret. The customer key and customer segment can be retrieved from Remote Access, we need to navigate to Developer->Remote Access and select the client.

The username is the same that we used to login Database.com, whilst the password is the concated string of the Database.com password and the security token (as you may remember, this is the very same notion that we used to login to Data Loader).

The first step is to request the token from Database.com, we demonstrate the REST query using curl command line tool:

Salesforce.com also provide a Java template that can be downloaded from Database.com website. This is a sample application running on Jetty and it uses web server flow based on AuthFilter class for OAuth authentication. When we enter http://localhost:5000/, an authentication page will be presented to her:

If the user clicks on Allow button then she will be sent to the main home page where a SOQL query can be entered:

Conclusion

As we have seen, Database.com is an ideal cloud database engine for mobile and social applications. It offers the same enterprise security and identity model that is used by other Salesforce.com platforms, making it a robust database platform choice for cloud developers. Since it is based on REST API, it can be accessed from any programming languages such as Java, C, C#, Ruby, Python, PHP, etc. Salesforce.com has also created a Java SDK for Database.com and Force.com that can be used to create Spring MVC applications quickly from a template.

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, these are objects that are being replicated . In NuoDB everything is an Atom; database, schema, sequence, table, index, records, blobs, data are all Atoms. NuoDB holds a patent on this peer-to-peer object replication.

NuoDB Architecture

NuoDB architecture has three layers: management layer, SQL layer and data layer. 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 the broker introduces the client to the transaction engine. From then on the client can communicate directly with the transaction engines. NuoDB management layer also offers a command line and a web-based management tool to manage the databases. NuoDB also offer a command line loader for exporting and importing data.

At the SQL layer NuoDB has transaction engines that 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 NuoDB has storage managers that provide persistence of the data. A storage manager uses key/value pairs to store the information but it can also use more sophisticated stores e.g. HDFS.

In case of a minimal configuration we can run every components (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 providing geo-redundancy. Below is an example of a redundant architecture with two brokers, two transaction engines and two storage managers.

First we need to start up the components as discussed above; 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

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

Then we can connect to the database using 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 use command to stock.:

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: