HADOOP Oracle connection workaround

[evana <ev...@ymail.com>]

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();
}
-- 
View this message in context: http://www.nabble.com/HADOOP-2536-supports-Oracle-too--tp21823199p22471395.html
Sent from the Hadoop core-user mailing list archive at Nabble.com.

Re: HADOOP Oracle connection workaround

[Mridul Muralidharan <mr...@yahoo-inc.com>]

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();
> }