六、Hive Sql 大全

本节基本涵盖了Hive日常使用的所有SQL,因为SQL太多,所以将SQL进行了如下分类:一、DDL语句(数据定义语句):
对数据库的操作:包含创建、修改数据库
对数据表的操作:分为内部表及外部表,分区表和分桶表
二、DQL语句(数据查询语句):
单表查询、关联查询
hive函数:包含聚合函数,条件函数,日期函数,字符串函数等
行转列及列转行:lateral view 与 explode 以及 reflect
窗口函数与分析函数
其他一些窗口函数

hive的DDL语法对数据库的操作创建数据库:create database if not exists myhive;
说明:hive的表存放位置模式是由hive-site．xml当中的一个属性指定的 :hive．metastore．warehouse．dir
创建数据库并指定hdfs存储位置 :
create database myhive2 location '/myhive2';
修改数据库:alter  database  myhive2  set  dbproperties('createtime'='20210329');

说明:可以使用alter  database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的,包括数据库的名称以及数据库所在的位置

查看数据库详细信息查看数据库基本信息
hive (myhive)> desc  database  myhive2;
查看数据库更多详细信息
hive (myhive)> desc database extended  myhive2;
删除数据库删除一个空数据库,如果数据库下面有数据表,那么就会报错
drop  database  myhive2;
强制删除数据库,包含数据库下面的表一起删除
drop  database  myhive  cascade;
对数据表的操作对管理表(内部表)的操作:建内部表:hive (myhive)> use myhive; -- 使用myhive数据库
hive (myhive)> create table stu(id int,name string);
hive (myhive)> insert into stu values (1,"zhangsan");
hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi");  -- 一次插入多条数据
hive (myhive)> select * from stu;
hive建表时候的字段类型:分类类型描述字面量示例原始类型BOOLEANtrue/falseTRUE
TINYINT1字节的有符号整数 -128～1271Y
SMALLINT2个字节的有符号整数,-32768～327671S
INT4个字节的带符号整数1
BIGINT8字节带符号整数1L
FLOAT4字节单精度浮点数1．0
DOUBLE8字节双精度浮点数1．0
DEICIMAL任意精度的带符号小数1．0
STRING字符串,变长“a”,’b’
VARCHAR变长字符串“a”,’b’
CHAR固定长度字符串“a”,’b’
BINARY字节数组无法表示
TIMESTAMP时间戳,毫秒值精度122327493795
DATE日期‘2016-03-29’
INTERVAL时间频率间隔
复杂类型ARRAY有序的的同类型的集合array(1,2)
MAPkey-value,key必须为原始类型,value可以任意类型map(‘a’,1,’b’,2)
STRUCT字段集合,类型可以不同struct(‘1’,1,1．0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1．0)
UNION在有限取值范围内的一个值create_union(1,’a’,63)

对decimal类型简单解释下:
用法:decimal(11,2) 代表最多有11位数字,其中后2位是小数,整数部分是9位;如果整数部分超过9位,则这个字段就会变成null;如果小数部分不足2位,则后面用0补齐两位,如果小数部分超过两位,则超出部分四舍五入
也可直接写 decimal,后面不指定位数,默认是 decimal(10,0)  整数10位,没有小数

创建表并指定字段之间的分隔符create  table if not exists stu2(id int ,name string) row format delimited fields terminated by ' ' stored as textfile location '/user/stu2';

row format delimited fields terminated by ' '  指定字段分隔符,默认分隔符为 '
'
stored as 指定存储格式
location 指定存储位置

根据查询结果创建表create table stu3 as select * from stu2;
根据已经存在的表结构创建表create table stu4 like stu2;
查询表的结构只查询表内字段及属性
desc stu2;
详细查询
desc formatted  stu2;
查询创建表的语句show create table stu2;
对外部表操作

外部表因为是指定其他的hdfs路径的数据加载到表当中来,所以hive表会认为自己不完全独占这份数据,所以删除hive表的时候,数据仍然存放在hdfs当中,不会删掉,只会删除表的元数据

