A Format Error Is Reported When Sqoop Is Used to Export Data from Hive to MySQL 8.0

Issue

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

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://IP address:Port number/Database name --username Username --password Password --table Table name --columns Column fields (multiple columns are separated by commas) -export-dir Export address --fields-terminated-by Delimiter --input-null-string '\\N' --input-null-non-string '\\N' -m 1

Example:

sqoop export --connect jdbc:mysql://172.16.0.6:3306/lidengpeng --username root --password Mrs@2021 --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