Hadoop学习（十）——HiveQL查询

SELECT...FROM...语句

FROM子句标识从哪个表、视图或嵌套中选择记录

SELECT指定要保存的列及输出函数要调用的一个或多个列

# 查询employees表的name和salary字段内容
SELECT name, salary FROM employees;

SELECT e.name, e.salary FROM employees as e;

# subordinates列是一个数组
# 引用其数据元素（引用第一个元素的查询）
# 引用一个不存在的元素将会返回NULL
# 同时提取出的String类型的值将不会加引号
SELECT name, subordinates[0] FROM employees;

# 引用MAP元素
# deductions是MAP类型，使用键值形式
SELECT name, deductions["State Taxes"] FROM empoyees;

# struct 也是使用MAP形式
SELECT name, address.city FROM employees;

使用正则

# 使用正则表达式选择想要的列
# 从stocks中选择symbol列和所有列名以price作为前缀的列
SELECT symbol, `price.*` FROM stocks;

使用列值进行计算

# 查询，转换为大写的雇员姓名，雇员对应的薪水，需要缴纳的税收比，税后薪资
SELECT upper(name), salary, seductions["Federal Taxes"], round(salary * (1 - deductions["Federal Taxes"])) FROM employees;

算术运算符

+     -     *     、     %（求余）     &（按位与）    |（按位或）     ^（按位异或）   ~（按位取反）

使用函数

返回值类型	函数	描述
BIGINT	round()	返回一个double类型的最近整数
DOUBLE	round(double d, int n)	返回double类型，保留n位小数的近似值
BIGING	floor(DOUBLE d)	返回   的最大的BIGINT型值
BIGINT	ceil(DOUBLE d) ceiling(DOUBLE d)	返回  的最小的BIGINT型值
DOUBLE	rand() rand(INT seed)	每行返回一个double型随机数，整数seed是随机因子
DOUBLE	exp(DOUBLE d)	返回 e 的 d 次幂
DOUBLE	ln(DOUBLE d)	以自然对数为底的 d 的对数
DOUBLE	log10(DOUBLE d)	以10为底的 d 的对数
DOUBLE	log(DOUBLE base, DOUBLE d)	以base 为底的 d 的对数
DOUBLE	pow(DOUBLE d, DOUBLE p) power(DOUBLE d, DOUBLE p)	计算 d 的 p 次幂
DOUBLE	sqrt(DOUBLE d)	计算 d 的平方根
STRING	bin(DOUBLE i)	计算二进制值 i 的STRING类型值
STRING	hex(BIGINT i)	计算十六进制值 i 的STRING类型值
STRING	hex(STRING str)	计算十六进制
STRING	hex(BINARY b)	计算二进制表达值 b 的STRING类型值
STRING	unhex(STRING i)	hex(STRING str)的逆方法
STRING	conv(BIGINT num, INT from_base, INT to_base)	将BIGINT类型的num 从from_base转换成to_base进制
DOUBLE	abs(DOUBLE d)	计算DOUBLE型值 d 的绝对值
INT	pmod(INT i1,INT i2)	i1对 i2 取模
DOUBLE	pmod(DOUBLE d1, DOUBLE d2)	d1对d2取模
DOUBLE	sin(DOUBLE d)	返回正弦值
DOUBLE	defress(DOUBLE d)	转换成角度值
DOUBLE	radians(DOUBEL d)	转换成弧度值
INT	positive(INT i )	返回 i
INT	negative(INT i)	返回 i 的负数
FLOAT	sign(DOUBLE d)	d 正数，返回1.0；负数返回-1.0，否则返回0.0
DOUBLE	e()	数学常数e ，超越数
DOUBLE	pi()	圆周率 pi

聚合函数

count        计算有多少行数据

avf                返回指定列的平均值

【加上DISTINCT，计算的都是排重后的值】

# 示例
SELECT count(*), avg(salary) FROM employees;

