HIVE数据库操作
4.数据库操作
1.创建数据库
1.创建hive数据仓库
创建mydb
create database if not exists mydb;
use mydb
show databases;
可以看到创建了一个mydb数据库
- 同时再hdfs上也创建了一个mydb的目录
- 数据库也增加了一条数据记录
2.创建指定目录的数据仓库
create database dbtest location ‘/mypath’;
在 hdfs 中的mypath目录下创建hive数据库
数据库中的数据库目录已经改变
2.设置数据库的信息
1.增加描述性的键值对信息
create database cui with dbproperties (‘owner’=‘lili’,‘date’=‘20191120’);
2.产看数据库键值信息
describe database extended cui;
3.修改键值信息
alter database cui set dbproperties (‘owner’=‘ditiantai’);
4.查看更多数据库信息
desc database extended cui;
5.删除一个空数据库
drop database cui;
6.强制删除一个数据库里面的内容也会丢失慎用
drop database cui cascade;
5.数据表操作
1.创建hive表
create [external] table [if not exists] table_name (
col_name data_type [comment '字段描述信息']
col_name data_type [comment '字段描述信息'])
[comment '表的描述信息']
[partitioned by (col_name data_type,...)]
[clustered by (col_name,col_name,...)]
[sorted by (col_name [asc|desc],...) into num_buckets buckets]
[row format row_format]
[storted as ....]
[location '指定表的路径']
说明:
-
create table
创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。 -
external
可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。 -
comment
表示注释,默认不能使用中文 -
partitioned by
表示使用表分区,一个表可以拥有一个或者多个分区,每一个分区单独存在一个目录下 . -
clustered by
对于每一个表分文件, Hive可以进一步组织成桶,也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。 -
sorted by
指定排序字段和排序规 -
row format
指定表文件字段分隔符 -
storted as指定表文件的存储格式, 常用格式:SEQUENCEFILE, TEXTFILE, RCFILE,如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 storted as SEQUENCEFILE。
指定表文件的存储路径
分类 | 类型 | 描述 | 字面量示例 |
---|---|---|---|
原始类型 | BOOLEAN | true/false | TRUE |
TINYINT | 1字节的有符号整数, -128~127 | 1Y | |
SMALLINT | 2个字节的有符号整数,-32768~32767 | 1S | |
INT | 4个字节的带符号整数 | 1 | |
BIGINT | 8字节带符号整数 | 1L | |
FLOAT | 4字节单精度浮点数 | 1.0 | |
DOUBLE | 8字节双精度浮点数 | 1.0 | |
DEICIMAL | 任意精度的带符号小数 | 1.0 | |
STRING | 字符串,变长 | “a”,’b’ | |
VARCHAR | 变长字符串 | “a”,’b’ | |
CHAR | 固定长度字符串 | “a”,’b’ | |
BINARY | 字节数组 | 无法表示 | |
TIMESTAMP | 时间戳,毫秒值精度 | 122327493795 | |
DATE | 日期 | ‘2016-03-29’ | |
INTERVAL | 时间频率间隔 | ||
复杂类型 | ARRAY | 有序的的同类型的集合 | array(1,2) |
MAP | key-value,key必须为原始类型,value可以任意类型 | map(‘a’,1,’b’,2) | |
STRUCT | 字段集合,类型可以不同 | struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0) | |
UNION | 在有限取值范围内的一个值 | create_union(1,’a’,63) |
2.内部表操作
创建表时,如果没有使用external关键字,则该表是内部表(managed table)
- 创建内部表
use mydb;
create table myuser(id int,name string);
insert into myuser values (1,"jialing"); #插入数据
select * from myuser;
执行insert 的时候会执行 hadoop 的map任务
- 创建表并指定分隔符
create table if not exists user2(id int ,name string) row format delimited fields terminated by ‘\t’;
insert into user2 values (1,“jialing”);
- 创建表并指定表文件的存放路径
create table if not exists user3(id int ,name string) row format delimited fields terminated by ‘\t’ location ‘/user/user3’;
hdfs dfs -ls /user/ # 查看hdfs /user路径会出现 user3
- 根据查询结果创建表 表结构和数据都会复制
create table user4 as select * from user2; # 通过复制表结构和表内容创建新表
- 根据已经存在的表结构创建表
create table user5 like myuser;
- 查询表的详细信息
desc formatted user5;
3.外部表操作
外部表因为是指定其他的hdfs路径的数据加载到表当中来,所以hive表会认为自己不完全独占这份数据,所以删除hive表的时候,数据仍然存放在hdfs当中,不会删掉也就是说,数据资源用的是hdfs中的资源引用,然后中间的数据分析结果可以放到自己的内部表中
1.新建外部表并加载数据
创建外部表,并导入数据
create external table teacher (t_id string,t_name string) row format
delimited fields terminated by ‘\t’;
新建 techer.csv
内容:
01 张三
02 李四
03 王五
上传到 hdfs对应的表目录中
hdfs dfs -put techer.csv /user/hive/warehouse/mydb.db/teacher
select * from techer;
2.新建外部表并从本地复制数据到表中
create external table student (s_id string,s_name string,s_birth string ,
s_sex string ) row format delimited fields terminated by ‘\t’;
在 /home/hive 目录下创建文件 student.csv
写入内容:
01 赵* 1990-01-01 男
02 钱* 1990-12-21 男
03 孙* 1990-05-20 男
04 李* 1990-08-06 男
05 周* 1991-12-01 女
06 吴* 1992-03-01 女
07 郑* 1989-07-01 女
08 王* 1990-01-20 女
3.覆盖外部表中的数据
load data local inpath ‘/home/hive/student.csv’ overwrite
into table student;
4.从hdfs文件中加载数据到外部表
cd /user/home
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put techer.csv /hivedatas/
load data inpath ‘/hivedatas/techer.csv’ into table teacher;
分区表的操作
在大数据中,最常用的一种思想就是分治,我们可以把大的文件切割划分成一个个的小的文件,这样每次操作一个小的文件就会很容易了,同样的道理,在hive当中也是支持这种思想的,就是我们可以把大的数据,按照每月,或者天进行切分成一个个的小的文件,存放在不同的文件夹中
1.创建一个分区表
create table score(s_id string,c_id string, s_score int) partitioned by
(month string) row format delimited fields terminated by ‘\t’;
2.创建一个多分区的表
create table score2 (s_id string,c_id string, s_score int) partitioned by
(year string,month string,day string) row format delimited fields
terminated by ‘\t’;
3.加载数据到分区表中
load data local inpath ‘/home/hive/score.csv’ into table
score partition (month=‘201806’);
4.加载数据到都分区表中
load data local inpath ‘/home/hive/score.csv’ into table
score2 partition(year=‘2018’,month=‘06’,day=‘01’);
- 多分区表联合查询
select * from score where month = ‘201806’ union all select * from score
where month = ‘201806’;
6.查看分区
show partitions score;
7.添加一个分区
alter table score add partition(month=‘201805’);
8.删除一个分区
alter table score drop partition(month = ‘201806’);
好了,hive的库表操作基本上就到这里了,后面还有一些hive优化方面的内容放到下个章节来写,能学下来的同学基本上可以升任hive工程师的日常工作是没问题了。欢迎进行技术探讨,有些的不好的地方还请多提意见。
赞赏是最好的支持