4 Integrating Hadoop Data

This chapter provides information about the steps you need to perform to integrate Hadoop data.

This chapter includes the following sections:

4.1 Integrating Hadoop Data

The following table summarizes the steps for integrating Hadoop data.

Table 4-1 Integrating Hadoop Data

Step Description

Set Up Data Sources

Set up the data sources to create the data source models. You must set up File, Hive, HDFS, and HBase data sources.

See Setting Up File Data Sources

See Setting Up Hive Data Sources

See Setting Up HBase Data Sources

See Setting Up Kafka Data Sources

See Setting Up Cassandra Data Sources

See Setting Up HDFS Data Sources

Import Hadoop Knowledge Modules

Import the Hadoop KMs into Global Objects or a project.

See Importing Hadoop Knowledge Modules

Create Oracle Data Integrator Models

Reverse-engineer the Hive and HBase models to create Oracle Data Integrator models.

See Creating a Oracle Data Integrator Model from a Reverse-Engineered Hive, HBase, and HDFS Models

Integrate Hadoop Data

Design mappings to load, validate, and transform Hadoop data.

See Loading Data from Files into Hive

See Loading Data from HBase into Hive

See Loading Data from Hive into Hbase

See Loading Data from an SQL Database into Hive, HBase, and File using SQOOP

See Validating and Transforming Data Within Hive

See Loading Data into an Oracle Database from Hive and File

See Loading Data into an SQL Database from Hbase, Hive and File using SQOOP

See Loading Data from Kafka to Spark

See Loading Data from HDFS File to Hive

See Loading Data from HDFS File to Spark

See Loading Data from Hive to Files

4.2 Setting Up File Data Sources

In the Hadoop context, there is a distinction between files in Hadoop Distributed File System (HDFS) and local files (outside of HDFS).

To define a data source:

  1. Create a Data Server object under File technology.

  2. Create a Physical Schema object for every directory to be accessed.

  3. Create a Logical Schema object for every directory to be accessed.

  4. Create a Model for every Logical Schema.

  5. Create one or more data stores for each different type of file and wildcard name pattern.

  6. For HDFS files, create a Data Server object under File technology by entering the HDFS name node in the field JDBC URL and leave the JDBC Driver name empty. For example:

    hdfs://bda1node01.example.com:8020

    Test Connection is not supported for this Data Server configuration.

    Note:

    No dedicated technology is defined for HDFS files.

Integrating Hadoop Data

4.3 Setting Up HDFS Data Sources

This topic provides steps in Oracle Data Integrator that are required for connecting to a HDFS system.

  1. Create a Data Server object under HDFS technology.

    Note:

    HDFS data server should reference an existing Hadoop data server.
  2. Create a Physical Schema object for every directory to be accessed.
  3. Create a Logical Schema object for every directory to be accessed.
  4. Create a Model for every Logical Schema
  5. Create one or more data stores for each different type of file.
    The definition tab has a Resource Name field that allows you to specify which file or files it represents. If wildcards are used, the files must have the same schema and be of the same format (all JSON or all Avro).
  6. Select the appropriate Storage Format and the Schema File.
    The contents of the schema are displayed.
  7. Select the Attributes Tab to either enter, or reverse engineer the Attributes from the supplied schema.

4.4 Setting Up Hive Data Sources

The following steps in Oracle Data Integrator are required for connecting to a Hive system. Oracle Data Integrator connects to Hive by using JDBC.

Prerequisites

The Hive technology must be included in the standard Oracle Data Integrator technologies. If it is not, then import the technology in INSERT_UPDATE mode from the xml-reference directory.

To set up a Hive data source:

  1. Create a Data Server object under Hive technology.

  2. Set the following locations under JDBC:

    JDBC Driver: weblogic.jdbc.hive.HiveDriver

    JDBC URL: jdbc:weblogic:hive://<host>:<port>[; property=value[;...]]

    For example, jdbc:weblogic:hive://localhost:10000;DatabaseName=default;User=default;Password=default

    Note:

    Usually User ID and Password are provided in the respective fields of an ODI Data Server. In case where a Hive user is defined without password, you must add password=default as part of the JDBC URL and the password field of Data Server shall be left blank.

  3. Set the following under on the definition tab of the data server:

    Hive Metastore URIs: for example, thrift://BDA:10000

  4. Ensure that the Hive server is up and running.

  5. Test the connection to the Data Server.

  6. Create a Physical Schema. Enter the name of the Hive schema in both schema fields of the Physical Schema definition.

  7. Create a Logical Schema object.

  8. Import RKM Hive into Global Objects or a project.

  9. Create a new model for Hive Technology pointing to the logical schema.

  10. Perform a custom reverse-engineering operation using RKM Hive.

