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 <[email protected]> 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, >> phone,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 <[email protected]>: > >> 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 <[email protected]> >> 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.ClassWriter.generate(ClassWriter.java:1 >>>> 671) >>>> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.ja >>>> va:107) >>>> at org.apache.sqoop.tool.ExportTool.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 <[email protected]> >>> : >>> >>>> 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, 王峰 <[email protected]> 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.... >>>>> >>>>> >>>> >>> >> >
