Hive 数据查询

一、基本查询

1.1.基本语法

查询语句的基本语法：

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]

和普通的SQL查询类似，部分有区别，如：CLUSTER BY、DISTRIBUTE BY、SORT BY 等

1.2.数据准备

1.2.1.创建订单表

准备数据，创建订单表：

CREATE DATABASE myhive;
USE myhive;

CREATE TABLE myhive.orders (
    orderId bigint COMMENT ' 订单 id',
    orderNo string COMMENT ' 订单编号 ',
    shopId bigint COMMENT ' 门店 id',
    userId bigint COMMENT ' 用户 id',
    orderStatus tinyint COMMENT ' 订单状态 -3: 用户拒收 -2: 未付款的订单 -1 ：用户取消 0: 待发货 1: 配送中 2: 用户确认收货 ',
    goodsMoney double COMMENT ' 商品金额 ',
    deliverMoney double COMMENT ' 运费 ',
    totalMoney double COMMENT ' 订单金额（包括运费） ',
    realTotalMoney double COMMENT ' 实际订单金额（折扣后金额） ',
    payType tinyint COMMENT ' 支付方式 ,0: 未知 ;1: 支付宝， 2 ：微信 ;3 、现金； 4 、其他 ',
    isPay tinyint COMMENT ' 是否支付 0: 未支付 1: 已支付 ',
    userName string COMMENT ' 收件人姓名 ',
    userAddress string COMMENT ' 收件人地址 ',
    userPhone string COMMENT ' 收件人电话 ',
    createTime timestamp COMMENT ' 下单时间 ',
    payTime timestamp COMMENT ' 支付时间 ',
    totalPayFee int COMMENT ' 总支付金额 '
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

导入数据

LOAD DATA LOCAL INPATH '/home/hadoop/hive_orders.txt' INTO TABLE myhive.orders;

导入数据后，基于此表进行简单的Hive查询

1.2.2.创建用户表

准备数据，创建用户表：

CREATE TABLE myhive.users (
    userId int,
    loginName string,
    loginSecret int,
    loginPwd string,
    userSex tinyint,
    userName string,
    trueName string,
    brithday date,
    userPhoto string,
    userQQ string,
    userPhone string,
    userScore int,
    userTotalScore int,
    userFrom tinyint,
    userMoney double,
    lockMoney double,
    createTime timestamp,
    payPwd string,
    rechargeMoney double
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

导入数据

LOAD DATA LOCAL INPATH '/home/hadoop/hive_users.txt' INTO TABLE myhive.users;
SELECT * FROM myhive.users;

1.3.select基本查询

基本查询和SQL一致，但是注意如果是中文字段起别名，需要使用反引号（`）来包裹中文别名。这样做可以避免Hive解析器出现问题。

-- 查询所有订单信息
SELECT * FROM myhive.orders;

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

-- 统计订单数量
SELECT COUNT(*) as order_number FROM myhive.orders;
-- 起中文别名号（`）来包裹中文别名。这样做可以避免Hive解析器出现问题
SELECT COUNT(*) as `数量` FROM myhive.orders;

-- 查询广东省的订单
SELECT * FROM myhive.orders WHERE useraddress LIKE '%广东%';

-- 查询广东省单笔营业额最大的订单
SELECT * FROM myhive.orders WHERE useraddress LIKE '%广东%' ORDER BY totalmoney DESC LIMIT 1;

1.4.分组、聚合查询

-- 统计未支付、已支付各自的订单数
SELECT ispay,COUNT(*) AS `数量` FROM  myhive.orders  GROUP BY ispay;

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

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

-- 统计每个用户的平均订单金额大于10000的信息，
SELECT userid,AVG(totalmoney) AS avg_totalmoney FROM myhive.orders GROUP BY userid HAVING avg_totalmoney>10000;

1.5.多表连接查询

多表查询还是可以使用JOIN ON语法：

-- 查询包括订单信息和用户信息的完整订单数据：
SELECT o.*,u.* FROM myhive.orders o JOIN myhive.users u ON o.userId = u.userId;

-- 显示订单状态为“用户取消”或“用户确认收货”的订单，并包括用户信息：
SELECT o.*,u.* FROM myhive.orders o JOIN myhive.users u ON o.userId = u.userId
WHERE o.orderStatus = -1 OR o.orderStatus = 2;

-- 查询订单金额大于1000的订单信息以及对应的用户信息：
SELECT o.*,u.* FROM myhive.orders o JOIN myhive.users u ON o.userId = u.userId
WHERE o.totalmoney > 1000;

-- 显示未支付的订单和对应的用户信息，按下单时间升序排列
SELECT o.*,u.* FROM myhive.orders o JOIN myhive.users u ON o.userId = u.userId
WHERE o.isPay=0 
ORDER BY o.createTime;

二、RLIKE正则匹配

2.1.正则表达式

正则表达式是一种规则集合，通过特定的规则字符描述，来判断字符串是否符合规则。

⑴.字符集合

字符	描述
[a-z]	字符范围。匹配指定范围内的任意字符。例如，'[a-z]' 可以匹配 'a' 到 'z' 范围内的任意小写字母字符。
[A-Z]	'[A-Z]' 可以匹配 'A' 到 'Z' 范围内的任意大写字母字符。
[^a-z]	匹配任何不在指定范围内的任意字符。例如，'[^a-z]' 可以匹配任何不在 'a' 到 'z' 范围内的任意字符。
\d	匹配一个数字字符，等价于[0-9],匹配所有的数字
\D	匹配一个非数字字符，等价于[^0-9]
\w	匹配字母、数字、下划线。等价于'[A-Za-z0-9_]'
\W	匹配非字母、数字、下划线。等价于 '[^A-Za-z0-9_]'
\s	匹配任何空白字符，包括空格、制表符、换页符等等。等价于 [ \f\n\r\t\v]
\S	匹配任何非空白字符。等价于 [^ \f\n\r\t\v]
\f	匹配一个换页符。
\n	匹配一个换行符。等价于 \x0a 和 \cJ
\r	匹配一个回车符。等价于 \x0d 和 \cM

⑵.边界集合

字符	描述
^	每一行的开头，单行模式下等价于字符串的开头
$	每一行的结尾，单行模式下等价于字符串的结尾

⑶.量词（重复次数）集合

字符	描述
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}
?	匹配前面的子表达式零次或一次。例如，"do(es)?" 可以匹配 "do" 或 "does" 。? 等价于 {0,1}
{n}	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}'能匹配 "food" 中的两个 o
{n,}	n 是一个非负整数。至少匹配n 次。例如，'o{2,}' 能匹配 "foooood" 中的所有 o
{n,m}	m 和n均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。例如，"o{1,3}" 将匹配 "fooooood" 中的前三个 o

