今天把sqoop job的数据库密码修改为从文件中获取,环境:
CDH6.2.1,Sqoop-1.4.7
1,修改配置文件sqoop-site.xml,在/opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/etc/sqoop文件夹下,增加配置
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
</property>
2,sqoop job 脚本:
$CDH_HOME ====== /opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/bin
$CDH_HOME/sqoop job \
--create 'order_H' \
-- import \
--connect 'jdbc:sqlserver://localhost:8888;database=database_new' \
--username 'HIVE_03' \
--password-file /user/root/pass.password \
--table order \
--hive-import \
--hive-database hive_database \
--hive-table order_h \
--fields-terminated-by '\001' \
--hive-drop-import-delims \
--incremental append \
--check-column id \
--last-value 1 \
--m 2
3,pass.password 密码文件,在根目录建文件
echo '123456' > pass.password ##此处一定注意步骤5!
hdfs dfs -put pass.password /user/root/
4,sqoop job --exec *
然后报下面的错误,疯狂改了半天,最后在官网文档中找到原因,也是很绝望了。。。
ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: 用户 'hive_007' 登录失败。 ClientConnectionId:ca0238c0-c6a1-437a-b1f0-59f969bb996e
com.microsoft.sqlserver.jdbc.SQLServerException: 用户 'hive_007' 登录失败。 ClientConnectionId:ca0238c0-c6a1-437a-b1f0-59f969bb996e
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:256)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:108)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4290)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3157)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:82)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3121)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:903)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:59)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:762)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:785)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:288)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:259)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:245)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:333)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1879)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1672)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:515)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:633)
at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:248)
at org.apache.sqoop.tool.JobTool.run(JobTool.java:303)
at org.apache.sqoop.Sqoop.run(Sqoop.java:146)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:182)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:233)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:242)
at org.apache.sqoop.Sqoop.main(Sqoop.java:251)
21/03/24 15:07:03 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1678)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:515)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:633)
at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:248)
at org.apache.sqoop.tool.JobTool.run(JobTool.java:303)
at org.apache.sqoop.Sqoop.run(Sqoop.java:146)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:182)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:233)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:242)
at org.apache.sqoop.Sqoop.main(Sqoop.java:251)
5,重点:
第三步修改为:echo -n '123456' > pass.password