每日日报 2021.10.6
完成内容：
1.编写 2019 级课堂测试试卷-数据清洗 地域维度清洗
使用sql语句创建一个新表存放清洗后的数据：create table qiye1(id int, QA04 string,QA05 string,QA07 string,QA15 string,QA19 int,hangyeweidu string,QB03 int,QB03ONE string,QB03TWO string,QB03_1 int,QB06 int,QB16 int,QB16V string,gaoxinjishuweidu string,QB16_1 int,QB16_1V string,QC02 double,QC05_0 double,QC24 double,QC40 double,QD01 int,QD28 int,QJ09 int,QJ20 double,QJ55 int,QJ74 int,diyuweidu string,SYEAR string)ROW format delimited fields terminated by ',';
链接两表清洗数据：insert overwrite table qiye1 select qiye.id as id,qiye.qa04 as qa04,qiye.QA05 as QA05,qiye.QA07 as QA07,qiye.QA15 as QA15,qiye.QA19 as QA19,qiye.hangyeweidu as hangyeweidu,qiye.QB03 as QB03,qiye.QB03ONE as QB03ONE,qiye.QB03TWO as QB03TWO,qiye.QB03_1 as QB03_1,qiye.QB06 as QB06,qiye.QB16 as QB16,qiye.QB16V as QB16V,qiye.gaoxinjishuweidu as gaoxinjishuweidu,qiye.QB16_1 as QB16_1,qiye.QB16_1V as QB16_1V,qiye.QC02 as QC02,qiye.QC05_0 as QC05_0,qiye.QC24 as QC24,qiye.QC40 as QC40,qiye.QD01 as QD01,qiye.QD28 as QD28,qiye.QJ09 as QJ09,qiye.QJ20 as QJ20,qiye.QJ55 as QJ55,qiye.QJ74 as QJ74,concat(qiye.QA19,xinzhen.dmms) as diyuweidu,qiye.SYEAR as SYEAR from qiye join xinzhen where (xinzhen.dm==qiye.QA19);
- 导入mysql
创建mysql表：CREATE TABLE `qiye1` (
`id` int(20) NOT NULL,
`qa04` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`qa05` varchar(500) CHARACTER SET utf8mb4 NOT NULL,
`qa07` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`qa15` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`qa19` int(20) NOT NULL,
`hangyeweidu` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL,
`qb03` int(20) NOT NULL,
`qb03one` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`qb03two` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`qb03_1` int(20) NOT NULL,
`qb06` int(20) NOT NULL,
`qb16` int(20) NOT NULL,
`qb16v` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`gaoxinjishuweidu` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL,
`qb16_1` int(20) NOT NULL,
`qb16_1v` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`qc02` double(20,2) NOT NULL,
`qc05_0` double(20,2) NOT NULL,
`qc24` double(20,2) NOT NULL,
`qc40` double(20,2) NOT NULL,
`qd01` int(20) NOT NULL,
`qd28` int(20) NOT NULL,
`qj09` int(20) NOT NULL,
`qj20` double(20,2) NOT NULL,
`qj55` int(20) NOT NULL,
`qj74` int(20) NOT NULL,
`diyuweidu` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL,
`syear` varchar(20) CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
使用sqoop导入mysql：bin/sqoop export \
--connect "jdbc:mysql://hadoop102:3306/metastore?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 000000 \
--table qiye1 \
--num-mappers 1 \
--export-dir /user/hive/warehouse/qiye1 \
--input-null-string '\\N'