⑷.转义操作符

字符	描述
\	将下一个字符标记为一个特殊字符、或一个原义字符等。例如，'n' 匹配字符 "n"。'\n' 匹配一个换行符

⑸.运算符优先级

正则表达式从左到右进行计算，并遵循优先级顺序。相同优先级的从左到右进行运算，不同优先级的运算先高后低。下表从最高到最低说明了各种正则表达式运算符的优先级顺序：

运算符	描述	示例
转义符\	`\` 是用于转义其他特殊字符的转义符号。它具有最高的优先级。	`\d` 匹配数字，`\.` 匹配点号
圆括号()	圆括号()用于创建子表达式，具有高于其他运算符的优先级。	`(abc)+` 匹配 "abc" 一次或多次
量词 *, +, ?, {n}, {n,}, {n,m}	量词指定前面的元素可以重复的次数。	`a*` 匹配零个或多个 "a"
定位点^, $	`^` 表示行的开头，`$` 表示行的结尾
管道符号\|	`\|` 表示"或"关系，用于在多个模式之间选择一个	`cat\|dog` 能够匹配到 "cat" 或 "dog"

2.2.RLIKE

Hive 中提供 RLIKE 关键字，可以供用户使用正则和数据进行匹配。以订单表为例，来简单使用一下 RLIKE 正则匹配。

查找广东省的数据

SELECT * FROM myhive.orders WHERE useraddress RLIKE '.*广东.*';

查找用户地址是： xx 省 xx 市 xx 区的数据

SELECT * FROM myhive.orders WHERE useraddress RLIKE '.*省.*市.*区';

查询用户姓为张、王、邓

SELECT * FROM myhive.orders WHERE username RLIKE '[张王邓]\\S+'

说明：

在正则表达式中，\ 是一个转义字符，用于标记下一个字符为特殊字符、原义字符、向后引用或八进制转义符。因此，在使用正则表达式时，如果需要匹配某些特殊字符或进行特定的字符组合，就必须使用双反斜杠 \\ 来表示单个反斜杠。
在正则表达式 [张王邓] 中，[ ] 表示字符集合，其中方括号内的字符可以任选出现。例如，[a-zA-Z] 可以匹配任何小写或大写字母。而 [张王邓] 则是匹配“张”、“王”、“邓”这四个字符中的任意一个。
然而，当我们将 [张王邓] 与 \S+ 连接时，问题出现在如何正确地表示这些字符之间的关系。在这种情况下，\S+ 用于匹配一个或多个非空白字符（即非空格、制表符、换页符等空白字符的字符序列）。如果直接写作 [张王邓]\S+，则会导致解释错误，因为这里的 [张王邓] 被视为一个整体，而不是单独的字符集合。
为了避免这种解释错误，我们需要在 [张王邓] 和 \S+ 之间添加一个转义字符 \，以确保它们被正确地解析和应用。这样，整个表达式就变成了 [张王邓]\\S+，其中 [张王邓] 被视为单独的字符集合，而 \S+ 被视为独立的部分，从而正确地匹配目标字符串。

总结来说，添加 \\ 是为了确保正则表达式中的特殊字符和字符集合能够被正确解析和应用，这是正则表达式中常见的做法，以避免潜在的语法错误和解释混淆。

查找手机号符合：188****0*** 规则

SELECT * FROM myhive.orders WHERE userphone RLIKE '188\\S{4}0\\d{3}'

注意：手机号码188****9264中间四位是隐藏的，所以使用\S匹配，后面的是数字，所以使用\d

三、UNIQN 联合

3.1.UNION联合语法

UNION 用于将多个 SELECT 语句的结果组合成单个结果集。每个 select 语句返回的列的数量和名称必须相同。否则，将引发架构错误。基础语法：

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

3.2.UNION联合数据准备

准备数据进行测试

CREATE TABLE myhive.course(
    c_id string,
    c_name string,
    t_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

导入数据

LOAD DATA LOCAL INPATH '/home/hadoop/course.txt' INTO TABLE myhive.course

连接两个查询结果集

SELECT * FROM myhive.course WHERE t_id = '百晓生'
UNION 
SELECT * FROM myhive.course WHERE t_id = '萧剑南'

结果如下：

3.3.UNION 联合 - 去重

UNION 默认有去重功能：

直接联合两个同样的查询结果

SELECT * FROM myhive.course
UNION 
SELECT * FROM myhive.course

会发现一张表在一起联合起来，然后发现只有一份数据，因为自动去重了

如果不需要去重效果

SELECT * FROM myhive.course
UNION ALL
SELECT * FROM myhive.course

如果不想让去重，则添加ALL，发现下面的数据都出现了两份：

3.4.其他写法

UNION 写在 FROM 中

SELECT t_id,COUNT(*) FROM
(
SELECT * FROM myhive.course WHERE t_id = '百晓生'
UNION ALL 
SELECT * FROM myhive.course WHERE t_id = '萧剑南'
)
AS u GROUP BY t_id

用于INSERT SELECT中

-- 根据myhive.course创建myhive.course2
CREATE TABLE myhive.course2 LIKE myhive.course;

-- 插入数据
INSERT OVERWRITE TABLE myhive.course2
SELECT * FROM myhive.course
UNION ALL
SELECT * FROM myhive.course

# 查看数据
SELECT * FROM myhive.course2

四、Sampling采样

4.1.为什么需要抽样表数据

对表进行随机抽样是非常有必要的。大数据体系下，在真正的企业环境中，很容易出现很大的表，比如体积达到 TB 级别。对这种表一个简单的 SELECT * 都会非常的慢，哪怕 LIMIT 10 想要看 10 条数据，也会走 MapReduce 流程，这个时间等待是不合适的。Hive 提供的快速抽样的语法，可以快速从大表中随机抽取一些数据供用户查看。

4.2.TABLESAMPLE 函数

进行随机抽样，本质上就是用 TABLESAMPLE 函数

语法 1 ，基于随机分桶抽样：

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

说明：

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

示例：

SELECT username, orderId, totalmoney FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON username);
SELECT * FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());

注意：

使用 colname 作为随机依据，则其它条件不变下，每次抽样结果一致
使用 rand() 作为随机依据，每次抽样结果都不同

语法 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

示例：

-- 语法 2 ，基于数据块抽样
-- 抽取10条数据
SELECT * FROM  myhive.orders TABLESAMPLE(10 ROWS);
-- 抽取10%的数据
SELECT * FROM  myhive.orders TABLESAMPLE(10 PERCENT);
-- 抽取10KB的数据
SELECT * FROM  myhive.orders TABLESAMPLE(10K);

注意：

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

4.3.TABLESAMPLE 函数的使用说明

桶抽样方式， TABLESAMPLE(BUCKET x OUT OF y ON(colname |rand())) ，推荐，完全随机，速度略慢块抽样，使用分桶表可以加速
块抽样方式， TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G)) ，速度快于桶抽样方式，但不随机，只是按照数据顺序从前向后取。

五、Virtual Columns虚拟列

5.1.Virtual Columns 虚拟列

虚拟列是 Hive 内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。Hive 目前可用 3 个虚拟列：

INPUT__FILE__NAME ，显示数据行所在的具体文件
BLOCK__OFFSET__INSIDE__FILE ，显示数据行所在文件的偏移量
ROW__OFFSET__INSIDE__BLOCK ，显示数据所在 HDFS 块的偏移量
- 此虚拟列需要设置： SET hive.exec.rowoffset=true 才可使用

示例：

SELECT *, INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK FROM myhive.course;

说明：

INPUT__FILE__NAME：这是一个虚拟列，用于获取当前处理的数据所在的文件名。

BLOCK__OFFSET__INSIDE__FILE：这个偏移量表示数据在文件中的偏移量。当数据被存储在文件中时，每条数据都有一个在文件中的位置，这个偏移量可以指示数据在文件中的具体位置。
ROW__OFFSET__INSIDE__BLOCK：这个偏移量表示数据在HDFS数据块中的行偏移量。在Hive中，数据一般会以数据块（block）的形式进行存储，而每个数据块中会包含多行数据，这个偏移量可以指示数据在数据块中的具体行位置。

5.2.虚拟列的作用

使用虚拟列，可以让我们更精准的查看到具体每一条数据在存储上的详细参数细节，虚拟列不仅仅可以用于 SELECT ，在 WHERE 、 GROUP BY 等均可使用如：

-- 统计数据在文件中的偏移量大于50
SELECT *,BLOCK__OFFSET__INSIDE__FILE FROM myhive.course WHERE BLOCK__OFFSET__INSIDE__FILE > 50;

-- 准备数据
CREATE TABLE myhive.orders_bucket (
    orderId bigint COMMENT ' 订单 id',
    orderNo string COMMENT ' 订单编号 ',
    shopId bigint COMMENT ' 门店 id',
    userId bigint COMMENT ' 用户 id',
    orderStatus tinyint COMMENT ' 订单状态 -3: 用户拒收 -2: 未付款的订单 -1 ：用户取消 0: 待发货 1: 配送中 2: 用户确认收货 ',
    goodsMoney double COMMENT ' 商品金额 ',
    deliverMoney double COMMENT ' 运费 ',
    totalMoney double COMMENT ' 订单金额（包括运费） ',
    realTotalMoney double COMMENT ' 实际订单金额（折扣后金额） ',
    payType tinyint COMMENT ' 支付方式 ,0: 未知 ;1: 支付宝， 2 ：微信 ;3 、现金； 4 、其他 ',
    isPay tinyint COMMENT ' 是否支付 0: 未支付 1: 已支付 ',
    userName string COMMENT ' 收件人姓名 ',
    userAddress string COMMENT ' 收件人地址 ',
    userPhone string COMMENT ' 收件人电话 ',
    createTime timestamp COMMENT ' 下单时间 ',
    payTime timestamp COMMENT ' 支付时间 ',
    totalPayFee int COMMENT ' 总支付金额 '
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA LOCAL INPATH '/home/hadoop/hive_orders.txt' INTO TABLE myhive.orders_bucket;
LOAD DATA LOCAL INPATH '/home/hadoop/hive_orders_bucket.txt' INTO TABLE myhive.orders_bucket;

-- 根据当前处理的数据所在的文件名分组查询
SELECT INPUT__FILE__NAME,COUNT(*) FROM myhive.orders_bucket GROUP BY INPUT__FILE__NAME;

posted @ 2019-10-29 11:48 酒剑仙* 阅读(4833) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Augus

Hive 数据查询

一、基本查询

1.1.基本语法

1.2.数据准备

1.2.1.创建订单表

1.2.2.创建用户表

1.3.select基本查询

1.4.分组、聚合查询

1.5.多表连接查询

二、RLIKE正则匹配

2.1.正则表达式

⑴.字符集合

⑵.边界集合

⑶.量词（重复次数）集合

⑷.转义操作符

⑸.运算符优先级

2.2.RLIKE

三、UNIQN 联合

3.1.UNION联合语法

3.2.UNION联合数据准备

3.3.UNION 联合 - 去重

3.4.其他写法

四、Sampling采样

4.1.为什么需要抽样表数据

4.2.TABLESAMPLE 函数

4.3.TABLESAMPLE 函数的使用说明

五、Virtual Columns虚拟列

5.1.Virtual Columns 虚拟列

5.2.虚拟列的作用

公告