You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by "Martin, Nick" <Ni...@pssd.com> on 2014/06/21 13:18:33 UTC
Oracle Special Character Password
I do a lot of Oracle imports/exports everyday using Sqoop with good success. However, I keep hitting a gotcha - any time we try to import from an Oracle database that had a password with special characters we get invalid username/pw error.
If we get the password changed to something without a special character it works like a charm. Problem is we're now faced with some databases that have a special character password required so we're kinda hosed.
Using --password via CLI currently.
Any ideas?
Sent from my iPhone
RE: Oracle Special Character Password
Posted by "Martin, Nick" <Ni...@pssd.com>.
Thanks for the tip Gwen. Long story short, got it working (used the link you sent, plus a couple other I found to frame up my fix). For posterity/user list search sake:
I ended up needing to escape several times inside the pw as I had three special characters in there. This worked:
--connect jdbc:oracle:thin:username/\"pass\&\!\123\#w\"@xxx.xxx.xxx.xxx:1523/SCHEMA
Fun ☺
From: Gwen Shapira [mailto:gshapira@cloudera.com]
Sent: Monday, June 23, 2014 10:55 AM
To: user@sqoop.apache.org
Subject: Re: Oracle Special Character Password
I think your password is getting mangled by the command line.
Try wrapping it in quotes.
The best way to test what quoting or escaping is needed is to test in SQLPlus.
Something like:
sqlplus nick/"p@ssword\!"@ORCL
Once you figure out how to use the password on the command line with SQLPlus, you can use the same thing with Sqoop.
See this blog for an example: http://nadvi.blogspot.com/2011/02/oracle-password-with-or-special.html
Gwen
On Mon, Jun 23, 2014 at 7:19 AM, Martin, Nick <Ni...@pssd.com>> wrote:
Thanks for having a look...tested the user/pw in SQL Developer and worked fine.
CLI:
sqoop import --target-dir /user/NiMartin/xxx/yyyyy --table SCHEMA.TABLE --connect jdbc:oracle:thin:@xxx.xxx.xxx.com:1523/SCHEMA<http://jdbc:oracle:thin:@xxx.xxx.xxx.com:1523/SCHEMA> --username user --password pw --hive-drop-import-delims -m 16 --verbose --escaped-by \\ --fields-terminated-by ,
exception:
java.sql.SQLException: ORA-01017: invalid username/password; logon denied
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:600)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:445)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:380)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:760)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:322)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:685)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:708)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:243)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:226)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:347)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1298)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
14/06/23 10:12:34 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1116)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
-----Original Message-----
From: Jarek Jarcec Cecho [mailto:jarcec@apache.org<ma...@apache.org>]
Sent: Saturday, June 21, 2014 9:55 AM
To: user@sqoop.apache.org<ma...@sqoop.apache.org>
Subject: Re: Oracle Special Character Password
Hi Martin,
could you please share your entire command line and exact exception (with stack trace) that you're hitting?
Jarcec
On Sat, Jun 21, 2014 at 11:18:33AM +0000, Martin, Nick wrote:
> I do a lot of Oracle imports/exports everyday using Sqoop with good success. However, I keep hitting a gotcha - any time we try to import from an Oracle database that had a password with special characters we get invalid username/pw error.
>
> If we get the password changed to something without a special character it works like a charm. Problem is we're now faced with some databases that have a special character password required so we're kinda hosed.
>
> Using --password via CLI currently.
>
> Any ideas?
>
>
>
> Sent from my iPhone
Re: Oracle Special Character Password
Posted by Gwen Shapira <gs...@cloudera.com>.
I think your password is getting mangled by the command line.
Try wrapping it in quotes.
The best way to test what quoting or escaping is needed is to test in
SQLPlus.
Something like:
sqlplus nick/"p@ssword\!"@ORCL
Once you figure out how to use the password on the command line with
SQLPlus, you can use the same thing with Sqoop.
See this blog for an example:
http://nadvi.blogspot.com/2011/02/oracle-password-with-or-special.html
Gwen
On Mon, Jun 23, 2014 at 7:19 AM, Martin, Nick <Ni...@pssd.com> wrote:
> Thanks for having a look...tested the user/pw in SQL Developer and worked
> fine.
>
> CLI:
> sqoop import --target-dir /user/NiMartin/xxx/yyyyy --table SCHEMA.TABLE
> --connect jdbc:oracle:thin:@xxx.xxx.xxx.com:1523/SCHEMA --username user
> --password pw --hive-drop-import-delims -m 16 --verbose --escaped-by \\
> --fields-terminated-by ,
>
>
> exception:
> java.sql.SQLException: ORA-01017: invalid username/password; logon denied
>
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
> at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:600)
> at
> oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:445)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
> at
> oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:380)
> at
> oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:760)
> at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
> at
> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
> at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
> at
> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
> at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
> at java.sql.DriverManager.getConnection(DriverManager.java:571)
> at java.sql.DriverManager.getConnection(DriverManager.java:215)
> at
> org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:322)
> at
> org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:685)
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:708)
> at
> org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:243)
> at
> org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:226)
> at
> org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:347)
> at
> org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1298)
> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
> at
> org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
> at
> org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
> 14/06/23 10:12:34 ERROR tool.ImportTool: Encountered IOException running
> import job: java.io.IOException: No columns to generate for ClassWriter
> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1116)
> at
> org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
> at
> org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>
> -----Original Message-----
> From: Jarek Jarcec Cecho [mailto:jarcec@apache.org]
> Sent: Saturday, June 21, 2014 9:55 AM
> To: user@sqoop.apache.org
> Subject: Re: Oracle Special Character Password
>
> Hi Martin,
> could you please share your entire command line and exact exception (with
> stack trace) that you're hitting?
>
> Jarcec
>
> On Sat, Jun 21, 2014 at 11:18:33AM +0000, Martin, Nick wrote:
> > I do a lot of Oracle imports/exports everyday using Sqoop with good
> success. However, I keep hitting a gotcha - any time we try to import from
> an Oracle database that had a password with special characters we get
> invalid username/pw error.
> >
> > If we get the password changed to something without a special character
> it works like a charm. Problem is we're now faced with some databases that
> have a special character password required so we're kinda hosed.
> >
> > Using --password via CLI currently.
> >
> > Any ideas?
> >
> >
> >
> > Sent from my iPhone
>
RE: Oracle Special Character Password
Posted by "Martin, Nick" <Ni...@pssd.com>.
Thanks for having a look...tested the user/pw in SQL Developer and worked fine.
CLI:
sqoop import --target-dir /user/NiMartin/xxx/yyyyy --table SCHEMA.TABLE --connect jdbc:oracle:thin:@xxx.xxx.xxx.com:1523/SCHEMA --username user --password pw --hive-drop-import-delims -m 16 --verbose --escaped-by \\ --fields-terminated-by ,
exception:
java.sql.SQLException: ORA-01017: invalid username/password; logon denied
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:600)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:445)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:380)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:760)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:322)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:685)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:708)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:243)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:226)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:347)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1298)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
14/06/23 10:12:34 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1116)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
-----Original Message-----
From: Jarek Jarcec Cecho [mailto:jarcec@apache.org]
Sent: Saturday, June 21, 2014 9:55 AM
To: user@sqoop.apache.org
Subject: Re: Oracle Special Character Password
Hi Martin,
could you please share your entire command line and exact exception (with stack trace) that you're hitting?
Jarcec
On Sat, Jun 21, 2014 at 11:18:33AM +0000, Martin, Nick wrote:
> I do a lot of Oracle imports/exports everyday using Sqoop with good success. However, I keep hitting a gotcha - any time we try to import from an Oracle database that had a password with special characters we get invalid username/pw error.
>
> If we get the password changed to something without a special character it works like a charm. Problem is we're now faced with some databases that have a special character password required so we're kinda hosed.
>
> Using --password via CLI currently.
>
> Any ideas?
>
>
>
> Sent from my iPhone
Re: Oracle Special Character Password
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
Hi Martin,
could you please share your entire command line and exact exception (with stack trace) that you're hitting?
Jarcec
On Sat, Jun 21, 2014 at 11:18:33AM +0000, Martin, Nick wrote:
> I do a lot of Oracle imports/exports everyday using Sqoop with good success. However, I keep hitting a gotcha - any time we try to import from an Oracle database that had a password with special characters we get invalid username/pw error.
>
> If we get the password changed to something without a special character it works like a charm. Problem is we're now faced with some databases that have a special character password required so we're kinda hosed.
>
> Using --password via CLI currently.
>
> Any ideas?
>
>
>
> Sent from my iPhone