返回的值类型	函数	描述
BIGINT	count(*)	计算总行数
DOUBLE	sum(col) sum(DISTINCT col)	计算（排重）值的和
DOUBLE	avg(col) avg(DISTINCT col)	计算均值
DOUBLE	min(col) max(col)	计算指定行最小值、最大值
DOUBLE	variance(col) var_pop(col)	返回集合col中一组数值的方差
DOUBLE	var_samp(col)	返回集合col中的一组数值样本方差
DOUBLE	stddev_pop(col)	返回一组数值的标准偏差
DOUBLE	covar_pop(col1,col2)	返回一组数值的协方差
DOUBLE	covar_samp(col1,col2)	返回一组数值的样本协方差
DOUBLE	corr(col1,col2)	返回两组数值的相关系数
DOUBLE	percentile(BIGINT int_expr,p)	int_expr在p（范围[0:1]）处对应的百分比
ARRAY<DOUBLE>	percentile(BIGINT int_expr, ARRAY(P1[,P2]...))	int_expr在p（DOUBLE型数组，范围[0:1]）处对应的百分比
DOUBLE	percentile_approx(DOUBLE col,p[,NB])	col在p处对应的百分比，NB是用于估计直方图中的仓库数量
ARRAY<STRUCT{'x', 'y'}>	histgram_numeric(col,NB)	返回NB数量的直方图仓库数组，返回结果中的值x是中心，值y 是仓库的高
ARRAY	collect_set(col)	返回集合col元素排重后的数组

【通常，可以将属性hive.map.aggr的值设置为true来提高聚合的性能】

表生成函数

可以将单列扩展为多列或多行

# 将employees表中每行记录中的subordinates字段内容转换为0个或多个新的记录行，若某行雇员记录该字段为空，则不产生新的记录；不为空，则每个元素都产生一行新纪录。
SELECT explod(subordinates) AS sub FROM employees;

SELECT parse_url_tuple(url, 'HOSt', 'PATH', 'QUERY') as (host, path, query)
FROM url_table;

表生成函数

返回值类型	函数	描述
N行结果	explode(ARRAY array)	返回0到多行结果，每行都对应输入的array数组中的一个元素
N行结果	explode(MAP map)	返回0到多行结果，每行对应每个map键值对，一个字段是map的键，另一个字段对应map值
TUPLE	json_tuple(STRING jsonStr,p1,p2,...,pn)	接受多个标签名称，对输入的JSON字符串进行处理，一次调用就能获得多个键值
TUPLE	parse_url_tuple(url, partname1, partname2,...,partnameN)	从URL解析出N个部分信息
N行结果	stack(INT n, col1,...,colM)	把M列转换成N行，每行有M/N个字段

其他内置函数

 

 

 

LIMIT语句 

限制典型查询返回的行数

# 查询，转换为大写的雇员姓名，雇员对应的薪水，需要缴纳的税收比，税后薪资
# 返回两条数据
SELECT upper(name), salary, seductions["Federal Taxes"], round(salary * (1 - deductions["Federal Taxes"])) FROM employees
LIMIT 2;

设置列别名

# 查询，转换为大写的雇员姓名，雇员对应的薪水，需要缴纳的税收比，税后薪资
SELECT upper(name), salary, seductions["Federal Taxes"] as fed_taxes, 
round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
FROM employees;

 嵌套使用SELECT语句

# 给前面的结果集起了名字，叫e 
FROM (
    SELECT upper(name), salary, seductions["Federal Taxes"] as fed_taxes,
    round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    FROM employees) e
SELECT e.name, e.salary_minus_fed_taxes
WHERE e.salary_minus_fed_taxes > 70000;

CASE...WHEN...THEN语句

类似if条件语句，用于处理单个列的查询结果

SELECT name, salary,
CASE
    WHEN salary < 50000.0 THEN 'low'
    WHEN salary >= 50000.0 AND salary <= 70000.0 THEN 'middle'
    WHEN salary >= 70000.0 AND salary <= 100000.0 THEN 'high'
    ELSE 'very high'
END AS bracket FROM employees;

WHERE 语句

过滤条件

使用谓词表达式，用 AND 或 OR 连接

不能在WHERE子句中使用列别名

但可以嵌套一个SELECT查询进行使用

# 错误
SELECT upper(name), salary, seductions["Federal Taxes"] as fed_taxes,
    round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    FROM employees
    WHERE round(salary_minus_fed_taxes) > 70000;


# 正确
FROM (
    SELECT upper(name), salary, seductions["Federal Taxes"] as fed_taxes,
    round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    FROM employees) e
