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 Thu, 28 Dec 2017 07:56:32 GMT
yes I have tried without those tick in the last email..and failed again
with error logs:
>
>
>  bin/sqoop export --connect jdbc:mysql://10.8.245.16:3303/IA --username
> "root" --password "password" --table "ALL_TYPES2" --columns
> "bankcard,sex,HTTPStateCode,boolean,domain,ip,array,map,idcard,phone,email,post,date,uniform_date,int,double,string"
>  --export-dir "/tmp/ALL_TYPES2.csv"  -m 1




2017-12-28 15:53:01,141 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 ('3533523165564735', 'Male', '242', 'false', 'buckri' 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)


2017-12-28 10:39 GMT+08:00 Ajana Chandiruthil Sathian <ajanacs@gmail.com>:

> Can you please try without those tick mark around into, string and double?
>
> On Dec 27, 2017 7:05 PM, "feng wang" <wangfengfighting@gmail.com> wrote:
>
>> Oh , I really forgot to input `domain` here is the log for your Sqoop
>> command:
>>
>> 2017-12-28 08:58:09,812 INFO [main] org.apache.hadoop.metrics2.impl.MetricsConfig:
>>> loaded properties from hadoop-metrics2.properties
>>> 2017-12-28 08:58:09,874 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl:
>>> Scheduled snapshot period at 10 second(s).
>>> 2017-12-28 08:58:09,874 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl:
>>> MapTask metrics system started
>>> 2017-12-28 08:58:09,882 INFO [main] org.apache.hadoop.mapred.YarnChild:
>>> Executing with tokens:
>>> 2017-12-28 08:58:09,882 INFO [main] org.apache.hadoop.mapred.YarnChild:
>>> Kind: mapreduce.job, Service: job_1506495220983_0104, Ident:
>>> (org.apache.hadoop.mapreduce.security.token.JobTokenIdentifier@1ed6388a)
>>> 2017-12-28 08:58:10,071 INFO [main] org.apache.hadoop.mapred.YarnChild:
>>> Sleeping for 0ms before retrying again. Got null now.
>>> 2017-12-28 08:58:10,311 INFO [main] org.apache.hadoop.mapred.YarnChild:
>>> mapreduce.cluster.local.dir for child: /home/hadoopData/tmp/nm-local-
>>> dir/usercache/root/appcache/application_1506495220983_0104
>>> 2017-12-28 08:58:10,491 INFO [main] org.apache.hadoop.conf.Configuration.deprecation:
>>> session.id is deprecated. Instead, use dfs.metrics.session-id
>>> 2017-12-28 08:58:10,894 INFO [main] org.apache.hadoop.mapred.Task: Using
>>> ResourceCalculatorProcessTree : [ ]
>>> 2017-12-28 08:58:11,087 INFO [main] org.apache.hadoop.mapred.MapTask:
>>> Processing split: Paths:/tmp/ALL_TYPES2.csv:0+11984
>>> 2017-12-28 08:58:11,092 INFO [main] org.apache.hadoop.conf.Configuration.deprecation:
>>> map.input.file is deprecated. Instead, use mapreduce.map.input.file
>>> 2017-12-28 08:58:11,092 INFO [main] org.apache.hadoop.conf.Configuration.deprecation:
>>> map.input.start is deprecated. Instead, use mapreduce.map.input.start
>>> 2017-12-28 08:58:11,092 INFO [main] org.apache.hadoop.conf.Configuration.deprecation:
>>> map.input.length is deprecated. Instead, use mapreduce.map.input.length
>>> 2017-12-28 08:58:11,475 INFO [Thread-12] org.apache.sqoop.mapreduce.AutoProgressMapper:
>>> Auto-progress thread is finished. keepGoing=false
>>> 2017-12-28 08:58:11,528 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 ('3533523165564735', 'Male', '242', 'false', 'buckri' at
>>> line 1
>>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>>> at sun.reflect.NativeConstructorAccessorImpl.newInstance(Native
>>> ConstructorAccessorImpl.java:62)
>>> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(De
>>> legatingConstructorAccessorImpl.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(PreparedSta
>>> tement.java:2082)
>>> at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302)
>>> at org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExec
>>> Thread.run(AsyncSqlOutputFormat.java:233)
>>
>>
>>
>>
>> As I try to use  * --columns
>> "bankcard,sex,HTTPStateCode,boolean,domain,ip,array,map,idcard,phone,email,post,date,uniform_date,`int`,`double`,`string`"
>> *  the column in table are still empty.
>>
>> [image: 内嵌图片 1]
>>
>> 2017-12-28 1:42 GMT+08:00 Ajana Chandiruthil Sathian <ajanacs@gmail.com>:
>>
>>> Hi,
>>> you were not passing domain in the column list.
>>> Could you please try the below Sqoop command? Please let me know if you
>>> have any issues.
>>>
>>> bin/sqoop export --connect jdbc:mysql://192.168.100.23:3303/IA \
>>> --username "root" \
>>> --password "password" \
>>> --table "ALL_TYPES" \
>>> --columns "bankcard,sex,HTTPStateCode,boolean,domain,ip,array,map,idca
>>> rd,phone,email,post,date,uniform_date,int,double,string" \
>>> --export-dir "/tmp/ALL_TYPES2.csv" \
>>> -m 1
>>>
>>> On Wed, Dec 27, 2017 at 11:36 AM, Ajana Chandiruthil Sathian <
>>> ajanacs@gmail.com> wrote:
>>>
>>>> Actually it is a misalignment, the values for the domain is in ip and
>>>> values for ip is in array. Did you see that?
>>>>
>>>> On Wed, Dec 27, 2017 at 11:32 AM, Ajana Chandiruthil Sathian <
>>>> ajanacs@gmail.com> wrote:
>>>>
>>>>> Yes, this is what I was asking.   I just noticed that the column
>>>>> domain also null
>>>>>
>>>>> On Wed, Dec 27, 2017 at 2:26 AM, feng wang <wangfengfighting@gmail.com
>>>>> > wrote:
>>>>>
>>>>>> Did you mean this command?
>>>>>>
>>>>>> [image: 内嵌图片 1]
>>>>>>
>>>>>> 2017-12-27 14:43 GMT+08:00 Ajana Chandiruthil Sathian <
>>>>>> ajanacs@gmail.com>:
>>>>>>
>>>>>>> Okay! Can I see the screenshot of the describe tablename command
?
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>

Mime
View raw message