Do you mind sending the screenshot of the ALL_TYPES2.csv? I would like to see the data in the in CSV corresponding to int, double and string.
On Tue, Dec 26, 2017 at 9:52 PM, feng wang <wangfengfight...@gmail.com> wrote: > Okay..I means the job finished without errors,and I check data in MySQL > database,all of values for columns "int","double" and "string" are null > [image: 内嵌图片 1] > > 2017-12-27 11:42 GMT+08:00 Ajana Chandiruthil Sathian <ajan...@gmail.com>: > >> I am glad that it worked. Let me look into sourcecode. If you found out >> before me, please let me know. >> >> On Tue, Dec 26, 2017 at 9:33 PM, feng wang <wangfengfight...@gmail.com> >> wrote: >> >>> I`m just testing the sqoop tools,actually I found when the column name >>> start with keys in MySQL like PRIMARY_people_name and the export job >>> will going to failed too with same error logs... >>> By the way int and double are keywords for MySQL please see >>> https://dev.mysql.com/doc/refman/5.7/en/keywords.html and the* "`"* is >>> not single quote but the *key on 'Tab' in keyboard* >>> >>> Thanks for your remind and I run >>> >>>> bin/sqoop export --connect jdbc:mysql://192.168.100.23:3303/IA --username >>>> "root" --password "password" --table "ALL_TYPES" --columns >>>> "bankcard,sex,HTTPStateCode,boolean,ip,array,map,idcard,phon >>>> e,email,post,date,uniform_date,`int`,`double`,`string`" --export-dir >>>> "/tmp/ALL_TYPES2.csv" -m 1 >>> >>> >>> It really works... >>> >>> But this is just a temporary solution, so I want edit the source code >>> automatic add "`" for column names,this is just for MySQL，as for >>> oracle，postgreSQL shoud add others special char. So could you mind tell me >>> where the functions is in the source code? As I mentioned before it >>> seems workless.. >>> >>>> *org.apache.sqoop.manager.SqlManager.**getColumnNamesForRawQuery* as >>>> shows: >>>> code at 152: columns.add(colName); --> columns.add("`"+colName+"`"); >>> >>> >>> >>> >>> >>> 2017-12-27 10:08 GMT+08:00 Ajana Chandiruthil Sathian <ajan...@gmail.com >>> >: >>> >>>> Hi, >>>> >>>> I am assuming that the reason you put single quotes around the last >>>> three columns : int, double, string is not to consider its original >>>> meaning( since they are data types). Please correct me if I misunderstood. >>>> If that is the case, you should provide that for string too. In the >>>> sqoop command you did not provide the single quotes around string column. >>>> Can I ask you one question? Why do you want to name the columns as >>>> datatypes? Does it even possible? Interesting. The whole time I thought >>>> those are reserved words and could not use as an identifier. Does not feel >>>> like a good practice though! >>>> >>>> On Tue, Dec 26, 2017 at 7:51 PM, feng wang <wangfengfight...@gmail.com> >>>> wrote: >>>> >>>>> Thanks for your reply. I have already used --columns argument like >>>>> this: >>>>>> >>>>>> bin/sqoop export --connect jdbc:mysql://192.168.100.23:3303/IA >>>>>> --username "root" --password "password" --table "ALL_TYPES" --columns >>>>>> "bankcard,sex,HTTPStateCode,boolean,ip,array,map,idcard,phon >>>>>> e,email,post,date,uniform_date,`int`,`double`,string" --export-dir >>>>>> "/tmp/ALL_TYPES.csv" -m 1 >>>>> >>>>> >>>>> I use escape char* "`" * with column name*s "int" and "double"* but >>>>> it return following information: >>>>> >>>>>> 17/12/26 18:37:38 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6 >>>>>> 17/12/26 18:37:38 WARN tool.BaseSqoopTool: Setting your password on >>>>>> the command-line is insecure. Consider using -P instead. >>>>>> 17/12/26 18:37:39 INFO manager.MySQLManager: Preparing to use a MySQL >>>>>> streaming resultset. >>>>>> 17/12/26 18:37:39 INFO tool.CodeGenTool: Beginning code generation >>>>>> 17/12/26 18:37:39 INFO manager.SqlManager: Executing SQL statement: >>>>>> SELECT t.* FROM `ALL_TYPES` AS t LIMIT 1 >>>>>> 17/12/26 18:37:39 ERROR sqoop.Sqoop: Got exception running Sqoop: >>>>>> java.lang.IllegalArgumentException: We found column without column >>>>>> name. Please verify that you've entered all column names in your query if >>>>>> using free form query import (consider adding clause AS if you're using >>>>>> column transformation) >>>>>> java.lang.IllegalArgumentException: We found column without column >>>>>> name. Please verify that you've entered all column names in your query if >>>>>> using free form query import (consider adding clause AS if you're using >>>>>> column transformation) >>>>>> at org.apache.sqoop.orm.ClassWrit >>>>>> er.generate(ClassWriter.java:1671) >>>>>> at org.apache.sqoop.tool.CodeGenT >>>>>> ool.generateORM(CodeGenTool.java:107) >>>>>> at org.apache.sqoop.tool.ExportTo >>>>>> ol.exportTable(ExportTool.java:64) >>>>>> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100) >>>>>> at org.apache.sqoop.Sqoop.run(Sqoop.java:143) >>>>>> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) >>>>>> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179) >>>>>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) >>>>>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227) >>>>>> at org.apache.sqoop.Sqoop.main(Sqoop.java:236) >>>>> >>>>> >>>>> >>>>> I checked the columns in MySQL table "ALL_TYPES" to ensure all column >>>>> names in my query. >>>>> Is there anything wrong with me? >>>>> >>>>> >>>>> >>>>> 2017-12-27 1:49 GMT+08:00 Ajana Chandiruthil Sathian < >>>>> ajan...@gmail.com>: >>>>> >>>>>> Hi, >>>>>> >>>>>> I am not sure whether I understand your question. Why do you want to >>>>>> add the escape character to the column names? Did you try using the >>>>>> --columns argument in your Sqoop command. Can you please send the Sqoop >>>>>> command as well as the HDFS file format? Is HDFS file is in CSV form? >>>>>> >>>>>> On Mon, Dec 25, 2017 at 3:35 AM, 王峰 <wangfengfight...@gmail.com> >>>>>> wrote: >>>>>> >>>>>>> Hello： >>>>>>> >>>>>>> I have a question on the export of sqoop(V1.4.6) need your help, >>>>>>> the problem is as follows： >>>>>>> >>>>>>> When I export HDFS file into MySQL table `test` the `TABLE_SCHEMA` of >>>>>>> `test` are as follows: >>>>>>> >>>>>>> "bankcard" >>>>>>> "sex" >>>>>>> "HTTPStateCode" >>>>>>> "boolean" >>>>>>> "domain" >>>>>>> "ip" >>>>>>> "array" >>>>>>> "map" >>>>>>> "idcard" >>>>>>> "phone" >>>>>>> "email" >>>>>>> "post" >>>>>>> "date" >>>>>>> "uniform_date" >>>>>>> "int" >>>>>>> "double" >>>>>>> "string" >>>>>>> >>>>>>> and got error like this: >>>>>>> >>>>>>> ERROR [Thread-11] org.apache.sqoop.mapreduce.AsyncSqlOutputFormat: Got >>>>>>> exception in update thread: >>>>>>> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an >>>>>>> error in your SQL syntax; check the manual that corresponds to your >>>>>>> MariaDB server version for the right syntax to use near 'int, double, >>>>>>> string) VALUES ('bankcard', 'sex', 'HTTPStateCode', 'boolean', 'dom' at >>>>>>> line 1 >>>>>>> at >>>>>>> sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) >>>>>>> at >>>>>>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) >>>>>>> at >>>>>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) >>>>>>> at >>>>>>> java.lang.reflect.Constructor.newInstance(Constructor.java:423) >>>>>>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) >>>>>>> at com.mysql.jdbc.Util.getInstance(Util.java:383) >>>>>>> at >>>>>>> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062) >>>>>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226) >>>>>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158) >>>>>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) >>>>>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) >>>>>>> at >>>>>>> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840) >>>>>>> at >>>>>>> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) >>>>>>> at >>>>>>> com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302) >>>>>>> at >>>>>>> org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:233) >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> 1. I know the reason why the job going to false,And I want to ask if >>>>>>> there is any way to specify the table columns like translate* int to >>>>>>> `int` & double to `double`* in my case? >>>>>>> >>>>>>> 2. I want to edit the source code adding the escape char to column >>>>>>> names,however, I could not find where the getColumnNamesForRawQuery is. >>>>>>> I try to edit >>>>>>> *org.apache.sqoop.manager.SqlManager.**getColumnNamesForRawQuery* as >>>>>>> shows: >>>>>>> >>>>>>> >>>>>>> code at 152: columns.add(colName); --> >>>>>>> columns.add("`"+colName+"`"); >>>>>>> >>>>>>> >>>>>>> But it did not work for me... >>>>>>> >>>>>>> >>>>>>> Hope your helps Thanks.... >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
