Hive中表的类型:
1.内部表(受控表):当删除内部表时,HDFS上的数据以及元数据都会被删除
2.外部表:当除外部,HDFS上的数据不会被删除但是元数据会被删除
3.临时表(测试):在当前会话期间内存在,会话结束时自动消失,即退出hive操作时。
4.分区表:将一批数据按照一定的关键字分为多个目录进行存储。
5.分桶表:将一批数据按照指定好的字段和桶的数量,对指定字段的数据取模运算,分成不同的“桶”进行存储,方便随机取样以及join等操作。
具体的建表操作:
内部表:
CREATE TABLE gfstbl( //创建一个内部表
id INT,
name STRING,
age INT,
gfs ARRAY<STRING>,
address MAP<STRING,STRING>,
info STRUCT<country:String,province:String,city:String>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';
LOCATION "/test" //可以设置源数据的位置,若不设置默认就在Hive的工作目录区
内部表的其他创建方式:
创建表方式二
create table gfstbl1 like gfstbl;只是创建表结构
创建表方式三
create table gfstbl2 AS SELECT id,name,gfs,address from gfstbl; 会创建相应的表结构,并且插入数据
加载数据:
1、insert 新数据
2、load
3、查询其他表数据 insert 到新表中
临时表:
create TEMPORARY table ttabc(id Int,name String) 临时表的声明周期是一次会话
临时表在创建时会放在HDFS的临时目录中
进入hive shell 创建一张表,关闭shell后,表丢失
外部表:
create external table wc_external
(word1 STRING,
word2 STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
location '/test/external'; location可加可不加,不加location默认是在hive的工作目录区
分区表:
将一个大文件拆分成多个子目录进行存储,防止暴力扫描全表,导致效率过低
动态分区表与静态分区表的创建方式相同:
create table day_table (id int, content string)
partitioned by (dt string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' ;
具体区分为在插入数据时的操作:
动态:
from gfstbl_pt
insert into gfstbl_dynamic partition(sex,age)
select id,name,gfs,address,info,sex,age;
静态:
① insert into day_hour_table partition(dt=9,hour=1) values(1,"a2 bc");
②load data local inpath "/root/ceshi" into table day_table partition (dt="9-27");
总结:往静态分区中添加数据有四种方式:
(1)insert 指定分区
(2)load data 指定分区
(3)查询已有表的数据,insert到新表中
from day_hour_table insert into table newt partition(dt=01,hour=9898) select id,content
(4)alter table add partition创建空分区,然后使用HDFS命令往空分区目录中上传数据
(5)创建分区,并且指定分区数据的位置
分桶表:
将大文件拆分成小文件存储,提高join和抽样的效率
CREATE TABLE psnbucket( id INT, name STRING, age INT)
CLUSTERED BY (age) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
分桶表插入数据:
insert into table psnbucket_partition partition(height) select id, name, age,height from original;
分桶表的抽样操作:
select * from psnbucket tablesample(bucket a out of b on age); //抽样抽bucketNum/b个数据 a为第一个位置的数,后续的数以b为步长
========================================================================================================
补充:
Hive视图: 是sql语句执行结果的映射,查询视图时,视图的sql语句才会执行,称为懒执行。
视图的特点:
不支持物化视图
只能查询,不能做加载数据操作 load data into
视图的创建,只是保存一份元数据,查询视图时才执行对应的子查询
view定义中若包含了ORDER BY/LIMIT语句,当查询视图时也进行ORDER BY/LIMIT语句操作,view当中定义的优先级更高
view支持迭代视图
一旦创建成功,无法修改
CREATE VIEW IF NOT EXISTS view1 AS SELECT * FROM logtbl order by age;
创建视图的时候不会启动MR任务
select * from view1;
但是在查询视图的时候会启动MR任务
why?视图的创建,只是保存一份元数据,查询视图时才执行对应的子查询
show tables可以查看已经创建的视图
drop view view1 删除视图
Hive索引:标识数据位置的数据
创建索引的步骤:
①创建索引库,用于存放索引
create index t2_index on table psnbucket_partition(age)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild
in table t2_index_table;
索引库中只是保存一些元数据,比如 对哪个字段创建索引,对哪个表创建索引等
②alter index t2_index on psnbucket_partition rebuild;
这一步是真正的创建索引信息,并且存储到索引库中,若数据库有新增数据,也可以使用以上语句重建索引
查询索引:
show index on psnbucket_partition;
删除索引
drop index t2_index on psnbucket_partition;
删除索引的同时 索引库也会被删除