Reverse engineered Hive table populates the attribute and storage tabs of the data store.

Integrating Hadoop Data

4.5 Setting Up HBase Data Sources

The following steps in Oracle Data Integrator are required for connecting to a HBase system.

Prerequisites

The HBase technology must be included in the standard Oracle Data Integrator technologies. If it is not, then import the technology in INSERT_UPDATE mode from the xml-reference directory.

To set up a HBase data source:

  1. Create a Data Server object under HBase technology.

    JDBC Driver and URL are not available for data servers of this technology.

  2. Set the following under on the definition tab of the data server:

    HBase Quorum: Quorum of the HBase installation. For example: zkhost1.mydomain.com,zkhost2.mydomain.com,zkhost3.mydomain.com

  3. Ensure that the HBase server is up and running.

    Note:

    You cannot test the connection to the HBase Data Server.

  4. Create a Physical Schema.

  5. Create a Logical Schema object.

  6. Import RKM HBase into Global Objects or a project.

  7. Create a new model for HBase Technology pointing to the logical schema.

  8. Perform a custom reverse-engineering operation using RKM HBase.

    Note:

    Ensure that the HBase tables contain some data before performing reverse-engineering. The reverse-engineering operation does not work if the HBase tables are empty.

At the end of this process, the HBase Data Model contains all the HBase tables with their columns and data types.

Integrating Hadoop Data

4.6 Setting Up Kafka Data Sources

This topic provides steps in Oracle Data Integrator that are required for connecting to a Kafka system.

The Kafka technology must be included in the standard Oracle Data Integrator technologies. If it is not, then import the technology in INSERT_UPDATE mode from the xml-reference directory.

  1. Create a Data Server object under Kafka technology.
  2. Create a Physical Schema object.
  3. Create a Logical Schema object.
  4. Create a Model for every Logical Schema
  5. Create one or more data stores for each different type of file.
    Resource Name in the definition tab of data store indicates the Kafka topic . Kafka topic name can be either entered by the user or selected from the list of available Kafka topics in the Kafka cluster. There are two ways to load data from Kafka topics which are receiver-based and direct and LKM Kafka to Spark supports both approaches.
  6. Test the connection to the Data Server.
    For information on Kafka Integration, see Kafka Integration with Oracle Data Integrator.
The Kafka data model contains all the Kafka tables with their columns and data types.

4.7 Setting Up Cassandra Data Sources

This topic provides steps in Oracle Data Integrator that are required for connecting to a Cassandra system. Oracle Data Integrator connects to Cassandra by using JDBC.

The Cassandra technology must be included in the standard Oracle Data Integrator technologies. If it is not, then import the technology in INSERT_UPDATE mode from the xml-reference directory.

You must add all Cassandra-specific flex fields.

  1. Create a Data Server object under Cassandra technology.
  2. Set the following locations under JDBC:
    Add the Cassandra JDBC Driver to the Driver List.

    JDBC Driver: weblogic.jdbc.cassandra.CassandraDriver

    JDBC URL: jdbc:weblogic:cassandra://<host>:<port>[;property=value[:...]]

    For example, jdbc:weblogic:cassandra://cassandra.mycompany.com:9042;KeyspaceName=mykeyspace

    Note:

    Latest driver uses the binary protocol and hence uses default port 9042.
  3. Ensure that the Cassandra server is up and running.
  4. Test the connection to the Data Server.
  5. Create a Physical Schema object.
  6. Create a Logical Schema object.
  7. Import RKM Cassandra into Global Objects or a project.
  8. Create a Model for every Logical Schema
  9. Perform a custom reverse-engineering operation using RKM Cassandra.

4.8 Importing Hadoop Knowledge Modules

Most of the Big Data Knowledge Modules are built-in the product. The exceptions are the RKMs and CKMs, and these will need to be imported into your project or as global objects before you use them.

  • CKM Hive

  • RKM Hive

  • RKM HBase

  • RKM Cassandra

Integrating Hadoop Data

4.9 Creating a Oracle Data Integrator Model from a Reverse-Engineered Hive, HBase, and HDFS Models