构建外部表create external table student (s_id string,s_name string) row format delimited fields terminated by ' ';
从本地文件系统向表中加载数据追加操作
load data local inpath '/export/servers/hivedatas/student．csv' into table student;
覆盖操作
load data local inpath '/export/servers/hivedatas/student．csv' overwrite  into table student;
从hdfs文件系统向表中加载数据load data inpath '/hivedatas/techer．csv' into table techer;
加载数据到指定分区
load data inpath '/hivedatas/techer．csv' into table techer partition(cur_date=20201210);
注意:
1．使用 load data local 表示从本地文件系统加载,文件会拷贝到hdfs上
2．使用 load data 表示从hdfs文件系统加载,文件会直接移动到hive相关目录下,注意不是拷贝过去,因为hive认为hdfs文件已经有3副本了,没必要再次拷贝了
3．如果表是分区表,load 时不指定分区会报错
4．如果加载相同文件名的文件,会被自动重命名对分区表的操作创建分区表的语法create table score(s_id string, s_score int) partitioned by (month string);
创建一个表带多个分区create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

注意:
hive表创建的时候可以用 location 指定一个文件或者文件夹,当指定文件夹时,hive会加载文件夹下的所有文件,当表中无分区时,这个文件夹下不能再有文件夹,否则报错
当表是分区表时,比如 partitioned by (day string), 则这个文件夹下的每一个文件夹就是一个分区,且文件夹名为 day=20201123这种格式,然后使用:msck  repair   table  score; 修复表结构,成功之后即可看到数据已经全部加载到表当中去了

加载数据到一个分区的表中load data local inpath '/export/servers/hivedatas/score．csv' into table score partition (month='201806');
加载数据到一个多分区的表中去load data local inpath '/export/servers/hivedatas/score．csv' into table score2 partition(year='2018',month='06',day='01');
查看分区show  partitions  score;
添加一个分区alter table score add partition(month='201805');
同时添加多个分区 alter table score add partition(month='201804') partition(month = '201803');

注意:添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

删除分区 alter table score drop partition(month = '201806');
对分桶表操作

将数据按照指定的字段进行分成多个桶中去,就是按照分桶字段进行哈希划分到多个文件当中去
分区就是分文件夹,分桶就是分文件

分桶优点:
1． 提高join查询效率
2． 提高抽样效率

开启hive的捅表功能set hive．enforce．bucketing=true;
设置reduce的个数set mapreduce．job．reduces=3;
创建桶表create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

桶表的数据加载:由于桶表的数据加载通过hdfs  dfs  -put文件或者通过load  data均不可以,只能通过insert  overwrite 进行加载
所以把文件加载到桶表中,需要先创建普通表,并通过insert  overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

通过insert  overwrite给桶表中加载数据insert overwrite table course select * from course_common cluster by(c_id);  -- 最后指定桶字段
修改表和删除表修改表名称alter  table  old_table_name  rename  to  new_table_name;
增加/修改列信息查询表结构
desc score5;
添加列
alter table score5 add columns (mycol string, mysco string);
更新列
alter table score5 change column mysco mysconew int;
删除表操作drop table score5;
清空表操作truncate table score6;
说明:只能清空管理表,也就是内部表;清空外部表,会产生错误

注意:truncate 和 drop:
如果 hdfs 开启了回收站,drop 删除的表数据是可以从回收站恢复的,表结构恢复不了,需要自己重新创建;truncate 清空的表是不进回收站的,所以无法恢复truncate清空的表
所以 truncate 一定慎用,一旦清空将无力回天

向hive表中加载数据直接向分区表中插入数据insert into table score partition(month ='201807') values ('001','002','100');
通过load方式加载数据 load data local inpath '/export/servers/hivedatas/score．csv' overwrite into table score partition(month='201806');
通过查询方式加载数据insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;
查询语句中创建表并加载数据create table score2 as select * from score1;
在创建表是通过location指定加载数据的路径create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';
export导出与import 导入 hive表数据(内部表操作)create table techer2 like techer; --依据已有表结构创建表
export table techer to  '/export/techer';
import table techer2 from '/export/techer';
hive表中数据导出insert导出将查询的结果导出到本地
insert overwrite local directory '/export/servers/exporthive' select * from score;
将查询的结果格式化导出到本地
insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by ' ' collection items terminated by '#' select * from student;
将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by ' ' collection items terminated by '#' select * from score;
Hadoop命令导出到本地dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local．txt;
hive shell 命令导出基本语法:(hive -f/-e 执行语句或者脚本 > file)
hive -e "select * from myhive．score;" > /export/servers/exporthive/score．txt
hive -f export．sh > /export/servers/exporthive/score．txt
export导出到HDFS上export table score to '/export/exporthive/score';
hive的DQL查询语法单表查询SELECT [ALL | DISTINCT] select_expr, select_expr, ．．．
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
 | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

