HiveQL：查询

最新推荐文章于 2023-06-01 13:05:41 发布

weixin_33725239

最新推荐文章于 2023-06-01 13:05:41 发布

阅读量108

点赞数

文章标签：大数据 python java

原文链接：https://my.oschina.net/lonelycode/blog/835751

版权

2019独角兽企业重金招聘Python工程师标准>>>

SELECT...FROM语句

select是SQL中的射影算子，from子句标识了从哪个表、视图或嵌套查询中选择记录：

hive> select name,salary from employees;

也可以写成，两者是等价的，但是下面这种写法在涉及到多个不同的表时会很有用：

hive> select e.name,e.salary from employees e;

可以使用列值来进行计算（比如现在要打印雇员的全大写名字，对应的薪水，需要缴纳的税收以及扣除税收后的税后薪资）：

hive> select upper(name),salary,deductions['Taxes'],
round(salary*(1-deductions['Taxes'])) from employees;

Hive支持所有典型的算数运算符，从加减乘除到求余（%）取与（&）取或（|）取亦或（^）取反（~）都支持。

此外，Hive还有内置的数学函数，比如求平方根的sqrt(double d)，求模的pmod(int i1,int i2)，还有求sin，cos，tan等等的函数，在此不一样列举。具体的函数可以查看我的另一篇博客：

https://my.oschina.net/lonelycode/blog/835411

Limit语句

limit子句用来限制返回的行数：

hive> select upper(name),salary,deductions['Taxes'],
round(salary*(1-deductions['Taxes'])) from employees
limit 2;

列别名

前面的示例查询语句可以认为是返回一个由新列组成的新的关系，其中有些新产生的结果列对于表employees来说是不存在的，通常有必要给这些新产生的列起一个名称，也就是别名：

hive> select upper(name),salary,deductions['Taxes'],
round(salary*(1-deductions['Taxes'])) as salary_minus_taxes
from employees limit 2;

嵌套SELECT语句

从下面的嵌套查询语句可以看出，我们给前面的结果集起了一个别名 e，在这个语句外面嵌套了name和salary_minus_taxes两个字段，同时约束后者的值要大于70000。

hive> from(
select upper(name),salary,deductions["Taxes"] as taxes,
round(salary*(1-deductions["Taxes"])) as salary_minus_taxes
from employees
) e
select e.name,e.salary_minus_taxes
where e.salary_minus_taxes > 70000;

CASE...WHEN...THEN句式

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

hive> 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语句

select语句用于选取字段，where语句用于过滤条件，两者结合使用可以查找到符合过滤条件的记录。前面已经用过很多次了，这里就不举例子了。

LIKE和RLIKE

like是个标准的SQL的操作符，它可以让我们通过字符串的开头或结尾，以及制定特定的子字符串，或当子字符串出现在字符串内的任何位置时进行匹配：

假设like语句是这样的 A [NOT] like B :

B的表达式说明如下：'x%'表示A必须以字母‘x’开头，‘%x’表示A必须以字母x结尾，而‘%x%’表示A包含有字母‘x’，可以位于开头，结尾或者字符串中间。类似地，下划线‘_’匹配单个字符。B必须要和整个字符串A相匹配才行。

以下的语句，第一条表示匹配以Ave.结尾的street，第二条匹配O开头的city，第三条匹配street中含有Chi字符串的street。

hive> select name,address.street from employees where address.street like '%Ave.';

hive> select name,address.city from employees where address.city like 'O%';

hive> select name,address.street from employees where address.street like '%Chi%';

rlike子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件：

hive> select name,address.street 
from employees where address.street rlike '.*(Chicago|Ontario).*';

上面的例子中点号（.）表示和任意的字符串匹配，星号（*）表示重复“左边的字符串”零次到无数次（上面的例子，左边的字符串指的是点号），加起来的意思和下面的例子一样：

hive> select name,address from employees
where address.street like '%Chicago%' or address.street like '%Ontario%';

GROUP BY语句

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

hive> select year(ymd),avg(price_close) from stocks
where exchange='NASSAAQ' and symbol='AAPL'
group by year(ymd);

HAVING语句

having子句允许用户通过一个简单的语法完成原本需要通过子查询才能对group by语句产生的分组进行条件过滤的任务：

hive> select year(ymd),avg(price_close) from stocks
where exchange='NASDAQ' and symbol='AAPL'
group by year(ymd)
having avg(price_close)>50.0;

JOIN语句

Hive支持通常的SQL JOIN语句，但是只支持等值连接。

