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

十二、Hive基本查询操作二第1关Hive排序任务描述本关任务2013年7月22日买入量最高的三种股票。相关知识为了完成本关任务你需要掌握1. Hive的几种排序2. limit使用。hive的排序① order byorder by后面可以有多列进行排序默认按字典排序(desc:降序asc(默认):升序)order by为全局排序order by需要reduce操作且只有一个reduce无法配置(因为多个reduce无法完成全局排序)如果指定了hive.mapred.modestrict默认值是nonstrict,这时就必须指定limit来限制输出条数。表名studentclass name scoresA xiaoming 89A xiaojun 72B xiaohong 88C xiaoqiang 92C xiaogang 84按scores降序select * from student order by scores desc输出C xiaoqiang 92A xiaoming 89B xiaohong 88C xiaogang 84A xiaojun 72② sort byHive中指定了sort by那么在每个reducer端都会做排序也就是说保证了局部有序每个reducer出来的数据是有序的但是不能保证所有的数据是有序的除非只有一个reducer好处是执行了局部排序之后可以为接下去的全局排序提高不少的效率其实就是做一次归并排序就可以做到全局排序了。按scores降序select * from student sort by scores desc输出C xiaoqiang 92A xiaoming 89B xiaohong 88C xiaogang 84A xiaojun 72③ distribute bydistribute 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 92A xiaoming 89B xiaohong 88C xiaogang 84A 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 72C xiaogang 84B xiaohong 88A xiaoming 89C xiaoqiang 92limit在Hive查询中要限制查询输出条数 可以用limit关键词指定只输出2条数据select * from student limit 2;输出A xiaoming 89A xiaojun 72编程要求在右侧编辑器补充代码查询出2013年7月22日的哪三种股票买入量最多。表名totalcol_name data_type commenttradedate string 交易日期tradetime string 交易时间securityid string 股票IDbidpx1 string 买入价bidsize1 int 买入量offerpx1 string 卖出价bidsize2 int 卖出量部分数据如下所示20130724 145004 152896 2.62 6960 2.63 1300020130724 145101 152896 2.86 13880 2.89 627020130724 145128 152896 2.85 327400 2.851 150020130724 145143 152896 2.603 44630 2.8 10650数据说明152896:每种股票id20130724: 2013年7月24日145004: 14点50分04秒测试说明平台会对你编写的代码进行测试预期输出股票id 买入量553211 680580680412233 230929160856947 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_volumeFROM totalWHERE tradedate 20130722GROUP BY securityidORDER BY total_buy_volume DESCLIMIT 3;----------end----------第2关Hive数据类型和类型转换任务描述本关任务2013年7月25日每种股票总共被客户买入了多少金额。相关知识为了完成本关任务你需要掌握1.Hive 的内置数据类型2.如何转换数据类型。Hive的内置数据类型Hive 的内置数据类型可以分为两大类(1)、基础数据类型(2)、复杂数据类型。基本数据类型数据类型 所占字节TINYINT 1byte-128 ~ 127SMALLINT 2byte-32,768 ~ 32,767INT 4byte,-2,147,483,648 ~ 2,147,483,647BIGINT 8byte,-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807BOOLEAN 布尔类型true或者falseFLOAT 4byte单精度DOUBLE 8byte双精度STRING 字符系列。可以指定字符集。可以使用单引号或者双引号BINARY 字节数组TIMESTAMP 时间类型CHARVARCHARDATE复杂数据类型数据类型 描述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 arraystring,deductions mapstring, double,address structstreet:string, city:string, state:string, zip:int) row format delimited fields terminated by \tcollection 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类型不能转换为其他任何数据类型显式转换表名username(string) sex(string) height(string)xiaohong 女 165.0xiaoming 男 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日每种股票总共被客户买入了多少元。测试说明表名totalcol_name data_type commenttradedate string 交易日期tradetime string 交易时间securityid string 股票IDbidpx1 string 买入价bidsize1 int 买入量offerpx1 string 卖出价bidsize2 int 卖出量部分数据如下所示20130724 145004 152896 2.62 6960 2.63 1300020130724 145101 152896 2.86 13880 2.89 627020130724 145128 152896 2.85 327400 2.851 150020130724 145143 152896 2.603 44630 2.8 10650数据说明(152896: 每种股票id(20130724: 2013年7月24日)(145004: 14点50分04秒)平台会对你编写的代码进行测试预期输出股票id 买入金额125896 1.2274221965454102E7425178 9731762.828186035668452 8799099.5741589 5.474477543066406E7745962 8010476.90625789562 3.2612930090820312E7792583 5969130.9295043945885478 2.469516101953125E7968956 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_amountFROM totalWHERE tradedate 20130725GROUP 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进行hashhash到指定的桶中这样可以支持高效采样工作。把表或者分区组织成桶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 bucketsrow format delimited fields terminated by \tstored 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 \tstored as textfile;load data local inpath /hive/users.txt into table bucket_user_temp;//往分桶表里开始插入数据insert into table bucket_userselect id,namefrom 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)//表示从桶中抽取530/6个bucket数据从第2个bucket开始抽取抽取的个数由每个桶中的数据量决定。相隔6个桶再次抽取因此依次抽取的桶为28142026数据块抽样该方式允许 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 TaskInputSplit每个取200行总共600行编程要求根据提示在右侧编辑器补充代码计算每个股票每天的交易量。采用桶表抽样的方法从第二个桶开始抽样每隔两个开始抽样创建分桶表total_bucket以股票ID进行分桶共分为 6 个桶数据从total表获取。表名totalcol_name data_type commenttradedate string 交易日期tradetime string 交易时间securityid string 股票IDbidpx1 string 买入价bidsize1 int 买入量offerpx1 string 卖出价bidsize2 int 卖出量部分数据如下所示20130724 145004 152896 2.62 6960 2.63 1300020130724 145101 152896 2.86 13880 2.89 627020130724 145128 152896 2.85 327400 2.851 150020130724 145143 152896 2.603 44630 2.8 10650数据说明(152896: 每种股票id)(20130724: 2013年7月24日)(145004: 14点50分04秒)测试说明平台会对你编写的代码进行测试预期输出交易日期 股票id 总交易量20130722 125896 3382330020130722 204001 2483070020130722 412233 24976938020130722 553211 74271270020130722 745962 9059260020130722 856947 16168560020130723 204001 7761790020130723 869547 25830090020130724 152896 1315858020130724 204001 4888950020130724 745896 19970626020130724 856974 1495822020130724 881125 24611800020130725 125896 158456020130725 425178 128690020130725 668452 141640020130725 745962 87472020130725 789562 407343020130725 968956 50890020130726 204001 263772500提示总交易量买入量卖出量*开始你的任务吧祝你成功----------禁止修改----------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;drop table if exists total_bucket;----------禁止修改--------------------begin------------ 创建分桶表total_bucket以股票ID进行分桶共分为6个桶CREATE TABLE total_bucket(tradedate string,tradetime string,securityid string,bidpx1 string,bidsize1 int,offerpx1 string,bidsize2 int)CLUSTERED BY(securityid) INTO 6 BUCKETSROW FORMAT DELIMITED FIELDS TERMINATED BY ,STORED AS TEXTFILE;-- 启用分桶强制设置SET hive.enforce.bucketing true;-- 从total表插入数据到分桶表INSERT INTO TABLE total_bucketSELECT * FROM total;-- 使用桶表抽样查询每个股票每天的总交易量-- 从第二个桶开始抽样每隔两个桶抽取一次即抽取桶2、4、6SELECT tradedate, securityid, SUM(bidsize1 bidsize2) AS total_volumeFROM total_bucket TABLESAMPLE(BUCKET 2 OUT OF 2 ON securityid)GROUP BY tradedate, securityidORDER BY tradedate, securityid;----------end----------有任何问题都可以随时关注私信