Hive常用函数

已于 2022-04-09 14:42:47 修改
阅读量443 收藏
点赞数
分类专栏： hive基础 初学大数据 文章标签： hive
于 2022-04-07 11:36:12 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ZZJXP/article/details/124007073
版权

If

hive> select if(1>0,1,0); 
OK
1
Time taken: 0.454 seconds, Fetched: 1 row(s)
hive> select if(1>0,if(-1>0,-1,1),0);
OK
1
Time taken: 0.438 seconds, Fetched: 1 row(s)
hive> select score,if(score>120,'优秀',if(score>100,'良好',if(score>90,'及格','不及格'))) as pingfen from score limit 20;
OK
98	及格
5	不及格
137	优秀
29	不及格
85	不及格
52	不及格
139	优秀
102	良好
44	不及格
18	不及格
46	不及格
91	及格
48	不及格
132	优秀
41	不及格
32	不及格
7	不及格
99	及格
147	优秀
69	不及格
Time taken: 0.029 seconds, Fetched: 20 row(s)

 COALESCE

hive> select COALESCE(null,'1','2');
OK
1
Time taken: 0.442 seconds, Fetched: 1 row(s)
hive> select COALESCE('1',null,'2');
OK
1
Time taken: 0.447 seconds, Fetched: 1 row(s)

hive> create table testCoalesce(
    > col1 string,
    > col2 string,
    > col3 string
    > )
    > row format delimited fields terminated by ',';
OK
Time taken: 0.027 seconds
hive> insert into testCoalesce values(1,2,3),(null,4,5),(null,null,6);
hive> select * from testCoalesce;
OK
1	2	3
NULL	4	5
NULL	NULL	6
Time taken: 0.034 seconds, Fetched: 3 row(s)
hive> select coalesce(col1,col2,col3) from testCoalesce;
OK
1
4
6
Time taken: 0.032 seconds, Fetched: 3 row(s)

case when

hive> select  score
    >         ,case when score>120 then '优秀'
    >               when score>100 then '良好'
    >               when score>90 then '及格'
    >         else '不及格'
    >         end as pingfen
    > from score limit 20;
OK
98	及格
5	不及格
137	优秀
29	不及格
85	不及格
52	不及格
139	优秀
102	良好
44	不及格
18	不及格
46	不及格
91	及格
48	不及格
132	优秀
41	不及格
32	不及格
7	不及格
99	及格
147	优秀
69	不及格
Time taken: 0.048 seconds, Fetched: 20 row(s)
hive> 
    > select  name
    >         ,case name when "施笑槐" then "槐ge"
    >                   when "吕金鹏" then "鹏ge"
    >                   when "单乐蕊" then "蕊jie"
    >         else "算了不叫了"
    >         end as nickname
    > from students limit 10;
OK
施笑槐	槐ge
吕金鹏	鹏ge
单乐蕊	蕊jie
葛德曜	算了不叫了
宣谷芹	算了不叫了
边昂雄	算了不叫了
尚孤风	算了不叫了
符半双	算了不叫了
沈德昌	算了不叫了
羿彦昌	算了不叫了
Time taken: 0.027 seconds, Fetched: 10 row(s)

concat('123','456'); // 123456
concat('123','456',null); // NULL

select concat_ws('#','a','b','c'); // a#b#c
select concat_ws('#','a','b','c',NULL); // a#b#c 可以指定分隔符，并且会自动忽略NULL
select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10;

select substring("abcdefg",1); // abcdefg HQL中涉及到位置的时候 是从1开始计数
// '2021/01/14' -> '2021-01-14'
select concat_ws("-",substring('2021/01/14',1,4),substring('2021/01/14',6,2),substring('2021/01/14',9,2));
// 建议使用日期函数去做日期
select from_unixtime(unix_timestamp('2021/01/14','yyyy/MM/dd'),'yyyy-MM-dd');

select split("abcde,fgh",","); // ["abcde","fgh"]
select split("a,b,c,d,e,f",",")[2]; // c