You must create a ODI Model from a reverse-engineered Hive, HBase, and HDFS Models. The reverse engineering process creates Hive and HBase data stores for the corresponding Hive and HBase tables. You can use these data stores as source or target in your mappings.

This section contains the following topics:

4.9.1 Creating a Model

To create a model that is based on the technology hosting Hive, HBase, or HDFS and on the logical schema created when you configured the Hive, HBase, HDFS or File connection, follow the standard procedure described in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator.

For backward compatibility, the Big Data LKMs reading from Files (LKM File to Hive LOAD DATA), also support reading from HDFS, however the source data store must be from a file model. If reading from HDFS, it is preferable to use KMs like the LKM HDFS to File LOAD DATA . In this case, the source data store must be from an HDFS model.

4.9.2 Reverse Engineering Hive Tables

RKM Hive is used to reverse engineer Hive tables and views. To perform a customized reverse-engineering of Hive tables with RKM Hive, follow the usual procedures, as described in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator. This topic details information specific to Hive tables.

The reverse-engineering process creates the data stores for the corresponding Hive table or views. You can use the data stores as either a source or a target in a mapping.

For more information about RKM Hive, see RKM Hive.

A storage tab is added to the Hive data store and there is flexibility of how data is stored and formatted within Hive. If the Hive table already exists, you can use the Reverse Engineer process on the Hive model, using the custom Hive RKM to populate the fields.

4.9.3 Reverse Engineering HBase Tables

RKM HBase is used to reverse engineer HBase tables. To perform a customized reverse-engineering of HBase tables with RKM HBase, follow the usual procedures, as described in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator. This topic details information specific to HBase tables.

The reverse-engineering process creates the data stores for the corresponding HBase table. You can use the data stores as either a source or a target in a mapping.

Note:

Ensure that the HBase tables contain some data before performing reverse-engineering. The reverse-engineering operation does not work if the HBase tables are empty.

For more information about RKM HBase, see RKM HBase.

4.9.4 Reverse Engineering HDFS Tables

HDFS files are used in reverse engineering. You can reverse engineer HDFS using file technology or HDFS technology.

Reverse Engineering HDFS with File Technology

HDFS files can be reverse engineered like regular files. To reverse-engineer HDFS files, you must copy them to your File System and follow the same process as that to reverse-engineer regular files.

Note:

If the file is large for your local File System, retrieve the first N records from HDFS and place them in a local file.

Reverse Engineering HDFS with HDFS Technology

To reverse engineer an HDFS file, perform the following steps:

  • Create a HDFS data store.

  • From the Storage Tab, choose from the Storage Format field and corresponding schema file must be specified in the Schema File field.

  • Click Reverse Engineer operation from the Attributes Tab of the HDFS data store.

Note:

There is no need to import an RKM into the project.

HDFS files are used in KMs such as File to Hive, File to Spark and this uses the ODI file technology as a source. You can also use the HDFS LKMs (LKM HDFS File to Hive) and these KMs use the ODI HDFS technology.

Depending on which KMs you want to use, you can select a different technology for the files. Reverse Engineering HDFS will support the Avro, Json, Parquet and delimited formats.

Refer to Reverse-engineer a File Model in Oracle Data Integrator Connectivity and Knowledge Modules Guide for Oracle Data Integrator Developer's Guide for more information.

Creating a Oracle Data Integrator Model from a Reverse-Engineered Hive, HBase, and HDFS Models

4.9.5 Reverse Engineering Cassandra Tables

RKM Cassandra is used to reverse engineer Cassandra tables. To perform a customized reverse-engineering of Cassandra tables with RKM Cassandra, follow the usual procedures, as described in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator. This topic details information specific to Cassandra tables.

The reverse-engineering process creates the data stores for the corresponding Cassandra table. For more information about RKM Cassandra, see RKM Cassandra.

4.10 Loading Data from Files into Hive

The KMs support Loading Data from HDFS, however, the preferred way is to use the HDFS KMs, as described in Loading Data from HDFS into Hive.

  1. Create the data stores for local files and HDFS files.

    Refer to Oracle Data Integrator Connectivity and Knowledge Modules Guide for Oracle Data Integrator Developer's Guide for information about reverse engineering and configuring local file data sources.

  2. Create a mapping using the file data store as the source and the corresponding Hive table as the target.
  3. Use the LKM File to Hive LOAD DATA or the LKM File to Hive LOAD DATA Direct knowledge module specified in the physical diagram of the mapping.

    These integration knowledge modules load data from flat files into Hive, replacing or appending any existing data.

