首页 > 代码库 > sqoop从mysql导入到hive

sqoop从mysql导入到hive

业务需求:将mysql的pis.T_MATCH表导入到hive上pms库的pis_t_match表

实现代码:

hive -e "set mapred.job.queue.name=pms;

create table if not exists pms.pis_t_match
(
id bigint,
merchant_id int,
product_id string,
product_name string,
product_code string,
oppon_product_code string,
oppon_product_name string,
oppon_product_url string,
site_id int,
score double,
create_time string,
creator_id string,
update_time string,
is_manual int,
is_auto int,
is_champion int,
is_candidate int,
is_confirm int,
is_delete int,
formula_ymbols int,
formula_number int,
remarks string,
data_status int,
is_barcode int,
is_doubt int,
is_search int,
instance_id string,
is_inshop int
) 
STORED AS TEXTFILE;"

hadoop fs -rmr /user/pms/T_MATCH

sqoop import -Dmapred.job.queue.name=pms --connect jdbc:mysql://mysql-pis-slave.int.yihaodian.com:3306/pis --username pisqry --password 123456 --table T_MATCH --hive-table pms.pis_t_match --fields-terminated-by '\t' --lines-terminated-by '\n' --hive-overwrite --hive-import

执行结果:

$hadoop fs -cat /user/hive/pms/pis_t_match/* | more
24894	1	1069320	西铁城CITIZEN电子体温计CT791SP	0010693206	1096041301	新款抢先上市 西铁城体温计CT791SP 30秒速计 软头设计 舒适测量 - 营养/保健/成人 - 当当网	http://product.dangdang.co
m/product.aspx?product_id=1096041301	1003	null	2011-11-15 10:15:26.0	mujingjing	2014-12-11 19:18:01.0	true	false	false	false	false	false	null	null	对手下架,取消确认
匹配关系	false	null	null	null	null	false
24905	1	1110373	天合健芦荟软胶囊500mg×40粒/盒	0011103734	20511906	[当当自营] 凯镛芦荟胶囊0.4g*40粒(健)	http://product.dangdang.com/product.aspx?product_id=20511906	1003	nu
ll	2011-11-15 10:19:22.0	caifei	2014-12-11 19:18:01.0	true	false	false	false	false	false	null	null	对手下架,取消确认匹配关系	false	null	null	null	null	fa
lse
24906	1	1111640	清华紫光金奥力DHA软胶囊500mg*100粒	0011116408	60139687	[当当自营] 清华紫光 金奥力DHA软胶囊 500mg*100粒	http://product.dangdang.com/Product.aspx?product_id=601396
87	1003	null	2011-11-15 10:19:27.0	caifei	2012-05-24 23:25:37.0	true	false	false	false	true	false	null	null	null	false	null	true	null	null	false
24908	1	1111642	清华紫光金奥力牌褪黑素胶囊(改善睡眠)  200mg*60粒	0011116420	60118666	[当当自营] 清华紫光金奥力牌褪黑素胶囊200mg*60s	http://product.dangdang.com/Product.aspx?p
roduct_id=60118666	1003	null	2011-11-15 10:19:29.0	caifei	2012-05-24 23:25:37.0	true	false	false	false	true	false	null	null	null	false	null	false	null	nu
ll	false

sqoop从mysql导入到hive