day57-hive

hive

准备数据

dept

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700

7369	SMITH	CLERK	7902	1980-12-17	800.00	 	20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WAFD	SALESMAN	7698	1981-2-2212	50.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00	 	20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-12	850.00	 	30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00	 	10
7788	SCOTT	ANALYST	7566	1987-4-1930	00.00	 	20
7839	KING	PRESIDENT	 	1981-11-17	5000.00	 	10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00	 	20
7900	JAMES	CLERK	7698	1981-12-3	950.00	 	30
7902	FORD	ANALYST	7566	1981-12-3	3000.00	 	20
7934	MILLER	CLERK	7782	1982-1-23	1300.00	 	10

创建表

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,
name string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t';

表结构的增删改查

语法：

1. 更新列
	alter table table_name change [column] col_old_name col_new_name column_type [comment col_comment] [first|after column_name]
2. 增加和替换列
	alter table table name add|replace columns (col_name data_type[comment col_comment],...)
	
注意：替换是替换表中所有的字段，不能把数据的类型从大类型往小的类型修改。

修改表的名字
	alter table table_name rename to new_table_name;
查看表结构
	desc dept;
	desc formatted dept;
添加列
	alter table dept add columns(deptdesc string);
更新列
	alter table dept change column deptdesc desc string;
替换列
	alter table dept replace columns(deptno string,dname string);

数据导入

向表中装载数据(load)

1. 语法
	load data [local] inpath '数据的路径' [overwrite] into table 表名 [partition];
2. local
	如果写：表示从linux本地将数据进行导入
	如果不写：表示从hdfs将数据进行导入
3. overwrite
	如果写：表示新导入的数据将表中原有的数据进行覆盖
	如果不写：表示新数据导入到表的目录下，表中已有的数据不会被覆盖

通过查询语句向表中插入数据

1. 基本插入数据
	insert into student values(1001,'name'); 需要执行mr
2. 基本模式插入
	insert into | overwrite table 表名1 select * from 表名2; 需要执行mr

查询语句中创建并加载数据（as select）

根据查询结果创建表（查询的结果）
create table 表名1 as select * from 表名2; 需要执行mr

创建表时通过location指定加载数据路径

create external table if not exists student(id int,name string)
row format delimited fields terminated by '\t'
location '/student'; 数据在hdfs的目录

inport导入数据

import table 表名 to 'hdfs路径'

数据导出

Insert导出

格式: insert overwrite [local] directory 'path'
	 row format delimited fields terminated by '\t'  导出数据的格式
	 select * from student;
overwrite: 覆盖
local: 写代表的linux本地，不写代表hdfs
directory 'path': 导出的目录

hadoop命令导出到本地

dfs -get 路径/文件 路径/文件

hive shell命令导出

hive -e 'select * from default.student' > 文件;

export导出到hdfs上

export table default.student to '路径'; 默认导出到hdfs上

sqoop导出

清除表中数据(Truncate)

truncate只能删除管理表，不能删除外部表中数据
truncate table 表名;

数据查询

注意事项

1. sql语言大小写不敏感
2. sql可以下在一行或者多行
3. 关键字不能被缩写也不能分行
4. 各子句一般要分行写
5. 使用缩进提高语句的可读性

基本语法

select [all | distinct] select_expr,select_expr,... -- 查询表中的那些字段
from table_reference								-- 从哪个表查
[where where_condition]								-- where过滤条件
[group by col_list]									-- 分组
[order by col_list]									-- 全局排序
[cluster by col_list								-- 分区排序
	| [distribute by col_list] [sort by col_list]]  -- 分区  排序
[limit number]				-- 分页

基本查询

全表查询

select * from emp;
select empon,ename,job,mgr,hiredate,sal,comm,deptno from emp;

特定列查询

select empon,ename from emp;

列别名

1. 重命名一个列
2. 便于计算
3. 紧跟列明，也可以在列名和别名之间加入关键字 as
举例：select ename as name,deptno dn from emp;

常用函数

conut(): 求总行数
max(): 求最大值
min(): 求最小值
sum(): 求总和
avg(): 求平均值

limit语句

limit 0,9; 下标从0开始，从0开始，获取9条数据

where语句

条件过滤
select * from emp where 
分组之前使用where 分组之后使用having

条件运算符

a<=>b	如果a和b都为null，则返回true，如果一边为null，返回false
a<>b	a或者b为null则返回null

like '%_': 模糊匹配，%任意个任意字符，_一个任意字符
rlike '正则': 正则模糊匹配

分组 group by

注意：分组以后，在select后面只能跟 分组字段(组标识)和聚合函数

select count(*) from emp group ename;
每个部门中岗位的最高薪水的哪个人

having 条件过滤

having 只用于group by分组统计语句

每个部门的平均薪水大于2000的部门

join语句

1. join的方式？内连接 外连接
2. 内连接
	语法: a inner join b on 连接条件
	结果集: 取交集
	小表驱动大表(优化器可以进行优化)
3. 外连接
	语法：a left outer join b on 连接条件
		 b right outer join b on 连接条件
	结果集:
		先确认主(驱动)从(匹配)表，左外连，左主右从；右外连，右主左从
		结果集为：主表所有的数据 + 从表中与主表匹配的数据
				 主表中没有与从表中匹配的数据，从表的字段取null值

等值join

hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接。

满外连接

满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的化，那么就使用null值替代。
select * 
from emp e
full join dept d
on e.deptno=d.deptno;

写sql的分析方式

1. 从哪差？from
2. 查什么？select
3. 怎么查？

表的别名

1. 使用别名可以简化查询
2. 使用表名前缀可以提高执行效率

多表连接

连接n个表，至少需要n-1个条件。

排序

order by 全局排序，只能有一个reducer（一个分区）
sort by 区内排序，有多个分区，在每个分区内排序，一般不单独使用，配合distribute by使用

distribute by分区

distribute by：在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似mr中partition(自定义分区)，进行分区，结合sort by使用
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果

cluster by 分区排序

如果distribute by 和 sort by使用的是同一个字段，且排序是升序，可以直接通过cluster by替换
	distribute by empno sort by empno asc 等级与 cluster by empno

posted @ 2022-05-16 22:14 黎白昼阅读(19) 评论(0) 收藏举报

刷新页面返回顶部

黎白昼

day57-hive

day57-hive

hive

准备数据

表结构的增删改查

数据导入

向表中装载数据(load)

通过查询语句向表中插入数据

查询语句中创建并加载数据（as select）

创建表时通过location指定加载数据路径

inport导入数据

数据导出

Insert导出

hadoop命令导出到本地

hive shell命令导出

export导出到hdfs上

sqoop导出

清除表中数据(Truncate)

数据查询

注意事项

基本语法

基本查询

列别名

常用函数

limit语句

where语句

条件运算符

分组 group by

having 条件过滤

join语句

写sql的分析方式

表的别名

多表连接

排序

distribute by分区

cluster by 分区排序

公告