SELECT e.name, e.salary_minus_fed_taxes
WHERE e.salary_minus_fed_taxes > 70000;

# 正确2
SELECT e.* FROM 
    (SELECT upper(name), salary, seductions["Federal Taxes"] as fed_taxes,
    round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    FROM employees) e
    WHERE round(e.salary_minus_fed_taxes) > 70000;

谓词操作符

操作符	支持的数据类型	描述
A=B	基本数据类型	若A=B，返回True
A<=>B	基本数据类型	若A和B都为NULL，返回TRUE
A==B	没有	SQL中使用=，不使用==
A<>B A!=B	基本数据类型	A或B为NULL，返回NULL A和B不相等返回True
A<B	基本数据类型	A或B为NULL，返回NULL A 小于B返回True
A <= B	基本数据类型	A或B为NULL，返回NULL A 小于等于B
A > B	基本数据类型	A或B为NULL，返回NULL
A>=B	基本数据类型	A或B为NULL，返回NULL
A [NOT] BETWEEN B AND C	基本数据类型	A、B、C任一为NULL，返回NULL
A IS [NOT] NULL	所有数据类型
A [NOT] LIKE B	STRING	B是一个SQL下的简单正则表达式
A  RLIKE  B A  REGEXP  B	STRING	B是一个正则表达式

关于浮点数的比较

从TEXTFILE文本文件中读取数据的话：表模式中对应字段类型定义为DOUBLE

使用cast操作符，将类型显式指定为FLOAT。   cast(0.2   AS  FLOAT)

LIKE和RLIKE

LIKE后面跟着的是简单的正则匹配，有三种格式

%xxx        匹配以xxx结尾的

xxx%        匹配以xxx开头的

%xxx%     匹配包含xxx的

RLIKE后面跟着的是类似Python中的正则匹配，用到.  *  |等符号

# 使用多个LIKE子句进行过滤
SELECT name, address FORM employees WHERE address.street LIKE '%Chicago%' OR address.street LIKE '%Ontario%';

GROUP BY 子句

通常和聚合函数一起使用，按照一个或多个列对结果进行分组，然后对每个组执行聚合操作

# 按照苹果公司股票年份对股票记录进行分组，然后计算每年平均收盘价
SELECT year(ymd), avg(price_close) FROM stocks WHERE exchange='NASDAQ' AND symbol='AAPL'
GROUP BY year(ymd);

HAVING语句

# 按照苹果公司股票年份对股票记录进行分组，然后计算每年平均收盘价
SELECT year(ymd), avg(price_close) FROM stocks WHERE exchange='NASDAQ' AND symbol='AAPL'
GROUP BY year(ymd);



# 限制输出结果中年平均收盘价要大于$50
SELECT year(ymd), avg(price_close) FROM stocks WHERE exchange='NASDAQ' AND symbol='AAPL'
GROUP BY year(ymd)
HAVING avg(price_close) > 50;

JOIN语句

【Hive不支持在ON子句中的谓词之间使用OR，不能实现非等值连接】

内连接（INNER JOIN），只有进行连接的两个表中都存在与连接标准相互匹配的数据才会被保留下来；

# 连接条件是ymd字段相等，也称为链接关键字
# ON 子句指定连接的条件
SELECT a.ymd, a.price_close, b.price_close
FROM stocks a JOIN stocks b ON a.ymd = b.ymd
WHERE a.symbol='APPL' AND b.symbol='IBM';

# 字段ymd和字段symbol作为等值连接键的内连接（INNER JOIN）
SESLECT s.ymd, s.symbol, s.pricr_close, d.diivdend
FROM stock s 
JOIN dicidends d 
ON s.ymd=d.ymd AND s.symbol=d.symbol
WHERE s.symbol='APPL';

# 3个表做内连接
SELECT a.ymd, a.price_close, b.price_close, c.price_close
FROM stocks a JOIN stocks b ON a.ymd=b.ymd JOIN stocks c ON a.ymd=c.ymd
WHERE a.symbol='APPL' AND b.symbol='IBM' AND c.symbol='GE';

JOIN优化

3个以上的表进行JOIN连接时，若每个ON子句都使用相同的连接 键，则只会产生一个MapReduce job

