Hive语法及其进阶(二)

database

1、使用JDBC连接Hive

 1import java.sql.Connection;

2import java.sql.DriverManager;

3import java.sql.PreparedStatement;

4import java.sql.ResultSet;

5

6publicclass HiveDemo {

7publicstaticvoid 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();

14while (rs.next()){

15int id = rs.getInt(1);

16 String name = rs.getString(2);

17int 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

基本数据类型

如果AB都为NULL,则返回TRUE,如果一边为NULL,返回False

A<>B, A!=B

基本数据类型

A或者BNULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE

A<B

基本数据类型

A或者BNULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE

A<=B

基本数据类型

A或者BNULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE

A>B

基本数据类型

A或者BNULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE

A>=B

基本数据类型

A或者BNULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE

A [NOT] BETWEEN B AND C

基本数据类型

如果AB或者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;反之返回FALSEB的表达式说明如下:‘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.日期函数

 

1select from_unixtime(1610611142,"YYYY/MM/dd HH:mm:ss");

2unix_timestamp(),获取当前时间的时间戳

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

4 //"2021年01月14日"->"2021-01-14"
6select from_unixtime(unix_timestamp("2021年01月14日","yyyy年MM月dd日"),"yyyy-MM-dd");
8select 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

createtable 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 groupby a.word;

 

4.开窗函数

 

##### row_number:无并列排名

 

分组求TOPN

select*from (select*, row_number() over(partition by clazz orderby 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 orderby score desc)as s,

> dense_rank() over(partition by clazz orderby score desc)as s from new_score;

 

 

 

##### rank:有并列排名,不依次递增

 

hive>select*, row_number() over(partition by clazz orderby score desc)as s,

> dense_rank() over(partition by clazz orderby score desc),

> rank() over(partition by clazz orderby 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 orderby score desc)as s,

> rank() over(partition by clazz orderby score desc),

> percent_rank() over(partition by clazz orderby score desc),

> cume_dist() over(partition by clazz orderby score desc) from new_score;

 

 

> 假定升序排序,则使用以下公式确定累积分布: 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中,x 等于 order by 子句中指定的列的当前行中的值。

 

##### NTILE(n):对分区内数据再分成n组,然后打上组号

 

##### max、min、avg、count、sum:基于每个partition分区内的数据做对应的计算

 

5.窗口帧格式
  格式1:按照行的记录取值

      ROWS BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)

  格式2:当前所指定值的范围取值

      RANGE BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
注意:
    UNBOUNDED:无界限

    CURRENT ROW:当前行

 

rows格式1:前2行+当前行+后两行

    sum(score) over (partition by clazz order by score desc rows between 2 PRECEDING and 2 FOLLOWING)

rows格式2:前记录到最末尾的总和

    sum(score) over (partition by clazz order by score desc rows between CURRENT ROW and UNBOUNDED FOLLOWING)

 

range格式1: 如果当前值在80,取值就会落在范围在80-2=78和80+2=82组件之内的行

    max(score) over (partition by clazz order by score desc range between 2 PRECEDING and 2 FOLLOWING)

 

以上是 Hive语法及其进阶(二) 的全部内容, 来源链接: utcz.com/z/535980.html

回到顶部