sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ajana Chandiruthil Sathian <ajan...@gmail.com>
Subject Re: How to specify the column names when export HDFS data into MySQL tables?
Date Wed, 27 Dec 2017 03:42:00 GMT
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 <wangfengfighting@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,
>> 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 <ajanacs@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 <wangfengfighting@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: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 <ajanacs@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, 王峰 <wangfengfighting@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....
>>>>>
>>>>>
>>>>
>>>
>>
>

Mime
View raw message