第一章 数据定义
1.1 数据库操作
1.1.1 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment] // 库的注释说明
[LOCATION hdfs_path] // 库在hdfs中的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; // 库的属性
- if not exists:防止数据库已经存在
- 示例:创建exampledb数据库,放在hdfs上的/example目录下,注释为this is a example,设置属性owner=tom,empid=1001
create database if not exists exampledb
comment 'this is a example'
location '/example'
dbproperties('owner'='tom','empid'='1001');
1.1.2 删除数据库
- 若不加cascade只能删除空库,加上可以删除非空库,if exists用于判断数据库是否存在,若不加上,若数据库不存在会报错
drop database [if exists] 库名 [cascade];
- 示例:判断example库是否存在,并删除库中所有内容
drop database if exists example cascade;
1.1.3 修改数据库
- 用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值,来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。修改数据库的属性值,同名的属性值会覆盖,之前没有的属性值会新增。
alter database 数据库名 set dbproperties(property_name=property_value, ...);
- 示例:将example库的所有者改为jack,编号1002,新增创建时间属性
alter database example dbproperties('owner'='tom','empid'='1002','createtime'='20210130');
1.1.4 查询数据库
- 切换数据库
use 数据库名;
- 示例:使用default库
use default;
- 查看当前所有数据库
show databases;
- 查看数据库中所有表
show tables in 库名;
- 示例:查看example中所有表
show tables in example;
- 查看数据库的描述信息,加上extended,可以显示库的详细描述信息
desc database [extended] 库名;
- 示例:查看example库的详细描述信息
desc database extended example;
第二章 表的操作
2.1 创建表的基本语法
- 在建表语句执行的时候,hive会在hdfs上生成表的路径,并向MySQL的metastore库中国呢插入表的元数据信息。
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];
- 各个字段含义
- create table:创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
- external:EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
- comment:为表和列添加注释
- partitioned by:创建分区表
- clustered by:创建分桶表
- sorted by:不常用,对桶中的一个或多个列另外排序
- row format:表中数据每行的格式,定义数据字段的分隔符,集合元素的分隔符等
- stored as:指定存储文件类型。常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)。如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
- location:指定表在HDFS上的存储位置。
- as:后跟查询语句,根据查询结果创建表。
- 示例:创建一个普通表
create table if not exists student(id int, name string)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';
- 示例:根据查询结果创建表
create table if not exists student1 as select id,name from student;
2.2 外部表和管理表
2.2.1 理论
-
默认创建的表都是所谓的管理表,有时也被称为内部表。因为这种表,Hive会(或多或少地)控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如,/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时,Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
-
内部表(管理表)在执行删除操作时,会将表的元数据(schema)和表位置的数据一起删除,外部表在执行删除表操作时,只删除表的元数据(schema)
2.2.2 外部表
- 因为表是外部表,所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉。
- 示例:创建一个外部表
create external table stu_external(
id int,
name string)
row format delimited fields terminated by '\t'
location '/student'
2.2.3 管理表与外部表之间转换
- 修改内部表为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
- 修改外部表为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
2.3 分区表
-
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多。
-
分区目的就是为了将数据分散到多个子目录中,在执行查询时,可以只选择查询某些子目录中的数据,加快查询效率。
-
分区目录由两部分组成:分区列列名=分区列列值,将输入导入到指定的分区之后,数据会附加上分区列的信息,分区的最终目的是在查询时,使用分区列进行过滤
2.3.1 分区表的基本操作
- 创建分区表
create external table if not exists default.deptpart1(
deptno int,
dname string,
loc int)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t';
- 创建多级分区表
create external table if not exists default.deptpart2(
deptno int,
dname string,
loc int)
PARTITIONED BY(area string,province string)
row format delimited fields terminated by '\t';
- 分区的查询
show partitions 表名;
- 创建分区方法一
- 会进行以下操作:
- 在hdfs上生成分区路径
- 在mysql中metastore.partitions表中生成分区的元数据
- 会进行以下操作:
alter table 表名 add partition(分区字段名=分区字段值);
- 创建分区方法二
- 直接使用load命令想分区加载数据,如果分区不存在,load时自动帮我们生成分区
load data [local] inpath '数据存放路径' into table 分区表明 partition(分区列名=分区列值,多级分区用逗号隔开);
2.3.2 分区表注意事项
- 如果表是个分区表,在导入数据时,必须指定向哪个分区目录导入数据
- 如果表是多级分区表,在导入数据时,数据必须位于最后一级分区的目录
2.4 分通表
- 上面分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可形成合理的分区。对于一张表或者分区,Hive 可以进一步组织成桶,也就是更为细粒度的数据范围划分。
- 分桶是将数据集分解成更容易管理的若干部分的另一个技术
- 分区针对的是数据的存储路径,分桶针对的是数据文件
- 分桶的意义是也是为了分散数据,可以结合hive提供的抽样查询,只查询指定桶的数据。
- 在分桶时,也可以指定将每个桶的数据根据一定的规则来排序,如果需要排序,那么可以在CLUSTERED BY后根SORTED BY。
2.4.1 创建分桶表
- 创建一个分桶表,将数据分为4桶,按照id分桶,并按照id降序排序
create table stu_buck(id int, name string)
clustered by(id)
SORTED BY (id desc)
into 4 buckets
row format delimited fields terminated by '\t';
2.4.2 分桶表导入数据
- 向分桶表中导入数据必须执行MapReduce程序,才能实现分桶操作,故不能使用load方式向分桶中导入数据,load方式只是执行put操作无法满足分桶导入数据。
- 通常我们可以先建立一个临时表,先用load向临时表中导入数据,再用insert向分桶表中导入数据。
- 在导入数据之前需要先打开强制分桶开关
set hive.enforce.bucketing=true;
- 若要进行排序,需要打开强制排序开关
set hive.enforce.sorting=true;
- 使用insert into导入数据
insert into table 表名 values(),(),();
或
insert into table 表名 select 语句;
- 示例
insert into table stu_buck select * from stu_buck_tmp;
2.4.3 抽样查询
- 格式
select * from 分桶表 tablesample(bucket x out of y on 分桶表分桶字段);
- 要求
- 抽样查询的表必须是分桶表
- bucket x out of y on 分桶表分桶字段
- x:从当前表的第几桶开始抽样,0<x<=y
- y:z/y 代表一共抽多少桶,要求y必须是z的因子或倍数,z为总桶数
- 从第x桶开始抽样,每间隔y桶抽一桶,直到抽满 z/y桶
- 示例:从第1桶(0号桶)开始抽,抽第x+y*(n-1),一共抽2桶 : 0号桶,2号桶
select * from stu_buck tablesample(bucket 1 out of 2 on id);
2.5 表的其他操作
2.5.1 删除表
drop table 表名;
2.5.2 查看表
- 查看表的描述
desc 表名;
- 查看表的详细描述
desc formatted 表名;
第三章 DML数据操作
3.1 数据导入
3.1.1 向表中装载数据
- 语法
load data [local] inpath '路径' [overwrite] into table 表名 [partition (partcol1=val1,…)];
- 说明
- load data:表示加载数据
- local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
- inpath:表示加载数据的路径
- overwrite:表示覆盖表中已有数据,否则表示追加
- into table:表示加载到哪张表
- partition:表示上传到指定分区
- 示例:加载本地文件到student表中
load data local inpath '/opt/module/datas/student.txt' into table default.student;
- 示例:加载hdfs中的文件到student表中,并覆盖原有的数据
load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;
3.1.2 通过查询语句向表中插入数据(insert)
- insert方式运行MR程序,通过程序将数据输出到表目录。
- 在某些场景,必须使用insert方式来导入数据
- 向分桶表插入数据
- 果指定表中的数据,不是以纯文本形式存储,需要使用insert方式导入
- 基本插入模式语法
insert into|overwrite table 表名 select xxx|values(),(),();
- 说明
- insert into:向表中追加新的数据
- insert overwrite: 先清空表中所有的数据,再向表中添加新的数据
- 示例:向分区表student指定分区中插入数据
insert into table student partition(month='201709') values(1,'wangwu');
- 多插入模式语法
from 源表
insert xxxx 目标表 select xxx
insert xxxx 目标表 select xxx
insert xxxx 目标表 select xxx;
- 示例
from deptpart2
insert into table deptpart1 partition(area='huaxi') select deptno,dname,loc
insert into table deptpart1 partition(area='huaxinan') select deptno,dname,loc;
3.1.3 查询语句中创建表并加载数据(as select)
- 根据查询结果创建表(查询的结果会添加到新创建的表中)
- 示例
create table if not exists student3 as select id, name from student;
3.1.4 创建表时通过lacation指定加载数据路径
- 示例
create table if not exists student5(
id int, name string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';
3.1.5 import数据到指定hive表中
- 注意:先用export导出后,再将数据导入。
- 示例
import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';
3.2 数据导出
3.2.1 Insert导出
- 示例:将查询的结果导出到本地
insert overwrite local directory '/opt/module/datas/export/student' select * from student;
- 示例:将查询结果格式化导出到本地
insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
- 示例:将查询的结果导出到HDFS上(无local关键字)
insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
3.2.2 Hadoop命令导出到本地
- 示例
dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;
3.2.3 Hive Shell命令导出
- 示例
bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;
3.2.4 Export导出到HDFS上
- 语法
export table 表名 to '路径';
- 说明
- export导出会将表的元数据一起导出
- 示例
export table default.student to '/user/hive/warehouse/export/student';
3.3 清除表中的数据
- 语法
truncate table 表名;
- 说明:Truncate只能删除管理表,不能删除外部表中数据
第四章 查询
- 大部分都与SQL语法一样
- 语法
[WITH CommonTableExpression (, CommonTableExpression)*]
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]
- 与SQL区别
- Hive支持通常的SQL JOIN语句,但是只支持等值连接,不支持非等值连接
第五章 排序
5.1 全局排序(order by)
- order by:全局排序,一个MapReduce
- 使用order排序时
- asc:升序
- desc:降序
- ORDER BY 子句在SELECT语句的结尾
- 用法基本和普通SQL中一致
- 示例:使用薪水升序排序
select * from emp order by sal asc;
5.2 每个MapReduce内部排序
- Sort By:每个Reducer内部进行排序,对全局结果集来说不是排序。
- 使用sort by之前需要先设置reduce个数
set mapreduce.job.reduces=个数
- 示例:根据部门编号查看员工信息
select * from emp sort by empno desc;
5.3 分区排序
- Distribute By:类似MR中partition,进行分区,结合sort by使用
- 注意:Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
- 也要设置reduce个数,否则无法看到效果
- 示例:
select * from emp DISTRIBUTE BY deptno sort by deptno desc ;
5.4 Cluster By
-
当distribute by和sorts by字段相同时,可以使用cluster by方式。
-
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序,不能指定排序规则为ASC或者DESC。
-
上面的示例还可以如下表示
select * from emp cluster by deptno;