9 Oracle DataSource for Apache Hadoop (OD4H)

While executing a query on a Hive external table through OTD4H, the underlying Oracle table is dynamically divided into granules, which correspond to splits on the Hadoop side. Each split is processed by a single map task. With the help of the ORACLE_SPLITTER_KIND property, you can specify how the splits are created. This ensures that the splits are a good match for the physical structure of the target table in Oracle Database.

The different kinds of splitters available are:

Following is an illustration of ROW_SPLITTER:

DROP TABLE employees;
 
CREATE EXTERNAL TABLE employees (
  EMPLOYEE_ID INT,
  FIRST_NAME  STRING,
  LAST_NAME   STRING,
  SALARY      DOUBLE,
  HIRE_DATE   TIMESTAMP,
  JOB_ID      STRING
 )
 STORED BY 'oracle.hcat.osh.OracleStorageHandler'
 
WITH SERDEPROPERTIES (
 'oracle.hcat.osh.columns.mapping' = 'employee_id,first_name,last_name,salary,hire_date,job_id')

TBLPROPERTIES (
'mapreduce.jdbc.url' = 'jdbc:oracle:thin:@localhost:1521:orcl',     
'mapreduce.jdbc.username' = 'hr',
'mapreduce.jdbc.password' = 'hr',
'mapreduce.jdbc.input.table.name' = 'EMPLOYEES',     
'oracle.hcat.osh.splitterKind' = 'ROW_SPLITTER',     
'oracle.hcat.osh.rowsPerSplit' = '1500' 
);  

SINGLE_SPLITTER is used by default if no splitter is specified in the table properties for Hive external table, and the target Oracle table is not partitioned.

For an unpartitioned table, the default value of oracle.hcat.osh.maxSplits will be 1. For partitioned table, the default value of the same will be 64, and the default splitter will be PARTITION_SPLITTER. The default for maxSplits is set to limit the number of connections to the Oracle server. To increase this limit, you must increase the value of oracle.hcat.osh.maxSplits explicitly in hive table properties.

Use the following guidelines while choosing a splitter kind for a hive external table:

Predicate Pushdown is an optimization technique, in which you push predicates (WHERE condition) down to be evaluated by Oracle Database at the time of querying. This minimizes the amount of data fetched from Oracle Database to Hive, while performing a query.

Set the configuration property hive.optimize.ppd to either true or false for enabling Predicate Pushdown. The default value on hive-1.1.0 is set to true. Hence, Predicate Pushdown is always performed, unless you want to disable it.

Another option to reduce the amount of data fetched from the Oracle Database is to specify a condition at the time of table creation, using TBLPROPERTY mapreduce.jdbc.input.conditions. For instance:

mapreduce.jdbc.input.conditions = 'key > 10 OR key = 0'.

This will restrict the rows fetched from Oracle Database whenever any query is performed based on the condition specified. The external table that gets created, is analogous to a view on Oracle Database. This approach is only useful when you want to push down complex predicates that cannot be analyzed and automatically pushed down by OD4H.

Projection Pushdown is an optimization technique that fetches only the required columns from Oracle Database when a query is performed. If you want to fetch all columns during a query (not recommended), you can disable it by setting the hive.io.file.read.all.columns connection property to true. On Hive–1.1.0, this property is false by default.

If you refer to Employee Data Partition table, the partitions irrelevant to the query are removed from the partition list. This is done by executing an explain plan on the query to obtain the list of partitions and sub-partitions that are relevant to the query.

Table level partition pruning uses table level predicate pushdown, on the other hand partition pruning at the query level uses query level predicate pushdown.

Partition pruning is active when a SELECT query is run, in which the WHERE clause uses the partitioning key. Following is an example of partition pruning:

To query the partition, where salary is in the above range and prune other partitions, perform the following:

Hive External Table:

CREATE EXTERNAL TABLE EmployeeDataPartitioned (
 Emp_ID int,
 First_Name string,
 Last_Name string,
 Job_Title string,
 Salary int
)
STORED BY 'oracle.hcat.osh.OracleStorageHandler'
WITH SERDEPROPERTIES (
     'oracle.hcat.osh.columns.mapping' = 'Emp_ID,First_Name,Last_Name,Job_Title,Salary')
TBLPROPERTIES (
 'mapreduce.jdbc.url' = '${hiveconf:connection_string}',
 'mapreduce.jdbc.username' = '${hiveconf:oracle_user}',
 'mapreduce.jdbc.password' = '${hiveconf:oracle_pwd}',
 'mapreduce.jdbc.input.table.name' = 'EmployeeData',
 'oracle.hcat.osh.oosKind' = 'PARTITIONED_TABLE'
);

The following SELECT statement shows how to query the partition, where salary is between 72000 to 78000, and prunes other partitions:

select * from EmployeeDataPartitioned where salary > 72000 and salary < 78000;

OD4H uses Oracle JDBC driver for connecting to Oracle Database. It provides all authentication methods supported by Oracle JDBC. OD4H supports authentication through use of basic authentication (username and password), Oracle Wallet, and Kerberos. You can specify the authentication to be used for a table created in Hive, through the oracle.hcat.osh.authentication table property. This is useful only for strong authentication.

