Hive基本查询操作

第1关：where操作

编程要求

查询出工作职责涉及hive的并且工资大于8000的公司名称以及工作经验。（其中库名：db1，表名：table1）

student表结构：

INFO	TYPE
eduLevel_name	String
company_name	String
jobName	String
salary	int
city_code	int
responsibility	String
workingExp	String

select workingExp,company_name from table1 where responsibility like '%hive%' and salary>8000;

第2关：group by操作

编程要求

计算不同工作年限以及其平均工资并且过滤出平均工资大于10000的。（其中库名：db1，表名：table1）

table1表结构：

INFO	TYPE
eduLevel_name	String
company_name	String
jobName	String
salary	int
city_code	int
responsibility	String
workingExp	String

select avg(salary) as avg_salary,workingExp from table1 group by workingExp having avg_salary>10000;

第3关：join操作

知识点

1.内连接：把符合两边连接条件的数据查询出来；

2.左外连接（LEFT OUTER JOIN）：左表全部查询出来，右表不符合连接条件的显示为空；

3.右外连接（RIGHT OUTER JOIN）：右表全部查询出来，左表不符合连接条件的显示为空；

4.全外连接（FULL OUTER JOIN）：左右表符合连接条件和不符合连接条件的都查出来，不符合的显示空；

5.左半开连接（LEFT SEMI JOIN)：查询出满足连接条件的左边表记录，需要注意的是select和where语句中都不能使用右表的字段。

编程要求

求出表table2中所有城市名的平均工资。（其中库名：db1，表名：table1，表名：table2）

表table1结构：

INFO	TYPE
eduLevel_name	String
company_name	String
jobName	String
salary	int
city_code	int
responsibility	String
workingExp	String

select avg(table1.salary),table2.city_name from table2 left outer join table1 on table2.city_code=table1.city_code group by table2.city_name;

第4关：Hive排序

知识点

1.order by：为全局排序；默认按字典排序(desc:降序，asc(默认):升序)；需要reduce操作且只有一个reduce；如果指定了hive.mapred.mode=strict（默认值是nonstrict）,就必须指定limit来限制输出条数

2.sort by：局部有序

3.distribute by：全局有序；控制map输出结果的分发,相同字段的map输出会发到一个reduce节点去处理；distribute by必须要写在sort by之前

4.cluster  by：被指定的列只能是升序

5.limit：限制查询输出条数

编程要求

查询出2013年7月22日的哪三种股票买入量最多。

表名：total

col_name	data_type	comment
tradedate	string	交易日期
tradetime	string	交易时间
securityid	string	股票ID
bidpx1	string	买入价
bidsize1	int	买入量
offerpx1	string	卖出价
bidsize2	int	卖出量

select securityid,sum(bidsize1) as s from total where tradedate='20130722' group by securityid order by s desc limit 3;

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

知识点

1.基本数据类型

数据类型	所占字节
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

2.复杂数据类型

数据类型	描述
STRUCT	通过“.”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, lastSTRING},那么第1个元素可以通过字段.first来引用。
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用

3.类型转换

（1）隐式转换：任何整数类型都可以隐式地转换成一个范围更大的类型

（2）显式转换

--string类型转换为float类型
select * from user where cast(height as float) > 170.0

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

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

Date类型数据的有效转换

有效的转换	结果
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

col_name	data_type	comment
tradedate	string	交易日期
tradetime	string	交易时间
securityid	string	股票ID
bidpx1	string	买入价
bidsize1	int	买入量
offerpx1	string	卖出价
bidsize2	int	卖出量

select securityid,sum(cast(bidpx1 as float)*bidsize1) from total where tradedate='20130725' group by securityid;

第6关：Hive抽样查询

知识点

1.随机抽样

（1）使用RAND()函数和LIMIT关键字来获取样例数据；

（2）使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的。

2.桶表抽样

（1）Hive分桶：采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中；

（2）桶表抽样

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

3.数据块抽样：允许 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行

编程要求

计算每个股票每天的交易量。

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

2. 创建分桶表total_bucket（以股票ID进行分桶）；

3. 数据从total表获取。

表名：total

col_name	data_type	comment
tradedate	string	交易日期
tradetime	string	交易时间
securityid	string	股票ID
bidpx1	string	买入价
bidsize1	int	买入量
offerpx1	string	卖出价
bidsize2	int	卖出量

create table if not exists total_bucket(
tradedate string,
securityid string,
bidsize1 int,
bidsize2 int
)clustered by(securityid) into 6 buckets
row format delimited fields terminated by ','
stored as textfile;
 
set hive.enforce.bucketing = true;
 
insert overwrite table total_bucket
select tradedate,securityid,bidsize1,bidsize2
from total;
 
select tradedate,securityid,sum(bidsize1+bidsize2) from total_bucket tablesample(bucket 2 out of 2 on securityid) group by tradedate,securityid;