hive select * 语法中去掉部分列_hive中select排除某些列

应用场景

hive表有很多列，大部分列需要，其中一列不需要，例如分区表的dt字段不要，例如1000列中去掉1列

实现方法

1. 方法

hive sql： 实现功能 select `(dt)?+.+` from test; 这里dt是不要的字段。

以上sql生效需要设置一个参数：

set hive.support.quoted.identifiers=none;

2.demo测试

让语法生效

hive> set hive.support.quoted.identifiers=none;
hive> set hive.cli.print.header;
hive.cli.print.header=false
hive> set hive.cli.print.header=true;
1
2
3
4
5

表全部字段

hive> select * from test;
hook status=true,operation=QUERY
OK
name    friends children        address
songsong        ["bingbing","lili"]     {"xiao song":18,"xiaoxiao song":19}     {"street":"hui long guan","city":"beijing"}
yangyang        ["caicai","susu"]       {"xiao yang":18,"xiaoxiao yang":19}     {"street":"chao yang","city":"beijing"}
Time taken: 0.14 seconds, Fetched: 2 row(s)
1
2
3
4
5
6
7
8

从select * 中去掉一个字段address

hive> select `(address)?+.+` from test;
hook status=true,operation=QUERY
OK
name    friends children
songsong        ["bingbing","lili"]     {"xiao song":18,"xiaoxiao song":19}
yangyang        ["caicai","susu"]       {"xiao yang":18,"xiaoxiao yang":19}
Time taken: 0.144 seconds, Fetched: 2 row(s)

1
2
3
4
5
6
7
8

从select * 中去掉多个字段

hive> select `(name|address)?+.+` from test;
hook status=true,operation=QUERY
OK
friends children
["bingbing","lili"]     {"xiao song":18,"xiaoxiao song":19}
["caicai","susu"]       {"xiao yang":18,"xiaoxiao yang":19}
Time taken: 0.149 seconds, Fetched: 2 row(s)
1
2
3
4
5
6
7

总结

set hive.support.quoted.identifiers=none;
select `(dt)?+.+` from test; 这里dt是不要的字段。 就可以实现从select * 中去掉一列