Hive 学习（五） Hive之HSQL基础

最新推荐文章于 2024-05-05 09:41:45 发布
最新推荐文章于 2024-05-05 09:41:45 发布
阅读量312 收藏 2
点赞数
文章标签： 大数据
原文链接：http://www.cnblogs.com/tashanzhishi/p/10901234.html
版权

一，前言

二，连接查询

　　2.1 内连接（join | inner join）

　　2.2 左外连接（left join | left outer join）

　　2.3 右外连接（right join | right outer join）

　　2.4 全外连接（full join | full outer join）

　　2.5 左半连接（left semi join）

三，分组聚合

　　3.1 分组聚合须知

　　3.2 分组聚合练习1

　　3.3 分组聚合练习2

四，条件判断

　　4.1 case...when...

　　4.2 if判断

 

 

 

 

 

 

正文

一，前言

　　和其他SQL一样，HSQL与其他的SQL差别不大，在这就进行一些简单的介绍。

二，连接查询

　　数据准备和表创建如下：

create table t_a(name string,numb int)
row format delimited
fields terminated by ',';

create table t_b(name string,nick string)
row format delimited
fields terminated by ',';

load data local inpath '/root/hivetest/a.txt' into table t_a;
load data local inpath '/root/hivetest/b.txt' into table t_b;

0: jdbc:hive2://localhost:10000> select * from t_a;
+-----------+-----------+--+
| t_a.name  | t_a.numb  |
+-----------+-----------+--+
| a         | 4         |
| b         | 5         |
| c         | 6         |
| d         | 8         |
+-----------+-----------+--+
4 rows selected (0.197 seconds)
0: jdbc:hive2://localhost:10000> select * from t_b;
+-----------+-----------+--+
| t_b.name  | t_b.nick  |
+-----------+-----------+--+
| a         | laowang   |
| b         | qiangzi   |
| d         | paoge     |
| g         | haizi     |
+-----------+-----------+--+
4 rows selected (0.135 seconds)

　　2.1 内连接（join | inner join）

-- 各类join
--1/ 内连接
-- 笛卡尔积
select a.*,b.*
from t_a a inner join t_b b;  // inner join == join

+-----------+-----------+-----------+-----------+--+
| t_a.name  | t_a.numb  | t_b.name  | t_b.nick  |
+-----------+-----------+-----------+-----------+--+
| a         | 4         | a         | laowang   |
| b         | 5         | a         | laowang   |
| c         | 6         | a         | laowang   |
| d         | 8         | a         | laowang   |
| a         | 4         | b         | qiangzi   |
| b         | 5         | b         | qiangzi   |
| c         | 6         | b         | qiangzi   |
| d         | 8         | b         | qiangzi   |
| a         | 4         | d         | paoge     |
| b         | 5         | d         | paoge     |
| c         | 6         | d         | paoge     |
| d         | 8         | d         | paoge     |
| a         | 4         | g         | haizi     |
| b         | 5         | g         | haizi     |
| c         | 6         | g         | haizi     |
| d         | 8         | g         | haizi     |
+-----------+-----------+-----------+-----------+--+
16 rows selected (14.104 seconds)


-- 指定join条件
select a.*,b.*
from 
t_a a join t_b b on a.name=b.name;

+---------+---------+---------+----------+--+
| a.name  | a.numb  | b.name  |  b.nick  |
+---------+---------+---------+----------+--+
| a       | 4       | a       | laowang  |
| b       | 5       | b       | qiangzi  |
| d       | 8       | d       | paoge    |
+---------+---------+---------+----------+--+
3 rows selected (13.429 seconds)

　　2.2 左外连接（left join | left outer join）

-- 2/ 左外连接（左连接）
select a.*,b.*
from 
t_a a left outer join t_b b on a.name=b.name;

+---------+---------+---------+----------+--+
| a.name  | a.numb  | b.name  |  b.nick  |
+---------+---------+---------+----------+--+
| a       | 4       | a       | laowang  |
| b       | 5       | b       | qiangzi  |
| c       | 6       | NULL    | NULL     |
| d       | 8       | d       | paoge    |
+---------+---------+---------+----------+--+
4 rows selected (9.25 seconds)

