Sqoop Error: Can‘t parse input data: ‘\N‘ [How to Solve]

Sqoop reports an error can’t parse input data: ‘\ n’

Problem Description: use sqoop to push the data in hive to Oracle. The program reports an error: can’t parse input data: ‘\ n’

. Null value cannot be recognized when derivative.

The reason for this exception is that when the table in hive is a partitioned table, the added fields do not use cascade, such as:

alter table app.suntest_user add  columns(name string comment 'name') cascade

When does not use the cascade keyword, hive does not update the history partition definition, resulting in differences between the columns in the table and the target table when transmitting history data.

For example:

False table suntest_ User created partition M08 in August, added a name column in September, and created partition M09, and added a name column to the table definition in the target database (Oracle). There is no problem exporting M09 partition data through the sqoop script, but an error will be reported when exporting M08 data: can’t parse input data: ‘\ n’

Solution:

You can rebuild the partition in August , or create a table for derivatives. The following is a table creation statement.

----- Create a table corresponding to the partition and use the temporary derivative 
	create table app.test_suntest_user as 
	select id,name from app.suntest_user where partition_month='M08' 

----Rebuild partition statement 
	--Rename partition 
	alter table app.suntest_user partition (partition_month='M08') rename to partition (partition_month='M08bak');
	--New Partition 
	insert overwrite table app.suntest_user partition(partition_month='M08')
	select id ,name from app.suntest_user where partition_month='M08bak'; 


The sqoop statement is as follows:

sqoop export --connect "jdbc:oracle:thin:@**********" \
--username "abc" \
--password "123" \
--input-null-string '\\N'  --input-null-non-string '\\N' \
--table "SUNTEST_USER" \
--export-dir /user/hive/warehouse/app/test_suntest_user\
--input-fields-terminated-by '\001' \

Read More: