当前位置：首页 > news >正文

头歌实践教学平台：大数据存储2023（十二）

news 2026/8/24 22:25:57

十二、Hive基本查询操作（二）

第1关：Hive排序

任务描述
本关任务：2013年7月22日买入量最高的三种股票。

相关知识
为了完成本关任务，你需要掌握：1. Hive的几种排序；2. limit使用。

hive的排序
① order by

order by后面可以有多列进行排序，默认按字典排序(desc:降序，asc(默认):升序)；

order by为全局排序；

order by需要reduce操作，且只有一个reduce，无法配置(因为多个reduce无法完成全局排序)；

如果指定了hive.mapred.mode=strict（默认值是nonstrict）,这时就必须指定limit来限制输出条数。

表名：student

class name scores
A xiaoming 89
A xiaojun 72
B xiaohong 88
C xiaoqiang 92
C xiaogang 84
按scores降序：

select * from student order by scores desc；

输出

C xiaoqiang 92
A xiaoming 89
B xiaohong 88
C xiaogang 84
A xiaojun 72
② sort by

Hive中指定了sort by，那么在每个reducer端都会做排序，也就是说保证了局部有序（每个reducer出来的数据是有序的，但是不能保证所有的数据是有序的，除非只有一个reducer），好处是：执行了局部排序之后可以为接下去的全局排序提高不少的效率（其实就是做一次归并排序就可以做到全局排序了）。

按scores降序：

select * from student sort by scores desc；

输出：

C xiaoqiang 92
A xiaoming 89
B xiaohong 88
C xiaogang 84
A xiaojun 72
③ distribute by

distribute by控制map输出结果的分发,相同字段的map输出会发到一个reduce节点去处理。sort by为每一个reducer产生一个排序文件，他俩一般情况下会结合使用。（这个肯定是全局有序的，因为相同的class会放到同一个reducer去处理。这里需要注意的是distribute by必须要写在sort by之前）。

按scores降序：

select * from student distribute by class sort by scores desc;

输出：

C xiaoqiang 92
A xiaoming 89
B xiaohong 88
C xiaogang 84
A xiaojun 72
④ cluster by

如果sort by和distribute by中所用的列相同，可以缩写为cluster by以便同时制定两者所用的列cluster by的功能就是distribute by和sort by相结合（注意被cluster by指定的列只能是升序，不能指定asc和desc）。

以下两句HQL查询结果相同：

select * from student cluster by scores;
select * from student distribute by scores sort by scores desc;
输出：

A xiaojun 72
C xiaogang 84
B xiaohong 88
A xiaoming 89
C xiaoqiang 92
limit
在Hive查询中要限制查询输出条数，可以用limit关键词指定

只输出2条数据：

select * from student limit 2;

输出：

A xiaoming 89
A xiaojun 72

编程要求
在右侧编辑器补充代码，查询出2013年7月22日的哪三种股票买入量最多。

表名：total

col_name data_type comment
tradedate string 交易日期
tradetime string 交易时间
securityid string 股票ID
bidpx1 string 买入价
bidsize1 int 买入量
offerpx1 string 卖出价
bidsize2 int 卖出量
部分数据如下所示：

20130724 145004 152896 2.62 6960 2.63 13000
20130724 145101 152896 2.86 13880 2.89 6270
20130724 145128 152896 2.85 327400 2.851 1500
20130724 145143 152896 2.603 44630 2.8 10650
数据说明：

（152896:每种股票id）
（20130724: 2013年7月24日）
（145004: 14点50分04秒）

测试说明
平台会对你编写的代码进行测试：

预期输出：

股票id 买入量

553211 680580680
412233 230929160
856947 104360800
开始你的任务吧，祝你成功！

----------禁止修改----------

create database if not exists mydb;

use mydb;

create table if not exists total(

tradedate string,

tradetime string,

securityid string,

bidpx1 string,

bidsize1 int,

offerpx1 string,

bidsize2 int)

row format delimited fields terminated by ','

stored as textfile;

truncate table total;

load data local inpath '/root/files' into table total;

----------禁止修改----------

----------begin----------

SELECT securityid, SUM(bidsize1) AS total_buy_volume

FROM total

WHERE tradedate = '20130722'

GROUP BY securityid

ORDER BY total_buy_volume DESC

LIMIT 3;

----------end----------

第2关：Hive数据类型和类型转换

任务描述
本关任务：2013年7月25日每种股票总共被客户买入了多少金额。

相关知识
为了完成本关任务，你需要掌握：1.Hive 的内置数据类型，2.如何转换数据类型。

Hive的内置数据类型
Hive 的内置数据类型可以分为两大类：(1)、基础数据类型；(2)、复杂数据类型。

基本数据类型

数据类型所占字节
TINYINT 1byte，-128 ~ 127
SMALLINT 2byte，-32,768 ~ 32,767
INT 4byte,-2,147,483,648 ~ 2,147,483,647
BIGINT 8byte,-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807
BOOLEAN 布尔类型，true或者false
FLOAT 4byte单精度
DOUBLE 8byte双精度
STRING 字符系列。可以指定字符集。可以使用单引号或者双引号
BINARY 字节数组
TIMESTAMP 时间类型
CHAR
VARCHAR
DATE

复杂数据类型

数据类型描述
STRUCT 通过“.”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, lastSTRING},那么第1个元素可以通过字段.first来引用。
MAP MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素
ARRAY 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用
CREATE TABLE employees (
name string,
salary double,
subordinates array<string>,
deductions map<string, double>,
address struct<street:string, city:string, state:string, zip:int>
) row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
stored as textfile;

类型转换
Hive中的数据类型转换包括隐式转换（implicit conversions）和显式转换（explicitly conversions）。

隐式转换

Hive在需要的时候将会对numeric类型的数据进行隐式转换。比如我们对两个不同数据类型的数字进行比较，假如一个数据类型是INT型，另一个是SMALLINT类型，那么SMALLINT类型的数据将会被隐式转换地转换为INT类型；但是我们不能隐式地将一个 INT类型的数据转换成SMALLINT或TINYINT类型的数据，这将会返回错误，除非你使用了CAST操作。

任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都可以隐式地转换成DOUBLE；是的你没看出，STRING也可以隐式地转换成DOUBLE！但是你要记住，BOOLEAN类型不能转换为其他任何数据类型！

显式转换

表名：user

name(string) sex(string) height(string)
xiaohong 女 165.0
xiaoming 男 180.0
将身高类型转换为float。

示例如下：

select * from user where cast(height as float) > 170.0

输出：xiaoming 男 180.0

这样height将会显示的转换成float。如果height是不能转换成float，这时候cast将会返回NULL！

注意：
(1) 如果将浮点型的数据转换成int类型的，内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现！

(2) 对于 BINARY 类型的数据，只能将 BINARY 类型的数据转换成 STRING 类型。如果你确信 BINARY 类型数据是一个数字类型(a number)，这时候你可以利用嵌套的cast操作，比如a是一个 BINARY，且它是一个数字类型，那么你可以用下面的查询：

SELECT (cast(cast(a as string) as double)) from src;

我们也可以将一个 String 类型的数据转换成 BINARY 类型。

(3) 对于 Date 类型的数据，只能在 Date、Timestamp 以及 String 之间进行转换。下表将进行详细的说明：

有效的转换结果
cast(date as date) 返回date类型
cast(timestamp as date) timestamp中的年/月/日的值是依赖与当地的时区，结果返回date类型
cast(string as date) 如果string是YYYY-MM-DD格式的，则相应的年/月/日的date类型的数据将会返回；但如果string不是YYYY-MM-DD格式的，结果则会返回NULL。
cast(date as timestamp) 基于当地的时区，生成一个对应date的年/月/日的时间戳值
cast(date as string) date所代表的年/月/日时间将会转换成YYYY-MM-DD的字符串。

编程要求
在右侧编辑器补充代码，2013年7月25日每种股票总共被客户买入了多少元。

测试说明
表名：total

20130724 145004 152896 2.62 6960 2.63 13000
20130724 145101 152896 2.86 13880 2.89 6270
20130724 145128 152896 2.85 327400 2.851 1500
20130724 145143 152896 2.603 44630 2.8 10650
数据说明：