注意:
1、order by 会对输入做全局排序,因此只有一个reducer,会导致当输入规模较大时,需要较长的计算时间。
2、sort by不是全局排序,其在数据进入reducer前完成排序。因此,如果用sort by进行排序,并且设置mapred．reduce．tasks>1,则sort by只保证每个reducer的输出有序,不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的reducer,且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外,还会对该字段进行排序。
因此,如果分桶和sort字段是同一个时,此时,cluster by = distribute by + sort by

WHERE语句select * from score where s_score < 60;

注意:
小于某个值是不包含null的,如上查询结果是把 s_score 为 null 的行剔除的

GROUP BY 分组select s_id ,avg(s_score) from score group by s_id;
分组后对数据进行筛选,使用having
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

注意:
如果使用 group by 分组,则 select 后面只能写分组的字段或者聚合函数
where和having区别:
1 having是在 group by 分完组之后再对数据进行筛选,所以having 要筛选的字段只能是分组字段或者聚合函数
2 where 是从数据表中的字段直接进行的筛选的,所以不能跟在gruopby后面,也不能使用聚合函数

join 连接INNER JOIN 内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
select * from techer t [inner] join course c on t．t_id = c．t_id; -- inner 可省略
LEFT OUTER JOIN 左外连接:左边所有数据会被返回,右边符合条件的被返回
select * from techer t left join course c on t．t_id = c．t_id; -- outer可省略
RIGHT OUTER JOIN 右外连接:右边所有数据会被返回,左边符合条件的被返回、
select * from techer t right join course c on t．t_id = c．t_id;
FULL OUTER JOIN 满外(全外)连接: 将会返回所有表中符合条件的所有记录。如果任一表的指定字段没有符合条件的值的话,那么就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t．t_id = c．t_id ;

注:1． hive2版本已经支持不等值连接,就是 join on条件后面可以使用大于小于符号了;并且也支持 join on 条件后跟or (早前版本 on 后只支持 = 和 and,不支持 > < 和 or)
2．如hive执行引擎使用MapReduce,一个join就会启动一个job,一条sql语句中如有多个join,则会启动多个job

注意:表之间用逗号(,)连接和 inner join 是一样的
select * from table_a,table_b where table_a．id=table_b．id;
它们的执行效率没有区别,只是书写方式不同,用逗号是sql 89标准,join 是sql 92标准。用逗号连接后面过滤条件用 where ,用 join 连接后面过滤条件是 on。

order by 排序全局排序,只会有一个reduce
ASC(ascend): 升序(默认) DESC(descend): 降序
SELECT * FROM student s LEFT JOIN score sco ON s．s_id = sco．s_id ORDER BY sco．s_score DESC;

注意:order by 是全局排序,所以最后只有一个reduce,也就是在一个节点执行,如果数据量太大,就会耗费较长时间

sort by 局部排序每个MapReduce内部进行排序,对全局结果集来说不是排序。
设置reduce个数
set mapreduce．job．reduces=3;
查看设置reduce个数
set mapreduce．job．reduces;
查询成绩按照成绩降序排列
select * from score sort by s_score;

将查询结果导入到文件中(按照成绩降序排列)
insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;
distribute by  分区排序distribute by:类似MR中partition,进行分区,结合sort by使用
设置reduce的个数,将我们对应的s_id划分到对应的reduce当中去
set mapreduce．job．reduces=7;
通过distribute by  进行数据的分区
select * from score distribute by s_id sort by s_score;

注意:Hive要求 distribute by 语句要写在 sort by 语句之前

cluster by当distribute by和sort by字段相同时,可以使用cluster by方式．
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是正序排序,不能指定排序规则为ASC或者DESC。
以下两种写法等价
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;
Hive函数聚合函数hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函数