For more information about the KMs, see the following sections:

4.11 Loading Data from Hive to Files

To load data from Hive tables to a local file system or a HDFS file:

  1. Create a data store for the Hive tables that you want to load in flat files.

    Refer to "Setting Up Hive Data Sources" for information about reverse engineering and configuring Hive data sources.

  2. Create a mapping using the Hive data store as the source and the corresponding File data source as the target.
  3. Use the LKM Hive to File Direct knowledge module, specified in the physical diagram of the mapping.

    This integration knowledge module loads data from Hive into flat Files.

    For more information about LKM Hive to File Direct, see LKM Hive to File Direct.

4.12 Loading Data from HBase into Hive

To load data from an HBase table into Hive:

  1. Create a data store for the HBase table that you want to load in Hive.

    Refer to "Setting Up HBase Data Sources" for information about reverse engineering and configuring HBase data sources.

  2. Create a mapping using the HBase data store as the source and the corresponding Hive table as the target.
  3. Use the LKM HBase to Hive HBASE-SERDE knowledge module, specified in the physical diagram of the mapping.

    This knowledge module provides read access to an HBase table from Hive.

For more information about LKM HBase to Hive HBASE-SERDE, see LKM HBase to Hive HBASE-SERDE.

4.13 Loading Data from Hive into Hbase

To load data from a Hive table into HBase:

  1. Create a data store for the Hive tables that you want to load in HBase.

    Refer to "Setting Up Hive Data Sources" for information about reverse engineering and configuring Hive data sources.

  2. Create a mapping using the Hive data store as the source and the corresponding HBase table as the target.
  3. Use the LKM Hive to HBase Incremental Update HBASE-SERDE Direct knowledge module, specified in the physical diagram of the mapping.

    This integration knowledge module loads data from Hive into HBase and supports inserting new rows as well as updating existing data.

For more information about LKM Hive to HBase Incremental Update HBASE-SERDE Direct, see LKM Hive to HBase Incremental Update HBASE-SERDE Direct.

4.14 Loading Data from an SQL Database into Hive, HBase, and File using SQOOP

To load data from an SQL Database into a Hive, HBase, and File target:

  1. Create a data store for the SQL source that you want to load into Hive, HBase, or File target.

    Refer to Oracle Data Integrator Connectivity and Knowledge Modules Guide for Oracle Data Integrator Developer's Guide for information about reverse engineering and configuring SQL data sources.

  2. Create a mapping using the SQL source data store as the source and the corresponding HBase table, Hive table, or HDFS files as the target.
  3. Use the IKM SQL to Hive-HBase-File (SQOOP) knowledge module, specified in the physical diagram of the mapping.

    This integration knowledge module loads data from a SQL source into Hive, HBase, or Files target. It uses SQOOP to load the data into Hive, HBase, and File targets. SQOOP uses parallel JDBC connections to load the data.

For more information about IKM SQL to Hive-HBase-File (SQOOP), see IKM SQL to Hive-HBase-File (SQOOP) [Deprecated].

4.15 Loading Data from an SQL Database into Hive using SQOOP

To load data from an SQL Database into a Hive target:

  1. Create a data store for the SQL source that you want to load into Hive target.

    Refer to Oracle Data Integrator Connectivity and Knowledge Modules Guide for Oracle Data Integrator Developer's Guide for information about reverse engineering and configuring SQL data sources.

  2. Create a mapping using the SQL source data store as the source and the corresponding Hive table as the target.
  3. Use the LKM SQL to Hive SQOOP knowledge module, specified in the physical diagram of the mapping.

    This KM loads data from a SQL source into Hive. It uses SQOOP to load the data into Hive. SQOOP uses parallel JDBC connections to load the data.

For more information about LKM SQL to Hive SQOOP, see LKM SQL to Hive SQOOP.

4.16 Loading Data from an SQL Database into File using SQOOP

To load data from an SQL Database into a File target:

  1. Create a data store for the SQL source that you want to load into File target.

    Refer to Oracle Data Integrator Connectivity and Knowledge Modules Guide for Oracle Data Integrator Developer's Guide for information about reverse engineering and configuring SQL data sources.

  2. Create a mapping using the SQL source data store as the source and the corresponding HDFS files as the target.
  3. Use the LKM SQL to File SQOOP Direct knowledge module, specified in the physical diagram of the mapping.

    This integration knowledge module loads data from a SQL source into Files target. It uses SQOOP to load the data into File targets. SQOOP uses parallel JDBC connections to load the data.

