2020.12.10课堂笔记(hive DQL查询语句)

最新推荐文章于 2022-12-27 22:33:49 发布

超可爱慕之

最新推荐文章于 2022-12-27 22:33:49 发布

阅读量153

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/m0_48758256/article/details/110948373

版权

笔记专栏收录该内容

138 篇文章 5 订阅

订阅专栏

hive 查询

一、hive查询即join

1.1 基本查询（SELECT…FROM）

语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number]

数据准备：
创建部门表：

create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

创建员工表：

create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t' ;

向表中导入数据：

load data local inpath '/opt/datas/dept.txt' into table dept;
load data local inpath '/opt/datas/emp.txt' into table emp;

1.全表和特定字段查询

# 全表查询
hive (default)> select * from emp;
# 选择特定列查询
hive (default)> select empno, ename from emp;

注意：
（1）SQL 语言大小写不敏感
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写（不分行写不会报错，但影响阅读）
（5）使用缩进提高语句的可读性

2. 列别名

和 MySQL 一样，我们同样可以通过列别名重命名一个列，以便于计算。
列别名可以紧跟列名，也可以在列名和别名之间加入关键字‘AS’。

# 查询名称和部门
hive (default)> select ename AS name, deptno dn from emp;

3. 算术运算符

运算符	描述
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 按位取反

# 案例
hive (default)> select sal +1 from emp;

4.LIMIT 语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。

hive (default)> select * from emp limit 5;

1.2 WHERE 语句

可以使用 WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。

# 查询出薪水大于 1000 的所有员工
hive (default)> select * from emp where sal >1000;

1.比较运算符（BETWEEN/IN/ IS NULL）

下面表中描述了比较操作符，这些操作符同样可以用于 JOIN…ON 和 HAVING
语句中。

操作符	支持的数据类型	描述
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 的所有员工
hive (default)> select * from emp where sal =5000;
（2）查询工资在 500 到 1000 的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
（3）查询 comm 为空的所有员工信息
hive (default)> select * from emp where comm is null;
（4）查询工资是 1500 和 5000 的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);

2.LIKE 和 RLIKE

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

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

3.逻辑运算符（AND/OR/NOT）

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

示例演示：

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

【扩展内容】

1.列匹配正则表达式

可以通过列匹配正则表达式来选择指定的列，比如想要除个别字段外的剩余
所有字段，全部列出来不方便且不美观，可以使用此方式。

-- 使用这个技巧需要配置以下参数，默认是 column
set hive.support.quoted.identifiers=none
-- 查询除了某个列以外的其他字段的内容语法
select `(col_name1|col_name2|col_name3)?+.+` from table;

示例演示：

（1）查询除了 hirdate 和 comm 的其他列的数据
hive (default)> select `(hirdate|comm)?+.+` from emp;

2.虚拟列（Virtual Columns）（了解）

两个连续下划线，常用于数据验证：
INPUT__FILE__NAME：Mapper Task 的输入文件名称
BLOCK__OFFSET__INSIDE__FILE：文件中的块内偏移量
ROW__OFFSET__INSIDE__BLOCK : 文件的行偏移量（默认不开启）

ROW__OFFSET__INSIDE__BLOCK 需要开启 hive.exec.rowoffset 选项：
set hive.exec.rowoffset=true;

场景：
已知 clickcube_mid 表中有一个字段 regioncode , regioncode 描述了一个 ip对应的 region 信息，这个 regioncode 目前使用的是原始值，为日志中直接获取。
某一天，由于 regioncode 异常，导致 spark 进程中断，查找得知是 regioncode 不合理导致，此时我们需要找到错误的 regioncode, 可以进行如下的
查询：

SELECT
INPUT__FILE__NAME,
BLOCK__OFFSET__INSIDE__FILE,
ROW__OFFSET__INSIDE__BLOCK,
substr(regioncode,0,20)
FROM clickcube_mid
WHERE length(regioncode) > 100;

1.3 CTE和嵌套查询

CTE语法示例

-- CTE语法示例
with 
a as (select * from employee),
b as (select * from a)
select * from b where name='Will';

嵌套查询：

-- 嵌套查询语法示例
select * from (select * from employee) a  where name='Will';

1.4 hive join

指对多表进行联合查询

1. 等值join

emp emp_id,dept_id,emp_name
dept dept_id,dept_name
类似MySQL的join，Hive 支持通常的 SQL JOIN 语句，但hive仅支持等值连接（不可以使用小于、大于符号来关联），不支持非等值连接。
员工表：
1,2,zhangsan
2,3,lisi
3,4,wangwu
部门表：
1,jishu
2,ceshi
3,renshi
示例演示：

