Hive的基本使用2

查询

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

1、基本查询

全表查询，select * from emp;

特定列查询， select empno, ename from emp;

列别名，select ename AS name, deptno dn from emp;

使用算术运算符，select sal +1 from emp;

常用函数：

1）求总行数（count），select count(*) cnt from emp;

2）求工资的最值（max，min），select max(sal) max_sal from emp;

3)  求工资的总和（sum）, select sum(sal) sum_sal from emp;

4)  求工资的平均值（avg）,select avg(sal) avg_sal from emp;

LIMIT子句用于限制返回的行数，select * from emp limit 5;

2、where语句

进行条件过滤，紧随from后面。

1）比较运算符的使用：

A [NOT] BETWEEN B AND C、A IS NULL、IN(数值1, 数值2)、A [NOT] LIKE B、A RLIKE B, A REGEXP B

2）Like的使用

使用like运算选择类似的值，% 代表零个或多个字符(任意个字符)，_ 代表一个字符，RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

3）逻辑运算符的使用（And/Or/Not）

3、分组

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

Having语句：

having与where不同点

（1）where后面不能写分组函数，而having后面可以使用分组函数。

（2）having只用于group by分组统计语句。

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

4、join语句

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

1）内连接

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

2）左外连接

JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

3）右外连接

JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

4）满外连接

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

5）多表连接

连接 n个表，至少需要n-1个连接条件。Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对前两个进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表3进行连接操作。

注意：因为Hive总是按照从左到右的顺序执行的。

优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

6）笛卡尔积

产生条件：（1）省略连接条件（2）连接条件无效（3）所有表中的所有行互相连接

select empno, dname from emp, dept;

7）连接谓词中不支持or

5、排序

1）全局排序（Order By）->只有一个reducer

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

ORDER BY 子句在SELECT语句的结尾, select * from emp order by sal desc;

2)按照别名排序

select ename, sal*2 twosal from emp order by twosal;

3）多个列排序

select ename, deptno, sal from emp order by deptno, sal ;

4）每个MapReduce内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。

Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

5）分区排序（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。(表示分区字段和排序字段不是同一个)

Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

6) Cluster By

当distribute by和sorts by字段相同时，可以使用cluster by方式。

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

6、分桶及抽样查询

对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分区针对的是数据的存储路径；分桶针对的是数据文件。

分桶规则：

根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

7、其他常用查询函数

1）空字段赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。

如果员工的comm为NULL，则用-1代替：select comm, nvl(comm，-1) from emp;

2）CASE WHEN

求出不同部门男女各多少人

select

dept_id,

sum(case sex when '男' then 1 else 0 end) male_count,

sum(case sex when '女' then 1 else 0 end) female_count

from emp_sex group by dept_id;

3）行转列

多个列可以合并到一列

CONCAT、CONCAT_WS、COLLECT_SET(col)--进行聚合

4）列转行

EXPLODE(col)---炸裂、LATERAL VIEW--侧写，跟原来表进行关联，不然容易产生笛卡尔积

5）窗口函数

窗口函数over()使得聚合函数sum()可以在限定的窗口中进行聚合

窗口函数又分为以下三类： 聚合型窗口函数、 分析型窗口函数 （比如三个排序）、取值型窗口函数（前后几行）

over()一般放在聚合函数后面制定窗口大小，开窗：对聚合函数这个参数之外的所有数据开了一个窗口，这个窗口就是数据集，对每一个数据集都开一个窗口，是被聚合参数使用的。

当ORDER BY后面缺少窗口从句条件，窗口规范默认是:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

当ORDER BY和窗口从句都缺失，窗口规范默认是(全部):

ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

注意：窗口函数over()和group by 的最大区别，在于group by之后其余列也必须按照此分区进行计算（即只产生一个结果），而over()函数使得单个特征可以进行分区（相同的分组可以产生多个结果）。

6）Rank

ROW_NUMBER()返回的是一列连续的序号。

RANK()对于数值相同的这一项会标记为相同的序号，而下一个序号跳过。

DENSE_RANK()对于数值相同的这一项，也会标记为相同的序号，但下一个序号并不会跳过。

函数

1、系统内置函数

显示系统自带的函数，show functions;

显示自带的函数的用法，desc function upper;

详细显示自带的函数的用法，desc function extended upper;

2、自定义函数UDF

三种：UDF->一进一出，UDAF->类似于聚集函数（count ，max），多近一出，UDTF->一进多出

（1）继承org.apache.hadoop.hive.ql.exec.UDF

（2）需要实现evaluate函数；evaluate函数支持重载；

（3）在hive的命令行窗口创建函数

a）添加jar

b）创建function

hive文件存储格式

Hive支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。

1）列式存储和行式存储

TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；

ORC和PARQUET是基于列式存储的。

存储文件的压缩比总结：ORC >  Parquet >  textFile

存储文件的查询速度总结：查询速度相近

存储方式和压缩总结

在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy，lzo。

调优