select explode(split("abcde,fgh",",")); // abcde
                                        //  fgh

// 解析json格式的数据
select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); // 100

hive> select concat_ws('#','a','b','c');
OK
a#b#c
Time taken: 0.428 seconds, Fetched: 1 row(s)
hive> select concat_ws('#','a','b','c',NULL);
OK
a#b#c
Time taken: 0.431 seconds, Fetched: 1 row(s)
hive> select substring('2022-04-06',6,5);
OK
04-06
Time taken: 0.428 seconds, Fetched: 1 row(s)
hive> select split('abc,efg,hijk',',');
OK
["abc","efg","hijk"]
Time taken: 0.438 seconds, Fetched: 1 row(s)
hive> select split('abc,efg,hijk',',')[1];
OK
efg
Time taken: 0.44 seconds, Fetched: 1 row(s)
hive> select explode(split('abc,efg,hijk',','));
OK
abc
efg
hijk
Time taken: 0.029 seconds, Fetched: 3 row(s)
hive> create table wordCount(
    > words string
    > )
    > row format delimited fields terminated by '\t';
OK
Time taken: 0.028 seconds
hive> load data local inpath '/usr/local/soft/data/words.txt' into table wordCount;
Loading data to table test5.wordcount
Table test5.wordcount stats: [numFiles=1, totalSize=435]
OK
Time taken: 0.507 seconds
hive> select * from wordCount;
OK
java,c,c++,sql,scale,python
mysql,springboot,redis
hadoop,hive,hbase,spark,flink
kafka,sqoop,flume,datax,kettle,flink
phoenix
mysql,springboot,redis
hadoop,hive,hbase,spark,flink
kafka,sqoop,flume,datax,kettle,flink
phoenix
mysql,springboot,redis
hadoop,hive,hbase,spark,flink
kafka,sqoop,flume,datax,kettle,flink
phoenix
mysql,springboot,redis
hadoop,hive,hbase,spark,flink
kafka,sqoop,flume,datax,kettle,flink
phoenix
Time taken: 0.031 seconds, Fetched: 17 row(s)
hive> select split(words,',') from wordCount;
OK
["java","c","c++","sql","scale","python"]
["mysql","springboot","redis"]
["hadoop","hive","hbase","spark","flink"]
["kafka","sqoop","flume","datax","kettle","flink"]
["phoenix"]
["mysql","springboot","redis"]
["hadoop","hive","hbase","spark","flink"]
["kafka","sqoop","flume","datax","kettle","flink"]
["phoenix"]
["mysql","springboot","redis"]
["hadoop","hive","hbase","spark","flink"]
["kafka","sqoop","flume","datax","kettle","flink"]
["phoenix"]
["mysql","springboot","redis"]
["hadoop","hive","hbase","spark","flink"]
["kafka","sqoop","flume","datax","kettle","flink"]
["phoenix"]
Time taken: 0.031 seconds, Fetched: 17 row(s)
hive> select explode(split(words,',')) from wordCount;
OK
java
c
c++
sql
scale
python
mysql
springboot
redis
hadoop
hive
hbase
spark
flink
kafka
sqoop
flume
datax
kettle
flink
phoenix
mysql
springboot
redis
hadoop
hive
hbase
spark
flink
kafka
sqoop
flume
datax
kettle
flink
phoenix
mysql
springboot
redis
hadoop
hive
hbase
spark
flink
kafka
sqoop
flume
datax
kettle
flink
phoenix
mysql
springboot
redis
hadoop
hive
hbase
spark
flink
kafka
sqoop
flume
datax
kettle
flink
phoenix
Time taken: 0.022 seconds, Fetched: 66 row(s)
hive> select word
    > ,count(*) as cnt
    > from(
    > select explode(split(words,',')) as word
    > from wordCount
    > )t1 group by word;
