关于数据仓库hive知识的学习(2)

第6章 SQL快速掌握

6.1 sql的运算模型 – 逐行运算模型

逐行运算：select后的运算表达式，是对每一行独立运算

表：一个数据的集合（集合中每一行就是一条数据：记录）

select：
    对一条数据的运算逻辑
    -- 常量 :"ok" ,10 ,8.9
    -- 变量 : id , name ,  age,
    -- 运算符表达式：  age+10,  id+8 , id>8  , id>2 and id<9
    -- 函数表达式：  upper(name) , greatest(s1,s2,s3) 
    -- 复合表达式：  greatest(s1,s2,s3)>2 , greatest(s1,s2,s3)+10 , lower(substr(upper(name),0,3))

from ：选择一个运算的数据集

join：准备数据集（它可以将多个数据集拼成一个数据集，拼的时候可以带条件）
join的拼接有多种方式：
t1 join t2  --> 笛卡尔积,结果的总行数的t1的行数*t2的行数；
t1 join t2 on t1.id=t2.id  -->内连接，满足拼接条件的才拼接..
t1 left join t2 on t1.id=t2.id --> 左（外）连接 left outer join ;左表所有行都保留，连接不上的右表字段为null
t1 right join t2 on t1.id=t2.id -->右（外）连接 right outer join;右表的所有行都保留，连接不上的左表字段为null
t1 full join t2  on t1.id=t2.id -->全（外）连接 full outer join;左、右表的行都保留，拼不上的字段为null
-- hive不支持不等值join
-- hive中有一种特别的join
    left semi join  -->左半连接，是sql中in子句的一个变种实现！
    hive1.x中不支持in，现在的新版本hive支持in子句

where：逐行过滤将要运算的数据集,where执行在select之前
   -- where id>2
   -- where (id+10)>2
   -- where upper(name) = 'ZHANGSAN'
   -- where id in (select id from t_x)

group by：
函数：类似java代码中的方法，接收变量，返回结果，也就是一个表达式
having：
紧跟group by之后，是对分组后的数据进行按组过滤，将不满足条件表达式的组去除；

6.2 sql运算模型–分组聚合运算

– 分组运算模型中，select后面的表达式只能有如下情形：

常量
分组key
聚合函数

select语句中非分组函数的字段必须声明在GORUP BY中反之，GROUP BY中声明的字段可以不出现在select语句中

分组key可以有多个，分组key越多，分出来的组也会越多

分组聚合还可以采用 partition by

6.3 sql运算模型–开窗运算（窗口分析运算模型）

– 可以用窗口分析函数：row_number() over() 来实现

row_number() over(partition by sex order by salary desc) as rn

第7章 HQL查询语法详解

[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]
  ]

7.1 with…as…临时表语法

示例：

with o as (
select
id,m,sum(sale) as amt
from t_sale
group by id,m
)
select * from o where o.amt>80;

7.2 排序

7.2.1 全局排序（Order By）

全局排序，强制只有一个reduce

7.2.2 Sort By

每个task内部进行排序，对全局结果集来说不是排序

要设置reduce个数：set mapreduce.job.reduces=3;

当设置的reduce为1时，结果与Order By一样

7.2.3 分桶排序1（Distribute By + Sort By）

Distribute By：类似MR中partition，进行分区，可以结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

示例：先按照部门编号分区，再按照员工编号降序排序

set mapreduce.job.reduces=3;
select * from emp distribute by 部门编号 sort by 员工编号 desc;

7.2.4 分桶排序2（Cluster By）

当distribute by和sorts by字段相同时，可以使用cluster by代替

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。