Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Sqoop >> mail # user >> Escaping column names when using sqoop export to mysql


Copy link to this message
-
Escaping column names when using sqoop export to mysql
Hi,

I've run into an issue, but I wanted to verify with the sqoop users
mailing list.  We have an existing schema defined as:

CREATE TABLE `TableXXXXX` (
`RecordID` varchar(45) NOT NULL default '',
`City` varchar(40) NOT NULL default '',
`State` varchar(2) NOT NULL default '',
`Zip` varchar(5) NOT NULL default '',
`Zip-4` varchar(4) NOT NULL default '',
KEY `RecordID` (`RecordID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2000000000
As you can see, column Zip-4 contains a special character, -, which
would need to be escaped before performing an insert.  Unfortunately,
when I run:

sqoop export --connect jdbc:mysql://build12.mycompany.com/DBXXXXX
--username sqoop_user --password sqoop_pwd --table TableXXXXX
--export-dir /user/ben/seq_out --input-escaped-by '\'
--input-fields-terminated-by '\t' --mysql-delimiters --verbose

The operation fails with exception:

java.io.IOException: Can't export data, please check task tracker logs
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:140)
at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:672)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:330)
at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
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:1408)
at org.apache.hadoop.mapred.Child.main(Child.java:262)
Caused by: java.io.IOException:
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 s

I have checked the task tracker logs and mysql logs, and it appears
that the issue is that the INSERT statements are being created as
such:

INSERT INTO Bankruptcies (RecordID, City, State, Zip, Zip-4) VALUES
(Values are here), (More values are here);

It seems that the issue is that the column names are not being escaped
when they ought to be.  I've checked a number of possible parameters
and have not been able to force the escaping of column names.  Does
anyone know how to do this, or is it an open issue?

Thank you very much in advance,
Ben
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB