Hive 基本数据查询

1. 基本查询

hive sql 基本查询和Mysql差不多

基本查询语句语法：

SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BYcol_list]
[HAVING where_condition]
[ORDER BYcol_list]
[CLUSTER BYcol_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

1.1 SELECT 基本查询

-- 1. 查询所有
SELECT * FROM itheima.orders;

-- 2. 查询单列
SELECT orderid, totalmoney, username, useraddress, paytime FROM itheima.orders;

-- 3. 查询数据量
SELECT COUNT(*) FROM itheima.orders;

-- 4. 过滤广东省订单
SELECT * FROM itheima.orders WHERE useraddress LIKE '%广东%';

-- 5. 找出广东省单笔营业额最大的订单
SELECT * FROM itheima.orders WHERE useraddress like '%广东%' ORDER BY totalmoney DESC LIMIT 1;

2. 分组、聚合查询

-- 1. 统计未支付、已支付各自的人数
SELECT ispay, COUNT(*) AS cnt FROM itheima.orders GROUP BY ispay;

-- 2. 在已付款订单中，统计每个用户最高的一笔消费金额
SELECT userid, MAX(totalmoney) AS max_money FROM itheima.orders WHERE ispay = 1 GROUP BY userid;

-- 3. 统计每个用户的平均订单消费额
SELECT userid, AVG(totalmoney) FROM itheima.orders GROUP BY userid;

-- 4. 统计每个用户的平均订单消费额，过滤大于10000的数据
SELECT userid, AVG(totalmoney) AS avg_money FROM itheima.orders GROUP BY userid HAVING avg_money > 10000;

3. join 查询

-- 1. JOIN订单表和用户表，找出用户名
SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o JOIN itheima.users u ON o.userid = u.userid;

-- 2. 左外关联，订单表和用户表，找出用户名
SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o LEFT JOIN itheima.users u ON o.userid = u.userid;

2. RLIKE正则匹配

在HQL里面使用正则查询，首先需要了解正则表达式的使用方法

简单正则示例如下：

-- 1. 查找广东省的数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE '.*广东.*';

-- 2. 查找用户地址是：xx省 xx市 xx区的数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE '..省 ..市 ..区';

-- 3. 查找用户姓为张、王、邓
SELECT * FROM itheima.orders WHERE username RLIKE '[张王邓]\\S+';

-- 4. 查找手机号符合：188****0*** 规则
SELECT * FROM itheima.orders WHERE userphone  RLIKEE '188\\S{4}0\\S{3}';

3. UNION联合

UNION 用于将多个 SELECT 语句的结果组合成单个结果集。

注意：每个 select 语句返回的列的数量和名称必须相同。否则，将引发架构错误。

基础语法如下：

SELECT ...
    UNION [ALL]
SELECT ...

示例如下：

-- 1. 对select结果去重
SELECT * FROM course
UNION 
SELECT * FROM course

-- 2. 对select结果不进行去重
SELECT * FROM course
UNION ALL
SELECT * FROM course

此外UNION 还可以用在子查询、insert select中

4. Sampling采样

当一个表中的数据非常大的时候，比如TB级别，对这种表一个简单的SELECT * 都会非常的慢，哪怕LIMIT 10想要看10条数据，也会走MapReduce流程，这个时间等待是不合适的。

Hive提供的快速抽样的语法，本质上就是用TABLESAMPLE函数，可以快速从大表中随机抽取一些数据供用户查看。

4.1 基于随机分桶抽样

SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))

• y表示将表数据随机划分成y份（y个桶）
• x表示从y里面随机抽取x份数据作为取样
• colname表示随机的依据基于某个列的值
• rand()表示随机的依据基于整行

示例如下：

-- 1. 使用colname作为随机依据，则其它条件不变下，每次抽样结果一致
SELECT username, orderId, totalmoney FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON username);

-- 2. 使用rand()作为随机依据，每次抽样结果都不同
SELECT * FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());

4.2 基于数据块抽样

SELECT ... FROM tbl TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G));

• num ROWS 表示抽样num条数据
• num PERCENT 表示抽样num百分百比例的数据
• num(K|M|G) 表示抽取num大小的数据，单位可以是K、M、G表示KB、MB、GB

注意：使用这种语法抽样，条件不变的话，每一次抽样的结果都一致，即无法做到随机，只是按照数据顺序从前向后取。