数据学习从0到1 Apache Hive基本函数

语兴数据运营
215 阅读9分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第26天 juejin.cn/post/716729…

内部表

内部表（Internal table） 也称为被Hive拥有和管理的托管表（Managed table）。默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。

当你删除表时，会同时删除数据和元数据

create table student(

 num int,

 name string,

 sex string,

 age int,

 dept string)

row format delimited

fields terminated by ',';

可以使用 DESC FORMATTED TABLENAME的格式来获取表的描述信息

外部表

外部表（External table） 中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。

删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。

而且外部表更为方便的是可以搭配location语法指定数据的路径。

create external table student_ext(

 num int,

 name string,

 sex string,

 age int,

 dept string)

row format delimited

fields terminated by ','

location '/stu';

差异

无论内部表还是外部表，Hive都在Hive Metastore中管理表定义及其分区信息。删除内部表会从Metastore中删除表元数据，还会从HDFS中删除其所有数据/文件。

删除外部表，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变

选择

当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。

当文件已经存在或位于远程位置时，请使用外部表，因为即使删除表，文件也会被保留。

官方文档

cwiki.apache.org/confluence/…

函数简介

如同RDBMS中标准SQL语法一样，Hive SQL也内建了不少函数，满足于用户在不同场合下的数据分析需求，提高开发SQL数据分析的效率。

可以使用show functions查看当下版本支持的函数，并且可以通过describe function extended funcname来查看函数的使用方式和方法。

函数分类

Hive的函数很多，除了自己内置所支持的函数之外，还支持用户自己定义开发函数。

针对内置的函数，可以根据函数的应用类型进行归纳分类，比如：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；

针对用户自定义函数，可以根据函数的输入输出行数进行分类，比如：UDF、UDAF、UDTF。

常用内置函数

字符串函数

  1. 字符串长度函数：length

  2. 字符串反转函数：reverse

  3. 字符串连接函数：concat

  4. 带分隔符字符串连接函数：concat_ws

  5. 字符串截取函数：substr,substring

  6. 字符串转大写函数：upper,ucase

  7. 字符串转小写函数：lower,lcase

  8. 去空格函数：trim

  9. 正则表达式替换函数：regexp_replace

  10. 正则表达式解析函数：regexp_extract

  11. URL解析函数：parse_url

  12. json解析函数：get_json_object

  13. 空格字符串函数：space

示例

select length("angelababy");

select reverse("angelababy");

select concat("angela","baby");

select concat_ws('.', 'www', array('hadoop', 'cn'));

-- 字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len]

select substr("angelababy",-2);

-- pos是从1开始的索引，如果为负数则倒着数

select substr("angelababy",2,2);

select upper("angelababy");

select ucase("angelababy");

select lower("ANGELABABY");

select lcase("ANGELABABY");

select trim(" angelababy ");

-- 正则表达式替换函数：regexp_replace(str, regexp, rep)

select regexp_replace('100-200', '(\d+)', 'num');

-- 正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容

select regexp_extract('100-200', '(\d+)-(\d+)', 2);

-- URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数

select parse_url('www.itcast.cn/path/p1.php…', 'HOST');

-- 空格字符串函数：space(n) 返回指定个数空格

select space(4);

​

日期函数

  1. 获取当前日期: current_date

  2. 获取当前时间戳: current_timestamp

  3. UNIX时间戳转日期函数: from_unixtime

  4. 获取当前UNIX时间戳函数: unix_timestamp

  5. 日期转UNIX时间戳函数: unix_timestamp

  6. 指定格式日期转UNIX时间戳函数: unix_timestamp

  7. 抽取日期函数: to_date

  8. 日期转年函数: year

  9. 日期转月函数: month

  10. 日期转天函数: day

  11. 日期转小时函数: hour

  12. 日期转分钟函数: minute

  13. 日期转秒函数: second

  14. 日期转周函数: weekofyear

  15. 日期比较函数: datediff

  16. 日期增加函数: date_add

  17. 日期减少函数: date_sub

示例

--获取当前日期: current_date

select current_date();

​

--获取当前时间戳: current_timestamp

--同一查询中对current_timestamp的所有调用均返回相同的值。

select current_timestamp();

​

--获取当前UNIX时间戳函数: unix_timestamp

select unix_timestamp();

​

--UNIX时间戳转日期函数: from_unixtime

select from_unixtime(1618238391);

select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');

​

--日期转UNIX时间戳函数: unix_timestamp

select unix_timestamp("2011-12-07 13:01:03");

​

--指定格式日期转UNIX时间戳函数: unix_timestamp

select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');

​

--抽取日期函数: to_date

select to_date('2009-07-30 04:17:52');

​

--日期转年函数: year

select year('2009-07-30 04:17:52');

​

--日期转月函数: month

select month('2009-07-30 04:17:52');

​

--日期转天函数: day

select day('2009-07-30 04:17:52');

​

--日期转小时函数: hour

select hour('2009-07-30 04:17:52');

​

--日期转分钟函数: minute

select minute('2009-07-30 04:17:52');

​

--日期转秒函数: second

select second('2009-07-30 04:17:52');

​

--日期转周函数: weekofyear 返回指定日期所示年份第几周

select weekofyear('2009-07-30 04:17:52');

​

--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'

select datediff('2012-12-08','2012-05-09');

​

--日期增加函数: date_add

select date_add('2012-02-28',10);

​

--日期减少函数: date_sub

select date_sub('2012-01-1',10);