hive> select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); 
OK
100
Time taken: 0.458 seconds, Fetched: 1 row(s)
hive> select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0]"); 
OK
{"course_name":"math","score":100}
Time taken: 0.444 seconds, Fetched: 1 row(s)
hive> select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score"); 
OK
[{"course_name":"math","score":100},{"course_name":"english","score":60}]
Time taken: 0.436 seconds, Fetched: 1 row(s)
hive> select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.name"); 
OK
zhangsan
Time taken: 0.428 seconds, Fetched: 1 row(s)

hive> select t1.id,
    > t1.name,
    > t1.age,
    > t1.gender,
    > t1.clazz,
    > t2.max_age
    > from students t1
    > left join(
    > select clazz,
    > max(age)as max_age
    > from students
    > group by clazz
    > )t2 on t1.clazz=t2.clazz
    > order by t1.clazz;
hive> 
    > select id
    > ,name
    > ,age
    > ,gender
    > ,clazz
    > ,max(age) over(partition by clazz order by clazz) as max_age
    > from students;

row_number：无并列排名

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

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

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

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

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

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

NTILE(n)：对分区内数据再分成n组，然后打上组号

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

窗口帧：用于从分区中选择指定的多条记录，供窗口函数处

hive> create table new_score(
    >     id  int
    >     ,score int
    >     ,clazz string
    >     ,department string
    > ) row format delimited fields terminated by ",";
OK
Time taken: 0.031 seconds
hive> load data local inpath '/usr/local/soft/data/new_score.txt' into table new_score;
Loading data to table test5.new_score
Table test5.new_score stats: [numFiles=1, numRows=0, totalSize=520, rawDataSize=0]
OK
Time taken: 0.51 seconds
hive> select * from new_score;
OK
111	69	class1	department1
112	80	class1	department1
113	74	class1	department1
114	94	class1	department1
115	93	class1	department1
121	74	class2	department1
122	86	class2	department1
123	78	class2	department1
124	70	class2	department1
211	93	class1	department2
212	83	class1	department2
213	94	class1	department2
214	94	class1	department2
215	82	class1	department2
216	74	class1	department2
221	99	class2	department2
222	78	class2	department2
223	74	class2	department2
224	80	class2	department2
225	85	class2	department2
Time taken: 0.018 seconds, Fetched: 20 row(s)
hive> SELECT id
    >      ,score
    >      ,clazz
    >      ,SUM(score) OVER w as sum_w
    >      ,round(avg(score) OVER w,3) as avg_w
    >      ,count(score) OVER w as cnt_w
    > FROM new_score
    > WINDOW w AS (PARTITION BY clazz ORDER BY score rows between 2 PRECEDING and 2 FOLLOWING);
Query ID = root_20220407112250_0a09ad5d-6a2c-4c1a-83af-af503ca25b2f
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1649211406017_0026, Tracking URL = http://master:8088/proxy/application_1649211406017_0026/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0026
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2022-04-07 11:22:58,635 Stage-1 map = 0%,  reduce = 0%
2022-04-07 11:23:03,875 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.67 sec
2022-04-07 11:23:10,015 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.27 sec
MapReduce Total cumulative CPU time: 2 seconds 270 msec
Ended Job = job_1649211406017_0026
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.27 sec   HDFS Read: 9806 HDFS Write: 506 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 270 msec
OK
111	69	class1	217	72.333	3
113	74	class1	297	74.25	4
216	74	class1	379	75.8	5
112	80	class1	393	78.6	5
215	82	class1	412	82.4	5
212	83	class1	431	86.2	5
211	93	class1	445	89.0	5
115	93	class1	457	91.4	5
213	94	class1	468	93.6	5
114	94	class1	375	93.75	4
214	94	class1	282	94.0	3
124	70	class2	218	72.667	3
121	74	class2	296	74.0	4
223	74	class2	374	74.8	5
222	78	class2	384	76.8	5
123	78	class2	395	79.0	5
224	80	class2	407	81.4	5
225	85	class2	428	85.6	5
122	86	class2	350	87.5	4
221	99	class2	270	90.0	3
Time taken: 21.628 seconds, Fetched: 20 row(s)
hive> select  id
    >         ,score
    >         ,clazz
    >         ,department
    >         ,row_number() over (partition by clazz order by score desc) as rn_rk
    >         ,dense_rank() over (partition by clazz order by score desc) as dense_rk
    >         ,rank() over (partition by clazz order by score desc) as rk
    >         ,percent_rank() over (partition by clazz order by score desc) as percent_rk
    >         ,round(cume_dist() over (partition by clazz order by score desc),3) as cume_rk
    >         ,NTILE(3) over (partition by clazz order by score desc) as ntile_num
    >         ,max(score) over (partition by clazz order by score desc range between 3 PRECEDING and 11 FOLLOWING) as max_p
    > from new_score;
