每日hive sql 2

最新推荐文章于 2024-05-19 13:01:37 发布
最新推荐文章于 2024-05-19 13:01:37 发布
阅读量72 收藏
点赞数
文章标签： hive sql hadoop Powered by 金山文档
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/peimina/article/details/129327233
版权

根据https://blog.csdn.net/helloHbulie/article/details/115376657继续学hive sql

hive的DQL查询语法

单表查询

  • 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;

  • 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 left 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;

  • 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;

  • 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.reduce=7;
通过distribute by 进行数据的分区
select * from score distribute by s_id sort by s_score;

  • cluster by

当distribute by和sort by 字段相同时，可以使用cluster by方式
cluster by 除了具有distribute by 的功能外还兼具sort by的功能，但是排序只能是正序排序，不能指定排序规则
以下两种写法等价
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

注意：

1.order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间

2.sort by不是全局排序，其在数据进入reduce前完成排序。因此，如果用sort by 进行排序，并且设置mapred.reduce.task>1，则sort by只保证每个reducer的输出有序，不保证全局有序

3.distribute by(字段)根据指定字段将数据分到不同的reducer，且分发算法是hash散列

4.cluster by(字段)除了具有distribute by 的功能外，还会对该字段进行排序。因此如果分桶和sort字段是同一时，此时cluster by = distribute by+sort by

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.*rs';
结果：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类型
示例：select round(3.1415926,4) from tableName;
结果：3.1416

  • 向下取整函数：floor

语法：floor(double a)
返回值：BIGINT
说明：返回等于或小于该double变量的最大整数
示例：select floor(3.641) from tableName;
结果：3

  • 向上取整函数：ceil

语法：ceil(double a)
返回值：BIGINT
说明：返回等于或者大于该double变量的最小整数
示例：select ceil(3.1415926) from tableName;
结果：4

  • 取随机数函数：rand

语法：rand(),rand(int seed)
返回值：double
说明：返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
示例1：select rand() from tableName; --每次指定此语句的结果都不同
示例2：select rand(100); --只要指定种子，每次执行此语句得到的结果都一样

  • 自然指数函数:exp

语法：exp(double a)
返回值：double
说明：返回自然对数e的a次方
示例：select exp(2);
结果：7.38905609893065

  • 以10为底对数函数：log10

语法：log10(double a)
返回值：double
说明：返回以10为底的a的对数
示例：select log10(100);
结果：2.0

  • 幂运算函数：pow

语法：pow(double a,double p)
返回值：double
说明：返回a的p次幂
示例：select pow(2,4)
结果：16.0

  • 开平方函数：sqrt

语法：sqrt(double a)
返回值：double
说明：返回a的平方根
示例：select sqrt(16);
结果：4.0

  • 二进制函数：bin

语法：bin(BIGINT a)
返回值：string
说明：返回a的二进制代码表示
示例：select bin(7);
结果：111

十六进制函数：hex()、将十六进制转化为字符串函数：unhex()