数学函数

  1. 取整函数: round

  2. 指定精度取整函数: round

  3. 向下取整函数: floor

  4. 向上取整函数: ceil

  5. 取随机数函数: rand

  6. 二进制函数: bin

  7. 进制转换函数: conv

  8. 绝对值函数: abs

示例

--取整函数: round 返回double类型的整数值部分 （遵循四舍五入）

select round(3.1415926);

​

--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型

select round(3.1415926,4);

​

--向下取整函数: floor

select floor(3.1415926);

select floor(-3.1415926);

​

--向上取整函数: ceil

select ceil(3.1415926);

select ceil(-3.1415926);

​

--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数

select rand();

​

--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列

select rand(2);

​

--二进制函数: bin(BIGINT a)

select bin(18);

​

--进制转换函数: conv(BIGINT num, int from_base, int to_base)

select conv(17,10,16);

​

--绝对值函数: abs

select abs(-3.9);

​

集合函数

  1. 集合元素size函数: size(Map<K.V>) size(Array)

  2. 取map集合keys函数: map_keys(Map<K.V>)

  3. 取map集合values函数: map_values(Map<K.V>)

  4. 判断数组是否包含指定元素: array_contains(Array, value)

  5. 数组排序函数:sort_array(Array)

示例

--集合元素size函数: size(Map<K.V>) size(Array)

select size(array(11,22,33));

select size(map("id",10086,"name","zhangsan","age",18));

​

--取map集合keys函数: map_keys(Map<K.V>)

select map_keys(map("id",10086,"name","zhangsan","age",18));

​

--取map集合values函数: map_values(Map<K.V>)

select map_values(map("id",10086,"name","zhangsan","age",18));

​

--判断数组是否包含指定元素: array_contains(Array, value)

select array_contains(array(11,22,33),11);

select array_contains(array(11,22,33),66);

​

--数组排序函数:sort_array(Array)

select sort_array(array(12,2,32));

​

条件函数

  1. if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

  2. 空判断函数: isnull( a )

  3. 非空判断函数: isnotnull ( a )

  4. 空值转换函数: nvl(T value, T default_value)

  5. 非空查找函数: COALESCE(T v1, T v2, ...)

  6. 条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END

  7. nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个

  8. 断言assert_true: 如果'condition'不为真，则引发异常，否则返回null

示例

--使用之前课程创建好的student表数据

select * from student limit 3;

​

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

select if(1=2,100,200);

select if(sex ='男','M','W') from student limit 3;

​

--空判断函数: isnull( a )

select isnull("allen");

select isnull(null);

​

--非空判断函数: isnotnull ( a )

select isnotnull("allen");

select isnotnull(null);

​

--空值转换函数: nvl(T value, T default_value)

select nvl("allen","itcast");

select nvl(null,"itcast");

​

--非空查找函数: COALESCE(T v1, T v2, ...)

--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select COALESCE(null,11,22,33);

select COALESCE(null,null,null,33);

select COALESCE(null,null,null);

​

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;

select case sex when '男' then 'man' else 'women' end from student limit 3;

​

--nullif( a, b ):

-- 果a = b，则返回NULL；否则返回NULL。否则返回一个

select nullif(11,11);

select nullif(11,12);

​

--assert_true(condition)

--如果'condition'不为真，则引发异常，否则返回null

SELECT assert_true(11 >= 0);

SELECT assert_true(-1 >= 0);

​

类型转换函数

--任意数据类型之间转换:cast

select cast(12.14 as bigint);

select cast(12.14 as string);

数据脱敏函数

mask

  1. mask_first_n(string str[, int n]

  2. mask_last_n(string str[, int n])

  3. mask_show_first_n(string str[, int n])

  4. mask_show_last_n(string str[, int n])

  5. mask_hash(string|char|varchar str)

示例

--mask

--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。

select mask("abc123DEF");

select mask("abc123DEF",'-','.','^'); --自定义替换的字母

​

--mask_first_n(string str[, int n]

--对前n个进行脱敏替换

select mask_first_n("abc123DEF",4);

​

--mask_last_n(string str[, int n])

select mask_last_n("abc123DEF",4);

​

--mask_show_first_n(string str[, int n])

--除了前n个字符，其余进行掩码处理

select mask_show_first_n("abc123DEF",4);

​

--mask_show_last_n(string str[, int n])

select mask_show_last_n("abc123DEF",4);

​

--mask_hash(string|char|varchar str)

--返回字符串的hash编码。

select mask_hash("abc123DEF");

​

其他函数

  1. hive调用java方法: java_method(class, method[, arg1[, arg2..]])

  2. 反射函数: reflect(class, method[, arg1[, arg2..]])

  3. 取哈希值函数:hash

  4. current_user()、logged_in_user()、current_database()、version()

  5. SHA-1加密: sha1(string/binary)

  6. SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)

  7. crc32加密:

  8. MD5加密: md5(string/binary)

示例

--hive调用java方法: java_method(class, method[, arg1[, arg2..]])

select java_method("java.lang.Math","max",11,22);

​

--反射函数: reflect(class, method[, arg1[, arg2..]])

select reflect("java.lang.Math","max",11,22);

​

--取哈希值函数:hash

select hash("allen");

​

--current_user()、logged_in_user()、current_database()、version()

​

--SHA-1加密: sha1(string/binary)

select sha1("allen");

​

--SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)

select sha2("allen",224);

select sha2("allen",512);

​

--crc32加密:

select crc32("allen");

​

--MD5加密: md5(string/binary)

select md5("allen");

​

avatar
文章
阅读
粉丝