　　2.3 右外连接（right join | right outer join）

-- 3/ 右外连接（右连接）
select a.*,b.*
from 
t_a a right outer join t_b b on a.name=b.name;
+---------+---------+---------+----------+--+
| a.name  | a.numb  | b.name  |  b.nick  |
+---------+---------+---------+----------+--+
| a       | 4       | a       | laowang  |
| b       | 5       | b       | qiangzi  |
| d       | 8       | d       | paoge    |
| NULL    | NULL    | g       | haizi    |
+---------+---------+---------+----------+--+
4 rows selected (8.914 seconds)

　　2.4 全外连接（full join | full outer join）

-- 4/ 全外连接
select a.*,b.*
from
t_a a full outer join t_b b on a.name=b.name;

+---------+---------+---------+----------+--+
| a.name  | a.numb  | b.name  |  b.nick  |
+---------+---------+---------+----------+--+
| a       | 4       | a       | laowang  |
| b       | 5       | b       | qiangzi  |
| c       | 6       | NULL    | NULL     |
| d       | 8       | d       | paoge    |
| NULL    | NULL    | g       | haizi    |
+---------+---------+---------+----------+--+
5 rows selected (1.74 seconds)

　　2.5 左半连接（left semi join）

　　注意：left semi是HSQL特有，且没有右半连接

-- 5/ 左半连接 :注意没有右半连接
select a.*
from 
t_a a left semi join t_b b on a.name=b.name;

+---------+---------+--+
| a.name  | a.numb  |
+---------+---------+--+
| a       | 4       |
| b       | 5       |
| d       | 8       |
+---------+---------+--+
3 rows selected (12.236 seconds)

三，分组聚合

　　数据下载：access.log

　　表创建和数据插入：

create table t_access(ip string,url string,access_time string)
partitioned by (dt string)
row format delimited fields terminated by ',';


-- 导入数据
load data local inpath '/root/hivetest/access.log.0804' into table t_access partition(dt='2017-08-04');
load data local inpath '/root/hivetest/access.log.0805' into table t_access partition(dt='2017-08-05');
load data local inpath '/root/hivetest/access.log.0806' into table t_access partition(dt='2017-08-06');

-- 查看表的分区
show partitions t_access;

　　3.1 分组聚合须知

 　　一旦有group by子句，那么，在select子句中就不能有 （分组字段，聚合函数） 以外的字段

　　3.2 分组聚合练习1

-- 针对每一行进行运算
select ip,upper(url),access_time  -- 该表达式是对数据中的每一行进行逐行运算
from t_access;



-- 求每条URL的访问总次数

select url,count(1) as cnts   -- 该表达式是对分好组的数据进行逐组运算
from t_access 
group by url;

+---------------------------------+------+--+
|               url               | _c1  |
+---------------------------------+------+--+
| http://www.edu360.cn/excersize  | 3    |
| http://www.edu360.cn/job        | 7    |
| http://www.edu360.cn/pay        | 1    |
| http://www.edu360.cn/register   | 2    |
| http://www.edu360.cn/stu        | 4    |
| http://www.edu360.cn/teach      | 2    |
+---------------------------------+------+--+
6 rows selected (1.683 seconds)


-- 求每个URL的访问者中ip地址最大的

select url,max(ip)
from t_pv_log
group by url;


+---------------------------------+----------------+--+
|               url               |      _c1       |
+---------------------------------+----------------+--+
| http://www.edu360.cn/excersize  | 192.168.33.46  |
| http://www.edu360.cn/job        | 192.168.33.55  |
| http://www.edu360.cn/pay        | 192.168.34.44  |
| http://www.edu360.cn/register   | 192.168.133.3  |
| http://www.edu360.cn/stu        | 192.168.33.44  |
| http://www.edu360.cn/teach      | 192.168.44.3   |
+---------------------------------+----------------+--+
6 rows selected (1.595 seconds)


-- 求每个用户访问同一个页面的所有记录中，时间最晚的一条

