[Sqoop] importing MySQL data tables to hive

Business Background

The MySQL data table yhd_categ_prior structure is 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;

Now you need to import the data table Yhd_categ_prior into hive.

Business Implementation

The script is as follows:

# Create Hive data table Pms.yhd_categ_prior_userhive-e " set Mapred.job.queue.name=pms; set mapred.job.name=[cis]yhd_categ_prior_user;--Hive DDL DROP TABLE IF EXISTS pms.yhd_categ_prior_user;create  TABLE  Pms.yhd_categ_prior_user (category_id bigint, category_name string, Categor Y_level int , Default_import_categ_prior int , User_import_categ_prior int , Default_eliminate_categ_prior int , User_eliminate_categ_prior int , upd Ate_time string) row  FORMAT delimited fields TERMINATED by   ' \ t '  LINES TERMINATED by   \ n '  STORED as  textfile; "# Sync MySQL's market. Yhd_categ_prior to Hive in Hadoop fs-rmr/user/pms/yhd_categ_prior sqoop Import-dmapred.job.queue.name=pms--connect jdbc:mysql://127.0.0.1:3306/market \--username admin \--password 123456 \--table yhd_categ_prior \--hive-table pms.yhd_categ_prior_user \--fields-terminated-by ' \ t ' \--lines-terminated-by ' \ n ' \--hive-overwrite \--hive-drop-import-delims \--hive-import

The above scripting workflow:

• Create hive Table pms.yhd_categ_prior_user
• Using Sqoop to synchronize the MySQL table yhd_categ_prior to the Hive table pms.yhd_categ_prior_user, the fields of the hive table are separated by \ t, and the rows are separated by \ n.

Experimental Results

Col_name data_type comment# col_name data_type Comment category_id bigint none Category_name string None Category_                level int none Default_import_categ_prior int None                     user_import_categ_prior int None Default_eliminate_categ_prior int             None User_eliminate_categ_prior int None update_time                      String None # detailed Table information Database:pms Owner:pms Createtime:fri June, 18:48:01 CST, Las                        Taccesstime:unknown Protect Mode:none retention:0 LoCation:hdfs://yhd-jqhadoop2.int.yihaodian.com:8020/user/hive/pms/yhd_categ_prior_user Table Type:           Managed_table TABLE parameters:numfiles 5 numpartitions                       0 NumRows 0 rawdatasize 0        TotalSize 447779 transient_lastddltime 1433501435 # Storage Information SerDe Library:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat:org.apache.hado Op.mapred.TextInputFormat OutputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compres                       Sed:no Num Buckets:-1 Bucket Columns: []             Sort Columns: [] Storage Desc Params:field.delim                    \ t  Line.delim \ Serialization.format \ t 

[Sqoop] importing MySQL data tables to hive