16.Hive常用内置函数示例

Leefs 2021-12-11 PM 2958℃ 2条

[TOC]

### 一、字符函数示例

**1.1字符串反转函数**

> 语法：reverse(string A)

说明：返回字符串A的反转结果

```sql
0: jdbc:hive2://hadoop001:10000> select reverse("abcdef");
+---------+
|   _c0   |
+---------+
| fedcba  |
+---------+
```

**1.2 字符串连接函数**

> 语法: concat(string A, string B…)

说明：返回输入字符串连接后的结果，支持任意个输入字符串

```sql
0: jdbc:hive2://hadoop001:10000> select concat("abc","-","def","-","hij");
+--------------+
|     _c0      |
+--------------+
| abc-def-hij  |
+--------------+
```

**1.3 带分隔符字符串连接函数**

> 语法: concat_ws(string SEP, string A, string B…)

说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

```sql
0: jdbc:hive2://hadoop001:10000> select concat_ws("-","abc","def","hij");
+--------------+
|     _c0      |
+--------------+
| abc-def-hij  |
+--------------+
```

**1.4 字符串截取函数**

>  语法: substr(string A, int start),substring(string A, int start)

说明：返回一个新的字符串（A从start位置到结尾的字符串）

```sql
0: jdbc:hive2://hadoop001:10000> select substr('abcdefh',3);
+--------+
|  _c0   |
+--------+
| cdefh  |
+--------+
0: jdbc:hive2://hadoop001:10000> select substring('abcdefh',3);
+--------+
|  _c0   |
+--------+
| cdefh  |
+--------+
```

> 语法: substr(string A, int start, int len),substring(string A, int start, int len)

说明：返回字符串A从start位置开始，长度为len的字符串

```sql
0: jdbc:hive2://hadoop001:10000> select substr('abcdefjh',3,2);
+------+
| _c0  |
+------+
| cd   |
+------+
0: jdbc:hive2://hadoop001:10000> select substring('abcdefjh',3,2);
+------+
| _c0  |
+------+
| cd   |
+------+
```

**1.5 正则表达式解析函数**

> 语法：regexp_extract(string subject, string pattern, int index)

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

```sql
0: jdbc:hive2://hadoop001:10000> select regexp_extract('foothebar','foo(.*?)(bar)',1);
+------+
| _c0  |
+------+
| the  |
+------+
0: jdbc:hive2://hadoop001:10000> select regexp_extract('foothebar','foo(.*?)(bar)',2);
+------+
| _c0  |
+------+
| bar  |
+------+
```

> 语法：regexp_replace(string x,string y,string z)

说明：替换字符串中的指定字段

+ 第一个参数 x 为字符串
+ 第二个参数 y 为需要替换的字符
+ 第三个参数 z 为替换成的字符

```sql
-- 将字符串中的一个或多个空格替换为，
select regexp_replace("hello word I Love you","\\s",",");
+------------------------+
|          _c0           |
+------------------------+
| hello,word,I,Love,you  |
+------------------------+
```

**1.6 解析URL字符串**

