目录
访问HIVE的三种方式
启动Hive命令，Ctrl C 退出客户端，执行测试语句，与sql一致
[wyc@hadoop102 hive]$ bin/hive
经验小结：在hive中执行语句报错：
Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
原因：namenode内存空间不够,JVM剩余内存空间不够新job运行所致
需要在运行命令前运行以下命令:
set hive.support.concurrency=false;Hive数据存放在HDFS中，默认路径：/user/hive/warehouse/test 使用 MapReduce 进行计算。
Hive 默认使用的元数据库为 derby，开启 Hive 之后就会占用元数据库，且不与其他客户端共享数据，只能使用一个客户端，所以我们需要将 Hive 的元数据地址改为 MySQL。
元数据库保存的是hdfs的映射关系：
元数据包括：表名、表所属的数据库（默认是
default
）、表的拥有者、列
/
分区字段、 表的类型（是否是外部表）、表的数据所在目录等；
hive的原理是通过查询语句解析元数据，根据元数据找到hdfs路径，
使用元数据服务的方式访问
（1）启动 metastore ：[wuc@hadoop202 hive]$ hive --service metastore
（2）启动hive：bin/hive
使用JDBC启动hive
（1）启动Hadoop集群：
脚本命令：myhadoop.sh start
分别启动hdfs，yarn
sbin/start-dfs.sh
sbin/start-yarn.sh
若有单个没有启动的，单个启动
yarn-daemon.sh start resourcemanager （启动resourcemanager ）
yarn-daemon.sh start nodemanager（启动nodemanager）
（2）启动MySQL服务：sudo systemctl start mysqld
（3）启动metastore：hive --service metastore
（4）启动hiveserver2：bin/hive --service hiveserver2
（5）启动动 beeline 客户端：bin/beeline -u jdbc:hive2://hadoop102:10000 -n 用户名
写启动脚本后：
hiveservices.sh start
常用命令：
1）“-e”不进入 hive 的交互窗口执行 sql 语句
[atguigu@hadoop102 hive]$ bin/hive -e "select id from student;"
“-f”执行脚本中 sql 语句
[wyc@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql
执行文件中的
sql
语句并将结果写入文件中
[wyc@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql >
/opt/module/datas/hive_result.txt
2）退出 hive 窗口：
hive(default)>exit;
hive(default)>quit;
3）在 hive cli 命令窗口中如何查看 hdfs 文件系统
hive(default)>dfs -ls /;
4）查看在 hive 中输入的所有历史命令
（1）进入到当前用户的根目录 /root 或/home/wyc
（2）查看. hivehistory 文件
[wyc@hadoop102 ~]$ cat .hivehistory
DDL
创建，查询，切换数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment] // 注释
[LOCATION hdfs_path] // 存储位置，数据库在
HDFS
上的默认存储路径是
/user/hive/warehouse/*.db。
[WITH DBPROPERTIES (property_name=property_value, ...)]； //创建人,创建时间等属性
查询数据库：
show databases;
显示数据库信息：
desc database db_hive;
显示数据库详细信息，extended
desc database extended db_hive;
避免要创建的数据库已经存在错误，增加 if not exists 判断。
create database if not exists db_hive;
切换当前数据库
use db_hive;修改数据库
ALTER DATABASE
命令为某个数据库的
DBPROPERTIES
设置键
-
值对属性值，
来描述这个数据库的属性信息
alter database db_hive set dbproperties('createtime'='20170830');
删除数据库
drop database db_name;
如果删除的数据库不存在，最好采用 if exists
判断数据库是否存在
drop database if exists db_name;
如果数据库不为空，可以采用 cascade 命令，强制删除
drop database db_name cascade;
创建表
CREATE
[EXTERNAL]
TABLE
[IF NOT EXISTS]
table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
（1）IF NOT EXISTS：如果表存在则不创建
（
2
）
EXTERNAL：
关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实
际数据的路径（
LOCATION
）
在删除表的时候，内部表的元数据和数据会被一起删除，而外
部表只删除元数据，不删除数据，有共享数据的最好用外部表
（
3
）
COMMENT：
为表和列添加注释。
（
4
）
PARTITIONED BY：
创建分区表
（
5
）
CLUSTERED BY：
创建分桶表
（
6
）
SORTED BY ：
不常用，对桶中的一个或多个列另外排序
（
7
）ROW FORMAT DELIMITED FIELDS TERMINATED BY char：指定插入表中数据按什么分割字段插入
（8）[TBLPROPERTIES (property_name=property_value, ...)] 是用于在创建表时为表添加附加属性（Table Properties）的关键字。它可以用来设置表的元数据信息，如存储格式、分区信息、压缩选项、表的描述等。
create table test6(id int,name string) row format delimited fields terminated by ',' ; -- 指定分隔符为 ，号
管理表 ：
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive
会（或多或
少地）控制着数据的生命周期。
外部表 ：
因为表是外部表，所以 Hive
并非认为其完全拥有这份数据。删除该表并不会删除掉这
份数据，不过描述表的元数据信息会被删除掉。
内部表和外部表的区别：
1、创建表的时候，内部表直接存储在默认的hdfs路径，不带数据;外部表需要自己指定路径,带数据
2、删除表的时候，内部表会将数据和元数据全部删除，外部表只删除元数据（metastore的元数据），数据（hdfs中的表数据）不删除
管理表与外部表的互相转换
查询表的类型
desc formatted student2;
修改内部表 student2 为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
修改外部表 student2 为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');修改删除表
修改表名：
ALTER TABLE table_name RENAME TO new_table_name
更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type ...)
添加列
alter table dept add columns(deptdesc string);
更新列
alter table dept change column deptdesc desc string;
替换列
alter table dept replace columns(deptno string, dname string, loc string);
删除表
drop table table_name;
修改列属性时string不可以改其他类型，只能从低往高修改
只修改属性不修改列名也需要加属性,加原先属性即可
replace是把表中所有的列全部替换，replace 属性比原表多则自动会用null补全，比原表少则不显示，所以由replace可以完成表的增、删、改，
DML 数据操作
向表中装载数据
load
load data [local] inpath '数据的路径' [overwrite] into table 表名 [partition (partcol1=val1,…)];
字段解释：
（
1
）
load data:
表示加载数据
（
2
）
local:
表示从本地加载数据到
hive
表；不加则默认从
HDFS
加载数据到
hive
表
（
3
）
inpath:
表示加载数据的路径
（
4
）
overwrite:
表示覆盖表中已有数据，否则表示追加
（
5
）
into table:
表示加载到哪张表
（6
）
partition:
表示上传到指定分区
load命令会修改元数据信息。
通过查询语句向表中插入数据（
Insert
）
insert overwrite table 表名 select id, name from student where age='18'（查询语句）;查询语句中创建表
create table if not exists student3 as select id, name from student;创建表时通过 Location 指定加载数据路径
create external table if not exists student5( id int, name string ) row format delimited fields terminated by '\t' location '/student; //将HDFS 根目录下的数据插入表中Import 数据到指定 Hive 表中
import table student2 from '/user/hive/warehouse/export/student';
inport 写进表中的数据必须是export出的用import导入有以下要求：
1、导入路径必须使export导出的路径。
2、写入的表要么不存在，要么表中没有数据
数据导出
Insert 导出
将查询的结果格式化导出到本地
hive (default)> insert overwrite local directory
'/opt/module/hive/data/export/student'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
将local去掉及导出到HDFSHadoop 命令导出到本地
dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;
Hive Shell
命令导出
Hive Shell 命令导出
基本语法：（hive -f/-e 执行语句或者脚本 > file）
基本语法：（hive -f/-e 执行语句或者脚本 > file）
[wyc@hadoop102 hive]$ bin/hive -e 'select * from default.student5;' >> student1.txt
导出到一个空文件，过程中不显示表中信息，但导出的数据包含列信息，不实用
Export
导出到
HDFS
上
export table default.student to '/user/hive/warehouse/export/student';
（库名.表名） (HDFS路径)
export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移。
清除表中数据（
Truncate
）
truncate table student;常用函数
1）求总行数（count）
select count(*) cnt from emp;
2）求工资的最大值（max）
select max(sal) max_sal from emp;
3）求工资的最小值（min）
select min(sal) min_sal from emp;
4）求工资的总和（sum）
select sum(sal) sum_sal from emp;
5）求工资的平均值（avg）
select avg(sal) avg_sal from emp;
6）LIMIT 子句用于限制返回的行数。
select * from emp limit 5;Like 和 RLike
1
）使用
LIKE
运算选择类似的值
2
）选择条件可以包含字符或数字：
%
代表零个或多个字符
(
任意个字符
)
。
_
代表一个字符。
3）RLIKE 子句
RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大
的语言来指定匹配条件。
查找名字中第二个字母为 A 的员工信息
select * from emp where ename LIKE '_A%';
查找名字中带有 A 的员工信息
select * from emp where ename RLIKE '[A]';分组
GROUP BY
语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然
后对每个组执行聚合操作
例：计算
emp
表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
Join
两张表共有的字段作为连接
内连接（两张共有的部分）：
select e.empno, e.ename, d.deptno from emp e join dept d
on e.deptno = d.deptno;
左外连接（包含join左边全部表的数据，和两张表共有的部分）
select e.empno, e.ename, d.deptno from emp e left join dept d
on e.deptno = d.deptno;
右外连接（包含join右边全部表的数据，和两张表共有的部分）
满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字
段没有符合条件的值的话，那么就使用 NULL 值替代
select e.empno, e.ename, d.deptno from emp e full join dept d
on e.deptno = d.deptno;
多表连接查询（用多个join on）
>SELECT e.ename, d.dname, l.loc_name FROM emp e
JOIN dept d ON d.deptno = e.deptno
JOIN location l ON d.loc = l.loc;排序
全局排序（Order By）
Order By：全局排序，只有一个 Reducer
ASC（ascend
）
: 升序（默认）
DESC（
descend
）
: 降序
查询员工信息按工资升序排列
select * from emp order by sal;
多个列排序
select ename, deptno, sal from emp order by deptno, sal;每个 Reduce 内部排序（Sort By）
Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排
序，此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集
来说不是排序，单独使用sort by它的分区是随机的
设置 reduce 个数
set mapreduce.job.reduces=3;
查看设置 reduce 个数
set mapreduce.job.reduces;
将根据部门编号降序查看员工信息导入到文件中（按照部门编号降序排序）
insert overwrite local directory
'/opt/module/data/sortby-result'
select * from emp sort by deptno desc;
分区（
Distribute By
）
Distribute By
： 在有些情况下，我们需要控制某个特定行应该到哪个
reducer
，通常是为
了进行后续的聚集操作。
distribute by
类似
MR
中
partition （自定义分区），进行分区，结合 sort by
使用。
对于
distribute by
进行测试，一定要分配多
reduce
进行处理，否则无法看到
distribute by 的效果。
distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后， 余数相同的分到一个区。
Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
insert overwrite local directory
'/opt/module/data/distribute-result' select * from emp distribute by
deptno sort by empno desc;
cluster by
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序
排序，不能指定排序规则为 ASC 或者 DESC。
例：以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
分区表和分桶表
分区表：分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所
有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据
集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率
会提高很多。分区表是避免全表扫描的一种优化方案，前提是where子句中必须有分区字段。
创建语句：
create table dept_partition(deptno int, dname string, loc string)
partitioned
by (day string)
row format delimited fields terminated by '\t';
加载数据到分区：
load data local inpath '/opt/module/data/dept2.txt' into table dept_par2 partition(day='2020-10-27',hour='12');
增加分区
alter table dept_partition add partition(day='2020-10-28');
增加多个分区用空格
删除单个分区
hive (default)> alter table dept_partition drop partition
(day='2020-10-29');
同时删除多个分区（用逗号）
alter table dept_partition drop partition (day='20200404'), partition(day='20200405');
查看分区表有多少分区
show partitions dept_partition;
查看分区表结构
desc formatted dept_partition;
二级分区
创建二级分区表
create table dept_partition2(deptno int, dname string, loc string ) partitioned by (day string, hour string) ；（两个分区字段）
二级分区就是多一层目录
把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
执行修复命令（自动创建分区）
hive> msck repair table dept_partition2;
HDFS创建目录后并上传数据后手动添加分区
hadoop fs -mkdir /user/hive/warehouse/dept_par/day=2020-10-28
hadoop fs -put dept1.txt /user/hive/warehouse/dept_par/day=2020-10-28
alter table dept_partition2 add partition(day='201709',hour='14');
Hdfs创建目录后 load 数据到分区
load data local inpath '/opt/module/data/dept2.txt' into table dept_par2 partition(day='2020-10-27',hour='12');
动态分区调整
insert into table dept_no_par partition(deptno)select dname,loc,deptno from dept;
insert into table dept_no_par select dname,loc,deptno from dept; 可以不加分区字段
表中字段比查询字段少，默认查询最后一个字段作为分区信息
831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
