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 05:56:30 GMT
Sure, in consideration of having no idea to skip first row during sqoop
export,so I remove the first row which is column names,and the int double
string is *O P Q* cols in the picture


[image: 内嵌图片 1]

Open with subline:

[image: 内嵌图片 2]




2017-12-27 12:04 GMT+08:00 Ajana Chandiruthil Sathian <ajanacs@gmail.com>:

> 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 <wangfengfighting@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 <ajanacs@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 <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,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 <
>>>> 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.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 <
>>>>>> 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