hive数据库建表语句
1.数据清洗建表语句
create table hive.yuanbiao ( ID string,QA04 string,QA05 string,QA07 string,QA15 string,QA19 string,HYWD string,QB03ONE string,QB03ONEONE string,QB03ONETWO string,
QB03ONETWO string,QB06 string,QB16 string,QB16V string,JSWD string,QB16_1 string,QB16_1V string,QC02 string,QC24 string,QC40 string,QD01 string,QD28 string,
QJ09 string,QJ20 string, QJ55 string,QJ74 string,DYWD string,SYEAR string)ROW format delimited fields terminated by ',' STORED AS TEXTFILE LOCATION '/aa';
create table hive.NewTable ( ID string,QA04 string,QA05 string,QA07 string,QA15 string,QA19 string,HYWD string,QB03ONE string,QB03ONEONE string,QB03ONETWO string,
QB03ONETWO string,QB06 string,QB16 string,QB16V string,JSWD string,QB16_1 string,QB16_1V string,QC02 string,QC24 string,QC40 string,QD01 string,QD28 string,
QJ09 string,QJ20 string, QJ55 string,QJ74 string,DYWD string,SYEAR string)ROW format delimited fields terminated by ',' STORED AS TEXTFILE
2.清洗语句
insert overwrite table NewTable
select yuanbiao.ID as ID,yuanbiao.QA04 as QA04,
yuanbiao.QA05 as QA05,yuanbiao.QA07 as QA07,yuanbiao.QA15 as QA15,
yuanbiao.QA19 as QA19,yuanbiao.HYWD as HYWD,yuanbiao.qb03 as qb03,
yuanbiao.QB03ONE as QB03ONE,yuanbiao.QB03TWO as QB03TWO,yuanbiao.QB03_1 as QB03_1,
yuanbiao.QB06 as QB06,yuanbiao.QB16 as QB16,yuanbiao.QB16V as QB16V,yuanbiao.JSWD as JSWD,
yuanbiao.QB16_1 as QB16_1,yuanbiao.QB16_1V as QB16_1V,yuanbiao.QC02 as QC02,yuanbiao.QC24 as QC24,
yuanbiao.QC40 as QC40,yuanbiao.QD01 as QD01,yuanbiao.QD28 as QD28,yuanbiao.QJ09 as QJ09,yuanbiao.QJ20 as QJ20,yuanbiao.QJ55 as QJ55,
yuanbiao.QJ74 as QJ74,concat(yuanbiao.QA19,area.area) as DYWD,yuanbiao.SYEAR as SYEAR from yuanbiao join area where (area.id==yuanbiao.QA19);
要点 原表与新表结构保持一致 名称保持一致
3. desc+表名查看表结构