进制转换函数：cov(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
示例：select if(1=2,100,200);
结果：200

  • 非空查找函数：coalesce

语法：coalesce(T v1,T v2,...)
返回值：T
说明：返回参数中的第一个非空值，如果所有值都为null，那么返回null
示例：select coalesce(null,'100','20');
结果：100

  • 条件判断函数：case when（两种写法，其一）

语法：case when a then b [when c then d] *[else e ] end
返回值：T
说明：如果a为TRUE，则返回b，如果c为TRUE则返回d，否则返回e
示例：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
示例：select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
结果：mary

日期函数

  • 获取当前UNIX时间戳函数：unix_timestamp

语法：unix_timestamp()
返回值：bigint
说明：获得当前时区的UNIX时间戳
示例：select unix_timestamp() from tableName;

  • UNIX时间戳转日期函数：from_unixtime

语法：from_unixtime(bigint unixtime,string format)
返回值：string
说明：转化UNIX时间戳到当前时区的时间格式
示例：select from_unixtime(1616906976,'yyyyMMdd') from tableName;
结果：20210328

  • 日期转UNIX时间戳函数：unix_timestamp

语法：unix_timestamp(string date)
返回值：bigint
说明：转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0
示例：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
示例：select unix_timestamp('2021-03-08 14:21:15','yyyy-MM-dd HH:mm:ss') from tableName;
结果：1615184475

  • 日期时间转日期函数：to_date

语法：to_date(string timestamp)
返回值：string
说明：返回日期时间字段中的日期部分
示例：select to_date('2021-03-08 14:21:15') from tableName;
结果：2021-03-08

  • 日期转年函数：year

语法：year(string date)
返回值：int
说明：返回日期中的年

  • 日期转月函数：month

语法：month(string date)
返回值：int
说明：返回日期中的月份

  • 日期转天函数：day

语法：day(string date)
返回值：int
说明：返回日期中的天

  • 日期转小时函数：hour

语法：hour(string date)
返回值：int
说明：返回日期中的小时

  • 日期转分钟函数：minute

语法：minute(string date)
返回值：int
说明：返回日期中的分钟

  • 日期转秒函数：second

语法：second(string date)
返回值：int
说明：返回日期中的秒

  • 日期转周函数：weekofyear

语法：weekofyear(string date)
返回值：int
说明：返回日期在当前的周数

  • 日期比较函数：datediff

语法：datediff(string enddate,string startdate)
返回值：int
说明：返回结束日期减去开始日期的天数

  • 日期增加函数：date_add

语法：date_add(string startdate,int days)
返回值：string
说明：返回开始日期startdate增加days后的日期

  • 日期减少函数：date_sub

语法：date_sub(string startdate, int days)
返回值：string
说明：返回开始日期startdate减少days后的日期
逗×
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
HiveSQL的编译过程
02-26
1、Hive是基于Hadoop的一个数据仓库系统，在...对这一过程的理解不仅帮助我们解决了一些Hive的bug，也有利于我们优化HiveSQL，提升我们对Hive的掌控力，同时有能力去定制一些需要的功能。详细讲解SQL编译为MapReduce之
hivesql查询每日累加数据
weixin_42497376的博客
10-23 452
这将返回一个结果集，其中包含每个日期和该日期之前的所有数据的累加总和。这将返回一个结果集，其中包含最后一个日期的所有数据的累加总和。
每日hive sql
peimina的博客
03-03 51
hive sql
Hive-SQL语法大全
S1406793的博客
01-20 1696
]，表示可选，如上[LOCATION]表示可写、可不写，表示或，如上ASC | DESC，表示二选一…，表示序列，即未完结，如上表示在SELECT后可以跟多个，以逗号隔开()，表示必填，如上(A | B | C)表示此处必填，填入内容在A、B、C中三选一。
Hive sql删除分区
热门推荐
h18208975507的博客
04-21 1万+
-- 删除指定分区 alter table shiye_data_center_new.dwd_mt_buss_event_cl drop if exists partition(filedate="xxx", space="xxx"); -- 删除库 drop database if exists db_name; -- 强制删除库 drop database if exists db_name cascade; -- 删.
SQL、Hive SQL等SQL血缘解析工具
01-14
String hql = "select id,name from (select id from table_1 where id={p0}) t1 inner join (select name --this is name\n from table_2) t2"; // 获取id字段的血缘 LineageNode idNode = Delegate.getDelegate...
hivesql语句练习
12-06
1.上传tar包 ... bin/beeline -u jdbc:hive2://mini1:10000 -n root 或者 bin/beeline ! connect jdbc:hive2://mini1:10000 4.Hive命令 hive -e ‘sql’ bin/hive -e 'select * from t_test'
Hive SQL语法总结
01-20
Hive是一个数据仓库基础的应用工具，在Hadoop中用来处理结构化数据，它架构在Hadoop之上，通过SQL来对数据进行操作，了解SQL的人，学起来毫不费力。 Hive 查询操作过程严格遵守Hadoop MapReduce 的作业执行模型，...
【Hive SQL 每日一题】统计最近7天内连续下单3日的用户量
JIE的博客 --- moon_coder
05-18 580
通过上面的规律可以发现，如果日期是连续的，那么当它们减去一串顺序的数字后，得到的结果是一致的，我们可以利用这一规则，判断日期是否连续。在这个需求中，并不关心用户购买了多少商品，在意的是用户是否进行了下单，同天下单多次的用户也只算作一次。日内满足多次该条件，但是在这个需求中，每个用户只能算一次，所以还需要去重才能得到最终结果。其实很简单，我们可以通过开窗的方式，计算日期是否连续，我这里是通过打标记来实现的。那么现在来说说这个需求是如何实现的，通过如下。现在得到的结果并不是最终的结果，因为某个用户（
计算机毕业设计hadoop+hive+hbase学情分析 在线教育大数据 课程推荐系统 机器学习 深度学习 人工智能 大数据毕业设计 知识图谱
从业计算机毕业设计10年，打算用一年时间分享10年毕设经验！
05-16 765
计算机毕业设计hadoop+hive+hbase学情分析 在线教育大数据 课程推荐系统 机器学习 深度学习 人工智能 大数据毕业设计 知识图谱
Hive的字符串操作
早拾碗吧的博客
05-17 297
【代码】Hive的字符串操作。
Hive的“分区”
早拾碗吧的博客
05-15 470
【代码】Hive的“分区”
Hive--数据库报错
很开心你能来~
05-18 113
Hive--数据库报错
Hive实战（使用30w条聊天数据构建分析报表 ，附加BUG解决）
qq_54828577的博客
05-17 660
hadoop+hive实例 使用jps检查各组件启动情况查看10000端口号是否被监听 下载安装后打开点击编辑驱动设置，删除原有的驱动，换成自己的驱动确认后进行连接测试1.检查metastore和hiveserver2是否打开，查看10000端口号是否被监听。否，重复1.1步骤2.打开windows终端，ping linux ip 地址能否 ping 通。
hive启动--客户端连接报错
很开心你能来~
05-18 239
hive启动--客户端连接报错
父子级分类统计分类下数量sql
最新发布
pscool的博客
05-19 71
【代码】父子级分类统计分类下数量sql。
小白也会SQL：大模型改变交互方式（中）
鲁班模锤
05-17 747
在人工智能与自然语言处理交汇点，有一种技术正悄然改变与数据交互的方式——将日常语言转化为精准SQL查询。这一“text-to-sql”转换任务，使非专业人士也能轻松驾驭复杂的数据库操作，极大地拓宽了数据应用的边界。
idea创建springboot项目报错:Initialization failed for ‘https://start.spring.io‘
爱吃Java的羊的博客
05-18 129
>勾选Automatic proxy configuration URL: 复选框，并在输入框中录入。->勾选Auto-detect proxy settings复选框。->点击Appearance & Behavior菜单。idea创建springboot项目报错。->点击HTTP Proxy菜单。打开Settings界面。->点击OK报错退出。
hive sql boy
03-15
2. 扩展性：Hive SQL可以处理PB级别的数据，并且可以与其他Hadoop生态系统工具（如HBase、Spark等）无缝集成。 3. 数据仓库功能：Hive SQL支持数据的存储、查询、分析和转换，可以满足数据仓库的需求。 4. 用户定义...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值