HiveSelect操作_hive select

保存select查询结果的几种方式

1.将查询结果保存到一张新的hive表中create table t_tmp
as
select * from t_p;2将查询结果保存到一张已经存在的hive表中

eg:(into 增量增加)
insert into table t_tmp
select * from t_p;

#-----------------------------------------------------
eg:(overwrite 覆盖增加)
insert overwrite table ol_oitf_interface_auc_dimension_value_business
select *
  from tmp_auc_dimension_value_business_new;
1
2
3
4
5
6
7
8
9

3.将查询结果保存到指定的文件目录（可以是本地，也可以是HDFS）

insert overwrite local directory '/home/hadoop/test'
select * from t_p;
#---------------------------------------------------
#插入HDFS
insert overwrite directory '/aaa/test'
select * from t_p;
1
2
3
4
5
6

Group By操作

GROUP BY子句的语法如下：

SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM
table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING
having_condition] [ORDER BY col_list]] [LIMIT number];eg： SELECT
pageid, age, count(1) FROM pv_users GROUP BY pageid, age;

具体实现过程如图：

实例：
presto:default> select *from(
             -> select one_department,
             ->   two_department,
             ->   three_department,
             ->   four_department,
             ->   five_department ,count(*) as num  from tmp_kn1_ehr_person_organization_20180817 where one_department is not Null group by one_department,
             ->   two_department,
             ->   three_department,
             ->   four_department,
             ->   five_department ) t2 where t2.num>1 limit 5;
1
2
3
4
5
6
7
8
9
10
11

Group By sum 计算

表数据：
    presto:default> select *from tab4 order by id1;
     id1  | size | name 
    ------+------+------
        1 |   10 | AAA  
        2 |   20 | BBB  
        3 |   30 | DDD  
        3 |   30 | CCC  
        4 |   40 | NULL 
        8 | NULL | HHH  
       99 | NULL | TTT  
     NULL | NULL | EEE  
     NULL |   76 | HHH  
    (9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

计算 size 的总数

Group By 排列组合

表语句 （查询tab4）：
    presto:default> select id1,size,name from tab4 group by id1,size,name order by id1;
     id1  | size | name 
    ------+------+------
        1 |   10 | AAA  
        2 |   20 | BBB  
        3 |   30 | DDD  
        3 |   30 | CCC  
        4 |   40 | NULL 
        8 | NULL | HHH  
       99 | NULL | TTT  
     NULL | NULL | EEE  
     NULL |   76 | HHH  
    (9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

tab4 分组：
    presto:default> select id1,size from tab4 group by id1,size order by id1;
     id1  | size 
    ------+------
        1 |   10 
        2 |   20 
        3 |   30 
        4 |   40 
        8 | NULL 
       99 | NULL 
     NULL | NULL 
     NULL |   76 
    (8 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

tab4 分组并 size列 赋值Null：
    presto:default> select id1,'null' as size from tab4 group by id1,size order by id1;
     id1  | size 
    ------+------
        1 | null 
        2 | null 
        3 | null 
        4 | null 
        8 | null 
       99 | null 
     NULL | null 
     NULL | null 
    (8 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

tab4 分组并 多出来一列 name 赋值Null： 
    presto:default> 
    presto:default> select id1,size,null as name from tab4 group by id1,size order by id1;
     id1  | size | name 
    ------+------+------
        1 |   10 | NULL 
        2 |   20 | NULL 
        3 |   30 | NULL 
        4 |   40 | NULL 
        8 | NULL | NULL 
       99 | NULL | NULL 
     NULL | NULL | NULL 
     NULL |   76 | NULL 
    (8 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

tab4 分组并 多出来一列 size 赋值Null：
presto:default> select id1,size,null as size from tab4 group by id1,size order by id1;
 id1  | size | size 
------+------+------
    1 |   10 | NULL 
    2 |   20 | NULL 
    3 |   30 | NULL 
    4 |   40 | NULL 
    8 | NULL | NULL 
   99 | NULL | NULL 
 NULL | NULL | NULL 
 NULL |   76 | NULL 
(8 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

tab4 分组并少一列 size ：
presto:default> select id1 from tab4 group by id1,size order by id1;
 id1  
------
    1 
    2 
    3 
    4 
    8 
   99 
 NULL 
 NULL 
(8 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

类型转化 decimal（）

create table tab5 as select res_pay_total,price_total,order_id,hotel_id,start_date from tmp_ol_dtfet_selftour_resales_date_go limit 10 union all 1.0000000000000000000000009 as res_pay_total, 2.00000000000009 as price_total,3 as order_id,5 as hotel_id,566 as start_date from dual;

1

Having

1