Search code examples
shellhadoopsqoopsqoop2

SQOOP export in shell script fails

I am exporting a table from hive to mysql with the help of shell script.The below is the sqoop export command 

sqoop export --connect jdbc:mysql://192.168.154.129:3306/ey -username root --table call_detail_records --export-dir /apps/hive/warehouse/xademo.db/call_detail_records --fields-terminated-by '|' --lines-terminated-by '\n' --m 4 --batch

The above command works fine from the CLI. but it doesnt work from the shell script and it generates the below Warning and error.

Warning :

15/05/05 13:30:06 WARN sqoop.SqoopOptions: Character argument '|' has multiple characters; only the first will be used.
15/05/05 13:30:06 WARN sqoop.SqoopOptions: Character argument '\n' has multiple characters; only the first will be used.

Error:

15/05/05 13:30:50 INFO mapreduce.Job:  map 0% reduce 0%
15/05/05 13:31:56 INFO mapreduce.Job: Task Id : attempt_1430805361424_0046_m_000001_0, Status : FAILED
Error: java.io.IOException: Can't export data, please check failed map task logs
    at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
    at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
    at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
    at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
    at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)
    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
    at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
    at java.security.AccessController.doPrivileged(Native Method)
    at javax.security.auth.Subject.doAs(Subject.java:415)
    at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1628)
    at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.lang.RuntimeException: Can't parse input data: 'PHONE_NUM|PLAN|DATE|STAUS|BALANCE|IMEI|REGION'
    at customer_details.__loadFromFields(customer_details.java:464)
    at customer_details.parse(customer_details.java:382)
    at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
    ... 10 more
Caused by: java.util.NoSuchElementException
    at java.util.ArrayList$Itr.next(ArrayList.java:834)
    at customer_details.__loadFromFields(customer_details.java:434)
    ... 12 more

My Sqoop command in shell script will have variables which will be expanded.

nohup sqoop export --connect jdbc:mysql://192.168.154.129:3306/ey -username root --table $TBL_NAME --export-dir $HIVE_DIR --fields-terminated-by "$FIELD_SEP" --lines-terminated-by "'"'\'"$LINE_SEP""'" --m $NUM_MAPPERS --batch > $sqoop_outs/$TBL_NAME.out 2>&1 &

Any help is highly appreciated. I am struggling with this for long time...

Solution

  • Atlast i found the reason, it is the disparate treatment of " and ' in the SQOOP command when i run from the CLI and Shell script.

    Solution : I had to change in my shell script as follows

    nohup sqoop export --connect jdbc:mysql://192.168.154.129:3306/ey -username root --table $TBL_NAME --export-dir $HIVE_DIR --fields-terminated-by "$FIELD_SEP" --lines-terminated-by '\'"$LINE_SEP" --m $NUM_MAPPERS --batch > $sqoop_outs/$TBL_NAME.out 2>&1 &

    which will issue the SQOOP command as follows, but it worked fine

    sqoop export --connect jdbc:mysql://192.168.154.129:3306/ey -username root --table call_detail_records --export-dir /apps/hive/warehouse/xademo.db/call_detail_records --fields-terminated-by | --lines-terminated-by \n --m 4 --batch