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 <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.ClassWriter.generate(ClassWriter.java:
>>> 1671)
>>> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.
>>> java: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 <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....
>>>>
>>>>
>>>
>>
>
