Oracle NoSQL Database with Cloudera Distribution for Hadoop

Using Oracle NoSQL Database with Cloudera Distribution for Hadoop

By Deepak Vohra

Get a test project up and running to explore the basic principles involved.

Introduced in 2011, Oracle NoSQL Database is a highly available, highly scalable, key/value storage based (nonrelational) database that provides support for CRUD operations via a Java API. A related technology, the Hadoop MapReduce framework, provides a distributed environment for developing applications that process large quantities of data in parallel on large clusters.

In this article we discuss integrating Oracle NoSQL Database with Cloudera Distribution for Hadoop (CDH) on Windows OS via an Oracle JDeveloper project (download). We will also demonstrate processing the NoSQL Database data in Hadoop using a MapReduce job.

Setup

The following software is required for this project. Download and install anything on the list you don’t already have according to the respective instructions.

• Oracle NoSQL Database, Community Edition
• Oracle JDeveloper 11.1.2 .1
• Cygwin
• CDH2 (or Apache Hadoop 0.22.0)
• Java SE 7 

Install Java 1.7 in a directory (without spaces in its name) in the directory path. Set the JAVA_HOME environment variable.

Configuring Oracle NoSQL Database in Oracle JDeveloper

First, we’ll need to configure the NoSQL database server as an external tool in JDeveloper. Select Tools>External Tools. In the External Tools window select New. In the Create External Tool wizard select Tool Type: External Program and click Next. In Program Optionsspecify the following program options.

Field	Value
Program Executable	C:\JDK7\Java\jdk1.7.0_05\bin\java.exe
Arguments	-jar ./lib/kvstore-1.2.123.jar kvlite
Run Directory	C:\OracleNoSQL\kv-1.2.123

Click Finish in Create External Tools:

 

Oracle NoSQL Database is now configured as an external tool; the external tool name may vary based on whether other tools requiring the same program executable are also configured.  Click on OK in External Tools.

Next, select Tools>Java 1. The Oracle NoSQL Database server starts up and a key-value (KV) store is created. 

The NoSQL Database store has the following args by default:

Arg	Value
-root	kvroot
-store	kvstore
-host	localhost
-port	5000
-admin	5001

On subsequent runs of the external tool for the NoSQL Database server the existing KV store is opened with the same configuration with which it was created: 

Running the HelloBigDataWorld Example

The NoSQL Database package includes some examples in the C:\OracleNoSQL\kv-1.2.123\examples directory. We will run the following examples in this article:

• hello.HelloBigDataWorld
• hadoop.CountMinorKeys 

The HelloBigDataWorld example can be run using an external tool configuration or as a Java application.

Using as an External Tool

To run HelloBigDataWorld as an external tool select Tools>External Tools and create a new external tool configuration with the same procedure as with the NoSQL Database server. We need to create two configurations, one for compiling the HelloBigDataWorld file and another for running the compiled application. Specify the following program options for compiling HelloBigDataWorld.

Program Option	Value
Program Executable	C:\JDK7\Java\jdk1.7.0_05\bin\javac.exe
Arguments	-cp ./examples;./lib/kvclient-1.2.123.jar examples/hello/HelloBigDataWorld.java
Run Directory	C:/OracleNoSQL/kv-1.2.123

The program options for compiling the hello/HelloBigDataWorld.java file are shown below. Click Finish.

 

An external tool Javac gets created. Select Tools>Javac to compile the hello/ HelloBigDataWorld.java class. Next, create an external tool for running the hello.HelloBigDataWorld class file using the following configuration.

Program Option	Value
Program Executable	C:\JDK7\Java\jdk1.7.0_05\bin\java.exe
Arguments	-cp ./examples;./lib/kvclient-1.2.123.jar hello.HelloBigDataWorld
Run Directory	C:/OracleNoSQL/kv-1.2.123

The classpath should include the kvclient-1.2.123.jar file. Click Finish. 

To run the hello.HelloBigDataWorld class select Tools>Java. The hello.HelloBigDataWorld application runs and a short message is written.

 

Running in a Java Application

Next, we will run the hello.HelloBigDataWorld application as a Java application in an Oracle JDeveloper project. To create a new application:

• Select Java Desktop Application in New Gallery.
• Specify an Application Name (e.g., NoSQLDB) and select the default directory. Click Next.
• Specify a Project Name (e.g., NoSQLDB) and click Finish. 

Next, create a Java class in the project.

