Re: HADOOP Oracle connection workaround

[Mridul Muralidharan]

Would be better to externalize this through either a template - or at 
the least, message bundles.

- Mridul

evana wrote:
Out of the box implementation hadoop has some issues in connecting to oracle.
Loos like DBInputFomat is built keeping mysql/hsqldb in mind. You need to
modify the out of the box implementation of getSelectQuery method in
DBInputFomat.

WORK AROUND
here is the code snippet...(remember this works only on oracle. if you want
to get it working on any db other than oracle you have to have if-else logic
on db type)

protected String getSelectQuery() {
        StringBuilder query = new StringBuilder();
if(dbConf.getInputQuery() == null) {
                        query.append("SELECT ");
                
                        for (int i = 0; i < fieldNames.length; i++) {
                          query.append(fieldNames[i]);
                          if(i != fieldNames.length -1) {
                            query.append(", ");
                          }
                        }
                
                        query.append(" FROM ").append(tableName);
                        if (conditions != null && conditions.length() > 0)
                                query.append(" WHERE ").append(conditions);
                        String orderBy = dbConf.getInputOrderBy();
                        if(orderBy != null && orderBy.length() > 0) {
                                query.append(" ORDER BY ").append(orderBy);
                        }
                      }else {
                        //PREBUILT QUERY
                        query.append(dbConf.getInputQuery());
                      }
		      
			try {
                                if(split.getLength() > 0 && split.getStart() > 
0){
                                        String querystring = query.toString();

                                        query = new StringBuilder();
                                        query.append("select * from (select 
a.*,rownum rno from ( ");
                                        query.append(querystring);
                                        query.append(" ) a where rownum <= 
").append(split.getStart()).append("
+ ").append(split.getLength());
                                        query.append(" ) where rno >= 
").append(split.getStart());
                                }
                        }catch (IOException ex) {
                                //ignore, will not throw
                        }
     return query.toString();
}