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