| Oracle® Big Data Connectors User's Guide Release 1 (1.1) Part Number E36049-03 |
|
|
PDF · Mobi · ePub |
| Oracle® Big Data Connectors User's Guide Release 1 (1.1) Part Number E36049-03 |
|
|
PDF · Mobi · ePub |
This chapter describes how to use the knowledge modules in Oracle Data Integrator (ODI) Application Adapter for Hadoop. It contains the following sections:
Apache Hadoop is designed to handle and process data that is typically from data sources that are nonrelational and data volumes that are beyond what is handled by relational databases.
Oracle Data Integrator (ODI) Application Adapter for Hadoop enables data integration developers to integrate and transform data easily within Hadoop using Oracle Data Integrator. Employing familiar and easy-to-use tools and preconfigured knowledge modules (KMs), the application adapter provides the following capabilities:
Loading data into Hadoop from the local file system and HDFS
Performing validation and transformation of data within Hadoop
Loading processed data from Hadoop to an Oracle database for further processing and generating reports
Knowledge modules (KMs) contain the information needed by Oracle Data Integrator to perform a specific set of tasks against a specific technology. An application adapter is a group of knowledge modules. Thus, ODI Application Adapter for Hadoop is a group of knowledge modules for accessing data stored in Hadoop.
Typical processing in Hadoop includes data validation and transformations that are programmed as MapReduce jobs. Designing and implementing a MapReduce job requires expert programming knowledge. However, when you use Oracle Data Integrator and ODI Application Adapter for Hadoop, you do not need to write MapReduce jobs. Oracle Data Integrator uses Hive and the Hive Query Language (HiveQL), a SQL-like language for implementing MapReduce jobs.
When you implement a big data processing scenario, the first step is to load the data into Hadoop. The data source is typically in the local file system, HDFS, Hive tables, or external Hive tables.
After the data is loaded, you can validate and transform it by using HiveQL like you use SQL. You can perform data validation (such as checking for NULLS and primary keys), and transformations (such as filtering, aggregations, set operations, and derived tables). You can also include customized procedural snippets (scripts) for processing the data.
When the data has been aggregated, condensed, or processed into a smaller data set, you can load it into an Oracle database for further processing and analysis. Oracle Loader for Hadoop is recommended for optimal loading into an Oracle database.
Oracle Data Integrator provides the knowledge modules (KMs) described in Table 4-1 for use with Hadoop.
Table 4-1 ODI Application Adapter for Hadoop Knowledge Modules
| KM Name | Description | Source | Target |
|---|---|---|---|
|
Loads data from local and HDFS files into Hive tables. It provides options for better performance through Hive partitioning and fewer data movements. |
File system |
Hive |
|
|
Integrates data into a Hive target table in truncate/insert (append) mode. Data can be controlled (validated). Invalid data is isolated in an error table and can be recycled. |
Hive |
Hive |
|
|
Integrates data into a Hive target table after the data has been transformed by a customized script such as Perl or Python |
Hive |
Hive |
|
|
Integrates data from an HDFS file or Hive source into an Oracle database target using Oracle Loader for Hadoop |
File system or Hive |
Oracle Database |
|
|
Validates data against constraints |
NA |
Hive |
|
|
Reverse engineers Hive tables |
Hive metadata |
NA |
For security information for Oracle Data Integrator, see the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
To set up the topology in Oracle Data Integrator, you identify the data server and the physical and logical schemas that are used to store the file system and Hive information.
This section contains the following topics:
Setting Up the Oracle Data Integrator Agent to Execute Hadoop Jobs
Configuring Oracle Data Integrator Studio for Executing Hadoop Jobs on the Local Agent
In the Hadoop context, there is a distinction between files in Hadoop Distributed File System (HDFS) and local files (files outside of HDFS).
To define a data source:
Create a DataServer object under File technology.
Create a Physical Schema object for every directory to be accessed.
Create a Logical Schema object for every directory to be accessed.
Create a Model for every Logical Schema.
Create one or more data stores for each different type of file and wildcard name pattern.
For HDFS files, create a DataServer object under File technology by entering the HDFS name node in the field JDBC URL. For example:
hdfs://bda1node01.example.com:8020
Note:
No dedicated technology is defined for HDFS files.The following steps in Oracle Data Integrator are required for connecting to a Hive system. Oracle Data Integrator connects to Hive by using JDBC.
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.
You must add all Hive-specific flex fields. For pre-11.1.1.6.0 repositories, the flex fields are added during the repository upgrade process.
To set up a Hive data source:
Place all required Hive JDBC JAR files into the Oracle Data Integrator user lib folder:
$HIVE_HOME/lib/*.jar $HADOOP_HOME/hadoop-*-core*.jar, $HADOOP_HOME/Hadoop-*-tools*.jar
Create a DataServer object under Hive technology.
Set the following locations under JDBC:
JDBC Driver: org.apache.hadoop.hive.jdbc.HiveDriver
JDBC URL: for example, jdbc:hive://BDA:10000/default
Set the following under Flexfields:
Hive Metastore URIs: for example, thrift://BDA:10000
Create a Physical Default Schema.
As of Hive 0.7.0, no schemas or databases are supported. Only Default is supported. Enter default in both schema fields of the physical schema definition.
Ensure that the Hive server is up and running.
Test the connection to the DataServer.
Create a Logical Schema object.
Create at least one Model for the LogicalSchema.
Import RKM Hive as a global knowledge module or into a project.
Create a new model for Hive Technology pointing to the logical schema.
Perform a custom reverse-engineering operation using RKM Hive.
At the end of this process, the Hive DataModel contains all Hive tables with their columns, partitioning, and clustering details stored as flex field values.
After setting up an Oracle Data Integrator agent, configure it to work with ODI Application Adapter for Hadoop.
To configure the Oracle Data Integrator agent:
Install Hadoop on your Oracle Data Integrator agent computer. Ensure that the HADOOP_HOME environment variable is set.
For Oracle Big Data Appliance, see the Oracle Big Data Appliance Software User's Guide for instructions for setting up a remote Hadoop client.
Install Hive on your Oracle Data Integrator agent computer. Ensure that the HIVE_HOME environment variable is set.
Copy these JAR files to the Oracle Data Integrator agent drivers directory.
$HIVE_HOME/lib/*.jar, $HADOOP_HOME/hadoop-*-core*.jar $HADOOP_HOME/hadoop-*-tools*.jar
See the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator for information about adding drivers to the agent. This step enables the Oracle Data Integrator agent to load the Hive JDBC driver.
Set environment variable ODI_HIVE_SESSION_JARS to include Hive Regex SerDe:
ODI_HIVE_SESSION_JARS=$HIVE_HOME/lib/hive-contrib-0.7.1-cdh3u3.jar
Include other JAR files as required, such as custom SerDes JAR files. These JAR files are added to every Hive JDBC session and thus are added to every Hive MapReduce job.
Set environment variable HADOOP_CLASSPATH:
HADOOP_CLASSPATH=$HIVE_HOME/lib/hive-metastore-0.7.1-cdh3u3.jar:$HIVE_HOME/lib/libthrift.jar:$HIVE_HOME/lib/libfb303.jar:$HIVE_HOME/lib/hive-common-0.7.1-cdh3u3.jar:$HIVE_HOME/lib/hive-exec-0.7.1-cdh3u3.jar.
This setting enables the Hadoop script to start Hive MapReduce jobs.
To use Oracle Loader for Hadoop:
Install Oracle Loader for Hadoop on your Oracle Data Integrator agent system. See "Installing Oracle Loader for Hadoop".
Install Oracle Database client on your Oracle Data Integrator agent system. See "Oracle Loader for Hadoop Setup" for the Oracle Database client version.
Set environment variable ODI_OLH_JARS.
You must list all JAR files required for Oracle Loader for Hadoop. See the oracle.hadoop.loader.libjars property in "OraLoader for Hadoop Configuration Properties".
This is a comma-separated list of JAR files:
ODI_OLH_JARS=OLH_HOME/jlib/ojdbc6.jar,$OLH_HOME/jlib/orai18n.jar,$OLH_HOME/jlib/orai18n-utility.jar,$OLH_HOME/jlib/orai18n-mapping.jar,$OLH_HOME/jlib/orai18n-collation.jar,$OLH_HOME/jlib/oraclepki.jar,$OLH_HOME/jlib/osdt_cert.jar,$OLH_HOME/jlib/osdt_core.jar,$OLH_HOME/jlib/commons-math-2.2.jar,$OLH_HOME/jlib/jackson-core-asl-1.5.2.jar,$OLH_HOME/jlib/jackson-mapper-asl-1.5.2.jar,$OLH_HOME/jlib/avro-1.5.4.jar,$OLH_HOME/jlib/avro-mapred-1.5.4.jar,$OLH_HOME/jlib/oraloader.jar,$HIVE_HOME/lib/hive-metastore-0.7.1-cdh3u3.jar,$HIVE_HOME/lib/libthrift.jar,$HIVE_HOME/lib/libfb303.jar,$HIVE_HOME/lib/hive-common-0.7.1-cdh3u3.jar,$HIVE_HOME/lib/hive-exec-0.7.1-cdh3u3.jar
Add paths to HADOOP_CLASSPATH:
$HADOOP_CLASSPATH= $OLH_HOME/jlib:$HADOOP_CLASSPATH
Verify that ODI_OLH_SHAREDLIBS lists all native libraries required for Oracle Loader for Hadoop. See the oracle.hadoop.loader.sharedLibs property in the "OraLoader for Hadoop Configuration Properties".
This is a comma-separated list of shared libraries files:
ODI_OLH_SHAREDLIBS= $OLH_HOME/lib/libolh11.so,$OLH_HOME/lib/libclntsh.so.11.1,$OLH_HOME/lib/libnnz11.so,$OLH_HOME/lib/libociei.so
If Oracle Loader for Hadoop is used in OCI Direct Path output format, then check these variables:
For executing Hadoop jobs on the local agent of an Oracle Data Integrator Studio installation, follow the configuration steps in the previous section with the following change: Copy JAR files into the Oracle Data Integrator userlib directory instead of the drivers directory.
Setting up a project follows the standard procedures. See the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Import the following KMs into Oracle Data Integrator project:
IKM File to Hive (Load Data)
IKM Hive Control Append
IKM Hive Transform
IKM File-Hive to Oracle (OLH)
CKM Hive
RKM Hive
This section contains the following topics:
To create a model that is based on the technology hosting Hive and on the logical schema created when you configured the Hive connection, follow the standard procedure described in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
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 the Oracle Fusion Middleware Developer's Guide for 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 an integration interface.
RKM Hive reverses these metadata elements:
Hive tables and views as Oracle Data Integrator data stores.
Specify the reverse mask in the Mask field, and then select the tables and views to reverse. The Mask field in the Reverse tab filters reverse-engineered objects based on their names. The Mask field cannot be empty and must contain at least the percent sign (%).
Hive columns as Oracle Data Integrator columns with their data types.
Information about buckets, partitioning, clusters, and sort columns are set in the respective flex fields in the Oracle Data Integrator data store or column metadata.
Table 4-2 describes the options for RKM Hive.
| Option | Description |
|---|---|
|
Log intermediate results? |
|
|
Path and file name of log file. Default path is the user home and the default file name is reverse.log. |
Table 4-3 describes the created flex fields.
Table 4-3 Flex Fields for Reverse-Engineered Hive Tables and Views
| Object | Flex Field Name | Flex Field Code | Flex Field Type | Description |
|---|---|---|---|---|
|
DataStore |
|
String |
Number of buckets to be used for clustering |
|
|
Column |
Hive Partition Column |
|
Numeric |
All partitioning columns are marked as "1". Partition information can come from the following:
|
|
Column |
Hive Cluster Column |
|
Numeric |
All cluster columns are marked as "1". |
|
Column |
Hive Sort Column |
|
Numeric |
All sort columns are marked as "1". |
After reverse engineering Hive tables and configuring them, you can choose from these interface configurations:
To load data from the local file system or the HDFS file system into Hive tables:
Create the data stores for local files and HDFS files.
Refer to the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator for information about reverse engineering and configuring local file data sources.
Create an interface using the file data store as the source and the corresponding Hive table as the target. Use the IKM File to Hive (Load Data) knowledge module specified in the flow tab of the interface. This integration knowledge module loads data from flat files into Hive, replacing or appending any existing data.
IKM File to Hive (Load Data) supports:
One or more input files. To load multiple source files, enter an asterisk or a question mark as a wildcard character in the resource name of the file DataStore (for example, webshop_*.log).
Fixed length
Delimited
Customized format
Immediate or deferred loading
Overwrite or append
Hive external tables
Table 4-4 describes the options for IKM File to Hive (Load Data). See the knowledge module for additional details.
Table 4-4 IKM File to Hive Options
| Option | Description |
|---|---|
|
Create target table. |
|
|
Truncate data in target table. |
|
|
Is the file in the local file system or in HDFS? |
|
|
Use an externally managed Hive table. |
|
|
Use a Hive staging table. Select this option if the source and target do not match or if the partition column value is part of the data file. If the partitioning value is provided by a file name fragment or a constant in target mapping, then set this value to |
|
|
Remove temporary objects after the interface execution. |
|
|
Load data into the final target now or defer? |
|
|
Provide a parsing expression for handling a custom file format to perform the mapping from source to target. |
|
|
Stop if no source file is found? |
After loading data into Hive, you can validate and transform the data using the following knowledge modules.
This knowledge module validates and controls the data, and integrates it into a Hive target table in truncate/insert (append) mode. Invalid data is isolated in an error table and can be recycled. IKM Hive Control Append supports inline view interfaces that use either this knowledge module or IKM Hive Transform.
Table 4-5 lists the options. See the knowledge module for additional details.
This knowledge module checks data integrity for Hive tables. It verifies the validity of the constraints of a Hive data store and diverts the invalid records to an error table. You can use CKM Hive for static control and flow control. You must also define these constraints on the stored data.
Table 4-6 lists the options for this check knowledge module. See the knowledge module for additional details.
This knowledge module performs transformations. It uses a shell script to transform the data, and then integrates it into a Hive target table using replace mode. The knowledge module supports inline view interfaces and can be used as an inline-view for IKM Hive Control Append.
The transformation script must read the input columns in the order defined by the source data store. Only mapped source columns are streamed into the transformations. The transformation script must provide the output columns in the order defined by the target data store.
Table 4-7 lists the options for this integration knowledge module. See the knowledge module for additional details.
Table 4-7 IKM Hive Transform Options
| Option | Description |
|---|---|
|
Create target table? |
|
|
Remove temporary objects after execution? |
|
|
Script file name |
|
|
Script content |
|
|
Provides an optional, comma-separated list of source column names, which enables the knowledge module to distribute the data before the transformation script is applied |
|
|
Provide an optional, comma-separated list of source column names, which enables the knowledge module to sort the data before the transformation script is applied |
|
|
Provides an optional, comma-separated list of target column names, which enables the knowledge module to distribute the data after the transformation script is applied |
|
|
Provides an optional, comma-separated list of target column names, which enables the knowledge module to sort the data after the transformation script is applied |
IKM File-Hive to Oracle (OLH) integrates data from an HDFS file or Hive source into an Oracle database target using Oracle Loader for Hadoop. Using the interface configuration and the selected options, the knowledge module generates an appropriate Oracle Database target instance. Hive and Hadoop versions must follow the Oracle Loader for Hadoop requirements.
See Also:
"Oracle Loader for Hadoop Setup" for required versions of Hadoop and Hive
"Setting Up the Oracle Data Integrator Agent to Execute Hadoop Jobs" for required environment variable settings
Table 4-8 lists the options for this integration knowledge module. See the knowledge module for additional details.
Table 4-8 IKM File - Hive to Oracle (OLH)
| Option | Description |
|---|---|
|
Specify JDBC, OCI, or Data Pump for data transfer. |
|
|
Create target table? |
|
|
Materialize Hive source data before extract? |
|
|
Use an Oracle database staging table? |
|
|
Shared file path used for Oracle Data Pump transfer. |
|
|
Local path for temporary files. |
|
|
Options for flow (stage) table creation when you are using an Oracle database staging table. |
|
|
Remove temporary objects after execution? |
|
|
Set to handle custom file formats. |
|
|
Optional Oracle Loader for Hadoop configuration file properties |
|
|
Truncate data in target table? |
|
|
Delete all data in target table? |
|
 Copyright © 2011, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