注意:
聚合操作时要注意null值
count(*) 包含null值,统计所有行数
count(id) 不包含null值
min 求最小值是不包含null,除非所有值都是null
avg 求平均值也是不包含null

非空集合总体变量函数: var_pop语法: var_pop(col)
返回值: double
说明: 统计结果集中col非空集合的总体变量(忽略null)
非空集合样本变量函数: var_samp语法: var_samp (col)
返回值: double
说明: 统计结果集中col非空集合的样本变量(忽略null)
总体标准偏离函数: stddev_pop语法: stddev_pop(col)
返回值: double
说明: 该函数计算总体标准偏离,并返回总体变量的平方根,其返回值与VAR_POP函数的平方根相同
中位数函数: percentile语法: percentile(BIGINT col, p)
返回值: double
说明: 求准确的第pth个百分位数,p必须介于0和1之间,但是col字段目前只支持整数,不支持浮点数类型
关系运算支持:等值(=)、不等值(!= 或 )、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)
空值判断(is null)、非空判断(is not null)
LIKE比较: LIKE语法: A LIKE B
操作类型: strings
描述: 如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合表达式B 的正则语法,则为TRUE;否则为FALSE。B中字符”_”表示任意单个字符,而字符”%”表示任意数量的字符。
JAVA的LIKE操作: RLIKE语法: A RLIKE B
操作类型: strings
描述: 如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合JAVA正则表达式B的正则语法,则为TRUE;否则为FALSE。
REGEXP操作: REGEXP语法: A REGEXP B
操作类型: strings
描述: 功能与RLIKE相同
示例:select 1 from tableName where 'footbar' REGEXP '^f．*r$';
结果:1
数学运算支持所有数值类型:加(+)、减(-)、乘(*)、除(/)、取余(%)、位与(&)、位或(|)、位异或(^)、位取反(～)
逻辑运算支持:逻辑与(and)、逻辑或(or)、逻辑非(not)
数值运算取整函数: round语法: round(double a)
返回值: BIGINT
说明: 返回double类型的整数值部分 (遵循四舍五入)
示例:select round(3．1415926) from tableName;
结果:3
指定精度取整函数: round语法: round(double a, int d)
返回值: DOUBLE
说明: 返回指定精度d的double类型
hive> select round(3．1415926,4) from tableName;
3．1416
向下取整函数: floor语法: floor(double a)
返回值: BIGINT
说明: 返回等于或者小于该double变量的最大的整数
hive> select floor(3．641) from tableName;
3
向上取整函数: ceil语法: ceil(double a)
返回值: BIGINT
说明: 返回等于或者大于该double变量的最小的整数
hive> select ceil(3．1415926) from tableName;
4
取随机数函数: rand语法: rand(),rand(int seed)
返回值: double
说明: 返回一个0到1范围内的随机数。如果指定种子seed,则会等到一个稳定的随机数序列
hive> select rand() from tableName; -- 每次执行此语句得到的结果都不同
0．5577432776034763
hive> select rand(100) ;  -- 只要指定种子,每次执行此语句得到的结果一样的
0．7220096548596434
自然指数函数: exp语法: exp(double a)
返回值: double
说明: 返回自然对数e的a次方
hive> select exp(2) ;
7．38905609893065
以10为底对数函数: log10语法: log10(double a)
返回值: double
说明: 返回以10为底的a的对数
hive> select log10(100) ;
2．0

此外还有:以2为底对数函数: log2()、对数函数: log()

幂运算函数: pow语法: pow(double a, double p)
返回值: double
说明: 返回a的p次幂
hive> select pow(2,4) ;
16．0
开平方函数: sqrt语法: sqrt(double a)
返回值: double
说明: 返回a的平方根
hive> select sqrt(16) ;
4．0
二进制函数: bin语法: bin(BIGINT a)
返回值: string
说明: 返回a的二进制代码表示
hive> select bin(7) ;
111

十六进制函数: hex()、将十六进制转化为字符串函数: unhex()
进制转换函数: conv(bigint num, int from_base, int to_base) 说明: 将数值num从from_base进制转化到to_base进制

此外还有很多数学函数:绝对值函数: abs()、正取余函数: pmod()、正弦函数: sin()、反正弦函数: asin()、余弦函数: cos()、反余弦函数: acos()、positive函数: positive()、negative函数: negative()

