每日日报 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);

 

 

 

 

 

1. 导入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'

 

 

 

 

 

 

遇到问题：

 无

目标：继续学习hadoop