[
https://issues.apache.org/jira/browse/SQOOP-1054?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13718074#comment-13718074
]
wenzhi wang commented on SQOOP-1054:
------------------------------------
Hi, Venkat
Yes, you're right, this issue is caused by wrong Sqoop version and fixed now.
But many of our tables contains 50+ columns or even more and it's not efficient
to list every attribute in query sql command. So, I write sql command like this
"select convert(mSubIdentificationNumberId using utf8) as split_key, IMSI.*
from IMSI where $CONDITIONS", but it doesn't work for Soop, see below:
bl1253{wenzhiw}/local/wenzhiw/sqoop2/sqoop-1.4.3.bin__hadoop-1.0.0/bin>cat
import_imsi.txt
import
--connect
jdbc:mysql://135.252.158.250/hlr
--username
root
--password
admin
--query
'select convert(mSubIdentificationNumberId using utf8) as split_key, IMSI.*
from IMSI where $CONDITIONS'
--split-by
split_key
--target-dir
/user/wenzhiw/imsi
bl1253{wenzhiw}/local/wenzhiw/sqoop2/sqoop-1.4.3.bin__hadoop-1.0.0/bin>./sqoop
--options-file ./import_imsi.txt
Warning: does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: $HADOOP_HOME is deprecated.
13/07/24 15:17:12 WARN tool.BaseSqoopTool: Setting your password on the
command-line is insecure. Consider using -P instead.
13/07/24 15:17:12 INFO manager.MySQLManager: Preparing to use a MySQL streaming
resultset.
13/07/24 15:17:12 INFO tool.CodeGenTool: Beginning code generation
13/07/24 15:17:14 INFO manager.SqlManager: Executing SQL statement: select
convert(mSubIdentificationNumberId using utf8) as split_key, IMSI.* from IMSI
where (1 = 0)
13/07/24 15:17:14 INFO manager.SqlManager: Executing SQL statement: select
convert(mSubIdentificationNumberId using utf8) as split_key, IMSI.* from IMSI
where (1 = 0)
13/07/24 15:17:14 INFO manager.SqlManager: Executing SQL statement: select
convert(mSubIdentificationNumberId using utf8) as split_key, IMSI.* from IMSI
where (1 = 0)
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 ERROR orm.ClassWriter: No parser available for Java type
org.apache.hadoop.io.BytesWritable
13/07/24 15:17:14 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is
/local/wenzhiw/hadoop-1.0.4
Note:
/tmp/sqoop-wenzhiw/compile/29ac46d178d7d8008684e6e404b9716d/QueryResult.java
uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/07/24 15:17:19 INFO orm.CompilationManager: Writing jar file:
/tmp/sqoop-wenzhiw/compile/29ac46d178d7d8008684e6e404b9716d/QueryResult.jar
13/07/24 15:17:19 INFO mapreduce.ImportJobBase: Beginning query import.
13/07/24 15:17:24 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT
MIN(split_key), MAX(split_key) FROM (select convert(mSubIdentificationNumberId
using utf8) as split_key, IMSI.* from IMSI where (1 = 1) ) AS t1
13/07/24 15:17:50 WARN db.TextSplitter: Generating splits for a textual index
column.
13/07/24 15:17:50 WARN db.TextSplitter: If your database sorts in a
case-insensitive order, this may result in a partial import or duplicate
records.
13/07/24 15:17:50 WARN db.TextSplitter: You are strongly encouraged to choose
an integral split column.
13/07/24 15:17:51 INFO mapred.JobClient: Running job: job_201307241426_0012
13/07/24 15:17:52 INFO mapred.JobClient: map 0% reduce 0%
13/07/24 15:18:03 INFO mapred.JobClient: Task Id :
attempt_201307241426_0012_m_000000_0, Status : FAILED
java.io.IOException: SQLException in nextKeyValue
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:265)
at
org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:532)
at
org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:67)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:764)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown
column 'split_key' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:236)
... 11 more
attempt_201307241426_0012_m_000000_0: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.hdfs.DFSClient).
attempt_201307241426_0012_m_000000_0: log4j:WARN Please initialize the log4j
system properly.
13/07/24 15:18:03 INFO mapred.JobClient: Task Id :
attempt_201307241426_0012_m_000001_0, Status : FAILED
java.io.IOException: SQLException in nextKeyValue
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:265)
at
org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:532)
at
org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:67)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:764)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown
column 'split_key' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:236)
... 11 more
attempt_201307241426_0012_m_000001_0: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.hdfs.DFSClient).
attempt_201307241426_0012_m_000001_0: log4j:WARN Please initialize the log4j
system properly.
13/07/24 15:18:04 INFO mapred.JobClient: Task Id :
attempt_201307241426_0012_m_000003_0, Status : FAILED
java.io.IOException: SQLException in nextKeyValue
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:265)
at
org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:532)
at
org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:67)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:764)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown
column 'split_key' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:236)
... 11 more
attempt_201307241426_0012_m_000003_0: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.hdfs.DFSClient).
attempt_201307241426_0012_m_000003_0: log4j:WARN Please initialize the log4j
system properly.
13/07/24 15:18:05 INFO mapred.JobClient: Task Id :
attempt_201307241426_0012_m_000002_0, Status : FAILED
java.io.IOException: SQLException in nextKeyValue
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:265)
at
org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:532)
at
org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:67)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:764)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown
column 'split_key' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
at
org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:236)
... 11 more
attempt_201307241426_0012_m_000002_0: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.hdfs.DFSClient).
attempt_201307241426_0012_m_000002_0: log4j:WARN Please initialize the log4j
system properly.
Is there a easy way to import all attributes?
Thanks,
Wenzhi
> New feature to support importing MySQL table via binary type splitter
> ----------------------------------------------------------------------
>
> Key: SQOOP-1054
> URL: https://issues.apache.org/jira/browse/SQOOP-1054
> Project: Sqoop
> Issue Type: New Feature
> Components: connectors/mysql, tools
> Affects Versions: 1.4.3
> Environment: $uname -a
> Linux bl1253.cn.alcatel-lucent.com 2.6.18-128.7.1.el5PAE #1 SMP Mon Aug 24
> 08:41:30 EDT 2009 i686 athlon i386 GNU/Linux
> Reporter: wenzhi wang
>
> I'm using (Sqoop 1.4.3 + Hadoop 1.0.4) handle data of our product, which
> employs MySQL 5.1.6 and most tables are defined with primary key on binary
> columns. Sqoop does not support binary type column as splitter now, and I
> have to import tables by restricting mapper number to 1 which lowers the
> performance. So, I open this feature to request Sqoop support binary type
> splitter.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