条件函数If函数: if语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull
hive> select if(1=2,100,200) ;
200
hive> select if(1=1,100,200) ;
100
非空查找函数: coalesce语法: coalesce(T v1, T v2, …)
返回值: T
说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL
hive> select coalesce(null,'100','50') ;
100
条件判断函数:case when (两种写法,其一)语法: case when a then b [when c then d]* [else e] end
返回值: T
说明:如果a为TRUE,则返回b;如果c为TRUE,则返回d;否则返回e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary
条件判断函数:case when (两种写法,其二)语法: case a when b then c [when d then e]* [else f] end
返回值: T
说明:如果a等于b,那么返回c;如果a等于d,那么返回e;否则返回f
hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary
日期函数

注:以下SQL语句中的 from tableName 可去掉,不影响查询结果

获取当前UNIX时间戳函数: unix_timestamp语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
hive> select unix_timestamp() from tableName;
1616906976
UNIX时间戳转日期函数: from_unixtime语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式
hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName;
20210328
日期转UNIX时间戳函数: unix_timestamp语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败,则返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15') from tableName;
1615184475
指定格式日期转UNIX时间戳函数: unix_timestamp语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败,则返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;
1615184475
日期时间转日期函数: to_date语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
hive> select to_date('2021-03-28 14:03:01') from tableName;
2021-03-28
日期转年函数: year语法: year(string date)
返回值: int
说明: 返回日期中的年。
hive> select year('2021-03-28 10:03:01') from tableName;
2021
hive> select year('2021-03-28') from tableName;
2021
日期转月函数: month语法: month (string date)
返回值: int
说明: 返回日期中的月份。
hive> select month('2020-12-28 12:03:01') from tableName;
12
hive> select month('2021-03-08') from tableName;
8
日期转天函数: day语法: day (string date)
返回值: int
说明: 返回日期中的天。
hive> select day('2020-12-08 10:03:01') from tableName;
8
hive> select day('2020-12-24') from tableName;
24
日期转小时函数: hour语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
hive> select hour('2020-12-08 10:03:01') from tableName;
10
日期转分钟函数: minute语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
hive> select minute('2020-12-08 10:03:01') from tableName;
3
日期转秒函数: second语法: second (string date)
返回值: int
说明: 返回日期中的秒。
hive> select second('2020-12-08 10:03:01') from tableName;
1
日期转周函数: weekofyear语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
hive> select weekofyear('2020-12-08 10:03:01') from tableName;
49
日期比较函数: datediff语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
hive> select datediff('2020-12-08','2012-05-09') from tableName;
213
日期增加函数: date_add语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
hive> select date_add('2020-12-08',10) from tableName;
2020-12-18
日期减少函数: date_sub语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
hive> select date_sub('2020-12-08',10) from tableName;
2020-11-28
字符串函数
字符串长度函数:length语法: length(string A)
返回值: int
说明:返回字符串A的长度
hive> select length('abcedfg') from tableName;
7
字符串反转函数:reverse语法: reverse(string A)
返回值: string
说明:返回字符串A的反转结果
hive> select reverse('abcedfg') from tableName;
gfdecba
字符串连接函数:concat语法: concat(string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,支持任意个输入字符串
hive> select concat('abc','def’,'gh')from tableName;
abcdefgh
带分隔符字符串连接函数:concat_ws语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符
hive> select concat_ws(',','abc','def','gh')from tableName;
abc,def,gh
字符串截取函数:substr,substring语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明:返回字符串A从start位置到结尾的字符串
hive> select substr('abcde',3) from tableName;
cde
hive> select substring('abcde',3) from tableName;
cde
hive> select substr('abcde',-1) from tableName; (和ORACLE相同)
e
字符串截取函数:substr,substring语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
说明:返回字符串A从start位置开始,长度为len的字符串
hive> select substr('abcde',3,2) from tableName;
cd
hive> select substring('abcde',3,2) from tableName;
cd
hive>select substring('abcde',-2,2) from tableName;
de
字符串转大写函数:upper,ucase语法: upper(string A) ucase(string A)
返回值: string
说明:返回字符串A的大写格式
hive> select upper('abSEd') from tableName;
ABSED
hive> select ucase('abSEd') from tableName;
ABSED
字符串转小写函数:lower,lcase语法: lower(string A) lcase(string A)
返回值: string
说明:返回字符串A的小写格式
hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed
去空格函数:trim语法: trim(string A)
返回值: string
说明:去除字符串两边的空格
hive> select trim(' abc ') from tableName;
abc
左边去空格函数:ltrim语法: ltrim(string A)
返回值: string
说明:去除字符串左边的空格
hive> select ltrim(' abc ') from tableName;
abc
右边去空格函数:rtrim语法: rtrim(string A)
返回值: string
说明:去除字符串右边的空格
hive> select rtrim(' abc ') from tableName;
abc
正则表达式替换函数:regexp_replace语法: regexp_replace(string A, string B, string C)
返回值: string
说明:将字符串A中的符合java正则表达式B的部分替换为C。注意,在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
hive> select regexp_replace('foobar', 'oo|ar', '') from tableName;
fb
正则表达式解析函数:regexp_extract语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明:将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符。
hive> select regexp_extract('foothebar', 'foo(．*?)(bar)', 1) from tableName;
the
hive> select regexp_extract('foothebar', 'foo(．*?)(bar)', 2) from tableName;
bar
hive> select regexp_extract('foothebar', 'foo(．*?)(bar)', 0) from tableName;
foothebar
strong>注意,在有些情况下要使用转义字符,下面的等号要用双竖线转义,这是java正则表达式的规则。
select data_field,
regexp_extract(data_field,'．*?bgStart\=([^&]+)',1) as aaa,
regexp_extract(data_field,'．*?contentLoaded_headStart\=([^&]+)',1) as bbb,
regexp_extract(data_field,'．*?AppLoad2Req\=([^&]+)',1) as ccc
from pt_nginx_loginlog_st
where pt = '2021-03-28' limit 2;
URL解析函数:parse_url语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
说明:返回URL中指定的部分。partToExtract的有效值为:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO．
hive> select parse_url
('https://www．tableName．com/path1/p．php?k1=v1&k2=v2#Ref1', 'HOST')
from tableName;
www．tableName．com
hive> select parse_url
('https://www．tableName．com/path1/p．php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
from tableName;
v1
json解析函数:get_json_object语法: get_json_object(string json_string, string path)
返回值: string
说明:解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。
hive> select  get_json_object('{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19．95,"color":"red"} },"email":"amy@only_for_json_udf_test．net","owner":"amy"}','$．owner') from tableName;
空格字符串函数:space语法: space(int n)
返回值: string
说明:返回长度为n的字符串
hive> select space(10) from tableName;
hive> select length(space(10)) from tableName;
10
重复字符串函数:repeat语法: repeat(string str, int n)
返回值: string
说明:返回重复n次后的str字符串
hive> select repeat('abc',5) from tableName;
abcabcabcabcabc
首字符ascii函数:ascii语法: ascii(string str)
返回值: int
说明:返回字符串str第一个字符的ascii码
hive> select ascii('abcde') from tableName;
97
左补足函数:lpad语法: lpad(string str, int len, string pad)
返回值: string
说明:将str进行用pad进行左补足到len位
hive> select lpad('abc',10,'td') from tableName;
tdtdtdtabc
注意:与GP,ORACLE不同,pad 不能默认
右补足函数:rpad语法: rpad(string str, int len, string pad)
返回值: string
说明:将str进行用pad进行右补足到len位
hive> select rpad('abc',10,'td') from tableName;
abctdtdtdt
分割字符串函数: split语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str,会返回分割后的字符串数组
hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]
集合查找函数: find_in_set语法: find_in_set(string str, string strList)
返回值: int
说明: 返回str在strlist第一次出现的位置,strlist是用逗号分割的字符串。如果没有找该str字符,则返回0
hive> select find_in_set('ab','ef,ab,de') from tableName;
2
hive> select find_in_set('at','ef,ab,de') from tableName;
0
复合类型构建操作Map类型构建: map语法: map (key1, value1, key2, value2, …)
说明:根据输入的key和value对构建map类型
hive> Create table mapTable as select map('100','tom','200','mary') as t from tableName;
hive> describe mapTable;
t       map