业务背景
mysql数据表YHD_CATEG_PRIOR结构如下:
-- Table "YHD_CATEG_PRIOR" DDL
CREATE TABLE `YHD_CATEG_PRIOR` (
`category_id` int(11) NOT NULL COMMENT '类目ID',
`category_name` varchar(250) DEFAULT NULL COMMENT '类目名称',
`category_level` int(11) DEFAULT '0' COMMENT '类目级别',
`default_import_categ_prior` int(11) DEFAULT '0' COMMENT '默认引入优先级',
`user_import_categ_prior` int(11) DEFAULT NULL COMMENT '用户引入优先级',
`default_eliminate_categ_prior` int(11) DEFAULT NULL COMMENT '默认淘汰优先级',
`user_eliminate_categ_prior` int(11) DEFAULT NULL COMMENT '用户淘汰优先级',
`UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据刷新时间',
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61125 DEFAULT CHARSET=utf8;
现在需要将数据表YHD_CATEG_PRIOR导入到Hive中。
业务实现
脚本如下:
# 创建Hive数据表pms.yhd_categ_prior_user
hive -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,
category_level int,
default_import_categ_prior int,
user_import_categ_prior int,
default_eliminate_categ_prior int,
user_eliminate_categ_prior int,
update_time string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;"
# 同步mysql的market.YHD_CATEG_PRIOR到hive中
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
上述的脚本工作流程:
- 创建hive表pms.yhd_categ_prior_user
- 利用sqoop将mysql表YHD_CATEG_PRIOR同步到hive表pms.yhd_categ_prior_user,导入后,hive表的字段以\t分隔,行以\n分隔,
实验结果
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 Jun 05 18:48:01 CST 2015
LastAccessTime: 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.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim \t
line.delim \n
serialization.format \t