Hive假定最后的表是最大的表，写查询时，从左到右表的大小依次增大

# "标记"机制，告诉查询器哪个表是大表
SELECT /*+STREAMTABLE(s)*/ s.ymd, s.symbol,s.price_close, d.dividend
FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
WHERE s.symbol='APPLE';

LEFT OUTER JOIN

左外连接通过关键字LEFTOUTER进行标识

JOIN操作符左边表中符合WHERE子句的所有记录将被返回，右边表中没有符合条件的记录时，指定的选择列会是NULL

# 字段ymd和字段symbol作为等值连接键的内连接（LEFT OUTER JOIN）
SESLECT s.ymd, s.symbol, s.pricr_close, d.diivdend
FROM stock s 
LEFT OUTER JOIN dicidends d 
ON s.ymd=d.ymd AND s.symbol=d.symbol
WHERE s.symbol='APPL';

OUTER JOIN

SESLECT s.ymd, s.symbol, s.pricr_close, d.diivdend
FROM stock s 
LEFT OUTER JOIN dicidends d 
ON s.ymd=d.ymd AND s.symbol=d.symbol
WHERE s.symbol='APPL' and s.exchange='NASDAQ';

【WHERE语句在连接操作执行之后才会执行，因此WHERE语句只用于过滤那些非NULL值的列值

ON语句中的分区过滤条件在外连接中是无效的】

RIGHT OUTER JOIN

返回右边表中所有符合WHERE语句的记录，左表中匹配不上的用NULL代替

SESLECT s.ymd, s.symbol, s.pricr_close, d.diivdend
FROM dicidends d
RIGHT OUTER JOIN stock s 
ON d.ymd=s.ymd AND d.symbol=s.symbol
WHERE s.symbol='APPL';

FULL OUTER JOIN

返回所有表中符合WHERE语句的所有记录

SESLECT s.ymd, s.symbol, s.pricr_close, d.diivdend
FROM dicidends d
FULL OUTER JOIN stock s 
ON d.ymd=s.ymd AND d.symbol=s.symbol
WHERE s.symbol='APPL';

LEFT SEMI-JOIN

左半开连接，返回左边表的记录

不支持右半开连接

对左表中一条指定的记录，右表中一旦匹配，就立即停止扫描

笛卡尔积JOIN

表示左边表的行数乘以右边表的行数等于笛卡尔积结果集的大小

map-side JOIN

只有一张表是小表的时候，在最大的表通过mapper时将小表完全放到内存中

或是设置hive.suto.convert.JOIN的属性值，默认为false

右外连接和全外连接不支持这个优化

SELECT /*+MAPJOIN(d)*/s.ymd, s.symbol,s.price_close, d.dividend
FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
WHERE s.symbol='APPLE';

ORDER BY和SORT BY

ASC升序，DESC降序

# 集体，耗时多
SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
ORDER BY s.ymd ASC, s.symbol DESC;

# 分布，多个reducer
SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
SORT BY s.ymd ASC, s.symbol DESC;

含有SORT BY 的 DISTRIBUTE BY

DISTRIBUTE BY控制map的输出在reducer中如何划分

写在ORDER BY 之前

SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
DISTRIBUTE BY s.symbol
SORT BY s.ymd ASC, s.symbol DESC;

CLUSTER BY

# DISTRIBUTE BY 语句和DORT BY涉及的语句如果完全相同，就可以用以下进行简化
SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
CLUSTER BY s.symbol;

类型转换

浮点数——>整数：使用round()或floor()函数，而非cast操作符

嵌套转换

# b的类型是BINARY，但b的值是数值
# 否则只能转换为STRING
SELECT (2.0*cast(cast(b as string) as double)) from src;

抽样查询

假设numbers表只有number字段，值是1-10

使用rand函数抽样，返回一个随机值

分桶语句中，分母表示数据将会被散列的桶的个数，分子表示将会选择的桶的个数

SELECT * FROM numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;

数据块抽样

基于行数，按照输入路径下的数据块百分比进行抽样

最小抽样单元是一个128MB的普通的HDFS的块

SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;

UNION ALL

将两个或多个表进行合并

每个union子查询需要有相同的列，对应的每个字段的字段类型必须一致