select ip,url,max(access_time) 
from  t_pv_log
group by ip,url;

+----------------+---------------------------------+----------------------+--+
|       it       |               url               |         _c2          |
+----------------+---------------------------------+----------------------+--+
| 192.168.111.3  | http://www.edu360.cn/register   | 2017-08-06 15:35:20  |
| 192.168.133.3  | http://www.edu360.cn/register   | 2017-08-06 15:30:20  |
| 192.168.33.25  | http://www.edu360.cn/job        | 2017-08-06 15:40:20  |
| 192.168.33.3   | http://www.edu360.cn/stu        | 2017-08-05 15:30:20  |
| 192.168.33.3   | http://www.edu360.cn/teach      | 2017-08-04 15:35:20  |
| 192.168.33.36  | http://www.edu360.cn/excersize  | 2017-08-06 16:30:20  |
| 192.168.33.4   | http://www.edu360.cn/job        | 2017-08-04 16:30:20  |
| 192.168.33.4   | http://www.edu360.cn/stu        | 2017-08-04 15:30:20  |
| 192.168.33.44  | http://www.edu360.cn/stu        | 2017-08-05 15:30:20  |
| 192.168.33.46  | http://www.edu360.cn/excersize  | 2017-08-06 16:30:20  |
| 192.168.33.46  | http://www.edu360.cn/job        | 2017-08-05 16:30:20  |
| 192.168.33.5   | http://www.edu360.cn/job        | 2017-08-04 15:40:20  |
| 192.168.33.55  | http://www.edu360.cn/job        | 2017-08-06 15:40:20  |
| 192.168.34.44  | http://www.edu360.cn/pay        | 2017-08-06 15:30:20  |
| 192.168.44.3   | http://www.edu360.cn/teach      | 2017-08-05 15:35:20  |
+----------------+---------------------------------+----------------------+--+
15 rows selected (1.597 seconds)

　　3.3 分组聚合练习二

-- 求8月4号以后，每天http://www.edu360.cn/job的总访问次数，及访问者中ip地址中最大的


select dt,'http://www.edu360.cn/job',count(1),max(it)
from t_access
where url='http://www.edu360.cn/job'
group by dt having dt>'2017-08-04';


select dt,max(url),count(1),max(ip)
from t_access
where url='http://www.edu360.cn/job'
group by dt having dt>'2017-08-04';


select dt,url,count(1),max(ip)
from t_access
where url='http://www.edu360.cn/job'
group by dt,url having dt>'2017-08-04';



select dt,url,count(1),max(ip)
from t_access
where url='http://www.edu360.cn/job' and dt>'2017-08-04'
group by dt,url;

+-------------+---------------------------+------+----------------+--+
|     dt      |            _c1            | _c2  |      _c3       |
+-------------+---------------------------+------+----------------+--+
| 2017-08-05  | http://www.edu360.cn/job  | 2    | 192.168.33.55  |
| 2017-08-06  | http://www.edu360.cn/job  | 3    | 192.168.33.55  |
+-------------+---------------------------+------+----------------+--+
2 rows selected (1.609 seconds)

-- 求8月4号以后，每天每个页面的总访问次数，及访问者中ip地址中最大的，且，只查询出总访问次数>2 的记录
-- 方式1：
select dt,url,count(1) as cnts,max(ip)
from t_access
where dt>'2017-08-04'
group by dt,url having cnts>2;


-- 方式2：用子查询
select dt,url,cnts,max_ip
from
(select dt,url,count(1) as cnts,max(ip) as max_ip
from t_access
where dt>'2017-08-04'
group by dt,url) tmp
where cnts>2;


+----------------+---------------------------------+-----------------------+--------------+--+
|  t_access.ip   |          t_access.url           | t_access.access_time  | t_access.dt  |
+----------------+---------------------------------+-----------------------+--------------+--+