Query ID = root_20220407112959_8db64286-c278-4a8b-b557-c54fb17ab190
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1649211406017_0027, Tracking URL = http://master:8088/proxy/application_1649211406017_0027/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0027
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2022-04-07 11:30:06,604 Stage-1 map = 0%,  reduce = 0%
2022-04-07 11:30:11,809 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.6 sec
2022-04-07 11:30:18,949 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.91 sec
MapReduce Total cumulative CPU time: 1 seconds 910 msec
Ended Job = job_1649211406017_0027
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 1.91 sec   HDFS Read: 13656 HDFS Write: 946 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 910 msec
OK
114	94	class1	department1	1	1	1      0.0	0.273	1	94
214	94	class1	department2	2	1	1      0.0	0.273	1	94
213	94	class1	department2	3	1	1      0.0	0.273	1	94
211	93	class1	department2	4	2	4      0.3	0.455	1	94
115	93	class1	department1	5	2	4      0.3	0.455	2	94
212	83	class1	department2	6	3	6      0.5	0.545	2	83
215	82	class1	department2	7	4	7      0.6	0.636	2	83
112	80	class1	department1	8	5	8      0.7	0.727	2	83
113	74	class1	department1	9	6	9      0.8	0.909	3	74
216	74	class1	department2	10	6	9      0.8	0.909	3	74
111	69	class1	department1	11	7	11     1.0	1.0	3	69
221	99	class2	department2	1	1	1      0.0	0.111	1	99
122	86	class2	department1	2	2	2      0.125	0.222	1	86
225	85	class2	department2	3	3	3      0.25	0.333	1	86
224	80	class2	department2	4	4	4      0.375	0.444	2	80
123	78	class2	department1	5	5	5      0.5	0.667	2	80
222	78	class2	department2	6	5	5      0.5	0.667	2	80
121	74	class2	department1	7	6	7      0.75	0.889	3	74
223	74	class2	department2	8	6	7      0.75	0.889	3	74
124	70	class2	department1	9	7	9      1.0	1.0	3	70
Time taken: 20.502 seconds, Fetched: 20 row(s)
hive> 
    > select  id
    >         ,score
    >         ,clazz
    >         ,department
    >         ,lag(id,2) over (partition by clazz order by score desc) as lag_num
    >         ,LEAD(id,2) over (partition by clazz order by score desc) as lead_num
    >         ,FIRST_VALUE(id) over (partition by clazz order by score desc) as first_v_num
    >         ,LAST_VALUE(id) over (partition by clazz order by score desc) as last_v_num
    >         ,NTILE(3) over (partition by clazz order by score desc) as ntile_num
    > from new_score;
