Hive基本查询操作

第1关：where操作

编程要求

查询出工作职责涉及

hive

的并且工资大于

8000

的公司名称以及工作经验。（其中库名：

db1

，表名：

table1

）

student

表结构：
INFOTYPE

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

表结构：
INFOTYPE

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

结构：
INFOTYPE

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_namedata_typecomment

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时间类型CHARVARCHARDATE
2.复杂数据类型
数据类型描述STRUCT通过“.”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, lastSTRING},那么第1个元素可以通过字段.first来引用。MAPMAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是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_namedata_typecomment

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_namedata_typecomment

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;