HIVE总是按照从左到右的顺序执行的。
嵌套SELECT 语句会按照要求执行“下推”过程,在数据进行连接操作之前会先进行分区过滤。
SELECT 和WHERE 语句中不能引用到右边表中的字段。

1、select....from语句

select是SQL中的射影算子。From子句标识了从哪个表、视图、嵌套查询中选择记录。
SELECT:指定了要保存的列以及输出函数需要调用的一个或多个列。

2、使用正则表达式来指定列

比如例子一表stocks中选择symbol列和所有列名以price为前缀的列:
SELECT symbol,'price.*' FROM stocks;

3、使用列值进行计算

用户不但可以选择表中的列,还可以使用函数调用和算术表达式操作列值。

employees表查询:
SELECT upper(name),salary,deductions["Federal Taxes"],round(salary * (1- deductions["Federal Taxes"])) FROM employees;

4.聚合函数

可以通过设置属性hive.map.aggr值为true来提高聚合的性能。
SET hive.map.aggr=ture;
查询employees表有多少员工,以及他们的平均薪水:SELECT count(*),avg(salary) FROM employees;
SELECT count(DISTINCT symbol) FROM stocks;
SELECT count(DISTINCT ymd),count(DISTINCT volume) FROM stocks;

5.表生成函数

与聚合函数相反就是表生成函数,其可以将单列扩展成多列或者多行。

SELECT explode(subordinates) AS sub 
FROM employees;

6.LIMIT语句

典型的查询会返回多行数据,limit子句用于限制返回的函数。

SELECT upper(name),salary,deductions["Federal Taxes"],round(salary * (1- deductions["Federal Taxes"])) 

FROM employees LIMIT 2;

7.列别名

列别名:返回一个由新列组成的新的关系。

SELECT upper(name),salary,deductions["Federal Taxes"] as fed_taxes,round(salary * (1- deductions["Federal Taxes"])) as salary_minus_fed_taxes 

FROM employees;

8.嵌套SELECT 语句

SELECT e.name,e.salary_minus_fed_taxes

FROM (SELECT upper(name),salary,deductions["Federal Taxes"] as fed_taxes,round(salary * (1- deductions["Federal Taxes"])) as salary_minus_fed_taxes FROM employees) e

 WHERE e.salary_minus_fed_taxes  >70000;

#9.CASE.....WHEN....THEN句式

CASE...WHEN...THEN语句和fi条件语句类似,用于处理单个列的查询结果。

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;

#10.什么情况下hive可以避免进行MapReduce

本地模式执行其他的操作:`set hive.exec.mode.local.auto=true;`

SELECT *

FROM employees

WHERE coutry ='US' AND state='CA' LIMIT 100;`

#11.WHERE 语句

SELECT 语句用于选取字段,WHERE语句用于过滤条件,两者结合使用可以查找到符合过滤条件的记录。

#12.LIKE和RLIKE

SELECT *

FROM employees

WHERE address.street LIKE '%Ave.';

从employees 表中查找所有地址的街道名称中含有单词Chicago或Ontario的信息

`SELECT * FROM employees WHERE address.street RLIKE '.*(Chicago| Ontario).*';`

等价于

`SELECT * FROM employees WHERE address.street LIKE '%Chicago%' OR  address.street LIKE '%Ontario%';`

其中关键字RLIKE后面的字符串含义:字符串中的点号(.)表示和任意的字符匹配,星号(*)表示重复左边的字符串(为点号)零次到无数次。

#13.JOIN语句

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

INNER JOIN:内连接,只有进行连接的两个表中都存在与连接标准相匹配的数据才会保留下来。

ON:ON子句指定了两个表间数据进行连接的条件。

WHERE子句:限制了左边表是AAPL的记录,右边表是IBM的记录。

SELECT a.ymd,a. ,b.price_close

FROM stocks a JOIN stocks b

ON a.ymd=b.ymd

WHERE a.symbol='AAPL' AND b.symbol='IBM';

#14.LEFT OUTER JOIN

LEFT OUTER JOIN左外连接通过关键字LEFT OUTER进行标识

在这种JOIN连接操作中,JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。JOIN操作符右边表中如果没有符合ON后面连接条件的记录时,那么从右边表指定选择的列的值将会是NULL。

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'

#15.RIGHT OUTER JOIN

右外连接会返回右边表所有符合WHERE语句的记录。左表中匹配不上的字段值用NULL代替。

SELECT s.ymd,s.symbol,s.price_close,d.dividend

FROM stocks s RIGHT OUTER JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol

WHERE s.symbol = 'AAPL'

#16.FULL OUTER JOIN

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

SELECT s.ymd,s.symbol,s.price_close,d.dividend

FROM stocks s FULL OUTER JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol

WHERE s.symbol = 'AAPL'

#17.LEFT SEMI-JOIN(hive不支持右半开连接)

左半开连接(LEFT SEMI-JOIN)会返回左边表的记录,前提是其记录对于右边表满足ON语句中的判定条件。

MYSQL语句,Hive中不支持这个操作

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);

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;