INNER JOIN（内连接）

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

hive> select a.ymd,a.price_close,b.price_close
from stocks a join stocks b on a.ymd=b.ymd
where a.symbol='AAPL' and b.symbol='IBM';

OUTER JOIN（外连接）

LEFT OUTER JOIN（左外连接）

在这种join连接操作中，join操作符左边表中符合where子句的所有记录将会被返回，右边表如果没有符合on后面连接条件的记录则返回NULL：

hive> select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s left outer join dividends d on s.ymd=d.ymd and s.symbol=d.symbol
where s.symbol='AAPL';

RIGHT OUTER JOIN（右外连接）

右外连接刚好和左外连接反过来，join操作符右边表符合where子句的所有记录将会被返回，左边表如果没有符合on后面连接条件的记录则会返回NULL：

FULL OUTER JOIN（完全外连接）

完全外连接将会返回所有表中符合where语句条件的所有记录，如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代：

hive> select s.ymd,s.symbol,s.price_close,d.dividend
from dividends d full outer join stocks s on d.ymd=s.ymd and d.symbol=s.symbol
where s.symbol='AAPL';

LEFT SEMI-JOIN（左半开连接）

左半开连接会返回左边表的记录，前提是其记录对于右边表满足on语句中的判定条件。以下是一条sql语句，在Hive中是不支持的：

mysql> select s.ymd,s.symbol,s.price_close from stocks s
where s.ymd,s.symbol in 
select(d.ymd,d.symbol from dividends d);

不过我们可以用下面的left semi-join语法来达到同样的目的：

hive> select s.ymd,s.symbol,s.price_close
from stocks s left semi join dividends d on s.ymd=d.ymd and s.symbol=d.symbol;

笛卡尔积JOIN

笛卡尔积是一种连接，表示左边表的行数乘以右边表的行数等于笛卡尔结果集的大小（也就是说如果左边有5行数据，右边有6行数据，那么产生的结果将会是30行数据）：

hive> select * from stocks join dividends;

map-side JOIN

如果所有表中只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中。Hive可以在map端执行连接过程（称为map-side JOIN），这是因为Hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需要的reduce过程。

ORDER BY和SORT BY

Hive中的order by和mysql的定义是一样的，其会对查询结果集执行一个全局排序：

hive> select s.ymd,s.symbol,s.price_close
from stocks s
order by s.ymd ASC,s.symbol DESC;

Hive中增加了一个可供选择的方式，也就是sort by，其只会在每个reducer中对数据进行排序，也就是执行一个局部排序过程。这可以保证每个reducer的输出数据都是有序的，这样可以提高后面进行的全局排序的效率：

hive> select s.ymd,s.symbol,s.price_close
from stocks s
sort by s.ymd,s.symbol desc;

含有SORT BY的DISTRIBUTE BY

distribute by控制map的输出在reducer中是如何划分的。

假设我们希望具有相同symbol的数据在一起处理。那么我们可以使用distribute by来保证相同的symbol会分发到同一个reducer中进行处理，然后使用sort by来按照我们的期望进行排序：

hive> select s.ymd,s.symbol,s.price_close
from stocks s
distribute by s.symbol
sort by s.symbol ASC,s.ymd ASC;

CLUSTER BY

在前面的例子里，s.symbol列被用在了distribute by语句中，而s.symbol列和s.ymd位于SORT BY语句中。如果这两个语句中涉及到的列完全相同，而且采用的是升序排序方式，那么就可以直接使用cluster by，它相当于前面两个句子的简写方式：

hive> select s.ymd,s.symbol,s.price_close
from stocks s
cluster by s.symbol;

类型转换

用case关键字可以进行类型转换，转换的语法是cast(value as type)：

hive> select name,salary from employees
where cast(salary as float)<100000.0;

抽样查询

Hive可以对表进行分桶抽样

假设numbers表只有number字段，其值为1到10：

hive> select * from numbers tablesample(bucket 3 out of 10 on rand()) s;

UNION ALL

union all 可以将两个或多个表进行合并。每一个union子查询都必须具有相同的列，而且对应的每个字段的字段类型必须是一致的：

hive> select log.ymd,log.level,log.message
from(
select l1.ymd,l1.level,
l1.message,'Log1' as source
from log1 l1
union all
select l2.ymd,l2.level,
l2.message,'Log2' as source
from log1 l2
) log
sort by log.ymd ASC;

转载于:https://my.oschina.net/lonelycode/blog/835751