Hive语法及其进阶(二)

2016-12-30 11:51

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

Hive语法及其进阶(二)

1、使用JDBC连接Hive

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.PreparedStatement;
 4 import java.sql.ResultSet;
 5 
 6 public class HiveDemo {
 7     public static void main(String[] args) throws Exception {
 8         Class.forName("org.apache.hive.jdbc.HiveDriver");
 9         //"jdbc:hive2://master:10000/test3"
10         Connection connection = DriverManager.getConnection("jdbc:hive2://master:10000/myhive");
11         String sql="select * from students";
12         PreparedStatement ps = connection.prepareStatement(sql);
13         ResultSet rs = ps.executeQuery();
14         while (rs.next()){
15             int id = rs.getInt(1);
16             String name = rs.getString(2);
17             int age = rs.getInt(3);
18             String gender = rs.getString(4);
19             String clazz = rs.getString(5);
20             System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
21         }
22         rs.close();
23         ps.close();
24         connection.close();
25     }
26 }

2、Hive常用函数

1.关系运算

// 等值比较 = == <=>
// 不等值比较 != <>
// 区间比较： select * from default.students where id between 1500100001 and 1500100010;
// 空值/非空值判断：is null、is not null、nvl()、isnull()

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

2 .数值计算

取整函数(四舍五入)：round
向上取整：ceil
向下取整：floor

like、rlike、

（1）查找名字以A开头的员工信息

hive (default)> select * from emp where ename LIKE "A%";

（2）查找名字中第二个字母为A的员工信息

hive (default)> select * from emp where ename LIKE "_A%";

（3）查找名字中带有A的员工信息

hive (default)> select * from emp where ename RLIKE "[A]";

3.日期函数

1 select from_unixtime(1610611142,"YYYY/MM/dd HH:mm:ss");
2unix_timestamp(),获取当前时间的时间戳

3 select from_unixtime(unix_timestamp(),"YYYY/MM/dd HH:mm:ss");

4 // "2021年01月14日" -> "2021-01-14" 
6 select from_unixtime(unix_timestamp("2021年01月14日","yyyy年MM月dd日"),"yyyy-MM-dd");
8 select from_unixtime(unix_timestamp("04-2021-16","MM-yyyy-dd"),"yyyy/MM/dd");

4.字符串函数

1）cancat（）字符串拼接当有空值则为NULL

2）cancat_ws()指定可以指定分隔符，并且会自动忽略NULL

3)substring字符串的截取

4)split字符串的切分

5）explode列转行

解析json格式的数据

select get_json_object
("{"name":"zhangsan",
"age":18,
"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}",
"$.score[0].score");

6) Hive中的wordcount

create table words(
    words string
)row format delimited fields terminated by "|";

// 数据
hello,java,hello,java,scala,python
hbase,hadoop,hadoop,hdfs,hive,hive
hbase,hadoop,hadoop,hdfs,hive,hive

select word,count(*) from (select explode(split(words,",")) word from words) a group by a.word;

4.开窗函数

##### row_number：无并列排名

分组求TOPN

select * from (select  *, row_number() over(partition by clazz order by score desc)as s from new_score)tt where tt.s<=3;

用法： select xxxx, row_number() over(partition by 分组字段 order by 排序字段 desc) as rn from tb group by xxxx

##### dense_rank：有并列排名，并且依次递增

hive> select  *, row_number() over(partition by clazz order by score desc)as s,
    > dense_rank()  over(partition by clazz order by score desc)as s from new_score;

##### rank：有并列排名，不依次递增

hive> select  *, row_number() over(partition by clazz order by score desc)as s,
    > dense_rank()  over(partition by clazz order by score desc),
    > rank() over(partition by clazz order by score desc)from new_score;

##### percent_rank：(rank的结果-1)/(分区内数据的个数-1)

select *, row_number() over(partition by clazz order by score desc)as s,
> rank() over(partition by clazz order by score desc),
> percent_rank() over(partition by clazz order by score desc)from new_score;

##### cume_dist：计算某个窗口或分区中某个值的累积分布。

select *, row_number() over(partition by clazz order by score desc)as s,
> rank() over(partition by clazz order by score desc),
> percent_rank() over(partition by clazz order by score desc),
> cume_dist() over(partition by clazz order by score desc) from new_score;