Query ID = root_20220407113407_9459dbac-54ca-4180-a706-8162025832dd
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1649211406017_0028, Tracking URL = http://master:8088/proxy/application_1649211406017_0028/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0028
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2022-04-07 11:34:15,939 Stage-1 map = 0%,  reduce = 0%
2022-04-07 11:34:21,172 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.59 sec
2022-04-07 11:34:27,428 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.66 sec
MapReduce Total cumulative CPU time: 1 seconds 660 msec
Ended Job = job_1649211406017_0028
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 1.66 sec   HDFS Read: 12286 HDFS Write: 872 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 660 msec
OK
114	94	class1	department1	NULL	213	114    213	1
214	94	class1	department2	NULL	211	114    213	1
213	94	class1	department2	114	115	114    213	1
211	93	class1	department2	214	212	114    115	1
115	93	class1	department1	213	215	114    115	2
212	83	class1	department2	211	112	114    212	2
215	82	class1	department2	115	113	114    215	2
112	80	class1	department1	212	216	114    112	2
113	74	class1	department1	215	111	114    216	3
216	74	class1	department2	112	NULL	114    216	3
111	69	class1	department1	113	NULL	114    111	3
221	99	class2	department2	NULL	225	221    221	1
122	86	class2	department1	NULL	224	221    122	1
225	85	class2	department2	221	123	221    225	1
224	80	class2	department2	122	222	221    224	2
123	78	class2	department1	225	121	221    222	2
222	78	class2	department2	224	223	221    222	2
121	74	class2	department1	123	124	221    223	3
223	74	class2	department2	222	NULL	221    223	3
124	70	class2	department1	121	NULL	221    124	3
Time taken: 20.61 seconds, Fetched: 20 row(s)

