Options to connect and integrate Hadoop with Oracle

Options to connect and integrate Hadoop with Oracle

Do you like our work......we hire!

Never miss our publications about Open Source, big data and distributed systems, low frequency of one email every two months.

I will list the different tools and libraries available to us developers in order to integrate Oracle and Hadoop. The Oracle SQL Connector for HDFS described below is covered in a follow up article with more details.

To summarize, we have Sqoop originally from Cloudera and now part of Apache, a Sqoop plugin from MapQuest and the Oracle Big Data connectors as a family of four distinct products which are Oracle Loader for Hadoop (OLH), Oracle SQL Connector for HDFS, Oracle R Connector for Hadoop and Oracle Data Integrator Application Adapter for Hadoop.


Apache Sqoop describes itself as a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases. In that sense, it is by no means limited to Oracle.

I have personally intensively used Sqoop to import full databases from Oracle. Here’s my experience. Overall, it works great but I didn’t have to import larger databases than 1 Go which is relatively insignificant in terms of load testing. The mapping between Oracle and Hive is ok but with a few glitches. The Raw data type isn’t handled. To bypass this limitation, you can use the columnsoption to filter out those columns, you can use the map-column-hiveto force the casting to a string type or you could use your own SQL import statement to filter or transform the unhandled columns. I also remember having some issue with column padded with space but I can’t recall in which exact situation. Note as well that compared to the import options offered by Sqoop, the export options are more limited and doesn’t come with Hive integration.

Quest Sqoop plugin

Quest, editor of the famous Toad products family, provides a plug-in for Apache Sqoop that lets you transfer data bi-directionally between Oracle and Hadoop. It is meant to compete with the native Oracle implementation present in Sqoop and advertise itself as more than five times faster. It is a free, Open Source plug in to SQOOP licensed under the Apache 2.0 license. More information this blog post.

Oracle Big Data connectors

The Oracle Big Data connectors is a suite of 4 products. With the Oracle SQL Connector, the user writes Oracle SQL queries which run against an external table that references files stored in HDFS. With the Oracle Data Integrator, the user uses graphical tools which generate HiveQL which in turn generate native Map Reduce programs.

Oracle Loader for Hadoop (OLH)

Uses MapReduce processing to format and load data efficiently into Oracle Database for analysis. The connector automatically creates an OSCH-enabled Oracle Database external table that references files stored in HDFS. Full SQL access allows users to join data in HDFS with data stored in Oracle Database, apply analytic functions, load data into tables, and more.

  • On-Line Load Option:
    Reducer nodes connect to the database for load, using JDBC or direct path load options
  • Off-Line Load Option:
    Reducer nodes write Oracle Data Pump binary files or delimited text files for loading into the database.
  • Load Balancing:
    “Perfect Balance” distributes work evenly to all reducers.
  • Input Formats:
    Supports multiple input formats: delimited text files, regular expressions, Oracle NoSQL Database, Avro, Hive tables or custom inputs.

Oracle Loader for Hadoop is a MapReduce application that is invoked as a command line utility. It accepts the generic command-line options that are supported by the Tool interface. The OLH driver uses Sqoop to perform operations that Oracle Loader for Hadoop does not support.

There are two modes for loading the data into an Oracle database from a Hadoop cluster:

  1. Online database mode:
    The data is loaded into the database using either a JDBC output format or an OCI Direct Path output format. The OCI Direct Path output format performs a high performance direct path load of the target table. The JDBC output format performs a conventional path load.
  2. Offline database mode:
    The reducer nodes create binary or text format output files. The Data Pump output format creates binary format files that are ready to be loaded into an Oracle database using an external table and the ORACLE_DATAPUMP access driver. The Delimited Text output format creates text files in delimited record format. (This is usually called comma separated value (CSV) format when the delimiter is a comma.) These text files are ready to be loaded into an Oracle database using an external table and the ORACLE_LOADER access driver. The files can also be loaded using the SQL*Loader utility.

Oracle SQL Connector for HDFS

Enables Oracle Database to access data seamlessly from Hadoop Distributed File System (HDFS) allowing for SQL processing.

  • Direct SQL Access:
    Query Hive tables and files in HDFS directly from Oracle Database.
  • Parallel Query:
    Fast, efficient parallel query of data in HDFS.
  • Automatic Table Creation:
    Easily generate Oracle external tables to access data in HDFS.

As previously written, more information are available in another blog post.

Oracle R Connector for Hadoop

Gives R users high performance native access to Hadoop Distributed File System (HDFS) and MapReduce programming framework. It uses Sqoop for access to Oracle Database.

  • Interactive R access to HDFS:
    Manipulate and explore data in HDFS using R functions; Using simple R functions, move data between HDFS and R, Oracle Database and User’s local file system
  • R integration with Hadoop:
    Leverage map-reduce programming paradigm in the familiar context of R without having to learn Hadoop concepts; Introduces new analytic techniques implemented in R, Java and natively in Hadoop; Native support for Hive tables

Oracle Data Integrator Application Adapter for Hadoop

It simplifies data integration between Oracle Data Integrator (ODI) and Hadoop through an easy to use interface. ODI generates optimized HiveQL which in turn generates native Map Reduce programs that are executed on the Hadoop cluster.

Share this article

Canada - Morocco - France

We are a team of Open Source enthusiasts doing consulting in Big Data, Cloud, DevOps, Data Engineering, Data Science…

We provide our customers with accurate insights on how to leverage technologies to convert their use cases to projects in production, how to reduce their costs and increase the time to market.

If you enjoy reading our publications and have an interest in what we do, contact us and we will be thrilled to cooperate with you.

Support Ukrain