• Select Java Class in New Gallery and click OK.
• In Create Java Class specify class name as “HelloBigDataWorld” and package as “hello”. Click OK. The hello.HelloBigDataWorld class is added to the application.
• Copy the hello/HelloBigDataWorld.java file from the C:\OracleNoSQL\kv-1.2.123\examples directory to the class file in Oracle JDeveloper.

In the example application, a new oracle.kv.KVStore is created using the KVStoreFactory class:

store = KVStoreFactory.getStore(new KVStoreConfig(storeName, hostName + ":" + hostPort));

Key/value pairs are created and stored in the KV store:

        final String keyString = "Hello";
        final String valueString = "Big Data World!";
store.put(Key.createKey(keyString), Value.createValue(valueString.getBytes()));

The key/value are retrieved from the store and output. Subsequently the KV store is closed.

final ValueVersion valueVersion = store.get(Key.createKey(keyString));
System.out.println(keyString + " " + new String(valueVersion.getValue().getValue())+ "\n ");
store.close();

The hello.HelloBigDataWorld class is shown below.

 

To run the HelloBigDataWorld class add the C:\OracleNoSQL\kv-1.2.123\lib\kvclient-1.2.123.jar file to the Libraries and Classpath.

 

To run the application right-click on the class and select Run. The hello.HelloBigDataWorld class runs and one line of output is generated.  The example application creates only one key/value pair.

In the next section we will run the hadoop.CountMinorKeys.java example. To prepare for that, rerun the HelloBigDataWorld example to create additional key/value pairs in the KV store:

  

Processing NoSQL Database Data in Hadoop

Next, we will run the Hadoop example in C:\OracleNoSQL\kv-1.2.123\examples\hadoop\CountMinorKeys.java. Create a Java class called hadoop/CountMinorKeys.java and copy the \examples\hadoop\CountMinorKeys.java file to that class.

 

Add the CDH jar file to the project..

 

Configuring the Hadoop Cluster

Next, we will configure the Hadoop cluster. In CDH2 there are three configuration files: core-site.xml, mapred-site.xml, and hdfs-site.xml.  In the conf/core-site.xml specify the fs.default.name parameter, which is the URI of NameNode.

        fs.default.name
        hdfs://localhost:9100
   

The core-site.xml is shown below.

 

In conf/mapred-site.xml specify the mapred.job.tracker parameter for the Host or IP and port of JobTracker. Specify host as localhost and port as 9101.

        mapred.job.tracker
        localhost:9101
   

The conf/mapred-site.xml is shown below.

 

Specify the dfs.replication parameter in conf/hdfs-site.xml configuration file. The dfs.replication parameter specifies how many machines a single file should be replicated to before becoming available. The value should not exceed the number of DataNodes. (We use one DataNode in this example.)

   
        dfs.replication
        1
   

The conf/hdfs-site.xml is shown below.

Having configured a Hadoop cluster, we now start the cluster. But, first, we need to create a Hadoop Distributed File System (HDFS) for the files used in processing the Hadoop data. Run the following command in Cygwin.

>cd hadoop-0.20.1+169.127

>bin/hadoop namenode -format

A storage directory, \tmp\hadoop-dvohra\dfs, is created.

 

• We also need to create a deployment profile for the hadoop.CountMinorKeys application. Select the project node in Application Navigator and select File>New.
• In New Gallery select Deployment Profiles JAR File and click OK.
• In Create Deployment Profile, specify Deployment Profile Name (hadoop) and click OK.
• In Edit JAR Deployment Profile Properties, select the default settings and click OK.
• A new deployment profile is created. Click OK.

To deploy the deployment profile right-click on the NoSQL project and select Deploy>hadoop.

 

In Deployment Action, select Deploy to JAR file and click Next. Click Finish in Summary. The hadoop.jar gets deployed to the deploy directory in the JDeveloper project. Copy the hadoop.jar to the C:\cygwin\home\dvohra\hadoop-0.20.1+169.127 directory as the application shall be run from the hadoop-0.20.1+169.127 directory in Cygwin.

Starting the Hadoop Cluster

Typically a multi-node Hadoop cluster consists of  the following nodes.

Node Name	Function	Type
NameNode	For the HDFS storage layer management. We formatted the NameNode to create a storage layer in the previous section.	master
JobTracker	MapReduce data processing management; assigns tasks	master
DataNode	Stores filesystem data, HDFS storage layer processing	slave
TaskTracker	MapReduce processing	slave
Secondary NameNode	Stores modifications to the filesystem and periodically merges the changes with the current HDFS state.

Next, we shall start the nodes in the cluster. To start the NameNode run the following commands in Cygwin.

> cd hadoop-0.20.1+169.127
> bin/hadoop namenode

 

Start the Secondary NameNode with the following commands:

> cd hadoop-0.20.1+169.127
> bin/hadoop secondarynamenode

Start the DataNode:

> cd hadoop-0.20.1+169.127
> bin/hadoop datanode

Start the JobTracker :

> cd hadoop-0.20.1+169.127
> bin/hadoop jobtracker

Start the TaskTracker:

> cd hadoop-0.20.1+169.127
> bin/hadoop tasktracker

 

Running a MapReduce Job

Next, we shall run the hadoop.CountMinorKeys application for which created the hadoop.jar file. The hadoop.CountMinorKeys  application runs a MapReduce job on the Oracle NoSQL Database data in the KV store and generates an output in the Hadoop HDFS. The NoSQL Database server Java API is in the kvclient-1.2.123.jar directory. Copy the kvclient-1.2.123.jar from the C:\NoSQLDB\kv-1.2.123\lib directory to the C:\cygwin\home\dvohra\hadoop-0.22.0\lib directory, which is in the classpath of Hadoop. Run the hadoop.jar with the following commands in Cygwin.

> cd hadoop-0.20.1+169.127
> bin/hadoop jar hadoop.jar hadoop.CountMinorKeys   kvstore dvohra-PC:5000 hdfs://localhost:9100/tmp/hadoop/output/  

The MapReduce job runs and the output is generated in the hdfs://localhost:9100/tmp/hadoop/output/  directory.

 

List the files in the temp/hadoop/output directory with the following command.

> bin/hadoop dfs -ls hdfs://localhost:9100/tmp/hadoop/output

The MapReduce job output is generated in the part-r-00000 file, which gets listed with the previous command.

Get the part-r-00000 file to the local filesystem with the command:

bin/hadoop dfs -get hdfs://localhost:9100/tmp/hadoop/output/part-r-00000  part-r-00000

The MapReduce job ouput is shown in Oracle JDeveloper; the output lists the number of records for each major key in the KV store, which was created with the first example application, hello.HelloBigDataWorld.

 

Congratulations, your project is complete! 

Cloudera Impala Overview

Cloudera, a provider of Apache Hadoop solutions for the enterprise, recently announced the general availability of Cloudera Impala, its open-source, interactive SQL query engine for analyzing data stored in Hadoop clusters in real time.
Cloudera claims to have been first to market with a SQL-on-Hadoop offering, releasing Impala to open source as a public beta offering in October 2012. Since that time, the company has worked closely with customers and open-source users, testing and refining the platform in real-world applications to deliver a production-hardened and customer-validated release, designed from the ground up for enterprise workloads, said Mike Olson, CEO of Cloudera.

As Cloudera Engineering team described in their blog, their work was inspired by Google Dremel paper which is also the basis for Google BigQuery. Cloudera Impala provides a HiveQL-like query language for wide variety of SELECT statements with WHERE, GROUP BY, HAVING clauses, with ORDER BY – though currently LIMIT is mandatory with ORDER BY -, joins (LEFT, RIGTH, FULL, OUTER, INNER), UNION ALL, external tables,  etc. It also supports arithmetic and logical operators and Hive built-in functions such as COUNT, SUM, LIKE, IN or BETWEEN. It can access data stored on HDFS but it does not use mapreduce, instead it is based on its own distributed query engine.

The current Impala release (Impala 1.0beta) does not support  DDL statements (CREATE, ALTER, DROP TABLE), all the table creation/modification/deletion functions have to be executed via Hive and then refreshed in Impala shell.

Cloudera Impala is open-source under Apache Licence, the code can be retrieved fromGithub. Its components are written in C++, Java and Python.

Commercial Hadoop distributor Cloudera is first out of the gate with a true SQL layer that sits atop Hadoop.

It lets normal people – if you can call people who've mastered SQL normal – perform ad hoc queries in real time against information crammed into the Hadoop Distributed File System or the HBase database that rides atop it.

Every Hadoop distie dev is working on their own standards-compliant SQL interface for HDFS and HBase, and that's because these are inherently batch-mode systems, like mainframes in the 1960s and 1970s. Hadoop is a perfectly acceptable platform into which you can pull vast sums and run algorithms against to make correlations between different data sets to do fraud detection, risk analysis, web page ad serving, and any other number of jobs.

But if you want to do a quick random query of all or part of a dataset residing out on HDFS or organized in HBase tables, you have to wait.

It took a while for interactive capabilities to be added to mainframes, and it's no surprise that it has taken many years and much complaining to transform the Hadoop stack from batch to interactive mode – but now there are a number of competing methods available.