(152896: 每种股票id）
(20130724: 2013年7月24日)
(145004: 14点50分04秒)
平台会对你编写的代码进行测试：

预期输出：

股票id 买入金额

125896 1.2274221965454102E7
425178 9731762.828186035
668452 8799099.5
741589 5.474477543066406E7
745962 8010476.90625
789562 3.2612930090820312E7
792583 5969130.9295043945
885478 2.469516101953125E7
968956 3356246.9372558594
提示：
(1)总共买入金额=买入量*买入价
(2)将买入价为 string，强转为 float 计算*
开始你的任务吧，祝你成功！

----------禁止修改----------

create database if not exists mydb;

use mydb;

create table if not exists total(

tradedate string,

tradetime string,

securityid string,

bidpx1 string,

bidsize1 int,

offerpx1 string,

bidsize2 int)

row format delimited fields terminated by ','

stored as textfile;

truncate table total;

load data local inpath '/root/files' into table total;

----------禁止修改----------

----------begin----------

SELECT securityid, SUM(CAST(bidpx1 AS FLOAT) * bidsize1) AS total_amount

FROM total

WHERE tradedate = '20130725'

GROUP BY securityid;

----------end----------

第3关：Hive抽样查询

任务描述
本关任务：计算每个股票每天的总交易量。

相关知识
为了完成本关任务，你需要掌握：1.随机抽样 2.桶表抽样 3.数据块抽样

随机抽样
使用RAND()函数和LIMIT关键字来获取样例数据，使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的。ORDER BY RAND()语句可以获得同样的效果，但是性能没这么高。

/**从table表里随机抽取5行数据*/
//第一种：
SELECT * FROM table DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 2;
//第二种(性能不太好)：
SELECT * FROM table ORDER BY RAND() LIMIT 2;
桶表抽样
① Hive分桶

对于每一个表（table）或者分区， Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

桶（bucket）是指将表或分区中指定列的值为key进行hash，hash到指定的桶中，这样可以支持高效采样工作。

把表（或者分区）组织成桶（Bucket）有两个理由：

获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
//创建一个分桶表(CLUSTERED BY 子句来指定划分桶所用的列和要划分的桶的个数)
create table bucket_user (
id int,
name string
)clustered by(id) into 4 buckets
row format delimited fields terminated by '\t'
stored as textfile;
//在这里，我们使用用户ID来确定如何划分桶(Hive使用对值进行哈希并将结果除以桶的个数取余数)。
配置所需环境（必须）：

要向分桶表中填充成员，需要将 hive.enforce.bucketing 属性设置为 true。①这样，Hive 就知道用表定义中声明的数量来创建桶。然后使用 INSERT 命令即可。需要注意的是： clustered by和sorted by不会影响数据的导入，这意味着，用户必须自己负责数据如何如何导入，包括数据的分桶和排序
'set hive.enforce.bucketing = true' 可以自动控制上一轮reduce的数量从而适配bucket的个数，当然，用户也可以自主设置mapred.reduce.tasks去适配bucket个数，推荐使用'set hive.enforce.bucketing = true'
/**往表里存入数据*/
//1.先创建一个没有分桶的表
create table if not exists bucket_user_temp(
id int,
name string
)row format delimited fields terminated by '\t'
stored as textfile;
load data local inpath '/hive/users.txt' into table bucket_user_temp;
//往分桶表里开始插入数据
insert into table bucket_user
select id,name
from bucket_user_temp;
②桶表抽样

select * from table_name tablesample(bucket X out of Y on field);
//X:从哪个桶开始抽取 Y:相隔几个桶后再次抽取 field:列名注意：x的值必须小于等于y的值
//示例：bkt表(总共30个桶)
select * from bkt tablesample(bucket 2 out of 6 on id)
//表示从桶中抽取5（30/6）个bucket数据，从第2个bucket开始抽取，抽取的个数由每个桶中的数据量决定。相隔6个桶再次抽取，因此，依次抽取的桶为：2，8，14，20，26

数据块抽样

该方式允许 Hive 随机抽取N行数据，数据总量的百分比（n百分比）或N字节的数据。

//抽取table表中50%的数据
SELECT * FROM table TABLESAMPLE (50 PERCENT);
//抽取table表中30m的数据
SELECT * FROM table TABLESAMPLE (30M);
//根据数据行数来取样
SELECT * FROM table TABLESAMPLE (200 ROWS);
//这种方式可以根据行数来取样，但要特别注意：这里指定的行数，是在每个InputSplit中取样的行数，也就是，每个Map中都取样n ROWS。
如果有3个Map Task（InputSplit），每个取200行，总共600行

编程要求
根据提示，在右侧编辑器补充代码，计算每个股票每天的交易量。

采用桶表抽样的方法（从第二个桶开始抽样，每隔两个开始抽样）；

创建分桶表total_bucket（以股票ID进行分桶，共分为 6 个桶）；

数据从total表获取。

表名：total

20130724 145004 152896 2.62 6960 2.63 13000
20130724 145101 152896 2.86 13880 2.89 6270
20130724 145128 152896 2.85 327400 2.851 1500
20130724 145143 152896 2.603 44630 2.8 10650
数据说明：

(152896: 每种股票id)
(20130724: 2013年7月24日)
(145004: 14点50分04秒)

测试说明
平台会对你编写的代码进行测试：

预期输出：

交易日期股票id 总交易量

20130722 125896 33823300
20130722 204001 24830700
20130722 412233 249769380
20130722 553211 742712700
20130722 745962 90592600
20130722 856947 161685600
20130723 204001 77617900
20130723 869547 258300900
20130724 152896 13158580
20130724 204001 48889500
20130724 745896 199706260
20130724 856974 14958220
20130724 881125 246118000
20130725 125896 1584560
20130725 425178 1286900
20130725 668452 1416400
20130725 745962 874720
20130725 789562 4073430
20130725 968956 508900
20130726 204001 263772500
提示：总交易量=买入量+卖出量*
开始你的任务吧，祝你成功！