A Data Format Error Is Reported When Data Is Exported from Hive to MySQL 8.0 Using Sqoop

Issue

A data format error is reported when Sqoop in an MRS 3.1.0 cluster is used to export data from Hive to MySQL 8.0.

Symptom

Cause Analysis

The log shows that the format is incorrect.

Procedure

Check that the formats of delimiters and table fields are all correct. Add the --columns parameter to the Sqoop statement to make data formats consistent between source and target tables.

sqoop export --connect jdbc:mysql://Database IP address:Port number/Database name --username Database username --password Password --table Table name --columns Column (separate multiple columns with commas) -export-dir Export address --fields-terminated-by Separator --input-null-string '\\N' --input-null-non-string '\\N' -m 1

There can be security risks if a command contains the authentication password. You are advised to disable the command recording function (history) before running the command.

Example:

sqoop export --connect jdbc:mysql://192.168.0.6:3306/lidengpeng --username root --password User password --table hkatg_agr_prod_city_summ --columns year,city_name,city_code,prod_code,prod_name,prod_type,sown_area,area_unit,yield_wegt,yield_unit,total_wegt,total_wegt_unit,data_sorc_code,etl_time -export-dir hdfs://hacluster/user/hive/warehouse/dm_agr_prod_city_summ02 --fields-terminated-by ',' --input-null-string '\\N' --input-null-non-string '\\N' -m 1