INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM order AS t WHERE 1=0 ERROR manager.SqlManager: Error executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order AS t WHERE 1=0' at line 1 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order AS t WHERE 1=0' at line 1
Sqoop 在执行任务前,会执行类似的语句来判断源表是否有问题: SELECT t.* FROM TABLE_NAME AS t WHERE 1=0 然而这里报的错表示这句语句有错误,无法执行。仔细一看原来业务库那边的表名为 order,显然这个订单表名使用了 MySQL 中的保留关键字了。在 MySQL 中,使用 `` 将含关键字的名称引起来即可正常执行,如:
1
SELECT t.*FROM `order` AS t WHERE1=0。
如果在 Sqoop 命令中直接使用 order 却无法正常执行,order 会被 shell 当成独立的命令来执行,也会直接报错。(经过测试使用单引号''、双引号""及转义字符都无法执行 Sqoop 任务。)
Error: java.io.IOException: SQLException in nextKeyValue at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:275) at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:568) at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80) at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91) at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145) at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:799) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1875) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168) Caused by: java.sql.SQLException: java.sql.SQLException: Illegal hour value '24'for java.sql.Time typein value '24:00:00. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.ResultSet.getTimeFromString(ResultSet.java:6096) at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5819) at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5645) at org.apache.sqoop.lib.JdbcWritableBridge.readString(JdbcWritableBridge.java:68) at partner_stores.readFields(partner_stores.java:505) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:242) ... 12 more
这是因为 sqoop 导入 MySQL 用的是 JDBC,然而 JDBC 的驱动中对 Time 类型最大支持到”23:59:59”,所以当 Time 类型数据出现超过”23:59:59”数据就会报这个错。