| 192.168.33.46  | http://www.edu360.cn/job        | 2017-08-05 16:30:20   | 2017-08-05   |
| 192.168.33.55  | http://www.edu360.cn/job        | 2017-08-05 15:40:20   | 2017-08-05   |
| 192.168.33.55  | http://www.edu360.cn/job        | 2017-08-06 15:40:20   | 2017-08-06   |
| 192.168.33.25  | http://www.edu360.cn/job        | 2017-08-06 15:40:20   | 2017-08-06   |
| 192.168.33.55  | http://www.edu360.cn/job        | 2017-08-06 15:40:20   | 2017-08-06   |
+----------------+---------------------------------+-----------------------+--------------+

四，条件判断

　　数据下载：点击下载

　　4.1 case...when...

　　语法结构：

case  // 开始提示符
    when 条件1 then 符合条件1的结果
    when 条件2 then 符合条件2的结果
    else 上述都不符合的结果
end   //结束提示符

　　表创建和数据导入：

create table t_user(id int,name string,info struct<age:int,sex:string,addr:string>)
row format delimited fields terminated by ','
collection items terminated by ':';

-- 导入数据
load data local inpath '/root/hivetest/user.dat' into table t_user;

　　数据查询：

select * from t_user;
+------------+--------------+----------------------------------------+--+
| t_user.id  | t_user.name  |              t_user.info               |
+------------+--------------+----------------------------------------+--+
| 1          | zhangsan     | {"age":18,"sex":"male","addr":"深圳"}    |
| 2          | lisi         | {"age":28,"sex":"female","addr":"北京"}  |
| 3          | wangwu       | {"age":38,"sex":"male","addr":"广州"}    |
| 4          | 赵六           | {"age":26,"sex":"female","addr":"上海"}  |
| 5          | 钱琪           | {"age":35,"sex":"male","addr":"杭州"}    |
| 6          | 王八           | {"age":48,"sex":"female","addr":"南京"}  |
+------------+--------------+----------------------------------------+--+

　　需求：查询出用户的id、name、年龄（如果年龄在30岁以下，显示年轻人，30-40之间，显示中年人，40以上老年人）

　　需求实现：

select id,name,
case
　　when info.age<30 then '青年'
　　when info.age>=30 and info.age<40 then '中年'
　　else '老年'
end
from t_user;

　　4.2 if判断

　　语法结构：

if(条件，条件成立结果，条件不成立结果)

　　表创建和数据导入：

-- 建表映射：
create table t_movie(movie_name string,actors array<string>,first_show date)
row format delimited fields terminated by ','
collection items terminated by ':';

-- 导入数据
load data local inpath '/root/hivetest/actor.dat' into table t_movie;

　　数据查询：

select * from t_movie;

+---------------------+------------------------+---------------------+--+
| t_movie.movie_name  |     t_movie.actors     | t_movie.first_show  |
+---------------------+------------------------+---------------------+--+
| 战狼2                 | ["吴京","吴刚","龙母"]       | 2017-08-16          |
| 三生三世十里桃花            | ["刘亦菲","痒痒"]           | 2017-08-20          |
| 普罗米修斯               | ["苍老师","小泽老师","波多老师"]  | 2017-09-17          |
| 美女与野兽               | ["吴刚","加藤鹰"]           | 2017-09-17          |
+---------------------+------------------------+---------------------+--+

　　需求：查询电影信息，并且如果主演中有吴刚的，显示好电影，否则烂片

　　需求实现：

select movie_name,actors,first_show,
if(array_contains(actors,'吴刚'),'好片儿','烂片儿')
from t_movie;

 

转载于:https://www.cnblogs.com/tashanzhishi/p/10901234.html

