hive转到mysql_将hive中的数据导入MySQL

.;%JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\lib\tools.jar

sqoop export --connect 'jdbc:mysql://172.16.30.54:3306/xh_xueqing?useUnicode=true&characterEncoding=utf-8' --username bigdatauser --password Bigdata@321 --table fact_application_homework_daily --hcatalog-database temp--hcatalog-table fact_app_homework_daily

sqoop export --connect 'jdbc:mysql://172.16.30.54:3306/xh_xueqing?useUnicode=true&characterEncoding=utf-8' --username bigdatauser --password Bigdata@321 --table fact_application_homework_daily --hcatalog-database temp--hcatalog-table fact_app_homework_daily

sqoop export \

--connect 'jdbc:mysql://172.16.30.54:3306/xh_xueqing?useUnicode=true&characterEncoding=utf-8' \

--username bigdatauser \

--password Bigdata@321 \

--table knowledge_mapping_daily \                  //mysql中表名

--input-null-string '\\N' \

--input-null-non-string '\\N' \

--hcatalog-database temp \

--hcatalog-table fact_knowledge_mapping_daily_00 \          //hive表名

--fields-terminated-by '\t' \

--lines-terminated-by '\n'

-------------------------------------------------------------------------------------------------

insert overwrite table temp.fact_app_homework_daily

select

id

,school_id

,school_name

,school_period_id

,school_period_name

,grade_id

,grade_name

,class_id

,class_name

,teacher_id

,teacher_name

,subject_id

,subject_name

,app_id

,app_name

,qst_num

,rigth_qst_num

,self_work_num

,correct_homework_count

,avg_score

,assign_homework_count

,assgin_qst_num

,teacher_micro_course_num

,create_time

,distinguish_flag

,statis_day

,hand_in_qst_num

from rptdata.fact_app_homework_daily

drop table temp.fact_app_homework_daily;

CREATE TABLE temp.fact_app_homework_daily(

id bigint,

school_id bigint,

school_name string,

school_period_id bigint,

school_period_name string,

grade_id bigint,

grade_name string,

class_id bigint,

class_name string,

teacher_id bigint,

teacher_name string,

subject_id bigint,

subject_name string,

app_id string,

app_name string,

qst_num bigint,

rigth_qst_num bigint,

self_work_num bigint,

correct_homework_count bigint,

avg_score bigint,

assign_homework_count bigint,

assgin_qst_num bigint,

teacher_micro_course_num bigint,

create_time string,

distinguish_flag string,

statis_day string,

hand_in_qst_num bigint

)

row format delimited fields terminated by '\t'

lines terminated by '\n'

stored as textfile;

CREATE TABLE `fact_application_homework_daily2` (

`id` bigint(20) DEFAULT NULL COMMENT 'ID',

`school_id` bigint(100)  DEFAULT '0' COMMENT '学校标识',

`school_name` varchar(200) DEFAULT NULL COMMENT '学校名称',

`school_period_id` bigint(10)  DEFAULT '0' COMMENT '学段标识',

`school_period_name` varchar(100) DEFAULT NULL COMMENT '学段名称',

`grade_id` bigint(10)  DEFAULT '0' COMMENT '年级标识',

`grade_name` varchar(100) DEFAULT NULL COMMENT '年级名称',

`class_id` bigint(100) DEFAULT NULL,

`class_name` varchar(100) DEFAULT NULL COMMENT '班级名称',

`teacher_id` bigint(10)  DEFAULT '0' COMMENT '老师标识',

`teacher_name` varchar(100) DEFAULT NULL COMMENT '老师名称',

`subject_id` bigint(10)  DEFAULT '0' COMMENT '科目标识',

`subject_name` varchar(100) DEFAULT NULL COMMENT '科目名称',

`app_id` varchar(100)  DEFAULT '0' COMMENT '应用标识',

`app_name` varchar(100) DEFAULT NULL COMMENT '应用名称',

`qst_num` bigint(20) DEFAULT '0' COMMENT '?????',

`rigth_qst_num` bigint(20) DEFAULT '0' COMMENT '????',

`self_work_num` bigint(20) DEFAULT '0' COMMENT '????',

`correct_homework_count` bigint(20) DEFAULT '0' COMMENT '???????',

`avg_score` bigint(20) DEFAULT '0' COMMENT '????',

`assign_homework_count` bigint(20) DEFAULT '0' COMMENT '??????',

`assgin_qst_num` bigint(20) DEFAULT '0' COMMENT '??????',

`teacher_micro_course_num` bigint(20) DEFAULT '0' COMMENT '????????',

`create_time` varchar(8) DEFAULT NULL COMMENT '筛选的时间',

`distinguish_flag` varchar(1) DEFAULT NULL COMMENT '区分',

`statis_day` char(8) DEFAULT '0' COMMENT '统计日期',

`hand_in_qst_num` bigint(20) DEFAULT '0' COMMENT '学生上交题量',

KEY `idx_schoolId` (`school_id`) USING HASH,

KEY `t1` (`school_period_id`) USING BTREE,

KEY `t2` (`grade_id`),

KEY `t3` (`class_id`),

KEY `t4` (`teacher_id`),

KEY `t5` (`app_id`),

KEY `t6` (`subject_id`),

KEY `t7` (`statis_day`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---mysql的问题：

修改mysql字段长度

alter table fact_app_homework_daily1  modify column class_id int(100);

更改数据：

UPDATE cs_user SET gender = '男' WHERE id = 4