[Sqoop] Exporting the Hive data table to MySQL

Business Background

MySQL table yhd_categ_prior is structured as follows:

--Table "Yhd_categ_prior" DDLCREATE TABLE ' Yhd_categ_prior '(' category_id '                     int( One) not NULLCOMMENT' Category ID ',' Category_name '                   varchar( -)DEFAULT NULLCOMMENT' category name ',' Category_level '                  int( One)DEFAULT ' 0 'COMMENT' class level ',' Default_import_categ_prior '      int( One)DEFAULT ' 0 'COMMENT' default ingestion priority ',' User_import_categ_prior '         int( One)DEFAULT NULLCOMMENT' user-introduced priority ',' Default_eliminate_categ_prior '   int( One)DEFAULT NULLCOMMENT' Default elimination priority ',' User_eliminate_categ_prior '      int( One)DEFAULT NULLCOMMENT' user-elimination priority ',' Update_time '                     timestamp  not NULL DEFAULT Current_timestamp  on UPDATE Current_timestampCOMMENT' Data refresh time ',PRIMARY KEY(' category_id ')) Engine=innodb auto_increment=61125 DEFAULTCharset=utf8;

The table structure of hive table Pms.yhd_categ_prior is as follows:

-- col_name                     data_type category_id                     bigintcategory_name                   stringcategory_level                  int      default_import_categ_prior      intuser_import_categ_prior         intdefault_eliminate_categ_prior   intuser_eliminate_categ_prior      intupdate_time                     string

The fields of the hive table are separated by \001, the rows are separated by \ n, and the empty fields are filled in \ n .

Now you need to export the hive table pms.yhd_categ_prior to the MySQL table yhd_categ_prior .

Business Implementation

The script is as follows:

###################################### synchronizes the Hive table to the mysql#################################### #rm-rf./YHD_CATEG_ Prior.javasqoop-export- dmapred. Job.queue.name=pms--connect jdbc:mysql://127.0.0.1:3306/market --username Admin--password 123456 --table yhd_categ_prior --export-dir/user/hive/pms/yhd_categ_prior--columns Category_id,category_name,category_level,default_import_categ_prior,user_import_categ_prior, Default_eliminate_categ_prior,user_eliminate_categ_prior--update-mode Allowinsert--update-key category_id--fields-terminated-by ' \001 ' --lines-terminated-by ' \ n ' --input-null-string ' \\n ' --input-null-non-string ' \\n ' 

The above script parsing is as follows:

• Export Hive table Pms.yhd_categ_prior to MySQL's yhd_categ_prior table
• The fields that the Hive table exports are:

category_id,category_name,category_level,default_import_categ_prior,user_import_categ_prior,default_eliminate_categ_prior,user_eliminate_categ_prior

[Sqoop] Exporting the Hive data table to MySQL