小胖超凶哦！
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive案例之成绩统计
cold_wolfie的专栏
05-11 2414
Hive案例之成绩统计 需求 ​ 创建classrecord.txt文件。其中数据如下： 序号 班级 总分 1 1603A 95 2 1603B 85 3 1603C 75 4 1603D 96 5 1604F 94 6 1604E 95 7 1604K 91 8 1604G 89 9 1501A 79 10 1502A 69 11 1503A 59 12 1504A 89 13 1701A 99 14 1702A 100 15 1703A 65 创建Hive分区表表名
大数据教程（12.4）hive实战--级联求和
weixin_34342207的博客
02-11 144
2019独角兽企业重金招聘Python工程师标准>>> ...
concat拼接null值问题
weixin_42143904的博客
10-29 1673
说明 当MySQL使用concat拼接多个字符串时，SELECT CONCAT('123','456',NULL,'789')结果为null。 解决 使用IFNULL()判空 Oracle用NVL()
In order to set a constant number of reducers: set mapreduce.job.reduces=＜number＞卡在这里不动
Knight
09-26 2499
Query ID = root_20200922113537_352b88b7-f5e8-4865-8c1b-68a5182230ab Total jobs = 1 Launching Job 1 out of 1 Number of reduce tasks not specified. Estimated from input data size: 1 In order to change the average load for a reducer (in bytes): set hive.exe
MySQL的函数以及相关案例与练习
A496608119的博客
02-10 1186
1.MySQL的主要函数 1.1 聚合函数 group_concat()函数的使用 CREATE DATABASE bianbian4; USE bianbian4; CREATE TABLE emp ( emp_id INT PRIMARY KEY auto_increment COMMENT '编号', emp_name VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '姓名', salary DECIMAL ( 10, 2 ) NOT NULL DEFA
hive常用函数
04-17
hive常用函数，包括时间、类型、udf、udaf等等的归纳。
Hive常用函数大全.pdf
04-14
hive常用函数大全，日期类别，函数类别等常用函数都可有；
hive常用函数参数手册
10-21
hive常用的函数参考手册，仅供参考，可能版本不一样，函数方法不一致,请以进官方文档为准，https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
oracle 中rank(),Oracle中的rank(),dense_rank() 用法
weixin_33332712的博客
04-03 1941
rank()对表中的数据进行分级排序.譬如有张学生成绩统计单的表STUDENT_RESULTname number kemu fenshuli 0113101 高数 90zhang 0113098 高数 80wang 0113077 高数 70li 0113101 物理 80zhang 0113098 物理 90wang 0113077 物理 70我如果要检索出表中高数的前两名和物理的前两名那么可...
【Hadoop】集群运行卡在Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop job -kill job_1582192539192_0001
HR的博客
01-18 2712
当hive运行卡主不动时，报错如下 hive (default)> select count(*) cnt from emp; Query ID = root_20200220175612_bb456a03-2298-4d20-82b9-c0a96ae859a0 Total jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the aver
主流ETL工具汇总
weixin_40479337的博客
04-01 2929
(一) kettle说明：是国外开源ETL工具，支持数据库、FTP、文件、rest接口、hdfs、hive等平台的灵敏据进行抽取、转换、传输等操作，Java编写跨平台，C/S架构，不支持浏览器模式。特点：易用性：有可视化设计器进行可视化操作，使用简单。功能强大：不仅能进行数据传输，能同时进行数据清洗转换等操作。支持多种源：支持各种数据库、FTP、文件、rest接口、hdfs、Hive等...
Hive SQL数据查询进阶
qq_35447918的博客
09-24 2810
教材第五章 Hive SQL数据查询进阶实验 一 Hive 内置函数 1 查看Hive提供的内置函数 hive> show functions; 2 数学函数 加减乘除 select 10/2; OK 5.0 hive> select 10/2; OK 5.0 round()函数：四舍五入 hive> select round(88.947,2), round(77.912,1), round(55.667, 2); OK 88.95 77.9 55.67 ce...
分区，分桶，JDBC
ZZJXP的博客
04-06 1077
分区 hive> create database test3; OK Time taken: 0.051 seconds hive> use test3; OK Time taken: 0.011 seconds hive> create external table students_pt1 > ( > id bigint, > name string, > age int, > .
Hive SQL执行mapreduce任务卡在Kill Command
zhuzuwei的博客
10-11 2167
执行 select sid,count(*) as cn from student group by sid; 任务一直卡住如下： 2020-10-11T14:00:31,407 INFO [main] exec.Task: Kill Command = /usr/local/hadoop-2.7.6/bin/hadoop job -kill job_1602395744321_0002 我的集群部署出错的地方在于hadoop的slaves文件中只配置了2个datanode，而...
Hive常用内置函数合集
zjy123078_zjy的博客
07-14 826
Hive常用函数合集
新书速览|深入理解Hive:从基础到高阶:视频教学版
最新发布
quanzhankaifaqua的博客
07-16 748
作为ChatGPT大模型技术的实践者和研究者，他在全网上撰写了多篇高质量的ChatGPT和Hive数据仓库相关技术文章，并出版了《Kafka并不难学》（电子工业出版社，2018年11月出版）和《Hadoop大数据挖掘从入门到进阶实战（视频教学版）》（机械工业出版社，2018年6月）两本书。在最后一章对Hive进行了拓展，深入探讨AI大模型在数据分析领域的应用，并介绍其与Hive的深度整合，解释如何利用AI大模型来加速Hive中的数据挖掘过程，使数据分析更为便捷、高效。，全面地介绍Hive的使用方法。
数仓工具—Hive语法之宏(Macro)
07-15 4212
Hive中的宏是一组SQL语句，它们被存储并通过调用宏函数名称来执行。宏在当前会话期间存在。宏是临时的，如果你想拥有永久性的宏，最好创建用户定义的函数并在Hive中注册。在关系数据库管理系统（RDBMS）中，宏存储在数据字典中。Hive宏与关系型数据库中的宏略有不同。在我的其他文章中，我们讨论了如何使用用户定义的函数检查一个字符串是否为数字。然而，你也可以通过创建Hive宏来实现相同的功能。请注意，错误是因为参数’inputString’中的大写’S’以及表达式中的大写。，如何使用它们，以及一些宏的示例。
Hive的基本操作（查询）
qq_73339471的博客
07-15 511
查询字段的【数量】与【类型】必须相同，字段名是以【第一张表为准】。union all：合并后保留重复项 ✔。union：合并后删除重复项（去重）

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值