Hive分桶、动态分区、查询汇总、窗口函详解、视图索引、运行方式

最新推荐文章于 2024-04-03 18:00:35 发布

原创

最新推荐文章于 2024-04-03 18:00:35 发布 · 3.1k 阅读

本文详细介绍了Hive的各种操作，包括参数设置、基本查询、动态分区、分桶查询、其他常用查询（如NVL、CASE WHEN、窗口函数等）以及视图和索引的使用。通过实例展示了如何进行数据导入、分组聚合、Join操作、排序和分区，同时也探讨了Hive的运行方式，如CLI、脚本和JDBC。

Hive分桶、动态分区、查询、视图索引

一、Hive参数

hive当中的参数、变量，都是以命名空间开头

通过${}方式进行引用，其中system、env下的变量必须以前缀开头

命名空间	读写权限	含义
hiveconf	可读写	hive-site.xml当中的各配置变量例：hive --hiveconf hive.cli.print.header=true
system	可读写	系统变量，包含JVM运行参数等例：system:user.name=root
env	只读	环境变量例：env:JAVA_HOME
hivevar	可读写	例：hive -d val=key

Hive 参数设置方式

1、修改配置文件 ${HIVE_HOME}/conf/hive-site.xml

2、启动hive cli时，通过–hiveconf key=value的方式进行设置

例：hive --hiveconf hive.cli.print.header=true

3、进入cli之后，通过使用set命令设置

在hive CLI控制台可以通过set对hive中的参数进行查询、设置

set设置： set hive.cli.print.header=true;

set查看： set hive.cli.print.header

hive参数初始化配置

当前用户家目录下的.hiverc文件如: ~/.hiverc

如果没有，可直接创建该文件，将需要设置的参数写到该文件中，hive启动运行时，会加载改文件中的配置。

hive历史操作命令集 ~/.hivehistory

二、查询

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;

注意：

1）SQL 语言大小写不敏感。

2）SQL 可以写在一行或者多行

3）关键字不能被缩写也不能分行

4）各子句一般要分行写。

5）使用缩进提高语句的可读性

列别名紧跟列名，也可以在列名和别名之间加入关键字‘AS’
select ename AS name, deptno dn from emp;

算术运算符

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A\|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

常用函数(MR操作) count(总行数)，max(最大值)，min(最小值)，sum(总和)，avg(平均值)

limit 返回指定的行数 select * from emp limit 5;

2 Where 语句

where 将不满足条件的过滤掉 where 紧随 from

比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

（1）查询出薪水等于5000的所有员工
select * from emp where sal =5000;
（2）查询工资在500到1000的员工信息
select * from emp where sal between 500 and 1000;
（3）查询comm为空的所有员工信息
select * from emp where comm is null;
（4）查询工资是1500或5000的员工信息
select * from emp where sal IN (1500, 5000);

3 Like和RLike

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)

_ 代表一个字符

[xx] 代表含有xx字符所有

（1）查找以2开头薪水的员工信息
select * from emp where sal LIKE ‘2%’;
（2）查找第二个数值为2的薪水的员工信息
select * from emp where sal LIKE ‘_2%’;
（3）查找薪水中含有2的员工信息
select * from emp where sal RLIKE ‘[2]’;

4 逻辑运算符

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

（1）查询薪水大于1000，部门是30
select * from emp where sal>1000 and deptno=30;
（2）查询薪水大于1000，或者部门是30
select * from emp where sal>1000 or deptno=30;
（3）查询除了20部门和30部门以外的员工信息
select * from emp where deptno not IN(30, 20);

5 分组

group by

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

（1）计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
（2）计算emp每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

having

having和where不同点

where 针对表中的列发挥作用，查询数据 having 针对查询结果中的列发挥作用，筛选数据

where 后面不能写分组函数 having 后面可以使用分组函数