文章目录
- 1. 基本查询
- 2. Where语句
- 3. 分组
- 4. Join
- 4.1 内连接(join)
- 4.2 左外连接(left join)
- 4.3 右外连接(right join)
- 4.4 满外连接(full join)
- 4.5 多表连接
- 4.6 笛卡尔集
- 5. 排序
- 5.1 全局排序（Order by）
- 5.2 内部排序（Sort By）
- 5.3 分区排序（Distribute By）
- 5.4 Cluster By
- 6. 分桶及抽样查询
- 6.1 分桶表数据查询
- 6.2 分桶抽样查询
- 6.3 数据块抽样
1. 基本查询
sql语句大小写不敏感
按位与（&）：只有两个数的二进制同时为1，结果才为1，否则为0。
例：3 &5 即 00000011 & 00000101 = 00000001 ，所以 3 & 5的值为1。
按位或（|）：参加运算的两个数只要两个数中的一个为1，结果就为1。
异或（^）：参加运算的两个数，如果两个相应位为“异”（值不同），则该位结果为1，否则为0。
按位取反（~）
2. Where语句
操作符 | 支持的数据类型 | 描述 |
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关键字则可达到相反的效果。% 代表零个或多个字符(任意个字符)，_ 代表一个字符。 eg：like ‘_A%’ ，like '__A%' |
A RLIKE B, A REGEXP B | STRING 类型 | B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。查找薪水中含有2的员工信息： select * from emp where sal RLIKE ‘[2]’; |
3. 分组
顺序：where, group by, having
where和having区别：
- where在结果返回之前起作用，having在结果返回后起过滤作用。
- where后面不能用聚合函数（sum，avg，count，min，max）因为where的执行顺序大于聚合函数，having可以。
- where后面不能使用字段的别名，having可以。
- having只用于group by分组统计语句。
4. Join
- Hive只支持等值连接，不支持非等值连接。
- 连接谓词中不支持or
# 错误的
hive(default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename;4.1 内连接(join)
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
hive(default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno; -- 等同于
hive(default)> select e.empno, e.ename, d.deptno from emp e, dept d where e.deptno = d.deptno;4.2 左外连接(left join)
JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
hive(default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;4.3 右外连接(right join)
JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
hive(default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;4.4 满外连接(full join)
将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
hive(default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;4.5 多表连接
-- 建表
hive(default)> create table if not exists location(loc int,loc_name string) row format delimited fields terminated by '\t';
-- 加载数据
hive(default)> load data local inpath '/usr/local/hive-3.1.2/test_files/location.txt' into table location;
-- 连接
hive(default)> SELECT e.ename, d.deptno, l.loc_name FROM emp e
JOIN dept d ON d.deptno = e.deptno
JOIN location l ON d.loc = l.loc;大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表 e 和表 d 进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表 l 进行连接操作。
注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
4.6 笛卡尔集
会在下面条件下产生:
1）省略连接条件
2）无效连接条件
3）所有表中的所有行互相连接
案例实操
hive (default)> select empno, deptno from emp, dept;FAILED: SemanticException Column deptno Found in more than One Tables/Subqueries
不支持笛卡尔积
5. 排序
顺序：where，group by，having， order by
5.1 全局排序（Order by）
-- 按照别名排序
hive(default)> select ename, sal*2 twosal from emp order by deptno, twosal;5.2 内部排序（Sort By）
每个分区内排序，区内排序。
每个MapReduce内部进行排序，按默认的分区规则，key的hash来运算，（区内排序）对全局结果集来说不是排序。
可以对多个字段进行内部排序，如sort by empno desc，deptno;
如果只有map，没有reduce，则无法进行sort by，因为没有分区。
-- 设置reduce个数，reduce个数是1，那么就只有一个分区。结果和 order by 结果一样。
hive(default)> set mapreduce.job.reduces=3;
-- 查看设置reduce个数
hive(default)> set mapreduce.job.reduces;
-- 根据部门编号降序查看员工信息（每个分区内，按照empno降序排序）
hive(default)> select * from emp sort by empno desc;
-- 将查询结果导入到文件中（按照部门编号降序排序）
hive(default)> insert overwrite local directory '/usr/local/hive-3.1.2/test_files/emp_sort' row format delimited fields terminated by '\t' select * from emp sort by empno desc;5.3 分区排序（Distribute By）
- Sort by相当于MapReduce默认分区，Distribute By 相当于MapReduce自定义分区（但底层使用的分区规则还是默认的hash，并不像MR自定义分区那么准确，要想准确需要对Hive进行二次开发）。
- 类似MR中partition，进行分区，结合sort by使用。如果只写Distribute By，相当于只做了分区。
- Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
- 对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
-- 先按照部门编号分区，再按照员工编号降序排序。
hive(default)> set mapreduce.job.reduces=3;
hive(default)> insert overwrite local directory '/usr/local/hive-3.1.2/test_files/emp_distribute' row format delimited fields terminated by '\t' select * from emp distribute by deptno sort by empno desc;5.4 Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。
hive(default)> insert overwrite local directory '/usr/local/hive-3.1.2/test_files/emp_cluster' row format delimited fields terminated by '\t' select * from emp cluster by deptno;
-- 等价于
hive(default)> select * from emp distribute by deptno sort by deptno;注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。
6. 分桶及抽样查询
6.1 分桶表数据查询
- 分区针对的是数据的存储路径；分桶针对的是数据文件。
- 分区分文件夹；分桶分文件。
- 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。分桶是将数据集分解成更容易管理的若干部分的另一个技术。
- 企业中用分桶的情况非常少。因为分桶必须要走MR，一旦走MR，效率就会非常低。但分区比较常用，分区加载数据可以不走MR，用load方式和insert方式都可以。而且分区还能防止一定的数据倾斜。
1）先创建分桶表，通过直接导入数据文件的方式, 查看创建的分桶表中是否分成4个桶。发现并没有分成4个桶。
-- 创建分桶表
hive(default)> create table stu_buck(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by '\t';
-- 查看表结构
hive(default)> desc formatted stu_buck;
Num Buckets: 4 -- 默认是-1
Bucket Columns: [id] -- 默认是[]
-- 导入数据到分桶表中
hive(default)> load data local inpath '/usr/local/hive-3.1.2/test_files/student_bucket.txt' into table stu_buck;2）创建分桶表时，数据通过子查询的方式导入，发现可以分桶成功。
-- 先建一个普通的stu表
hive(default)> create table stu(id int, name string) row format delimited fields terminated by '\t';
-- 向普通的stu表中导入数据
hive(default)> load data local inpath '/usr/local/hive-3.1.2/test_files/student_bucket.txt' into table stu;
-- 创建分桶表
hive(default)> create table stu_buck(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by '\t';
--导入数据到分桶表，通过子查询的方式(走了MapReduce，注意运行过程，这时map个数是1，reduce个数是0，没有分区，说明还没分桶)
hive(default)> insert into table stu_buck select id, name from stu;
-- 设置一个属性
hive(default)> set hive.enforce.bucketing=true;
hive(default)> set mapreduce.job.reduces=-1;
-- 清空stu_buck表中数据
hive(default)> truncate table stu_buck;
-- 导入数据到分桶表，通过子查询的方式（注意运行过程，map个数是1，reduce个数是4，有分区了）
insert into table stu_buck select id, name from stu;
-- 查询分桶的数据，发现分成了4个文件。
hive(default)> select * from stu_buck;
-- 跟普通的查询并没有什么区别，那么有什么用呢？-> 分桶抽样查询6.2 分桶抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
hive(default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);
hive(default)> select * from stu_buck tablesample(bucket 1 out of 2 on id);
hive(default)> select * from stu_buck tablesample(bucket 1 out of 8 on id);注：
- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
- x表示从哪个bucket开始抽取。例如，table总bucket数为4，tablesample(bucket 4 out of 4)，表示总共抽取（4/4=）1个bucket的数据，抽取第4个bucket的数据。
- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。不是桶数的倍数或者因子也可以，但是不推荐。
hive(default)> select * from stu_buck tablesample(bucket 1 out of 3 on id); - x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck
6.3 数据块抽样
Hive提供了另外一种按照百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样。
hive (default)> select * from stu tablesample(0.1 percent) ;提示：这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个HDFS数据块。因此，如果表的数据大小小于普通的块大小128M的话，那么将会返回所有行。
