03.大型数据库应用技术课堂测试3

hive命令行
create table AA2019(id varchar(30),QA04 varchar(30),QA05 varchar(30),QA07 varchar(30),QA15 varchar(30),QA19 varchar(30),HYdimension varchar(30),QB03 varchar(30),QB03ONE varchar(30),QB03TWO varchar(30),QB03_1 varchar(30),QB06 varchar(30),QB16 varchar(30),QB16V varchar(30),GXJSdimension varchar(30),QB16_1 varchar(30),QB16_1V varchar(30),QC02 varchar(30),QC05_0 varchar(30),QC24 varchar(30),QC40 varchar(30),QD01 varchar(30),QD28 varchar(30),QJ09 varchar(30),QJ20 varchar(30),QJ55 varchar(30),QJ74 varchar(30),DYdimension varchar(30),SYEAR varchar(30)) row format delimited fields terminated by ',' ;

load data local inpath '/linmob/data/AA2019.csv' overwrite into table AA2019;

create table XZQHcode(dm varchar(30),dmms varchar(30)) row format delimited fields terminated by ',' ;

load data local inpath '/linmob/data/XZQHcode.csv' overwrite into table XZQHcode;

create table AA2019_xzqh(id varchar(30),QA04 varchar(30),QA05 varchar(30),QA07 varchar(30),QA15 varchar(30),QA19 varchar(30),HYdimension varchar(30),QB03 varchar(30),QB03ONE varchar(30),QB03TWO varchar(30),QB03_1 varchar(30),QB06 varchar(30),QB16 varchar(30),QB16V varchar(30),GXJSdimension varchar(30),QB16_1 varchar(30),QB16_1V varchar(30),QC02 varchar(30),QC05_0 varchar(30),QC24 varchar(30),QC40 varchar(30),QD01 varchar(30),QD28 varchar(30),QJ09 varchar(30),QJ20 varchar(30),QJ55 varchar(30),QJ74 varchar(30),DYdimension varchar(30),SYEAR varchar(30)) row format delimited fields terminated by ',' ;

insert into table AA2019_xzqh select id as id, QA04 as QA04, QA05 as QA05, QA07 as QA07,QA15 as QA15, QA19 as QA19, HYdimension as HYdimension,QB03 as QB03, QB03ONE as QB03ONE, QB03TWO as QB03TWO, QB03_1 as QB03_1,QB06 as QB06,QB16 as QB16,QB16V as QB16V, GXJSdimension as GXJSdimension,QB16_1 as QB16_1, QB16_1V as QB16_1V, QC02 as QC02, QC05_0 as QC05_0, QC24 as QC24, QC40 as QC40, QD01 as QD01, QD28 as QD28 , QJ09 as QJ09, QJ20 as QJ20 , QJ55 as QJ55 , QJ74 as QJ74 ,concat(QA19,(select dmms from xzqhcode where dm=QA19)) as DYdimension, SYEAR as SYEAR from aa2019;

mysql命令行
create table AA2019_xzqh(id varchar(30),QA04 varchar(30),QA05 varchar(30),QA07 varchar(30),QA15 varchar(30),QA19 varchar(30),HYdimension varchar(30),QB03 varchar(30),QB03ONE varchar(30),QB03TWO varchar(30),QB03_1 varchar(30),QB06 varchar(30),QB16 varchar(30),QB16V varchar(30),GXJSdimension varchar(30),QB16_1 varchar(30),QB16_1V varchar(30),QC02 varchar(30),QC05_0 varchar(30),QC24 varchar(30),QC40 varchar(30),QD01 varchar(30),QD28 varchar(30),QJ09 varchar(30),QJ20 varchar(30),QJ55 varchar(30),QJ74 varchar(30),DYdimension varchar(30),SYEAR varchar(30))charset utf8 collate utf8_general_ci;

sqoop/bin目录下
./sqoop export --connect "jdbc:mysql://192.168.111.100:3306/tab?characterEncoding=UTF-8" --username root --password root --table AA2019_xzqh --export-dir /user/hive/warehouse/aa2019_xzqh --input-null-string "\\\\N" --input-null-non-string "\\\\N" --input-fields-terminated-by "," --input-lines-terminated-by "\\n" -m 1

create table result(ip varchar(30),times varchar(30),day varchar(30),traffic varchar(30),type varchar(30),id varchar(30)) row format delimited fields terminated by ',' ;

load data local inpath '/linmob/data/result.txt' overwrite into table result;

create temporary function rm as "hiveUDF.RemoveQuote";
create temporary function df as "hiveUDF.DateTransform";

create table clean_result(ip varchar(30),times varchar(30),day varchar(30),traffic varchar(30),type varchar(30),id varchar(30)) row format delimited fields terminated by ',' ;

insert overwrite table clean_result select ip,df(rm(times)),day,traffic,type,id from result;

create table clean_result(ip varchar(30),times varchar(30),day varchar(30),traffic varchar(30),type varchar(30),id varchar(30)) row format delimited fields terminated by ',' ;

create table clean_result(ip varchar(30),times varchar(30),day varchar(30),traffic varchar(30),type varchar(30),id varchar(30))charset utf8 collate utf8_general_ci;
./sqoop export --connect "jdbc:mysql://192.168.111.100:3306/tab?characterEncoding=UTF-8" --username root --password root --table clean_result --export-dir /user/hive/warehouse/clean_result --input-null-string "\\\\N" --input-null-non-string "\\\\N" --input-fields-terminated-by "," --input-lines-terminated-by "\\n" -m 1