Hive(2) DDL语句
DDL 数据定义语言
数据库操作
创建数据库
create database [if not exists] <表名>
[comment <表的说明>]
[localtion <路径>]
[with dbproperties (<属性名> = <属性值>, ... )];
说明
- if not exists: 如果不存在就创建, 如果存在就不创建
- localtion <路径>: 指定该数据库在HDFS中的路径, 如果不加, 默认是
/user/hive/warehouse/目录
- with dbproperties (<属性名> = <属性值>, … ) : 这里的属性值是自定义的, 比如创建时间, 备注等
查询数据库
show database like 'defau*';
desc database [extended] <数据库名>;
说明
user <数据库名>;
修改和删除数据库
alter database <数据库名> set dbproperties("<属性名>" = "<值>");
举例
alter database test set dbproperties("create_time" = "2019");
drop database <数据库名> [cascade];
说明
cascade : 默认只能删除空的数据库, 如果数据库非空, 就加上这个属性就可以删除了
表操作
创建和删除表
create [external] table [if not exists] <表名>
[(列名 <数据类型> [comment <该列的备注>, ...]]
[comment <表的备注>]
[partitioned by (列名 <数据类型> [comment <列的备注>], ...)]
[clustered by (列名1, 列名2, ...) [sorted by (列名 [asc|desc], ...)] into <分桶数量> buckets]
[row format <字段切分格式> ...]
[stored as <文件格式>]
[location <HDFS中文件路径>]
[tblproperties (<属性名> = <值>, ...)]
[as <select_statement>]
说明 下面只是对上面的部分属性做一个解释, 详细使用后面会说到
- if not exists : 判断表是否存在
- partitioned by … : 分区
- clustered by … : 分桶
- row format : 行的格式, 指定每个字段用什么分割, 例如指定字段之间用’,‘隔开, 集合之间用’_‘隔开, map之间用’:‘隔开, 行之间用’\n’隔开
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n'
- stored as <文件格式> : 指定这张表的存储类型
- location <HDFS中文件路径> : 表的位置
- tblproperties (<属性名> = <值>, …) : 表的属性的描述
- as <select_statement> : 把结果选成一张新的表
举例 先创建一张表, 字段之间用’\t’隔开
create table if not exists student2(id int, name string)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/studnet2';
然后用某个查询结果再次作为一张表, 这样会将查询结果作为一张表输出到默认目录下
create table if not exists student3
as select id, name from student2;
创建一个与student2结构一样的表
drop table <表名>;
查询和修改表
desc formatted <表名>;
注意
查看表信息时, 有一行属性为Table Type, 其中’MANAGED_TABLE`表示内部表, 'EXTERNAL_TABLE’表示外部表
alter table <表名> rename to <新的表名>;
alter table <表名> change [column] <旧列名> <新列名> <数据类型> [comment <列的备注>] [FIRST|AFTER <其他列名>];
说明
- FIRST|AFTER <其他列名> : 表示将该列添加到其他列的前面|后面
外部表
外部表就是Hive中的一张表, 映射一个文件的数据, 但是Hive中存储的只是该文件的一个映射, 并非这个文件本身, 所以在Hive中执行的操作, 并不会影响到文件本身, 相对于内部表, 比较安全
一般来说, 做网站流量统计时, 网站每天会有大量的日志, 使用外部表绑定这些日志数据, 然后分析的结果使用内部表, 这样可以保证日志数据不被破坏
create external table(...)
...
alter table <表名> set tblproperties('EXTERNAL' = 'TRUE');
分区表
分区的基本介绍
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多
分区表的操作
创建分区表
create table <表名>(<字段列表>)
partitioned by (<分区字段> <分区字段类型>)
row formatde limited <字段划分格式>;
注意
分区字段不能是表中已经存在的数据, 需要单独的自定义一列, 在导入数据时指定这一列的数据, 然后会根据这一列的数据分区
比如在网站生成的日志中, 根据日志文件生成的日期来导入到不同的分区表
举例 根据星期几创建一个学生的分区表
create table stu_partition(
stu_id string,
stu_name string,
class_id int,
job string,
score double,
add_score double,
entrance_time string,
age int,
monitor_id int
)
partitioned by (week string)
row format delimited
fields terminated by '\t';
然后导入数据到表中, 指定2个分区, 分别是周一和周四
load data local inpath "/home/spartan/data/stu_info.txt" into table stu_partition partition(week='monday');
load data local inpath "/home/spartan/data/stu_info.txt" into table stu_partition partition(week='thursday');
查询的时候, 只需要指定一下查询的分区就可以了
select * from stu_partition
where week='thursday';
如果想要查询多个分区, 就使用关联查询
select * from stu_partition where week='thursday'
union
select * from stu_partition where week='monday';
新增分区
alter table <表名> add partition(<分区字段> = <分区名>);
alter table <表名> add partition(<分区字段> = <分区名1>) partition(<分区字段> = <分区名1>) ... ;
删除分区
alter table <表名> drop partition(<分区字段> = <分区名>);
alter table <表名> drop partition(<分区字段> = <分区名1>) artition(<分区字段> = <分区名2>) ... ;
二级分区
create table <表名>(<字段列表>)
partitioned by (<分区字段1> <分区字段1类型>, <分区字段2> <分区字段2类型>)
row formatdelimited <字段划分格式>;
注意
二级分区的本质其实就是在一级分区的目录下再创建二级目录, 操作跟一级分区是一样的
分区数据的加载方式
-
正常使用load 加载分区, 这样会自动在表的文件夹下面创建分区目录
-
手动在HDFS上创建分区目录, 然后使用load 将数据直接导入分区中
-
手动在HDFS上创建分区目录, 然后将数据文件直接拷贝到分区目录下, 这种情况下, 因为数据并没有元数据, 所以加载不到, 只需要修复一下分区就可以了
msck repair table <表名>;
分桶表
分桶(bucket)查询类似于分区(partition), 但是不同的是, 分桶是在一张表中进行的(即将一张表分成几部分), 主要是用于在查询的时候起到一个抽样查询的作用, 如果一个表有几百万行数据, 如果每次做一个查询都要扫描所有数据, 速度会很慢, 比较好的方法就是从这几百万调数据中做一个比较有代表性的抽样, 在样本中再做查询, 这样处理的数据会减少很多
分桶表是一种相比分区表粒度更细的数据分配方式, 一个表既可以分区, 也可以分桶
分桶(bucket)与分区(partition)的区别
-
分区是目录级别的, 每个分区对应一个目录, 目录里面是表; 分桶是文件级别的, 一个文件划分成几个文件, 存在同一个目录下(本质上还是一张表)
-
分区是在原表的基础上添加一个分区的字段; 而分桶是直接根据表原本的某个字段划分
分桶的原理
Hive的分桶跟MapReduce中的HashPartitioner(默认的分区形式)的原理一模一样
MapReduce中：按照key的hash值去模除以reductTask的个数
Hive中：按照分桶字段的hash值去模除以分桶的个数
Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
分桶的作用
如果一个表有几百万行数据, 如果每次做一个查询都要扫描所有数据, 速度会很慢, 比较好的方法就是从这几百万调数据中做一个比较有代表性的抽样, 在样本中再做查询, 这样处理的数据会减少很多
由于分桶默认是按照列的hash值进行分桶, 这种形式会提高数据的散列性, 有比较好的随机性, 所以比较适合做抽样
桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
分桶的步骤
- 在客户端打开强制分桶, 如果测试的数据量太小, 将不会自动分桶
set hive.enforce.bucketing=true;
- 设置Reduce数量为默认或者与桶的数量相同, 这里就直接设置成默认(-1)了, 因为分桶
set mapreduce.job.reduces=-1;
-
创建分桶表
语法:
create table <表名>(<字段列表>)
clustered by(<字段名>)
into <分桶数量> buckets
row format <字段分割的格式>;
注意
这里的clustered by <列名> 是指根据哪一列分桶, 不同于Reduce内部排序的cluster by <字段名>
- 导入数据到分桶表, 注意不能使用
load 的形式直接从文件中导入数据到分桶表, 只能使用insert 的方式, 从另一张表导入数据到分桶表, 下面会说明原因
insert into <分桶表名>
select * from <原表名>;
注意
因为表的分桶默认是通过某一列的hash值来计算的, 所以分桶的时候会开启一个MapReduce来进行分桶的计算, 所以不敢通过load 的形式直接加载数据到表中(这个操作不会经过MapReduce), 如果通过load 直接加载数据, 即便是指定了分桶, 也不会进行分桶.
而通过select 语句插入到分桶表中, 会开启一个MapReduce, 这时就可以进行分桶了
