sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From feng wang <wangfengfight...@gmail.com>
Subject Re: How to specify the column names when export HDFS data into MySQL tables?
Date Wed, 27 Dec 2017 01:51:30 GMT
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,phone,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 <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