-- 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部
门编号；
hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join
dept d on e.deptno = d.deptno;

表的别名：
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。

2.表的连接类型：

1. 内连接：inner join
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
外连接：outer join
2.right join 右连接
JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
3.left join 左连接
JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
左表有的右表没有的也会查出来，右表没有的变成null
4.full join 全连接
将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。
5.多表连接
注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少
需要两个连接条件。

（1）数据准备
数据文件 location.txt，内容如下：

1700 Beijing
1800 London
1900 Tokyo

（2）创建位置表

create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

（3）导入数据

hive (default)> load data local inpath '/opt/datas/location.txt' into table location;

（4）多表连接查询
数据详见上一章创建的两张表 emp 和 dept。

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 的输出和表 location 进行连接操作。
注意：为什么不是表 d 和表 location 先进行连接操作呢？这是因为 Hive 总是
按照从左到右的顺序执行。

3.交叉连接：cross join 笛卡尔积

非常重要的概念：左表每条数据和右边每条数据相乘，数据量非常大，在查询过程中避免笛卡尔积，一种情况join的时候没有加条件（on emp.dept_id=dept.dept_id），join条件失效了，主要是没有加符合条件的关联条件
隐式链接：省略join的关键字，但没有显示的指定join
join发生在where子句之前

笛卡尔集会在下面条件下产生:
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

怎么执行hive的脚本：

hive -f setup_table.sql

做一个查询：

select * from emp_basic limit 1;
+-------------------+---------------------+----------------------+--------------------+-----------------------+----------------------+--+
| emp_basic.emp_id  | emp_basic.emp_name  | emp_basic.job_title  | emp_basic.company  | emp_basic.start_date  | emp_basic.quit_date  |
+-------------------+---------------------+----------------------+--------------------+-----------------------+----------------------+--+
| 1                 | Erhart Scambler     | Recruiter            | Cogilith           | NULL                  | NULL                 |
+-------------------+---------------------+----------------------+--------------------+-----------------------+----------------------+--+

select * from emp_psn limit 1;
+-----------------+------------------+---------------+----------------+--------------------+-----------------+--------------+--+
| emp_psn.emp_id  | emp_psn.address  | emp_psn.city  | emp_psn.phone  |   emp_psn.email    | emp_psn.gender  | emp_psn.age  |
+-----------------+------------------+---------------+----------------+--------------------+-----------------+--------------+--+
| 1               | 40 Elka Alley    | Torslanda     | 305-478-4241   | ohenmarsh0@si.edu  | F               | 59           |
+-----------------+------------------+---------------+----------------+--------------------+-----------------+--------------+--+

select
eb.emp_id        emp_id,
eb.job_title     job_title,
eb.company       company,
ep.gender        gender,
ep.age           age
from emp_basic eb inner join emp_psn ep
on eb.emp_id=ep.emp_id
limit 10;

知道语法就可以了，在写MySQL的时候也是每天去写join

mapjoin

在map端完成，小表关联大表，数据量大的时候减少数据的传输，在hive里面默认事开启map join的，如果条件适合的话，会自动的去运行mapjoin
有一些版本需要把小表写在左边，大表写在右边，后面的版本会自动的去做判断

局限性：
不能放在很多操作后面
union all , lateral view, group by
也不能放在其他的join前面

hive集合操作：union
union all 合并后保留重复项
uniion 合并后删除重复项(v1.2之后)

select * from employee
union all 
select * from employee
where name='Will'

使用insert语句从表将数据插入/导出到文件

from employee
insert overwrite local directory '/root/out1'
select * ;

使用的是默认分割符，也可以指定分割：

from employee
insert overwrite local directory '/root/out2'
row format delimited
fields terminated by ','
select * ;

会按照指定的分割符生成文件。
使用import和export导入和导出数据

export table employee to '/export/out1';
//再使用import把数据导入
import table employee_import from '/export/out1';

超可爱慕之

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2020.12.10课堂笔记(hive DQL查询语句)

hive 查询CTE和嵌套查询CTE语法示例with hive join指对多表进行联合查询emp emp_id,dept_id,emp_namedept dept_id,dept_name类似MySQL的join，但hive仅支持等值连接（不可以使用小于、大于符号来关联）员工表：1,2,zhangsan2,3,lisi3,4,wangwu部门表：1,jishu2,ceshi3,renshi内连接：inner join外连接：outer joinright j
复制链接

扫一扫