• Kerberos

• Oracle Wallet

• Basic Authentication

The various authentication processes are described with examples as follows:

1. Kerberos

   Uses Kerberos credentials of the Hadoop engine process. This principal should have access to the table.

   See Also:

   Oracle Database JDBC Developer's Guide for information on configuring database for Kerberos and details of client parameters

   You can enable Kerberos configuration on Hive, by adding to hive-env.sh the following:

   export HADOOP_OPTS="$HADOOP_OPTS -Djava.security.krb5.conf=<path to kerberos configuration>
   

   To enable child JVMs to use Kerberos configuration, edit the mapred-site.xml to include the following property on all nodes of the cluster:

   <property><name>mapred.child.java.opts</name>  <value>-Djava.security.krb5.conf=<path to kerberos configuration>></value></property>
   

   Enable these configurations on BDA using Cloudera manager..

   Following is an illustration of Kerberos authentication:

   CREATE EXTERNAL TABLE kerb_example (
   id DECIMAL,
   name STRING,
   salary DECIMAL
   )
   STORED BY 'oracle.hcat.osh.OracleStorageHandler'
   WITH SERDEPROPERTIES (
                   'oracle.hcat.osh.columns.mapping' = 'id,name,salary')
   TBLPROPERTIES (
   'mapreduce.jdbc.url' = 'jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
   (HOST=adc*******.xxxxxx.com)(PORT=5521))(CONNECT_DATA=
   (SERVICE_NAME=project_name.xxx.rdbms.xxxx.com)))',
   'mapreduce.jdbc.input.table.name' = 'kerb_example',
   'mapreduce.jdbc.username' = 'CLIENT@xxxxxx.COM',
   'oracle.hcat.osh.authentication' = 'KERBEROS',
   'oracle.net.kerberos5_cc_name' = '/tmp/krb5cc_xxxxx',
   'java.security.krb5.conf' = '/home/user/kerberos/krb5.conf',
   'oracle.hcat.osh.kerb.callback' = 'KrbCallbackHandler',
   'sun.security.krb5.principal' = 'CLIENT@xxxxx.COM'
   );
   

   The path specified in oracle.security.krb5.conf should be accessible to all nodes of the cluster. These paths should also match with the path of the corresponding properties in Oracle Database sqlnet.ora.The keytab path provided in sqlnet.ora should also be accessible from all nodes of the cluster.

   If sun.security.krb5.principal is not specified, OD4H will attempt to authenticate using default principal in Credential Cache specified by the oracle.net.kerberos5_cc_name property.

   Note:

   The callback will be called only if the principal cannot be authenticated using a ticket obtained from the credential cache specified in oracle.net.kerberos5_cc_nameproperty.

   A simple callback handler class is described as follows (The callback class must be available to the hive classpath):

   class KrbCallbackHandler 
                   implements CallbackHandler{
   
   @Override
   public void handle(Callback[] callbacks) throws IOException,
                   UnsupportedCallbackException{
   for (int i = 0; i < callbacks.length; i++){
           if (callbacks[i] instanceof PasswordCallback){
           PasswordCallback pc = (PasswordCallback)callbacks[i];
           System.out.println("set password to 'welcome'");
           pc.setPassword((new String("welcome")).toCharArray());
   } else if (callbacks[i] instanceof NameCallback) {
           ((NameCallback)callbacks[i]).setName("client@xxxxx.COM");
   }else{
           throw new UnsupportedCallbackException(callbacks[i],
                           "Unrecognized Callback");
                   }
           }
   }
   

2. Oracle Wallet

   The wallet should be available in the OS environment of each engine process. Following is an illustration of how to add Wallet authentication:

   CREATE EXTERNAL TABLE wallet_example (
           id DECIMAL,
           name STRING,
           salary DECIMAL
   )
   STORED BY 'oracle.hcat.osh.OracleStorageHandler'
   WITH SERDEPROPERTIES (
                   'oracle.hcat.osh.columns.mapping' = 'id,name,salary')
   TBLPROPERTIES (
   'mapreduce.jdbc.url' = 'jdbc:oracle:thin:/@inst1',
   'mapreduce.jdbc.input.table.name' = 'wallet_example',
   'oracle.hcat.osh.authentication' = 'ORACLE_WALLET',
   'oracle.net.tns_admin' = '/scratch/user/view_storage/user_project6/work',
   'oracle.net.wallet_location' = '/scratch/user/view_storage/user_project6/work'
   );
   

   Note:

   The paths specified in oracle.net.tns_admin and oracle.net.wallet_location should be accessible from all nodes of the cluster.

   See Also:

   Managing the Secure External Password Store for Password Credentials section in the Oracle Database Security Guide.

3. Basic Authentication (for demo purposes only)

   This is stored in HCatalog TBLPROPERTIES or supplied on HiveQL SELECT statement.

   When Basic Authentication is used, the username and password for Oracle Schema is specified in Hive external Table properties.

   Note

   Oracle does not recommend this in the production environment, since the password is stored in clear in HCatalog.