For more information about IKM SQL to Hive-HBase-File (SQOOP), see IKM SQL to Hive-HBase-File (SQOOP) [Deprecated].

4.17 Loading Data from an SQL Database into HBase using SQOOP

To load data from an SQL Database into a HBase target:

  1. Create a data store for the SQL source that you want to load into HBase target.

    Refer to Oracle Data Integrator Connectivity and Knowledge Modules Guide for Oracle Data Integrator Developer's Guide for information about reverse engineering and configuring SQL data sources.

  2. Create a mapping using the SQL source data store as the source and the corresponding HBase table as the target.
  3. Use the LKM SQL to HBase SQOOP Direct knowledge module, specified in the physical diagram of the mapping.

    This integration knowledge module loads data from a SQL source into HBase target. It uses SQOOP to load the data into HBase targets. SQOOP uses parallel JDBC connections to load the data.

For more information about LKM SQL to HBase SQOOP Direct, see LKM SQL to HBase SQOOP Direct.

4.18 Validating and Transforming Data Within Hive

After loading data into Hive, you can validate and transform the data using the following knowledge modules.

4.19 Loading Data into an Oracle Database from Hive and File

Use the knowledge modules listed in the following table to load data from an HDFS file or Hive source into an Oracle database target using Oracle Loader for Hadoop.

Table 4-2 Knowledge Modules to load data into Oracle Database

Knowledge Module Use To...

IKM File-Hive to Oracle (OLH-OSCH)

Load data from an HDFS file or Hive source into an Oracle database target using Oracle Loader for Hadoop.

For more information, see IKM File-Hive to Oracle (OLH-OSCH) [Deprecated].

LKM File to Oracle OLH-OSCH

Load data from an HDFS file into an Oracle staging table using Oracle Loader for Hadoop.

For more information, see LKM File to Oracle OLH-OSCH.

LKM File to Oracle OLH-OSCH Direct

Load data from an HDFS file into an Oracle database target using Oracle Loader for Hadoop.

For more information, see LKM File to Oracle OLH-OSCH Direct.

LKM Hive to Oracle OLH-OSCH

Load data from a Hive source into an Oracle staging table using Oracle Loader for Hadoop.

For more information, see LKM Hive to Oracle OLH-OSCH.

LKM Hive to Oracle OLH-OSCH Direct

Load data from a Hive source into an Oracle database target using Oracle Loader for Hadoop.

For more information, see LKM Hive to Oracle OLH-OSCH Direct.

4.20 Loading Data into an SQL Database from Hbase, Hive and File using SQOOP

Use the knowledge modules listed in the following table to load data from a HDFS file, HBase source, or Hive source into an SQL database target using SQOOP.

Table 4-3 Knowledge Modules to load data into SQL Database

Knowledge Module Use To...

IKM File-Hive to SQL (SQOOP)

Load data from an HDFS file or Hive source into an SQL database target using SQOOP.

For more information, see IKM File-Hive to SQL (SQOOP) [Deprecated].

LKM HBase to SQL SQOOP

Load data from an HBase source into an SQL database target using SQOOP.

For more information, see LKM HBase to SQL SQOOP.

LKM File to SQL SQOOP

Load data from an HDFS file into an SQL database target using SQOOP.

For more information, see LKM File to SQL SQOOP.

LKM Hive to SQL SQOOP

Load data from a Hive source into an SQL database target using SQOOP.

For more information, see LKM Hive to SQL SQOOP.

4.21 Loading Data from Kafka to Spark

Loading data from Kafka to Spark.

  1. Create a data store for the Kafka tables that you want to load in Spark.

    Refer to Setting Up Kafka Data Sources for configuring Kafka data sources.

  2. Create a mapping using the Kafka data store as the source and the corresponding Spark table as the target.
  3. Use the LKM Kafka to Spark zookeeper.connect in case of receiver-based connection or metadata.broker.list in case of direct connection knowledge module, specified in the physical diagram of the mapping.

    This integration knowledge module loads data from Kafka into Spark and supports inserting new rows as well as updating existing data.

    Note:

    Every Kafka source in an ODI mapping allocates a Spark executor. A Spark Kafka mapping hangs if the number of available executors is low. The number of executors must be atleast n+1 where n is the number of Kafka sources in the mapping. For additional information, refer to Spark Documentation.
For more information about LKM Kafka to Spark, see LKM Kafka to Spark.