Cloudera has been working on the Project Impala SQL query engine for HDFS and HBase for years, and formally launched the project at Hadoop World last October when it entered an open beta program in which nearly 50 customers put Impala through some serious paces. Mike Olson, CEO at Cloudera, tells El Reg that there were over 1,000 unique customers who downloaded Impala in the past six months to see how it works, and after all of that testing, Cloudera is ready to sell support services to customers as they put it into production on their big data munchers.

Olson dissed the other methods his rivals have come up to add true SQL functionality to Hadoop.

"Many of the methods we see are rear-guard actions to try to preserve legacy approaches," Olson told us. "This is not really just about putting SQL on Hadoop, but rather making one big-data repository and allowing access to that data in a lot of different ways."

Cloudera went right to the source to get some help to create the Impala real-time SQL engine for HDFS and HBase: Google. Or more precisely, Cloudera hired Marcel Kornacker, one of the architects of the query engine in Google's F1 fault tolerant, distributed relational database that now underpins its AdWords ad serving engine. Impala also borrows heavily from Google's Dremel ad hoc query tool, which has been cloned as the Apache Drill project.

This is the way it works on the modern Internet: Google invents something, publishes a paper, and the Hadoop community clones it. In a way, this is how Google gives back to the open source community, merely by proving to smart people that something can be done so they will imitate it.

Hadoop's MapReduce batch algorithm for chewing on large data sets and its HDFS are riffs on ideas that were part of Google's search engine infrastructure years ago. HBase is a riff on Google's BigTable distributed database overlay for its original Google File System, which has been replaced by a much more elegant solution called Spanner.

In Cloudera's case with Impala, the company not only got the idea for Impala from the F1 and Dremel papers, but hired a Googler to clone many of its core ideas.

The Hadoop stack not only has the HBase distributed tabular data store, but also the Hive query tool, which has an SQL-like query function. But in business, SQL-like don't cut it. What companies – the kind who will pay thousands and thousands of dollars per node for a support contract – want is actual SQL.

In this case, the Impala query engine is compliant with the ANSI-92 SQL standard. What that means is that most tools that use Microsoft's ODBC query tool are compliant with Impala, and marketeers and other line-of-business people who use SQL queries to do their jobs can therefore query data in Hadoop without having to learn to create MapReduce algorithms in Java.

"Large customers running Hadoop today have tens of users who know how to do MapReduce," says Justin Erickson, senior product manager at Cloudera. "But there are a lot more SQL users, on the order of hundreds to thousands, and they want access to the data stored in Hadoop in a way that is familiar to them."

And not surprisingly, as is the case on mainframes even today, there is a mix of batch and interactive workloads running on these behemoths, and this is exactly what Olson expects to see happen eventually on Hadoop systems thanks to tools like Impala.

Based on his inside knowledge of the workloads at Facebook and Google, Olson says that these organizations have already passed the point where the total number of cycles burned on SQL interactive jobs exceeds MapReduce batch jobs. MapReduce will never go to zero, just like batch jobs have not gone away in the data center – you still need to generate and print statements, bills, and other business documents – they are not going away in Hadoop clusters.

The Impala tool replaces bits of the HBase tabular data store and the Hive query tool without breaking API compatibility but radically speeding up the processing of ad hoc queries and providing ANSI-92 SQL compliance. Hive uses MapReduce, which is why it is so damned slow, but Impala has a new massively parallel distributed database. Customers can use Impala without changing their HDFS or HBase file formats, and because it is open source, you can download it and not pay Cloudera a dime. It will snap right into the Apache Hadoop distribution if you want to roll your own big-data muncher.

While having ad hoc SQL query capability that happens in real time is important, there is more to it than that – this is all about money, as you might expect. "If you are spending tens of thousands of dollars per terabyte in a data warehouse," explains Olson, "you have an imperative to actually throw data away. Impala and Hadoop will let you do it for hundreds of dollars per terabyte, and you never have to throw anything away."

Cloudera is unnecessarily cagey about pricing for its Hadoop tools, but here is how it works. The Cloudera Distribution for Hadoop is open source, and that includes the code for Impala. If you want support and the closed-source Cloudera Manager tool, then you have to pay more. The base Cloudera Manager tool cost $2,600 per server node per year, including 24x7 support, but Cloudera says it is going to split up the functionality for Cloudera Manager into Core (for MapReduce), Real Time Data (for HBase), and Real Time Query (for Impala) data-extraction methods. Cloudera won't say what Core and RTD cost, but RTQ, which manages Impala and provides the support contract, costs $1,500 per node per year