先看下官网的查询语法:
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
WHERE :查询条件,加分区条件可以值过滤对应分区的文件数据提高效率;
GROUP BY:分组,后面可以跟HAVING 条件
ORDER BY:全局排序,所以最终会只用一个reduce task来完成;
SORT BY: 分区内排序,就是在每个reduce task内排序,如果设置reduce task个数是1:set mapred.reduce.tasks=1,那么结果和ORDER BY一样。
DISTRIBUTE BY:指定map处理后数据分配reduce的方式,功能和mapreduce的partitioner接口一样;
CLUSTER BY:等于DISTRIBUTE BY+SORT BY
子查询:
子查询和标准SQL中的子查询语法和用法基本一致,需要注意的是,Hive中如果是从一个子查询进行SELECT查询,那么子查询必须设置一个别名。
SELECT col
FROM (
SELECT a+b AS col
FROM t1
) t2
从Hive0.13开始,在WHERE子句中也支持子查询,比如:
SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)
将子查询作为一个表的语法,叫做Common Table Expression(CTE):
with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select * from q1 union all select * from q2;
with q1 as ( select key, value from src where key = '5')
from q1
insert overwrite table s1
select *;