You are viewing a plain text version of this content. The canonical link for it is here.
Posted to common-user@hadoop.apache.org by "Biju .B" <bi...@gmail.com> on 2010/09/24 10:36:45 UTC

Help for Sqlserver querying with hadoop

Hi

Need urgent help on using sql server with hadoop

am using following code to connect to database

DBConfiguration.configureDB(conf,"com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://xxx.xxx.xxx.xxx;user=abc;password=abc;DatabaseName=dbname");
String [] fields = { "id", "url" };
String [] fields = { "id", "url" };
DBInputFormat.setInput(conf,MyRecord.class,"urls",null,"id", fields);

Am getting following error

10/09/24 13:26:42 INFO mapred.JobClient: Task Id :
attempt_201009231924_0008_m_000001_2, Status : FAILED
java.io.IOException: Incorrect syntax near 'LIMIT'.
    at
org.apache.hadoop.mapreduce.lib.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
    at
org.apache.hadoop.mapreduce.lib.db.DBRecordReader.next(DBRecordReader.java:204)
    at
org.apache.hadoop.mapred.lib.db.DBInputFormat$DBRecordReaderWrapper.next(DBInputFormat.java:118)
    at
org.apache.hadoop.mapred.lib.db.DBInputFormat$DBRecordReaderWrapper.next(DBInputFormat.java:87)
    at
org.apache.hadoop.mapred.MapTask$TrackedRecordReader.moveToNext(MapTask.java:192)
    at
org.apache.hadoop.mapred.MapTask$TrackedRecordReader.next(MapTask.java:176)
    at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:48)
    at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:358)
    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)
    at org.apache.hadoop.mapred.Child.main(Child.java:170)



Found that the error is due to query that each task tries to execute

SELECT id, url FROM urls AS urls ORDER BY id LIMIT 13228 OFFSET 13228


the "LIMIT" and "OFFSET" are not valid in Sqlserver and it returns error

Pls tell me how to solve this problem

Regards
Biju

Re: Help for Sqlserver querying with hadoop

Posted by "Biju .B" <bi...@gmail.com>.
Sonal

Thanks, Will try it and and let you know

Regards
Biju

On Sun, Sep 26, 2010 at 12:00 AM, Sonal Goyal <so...@gmail.com> wrote:

> Biju,
>
> Have you tried using DataDrivenDBInputFormat?
>
> Thanks and Regards,
> Sonal
>
> Sonal Goyal | Founder and CEO | Nube Technologies LLP
> Ph: +91-8800541717 | sonal@nubetech.co | Skype: sonal.goyal
> http://www.nubetech.co | http://in.linkedin.com/in/sonalgoyal
>
>
>
>
>
> On Fri, Sep 24, 2010 at 2:06 PM, Biju .B <bi...@gmail.com> wrote:
>
> > Hi
> >
> > Need urgent help on using sql server with hadoop
> >
> > am using following code to connect to database
> >
> >
> >
> DBConfiguration.configureDB(conf,"com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://xxx.xxx.xxx.xxx;user=abc;password=abc;DatabaseName=dbname");
> > String [] fields = { "id", "url" };
> > String [] fields = { "id", "url" };
> > DBInputFormat.setInput(conf,MyRecord.class,"urls",null,"id", fields);
> >
> > Am getting following error
> >
> > 10/09/24 13:26:42 INFO mapred.JobClient: Task Id :
> > attempt_201009231924_0008_m_000001_2, Status : FAILED
> > java.io.IOException: Incorrect syntax near 'LIMIT'.
> >    at
> >
> >
> org.apache.hadoop.mapreduce.lib.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
> >    at
> >
> >
> org.apache.hadoop.mapreduce.lib.db.DBRecordReader.next(DBRecordReader.java:204)
> >    at
> >
> >
> org.apache.hadoop.mapred.lib.db.DBInputFormat$DBRecordReaderWrapper.next(DBInputFormat.java:118)
> >    at
> >
> >
> org.apache.hadoop.mapred.lib.db.DBInputFormat$DBRecordReaderWrapper.next(DBInputFormat.java:87)
> >    at
> >
> >
> org.apache.hadoop.mapred.MapTask$TrackedRecordReader.moveToNext(MapTask.java:192)
> >    at
> >
> org.apache.hadoop.mapred.MapTask$TrackedRecordReader.next(MapTask.java:176)
> >    at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:48)
> >    at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:358)
> >    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)
> >    at org.apache.hadoop.mapred.Child.main(Child.java:170)
> >
> >
> >
> > Found that the error is due to query that each task tries to execute
> >
> > SELECT id, url FROM urls AS urls ORDER BY id LIMIT 13228 OFFSET 13228
> >
> >
> > the "LIMIT" and "OFFSET" are not valid in Sqlserver and it returns error
> >
> > Pls tell me how to solve this problem
> >
> > Regards
> > Biju
> >
>

Re: Help for Sqlserver querying with hadoop

Posted by Sonal Goyal <so...@gmail.com>.
Biju,

Have you tried using DataDrivenDBInputFormat?

Thanks and Regards,
Sonal

Sonal Goyal | Founder and CEO | Nube Technologies LLP
Ph: +91-8800541717 | sonal@nubetech.co | Skype: sonal.goyal
http://www.nubetech.co | http://in.linkedin.com/in/sonalgoyal





On Fri, Sep 24, 2010 at 2:06 PM, Biju .B <bi...@gmail.com> wrote:

> Hi
>
> Need urgent help on using sql server with hadoop
>
> am using following code to connect to database
>
>
> DBConfiguration.configureDB(conf,"com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://xxx.xxx.xxx.xxx;user=abc;password=abc;DatabaseName=dbname");
> String [] fields = { "id", "url" };
> String [] fields = { "id", "url" };
> DBInputFormat.setInput(conf,MyRecord.class,"urls",null,"id", fields);
>
> Am getting following error
>
> 10/09/24 13:26:42 INFO mapred.JobClient: Task Id :
> attempt_201009231924_0008_m_000001_2, Status : FAILED
> java.io.IOException: Incorrect syntax near 'LIMIT'.
>    at
>
> org.apache.hadoop.mapreduce.lib.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
>    at
>
> org.apache.hadoop.mapreduce.lib.db.DBRecordReader.next(DBRecordReader.java:204)
>    at
>
> org.apache.hadoop.mapred.lib.db.DBInputFormat$DBRecordReaderWrapper.next(DBInputFormat.java:118)
>    at
>
> org.apache.hadoop.mapred.lib.db.DBInputFormat$DBRecordReaderWrapper.next(DBInputFormat.java:87)
>    at
>
> org.apache.hadoop.mapred.MapTask$TrackedRecordReader.moveToNext(MapTask.java:192)
>    at
> org.apache.hadoop.mapred.MapTask$TrackedRecordReader.next(MapTask.java:176)
>    at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:48)
>    at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:358)
>    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)
>    at org.apache.hadoop.mapred.Child.main(Child.java:170)
>
>
>
> Found that the error is due to query that each task tries to execute
>
> SELECT id, url FROM urls AS urls ORDER BY id LIMIT 13228 OFFSET 13228
>
>
> the "LIMIT" and "OFFSET" are not valid in Sqlserver and it returns error
>
> Pls tell me how to solve this problem
>
> Regards
> Biju
>