今天在将一个hive数仓表导出到mysql数据库时出现进度条一直维持在95%一段时间后提示失败的情况,搞了好久才解决。使用的环境是HUE中的Oozie的workflow任何调用sqoop命令,该死的oozie的日志和异常提示功能太辣鸡了,最后发现是重复数据导致数据进入mysql表时出现主键冲突进而导致数据同步失败。
(1)众所周知hive表是没有主键与索引的,但是mysql的表一般在创建时就会指定主键,所以在把hive表中的数据导入mysql表的时候通常会使用原hive表中的多个字段构成联合主键,这几个主键字段的值必须能唯一地标识表中的每一条记录,也即导入数据的这几个字段的联合值不能出现重复值。
比如下面这张mysql表中主键由5个字段构成agent_id,estate_id,event_name,action_timestamp,dt,因此在导入数据时需保证不能出现重复值。
CREATE TABLE IF NOT EXISTS tmp_shujuxiong_20190116( agent_id bigint(20) NOT NULL DEFAULT '0' comment '经纪人id', agent_true_name varchar(1000) DEFAULT NULL comment '经纪人姓名', longitude varchar(1000) DEFAULT NULL comment '用户所在经度', latitude varchar(1000) DEFAULT NULL comment '用户所在纬度', action_timestamp VARCHAR(200) NOT NULL comment '动作时间', action_date VARCHAR(200) NOT NULL comment '动作日期', event_name varchar(200) NOT NULL comment '事件名称', estate_id bigint(20) NOT NULL comment '楼盘id', estate_name varchar(1000) DEFAULT NULL comment '楼盘名称', estate_developer_name varchar(1000) DEFAULT NULL comment '楼盘开发商名称', estate_developer_brand_name varchar(1000) DEFAULT NULL comment '楼盘开发商品牌', load_job_number varchar(1000) DEFAULT NULL comment '数据仓库调度工具oozie job单次运行id, 使用oozie EL function: ${wf:id()}', load_job_name varchar(1000) DEFAULT NULL comment '数据仓库调度工具oozie_job名称: 使用oozie EL function: ${wf:name()}', insert_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '数据仓库数据插入时间', source_system_code INT(11) comment '数据仓库的源系统数据分类:1-二手房;2-新房;3-金融;4-租房', dt varchar(8) NOT NULL comment '分区字段', PRIMARY KEY (agent_id,estate_id,event_name,action_timestamp,dt), KEY agent_id (agent_id) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT 'XXX接口数据' ;
(2)另一个需要注意的问题的是字段类型与字段长度,hive的数据类型与mysql的数据不完全相同,因此mysql中创建表的时候需要特别注意字段的类型与长度。
(3)可以在hive表中使用一个自增字段起到类似主键的作用,以便于在hive和mysql间进行数据同步,确保数据同步时不发生主键冲突。
如下面这段HQL脚本中可以使用自增序号字段起到主键的作用,在其他字段组合不能保证数据唯一性的时候
alter table tmp_table_name drop if exists partition (dt = '${dt}'); alter table tmp_table_name add if not exists partition (dt = '${dt}'); insert overwrite table tmp_table_name partition(dt = '${dt}') select row_number()over() as row_number -- 自增序号 , id ... from ...