weixin_30567471
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
hive的基础操作，用于学习
03-01
hive基础DML操作，用于学习
Spring-jt-Day03-MybatisPlus
qq_15740267的博客
04-29 655
版权声明：本文为闪耀太阳原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 原文链接：https://blog.csdn.net/qq_16804847/article/details/116259283 1.MybatisPlus 1.1 MP介绍 MyBatis-Plus (opens new window)（简称 MP）是一个 MyBatis (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。 1.
三、jt项目笔记
weixin_52375283的博客
09-13 1553
一 1.完成项目安装(一周完成) 1.1 VUE脚手架安装 B站名称: 不二子阳 VUE后台脚手架安装过程 1.2 VMware安装说明 VMware配置说明 2 IDEA环境配置 2.1 准备项目目录 程序员操守: 1.路径中不要出现中文/空格/特殊字符. 2.中文对C语言的程序有致命的影响. 目录: IDEA软件打开 2.2 IDEA环境配置 具体配置查看PPT文档 2.3 关于Maven环境说明 组件: 1. 远程仓库 官方维护了几乎所有的项目的jar包.(版权问.
Spring数据库访问（HSQL）（二）
nanlei1987的专栏
01-11 122
[url=http://sarin.iteye.com/blog/866578]上一篇[/url]我们介绍了HSQL数据库的基本概念和操作，我们现在从HSQL的服务器模式开始，使用Spring来操作JDBC。HSQL内嵌了服务器模式的启动方式，不过需要使用java命令来运行，可以参考官方文档。为了演示的方便，我们使用程序启动服务器，做法也很简单。如下： [code="java"]pack...
Hive SQL 语法大全 整理（建议收藏）
qq_654603797
12-02 4702
链接https://mp.weixin.qq.com/s/dYL_JqbC7DXgP0lc9pSQiA 一、DDL 操作 注：SCHEMA/DATABASE 是相同的概念，只是叫法不同而已 --创建数据库/SCHEMA，表，视图，函数，索引CREATEDATABASE/SCHEMA,TABLE,VIEW,FUNCTION,INDEX --删除数据库/SCHEMA，表，视图，索引DROPDATABASE/SCHEMA,TABLE,VIEW,INDEX --清空表TRUNCATE...
Hive中数据库和表的操作(HSQL)
weixin_37536020的博客
02-25 1396
数仓管理工具Hive可以将HDFS文件中的结构化数据映射成表，利用HSQL对表进行分析，HSQL的底层运行机制，默认是MapReduce计算，也可以替换成Spark、Tez、Flink计算结果存储在HDFS，像Hive中的库、表、字段、表所属库、表的数据所在目录、分区等信息称为元数据，元数据默认存储在自带的derBy数据库，也可以切换MySQL、Postgres、Oracle、MS SQL Server存储元数据，对应的库为hive数据库实例，对应的元数据表为hive数据库实例下的表。
Hive SQL常用函数
m0_58725148的博客
10-29 3391
Hive SQL常用函数
HSQL之基础函数
qq_43149023的博客
03-23 2400
基础函数将从数字函数,日期函数,条件函数,字符串函数这几个方面进行总结 1.数字函数: round(double d, int n):d是要进行四舍五入的数值,n是保留多少位小数. floor(double d): 返回小于d的最大整值 ceil(double d): 返回大于d的最小整值 rand(int seed): 返回随机数,seed是随机因子,比如rand(100),他的随机数是一定...
HIVE的基本使用05(HSQL调优)
RZH_long的博客
12-20 1715
1.fetch抓取 select * from A;这种语句可以直接读取文件，不走MR程序会快很多； 不走MR程序的情况： 在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。 设置conversion参数后再执行sql: hive (default)> set hive.fetch.task.
大数据技术之hive学习文档
06-29
包括hive基本概念,hive的安装,hive的数据类型,ddl数据定义,查询,函数,压缩和存储,企业级优化,实战项目,及常见错误及解决方案等, 适合新手入门,期末复习等.
hsql查询语法
12-11
从网页上复制下来的，感觉挺不错的，文章中有注明出处尊重版权
11.spark学习之旅之hive（五）
01-07
hive概述1.1. 简介1.2. 为什么要使用hive1.3. hive的特点1.4. [架构](https://blog.csdn.net/u013595419/article/details/79632928)1.5. hive的基本操作1.6. hive的数据存储2. hive的安装部署2.1. [hive为什么要...
hive学习笔记精华版
10-08
初学者看这个就够了，越多越难学，Hive是基于Hadoop的一个数据仓库工具(离线)，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。
HiveSQL基础窗口函数学习笔记
08-30
HiveSQL基础窗口函数学习笔记
Debezium日常分享系列之：Debezium 2.7.0.Alpha1发布
zhengzaifeidelushang的博客
04-29 774
虽然 Oracle 建议用户避免使用基于 RAW 的列，但出于向后兼容性的原因，这些列仍广泛用于标准 Oracle 表中。但在某些业务用例中，继续使用 RAW 列而不是其他数据类型是有意义的。Debezium 2.7 引入了一个专门针对 Oracle 的新自定义转换器，称为 RawToStringConverter。此自定义转换器旨在允许您使用 STRING 架构类型快速将 RAW 列的字节数组内容转换为基于字符串的字段。
Elasticsearch：如何使用 Java 对索引进行 ES|QL 的查询
最新发布
Elastic 中国社区官方博客
05-05 1158
​在我之前的文章 “Elasticsearch：对 Java 对象的 ES|QL 查询”，我详细介绍了如何使用 Java 来对 ES|QL 进行查询。对于不是很熟悉 Elasticsearch 的开发者来说，那篇文章里的例子还是不能单独来进行运行。在今天的这篇文章中，我来详细地介绍如何把那个例子跑起来。更多关于 ES|QL 的动手实践，请阅读文章 “Elasticsearch：ES|QL 查询展示”。
ruoyi漏洞总结
qq_59468567的博客
05-03 626
baseurl 就有了，有的是全的 url，大多数是只给个 baseurl：prod-api，这类。js 下面有个 app.xxxxxx(一些个字母数字).js，直接里面搜。如果页面访问显示不正常，可添加默认访问路径尝试是否显示正常。若依有个特点，密码可能在前台直接显示，点击并能够成功登录。监控中存在大量接口路径，可通过未授权获取更多敏感信息在。图标和登录页面及验证码处的样式识别出若依。认证失败，无法访问系统资源。进行替换实现登录系统。数据库存在未授权访问。
【翻译】Elasticsearch-索引模块
qq_49444793的博客
04-30 1065
索引块限制对指定的索引的可用的操作类型（就是指对该索引能进行什么操作）。这些块有不同的风格，可以阻止写、读或元数据操作。块可以通过动态索引设置来设置/移除，也可以通过专用API添加，这也可以确保写入块一旦成功返回用户，所有索引分片都能正确地计算该块，例如，在添加写块后，所有对索引进行中的写入会完成。
《崇明新闻》报道智慧岛大数据云计算中心
YUNDUANNET的博客
04-29 694
4月22日，《崇明新闻》报道了云端股份目前在建的上海智慧岛大数据云计算中心，采访了新基建项目CEO张海东，向其了解目前云计算中心的项目概况以及建设进度。
hive hsql表分区该如何操作
06-08
在Hive中创建分区表的语法如下： ``` CREATE TABLE table_name (column1 data_type, column2 data_type...) PARTITIONED BY (partition_column1 data_type, partition_column2 data_type...) ``` 具体操作步骤如下： 1. 创建一个非分区表 ``` CREATE TABLE test( id INT, name STRING ); ``` 2. 创建一个分区表 ``` CREATE TABLE test_partition( id INT, name STRING ) PARTITIONED BY (dt STRING, country STRING); ``` 3. 添加分区： ``` ALTER TABLE test_partition ADD PARTITION(dt='20200101', country='CN'); ``` 4. 加载数据到分区： ``` LOAD DATA INPATH '/path/to/data' INTO TABLE test_partition PARTITION(dt='20200101', country='CN'); ``` 5. 查询分区数据： ``` SELECT * FROM test_partition WHERE dt='20200101' AND country='CN'; ``` 注意事项： 1. 分区列必须是表中的列，不能是表中不存在的列。 2. 分区列可以是任何 Hive 支持的数据类型。 3. 分区列的数据类型必须与添加分区时指定的数据类型相同。 4. 如果你在添加分区时指定了一个不存在的分区列值，Hive 会自动创建这个分区列值，并将它的值设置为 NULL。 5. 分区列的顺序必须与表定义的顺序相同。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值