分享知识,分享快乐

0%

hive建表create table xxx as select的问题

hive建表create table xxx as select的问题

create table xxx as select的方式创建的表默认存储格式是text,所以要注意了假如as select的是其他格式的比如RCFile,则可能会导致一行变多行的情况(因为RCFile格式的可能字段包含换行符等),所以必须要加上
create table xxx stored as RCFile as select…
所以使用这种方式建表注意加上指定的存储格式。

测试示例:

1
2
3
4
5
6
drop table if exists decision_model.member_close_reason;
create table decision_model.member_close_reason as
select 'mongodb_member' as intype, *
from mongo_baofoo_log.log_update_member_state
;
select * from decision_model.member_close_reason where intype <> 'mongodb_member';

img

正确的应该加上指定的存储格式。

1
2
3
4
5
6
7
8
9
10
11
drop table if exists decision_model.member_close_reason;
create table decision_model.member_close_reason
row format delimited
fields terminated by '|'
STORED AS RCFile
as
select 'mongodb_member' as intype, *
from mongo_baofoo_log.log_update_member_state
;

select * from decision_model.member_close_reason where intype <> 'mongodb_member';