How to use sqoop to export the default hive delimited output?

Julias picture Julias · Sep 24, 2013 · Viewed 23.1k times · Source

I have a hive query:

insert override directory /x
select ...

Then I'm try to export the data with sqoop

sqoop export --connect jdbc:mysql://mysqlm/site --username site --password site --table x_data --export-dir /x  --input-fields-terminated-by 0x01 --lines-terminated-by '\n'

But this seems to fail to parse the fields according to delimiter What am I missing? I think the --input-fields-terminated-by 0x01 part doesn't work as expected?

I do not want to create additional tables in hive that contains the query results.

stack trace:

 2013-09-24 05:39:21,705 ERROR org.apache.sqoop.mapreduce.TextExportMapper: Exception: 
 java.lang.NumberFormatException: For input string: "9-2"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
    at java.lang.Integer.parseInt(Integer.java:458)
 ...

The vi view of output

16-09-2013 23^A1182^A-1^APub_X^A21782^AIT^A1^A0^A0^A0^A0^A0.0^A0.0^A0.0
16-09-2013 23^A1182^A6975^ASoMo Audience  Corp^A2336143^AUS^A1^A1^A0^A0^A0^A0.2^A0.0^A0.0
16-09-2013 23^A1183^A-1^APub_UK, Inc.^A1564001^AGB^A1^A0^A0^A0^A0^A0.0^A0.0^A0.0
17-09-2013 00^A1120^A-1^APub_US^A911^A--^A181^A0^A0^A0^A0^A0.0^A0.0^A0.0

Answer

Julias picture Julias · Nov 3, 2013

I've found the correct solution for that special character in bash

#!/bin/bash

# ... your script
hive_char=$( printf "\x01" )

sqoop export --connect jdbc:mysql://mysqlm/site --username site --password site --table x_data --export-dir /x  --input-fields-terminated-by ${hive_char} --lines-terminated-by '\n'

The problem was in correct separator recognition (nothing to do with types and schema) and that was achieved by hive_char.

Another possibility to encode this special character in linux to command-line is to type Cntr+V+A