> 语法：parse_url(url, partToExtract[, key]) (这里要大写）

解析URL字符串，partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。

```sql
-- HOST:解析主机名
select parse_url('https://www.lilinchao.com/path?user_id=10000&platform=ios','HOST');
+--------------------+
|        _c0         |
+--------------------+
| www.lilinchao.com  |
+--------------------+
-- path（路径）：由零或多个“/”符号隔开的字符串，一般用来表示主机上的一个目录或文件地址
select parse_url('https://www.lilinchao.com/path?user_id=10000&platform=ios','PATH');
+--------+
|  _c0   |
+--------+
| /path  |
+--------+
-- query(查询)：可选，用于给动态网页传递参数，可有多个参数，用“&”符号隔开，每个参数的名和值用“=”符号隔开
select parse_url('https://www.lilinchao.com/path?user_id=10000&platform=ios','QUERY');
+-----------------------------+
|             _c0             |
+-----------------------------+
| user_id=10000&platform=ios  |
+-----------------------------+
-- 解析QUERY部分的value值
select parse_url('https://www.lilinchao.com/path?user_id=10000&platform=ios','QUERY','user_id');
+--------+
|  _c0   |
+--------+
| 10000  |
+--------+
-- FILE:解析域名后的路径和参数
select parse_url('https://www.lilinchao.com/path?user_id=10000&platform=ios','FILE');
+-----------------------------------+
|                _c0                |
+-----------------------------------+
| /path?user_id=10000&platform=ios  |
+-----------------------------------+
-- REF:锚点
select parse_url('https://www.lilinchao.com/path?user_id=10000&platform=ios#Ref','REF');
+------+
| _c0  |
+------+
| Ref  |
+------+
-- PROTOCOL:链接请求协议
select parse_url('https://www.lilinchao.com/path?user_id=10000','PROTOCOL');
+--------+
|  _c0   |
+--------+
| https  |
+--------+
-- AUTHORITY
select parse_url('https://www.lilinchao.com/path?user_id=10000','AUTHORITY');
+--------------------+
|        _c0         |
+--------------------+
| www.lilinchao.com  |
+--------------------+
-- USERINFO
select parse_url('https://www.lilinchao.com/path?user_id=10000','USERINFO');
+-------+
|  _c0  |
+-------+
| NULL  |
+-------+
```

**1.7  json解析函数**

> 语法：get_json_object(string json_string, string path)

说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

```sql
-- Json数据
{
    "class":{
        "student":[
            "FT1",
            "FT2",
            "FT3"
        ]
    },
    "leader":"siwo"
}

-- 取出leader中的值
select get_json_object('{"class":{"student":\["FT1","FT2","FT3"]},"leader":"siwo"}','$.leader');
+-------+
|  _c0  |
+-------+
| siwo  |
+-------+
-- 取出student中第二个元素值
select get_json_object('{"class":{"student":\["FT1","FT2","FT3"]},"leader":"siwo"}','$.class.student[1]');
+------+
| _c0  |
+------+
| FT2  |
+------+
```

**1.8 重复字符串**

> 语法: repeat(string str, int n)

说明：返回重复n次后的str字符串

```sql
select repeat('abc',3);
+------------+
|    _c0     |
+------------+
| abcabcabc  |
+------------+
```

**1.9  分割字符串函数**

> 语法: split(string str, string pat)

说明: 按照pat字符串分割str，会返回分割后的字符串数组

```sql
select split('aa,bb,cc,dd',',');
+------------------------+
|          _c0           |
+------------------------+
| ["aa","bb","cc","dd"]  |
+------------------------+
```

**1.10 集合查找函数**

> 语法: find_in_set(string str, string strList)

说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0

```sql
select find_in_set('de','ef,ab,de');
+------+
| _c0  |
+------+
| 3    |
+------+
```

**1.11 字符串转Map函数**

> 语法：str_to_map(string text,string delimiter1,string delimiter2)

说明：将字符串text按照指定分隔符转换成Map

```sql
select str_to_map("id:001,name:Leefs,age:20",",");
+-----------------------------------------+
|                   _c0                   |
+-----------------------------------------+
| {"id":"001","name":"Leefs","age":"20"}  |
+-----------------------------------------+
```

### 二、类型转换函数示例

**2.1 转换类型函数**

> 语法：cast(expr as type)

说明：将 expr 转换成 type 类型

```sql
select cast('1.05' as int);
+------+
| _c0  |
+------+
| 1    |
+------+
select cast('1.05' as bigint);
+------+
| _c0  |
+------+
| 1    |
+------+
```

**2.2 二进制转换**

> 语法：binary(string str)

说明：将输入的值转换成二进制

```sql
select binary('8');
+------+
| _c0  |
+------+
| 8    |
+------+
```

### 三、日期函数

**3.1 时间戳转化函数**

> 语法：from_unixtime(bigint unixtime, string format)

说明：将时间戳转换成format格式，时间格式可选，不选则为默认时间格式

```sql
select from_unixtime(1639217570,"yyyy-MM-dd");
+-------------+
|     _c0     |
+-------------+
| 2021-12-11  |
+-------------+
```

**3.2 时间字符串转换成时间戳**

> 语法：unix_timestamp(string date)
>
> 时间格式（yyyy-MM-dd HH:mm:ss）

说明：将时间字符串转换成时间戳

```sql
select unix_timestamp('2021-12-11 10:12:50');
+-------------+
|     _c0     |
+-------------+
| 1639217570  |
+-------------+
-- 返回当前时间的时间戳
select unix_timestamp();
+-------------+
|     _c0     |
+-------------+
| 1639221203  |
+-------------+
```

**3.3 日期时间转日期函数**

> 语法：to_date(string datetime)

说明：返回日期时间字段中的日期部分

```sql
select to_date('2021-12-11 10:12:50');
+-------------+
|     _c0     |
+-------------+
| 2021-12-11  |
+-------------+
```

**3.4 日期比较函数**

> 语法：datediff(string enddate, string startdate)

说明：计算开始时间到结束时间相差的天数

```sql
select datediff("2021-12-11","2021-12-01");
+------+
| _c0  |
+------+
| 10   |
+------+
```

**3.5 日期增加函数**

> 语法：date_add(string startdate, int days)

说明：返回开始日期startdate增加days天后的日期

```sql
select date_add('2021-12-01',10);
+-------------+
|     _c0     |
+-------------+
| 2021-12-11  |
+-------------+
```

**3.6 日期减少函数**

> 语法：date_sub (string startdate, int days)

说明：返回开始日期startdate减少days天后的日期

```sql
select date_sub ('2021-12-11',10);
+-------------+
|     _c0     |
+-------------+
| 2021-12-01  |
+-------------+
```

**3.7 获取当前时间**

> 语法：current_timestamp()/current_date()

说明

current_timestamp():返回当前时间，精确到毫秒

current_date():返回当前时间的日期

```sql
select current_timestamp();
+--------------------------+
|           _c0            |
+--------------------------+
| 2021-12-11 19:48:39.563  |
+--------------------------+
select current_date();
+-------------+
|     _c0     |
+-------------+
| 2021-12-11  |
+-------------+
```

**3.8 时间格式化函数**

> 语法：date_format(date/timestamp/string ts, string fmt)

说明：按指定格式返回时间date

```sql
select date_format('2021-12-11 19:48:39.563', 'yyyy-MM-dd HH:mm:ss');
+----------------------+
|         _c0          |
+----------------------+
| 2021-12-11 19:48:39  |
+----------------------+
```

### 四、集合函数

**4.1 统计元素个数**

> 语法：`size(Map<K,V> map)`、`size(Array<T> array)`

说明：返回 map 中键值对个数、数组的元素数量

```sql
select size((map("leefs",20,"tianfei",22,"wangsan",36)));
+------+
| _c0  |
+------+
| 3    |
+------+
select size(array(1,2,3,4,5));
+------+
| _c0  |
+------+
| 5    |
+------+
```

**4.2 获取map中的key/value**

> 语法：`map_keys/map_values(Map<K,V> map)`

说明：返回 map 中的所有 key/value

```sql
select map_keys((map("aaa",25,"bbb",22,"ccc",36)));
+----------------------+
|         _c0          |
+----------------------+
| ["aaa","bbb","ccc"]  |
+----------------------+
select map_values((map("aaa",25,"bbb",22,"ccc",36)));
+-------------+
|     _c0     |
+-------------+
| [25,22,36]  |
+-------------+
```

**4.3 查询数组中元素**

> 语法：`array_contains(Array<T> array, value)`

说明：判断array中是否包含某个元素

```sql
select array_contains(array(1,2,3,4,5),5);
+-------+
|  _c0  |
+-------+
| true  |
+-------+
```

**4.4 数组排序**

> 语法：sort_array(Array<T> array)

说明：对数组进行正序排序

```sql
select sort_array(array(1,2,5,4,2,3,8,9,6,7,2,1,0));
+------------------------------+
|             _c0              |
+------------------------------+
| [0,1,1,2,2,2,3,4,5,6,7,8,9]  |
+------------------------------+
```

标签: Hadoop, Hive

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/1737.html

上一篇 15.Hive常用内置函数总结

下一篇 17.Hive行转列和列转行

16.Hive常用内置函数示例

评论已关闭

栏目分类

标签云

友情链接申请

16.Hive常用内置函数示例

评论已关闭

 栏目分类

标签云

友情链接申请

栏目分类

标签云

友情链接申请