1.hive中表的类型
- 内部表(受控表):当删除内部表的时候,hdfs上的数据以及元数据都会被删除。
- 外部表:当删除外部表的时候,HDFS上的数据不会被删除,但是元数据会被删除。
- 临时表(测试环境):在当前会话期间内存在,会话结束自动消失,生命周期随之session。
- 分区表:将一批数据分成多个目录来存储。
- 分桶表:
2.内部表
①创建表的方式有三种:
- 直接创建
CREATE TABLE gfstbl( //table前没有修饰符,说明创建的是一个内部表
id INT,
name STRING,
age INT,
gfs ARRAY<STRING>,
address MAP<STRING,STRING>,
info STRUCT<country:String,province:String,shi:String>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' ' //字段间的分隔符
COLLECTION ITEMS TERMINATED BY ',' //集合元素之间的分隔符
MAP KEYS TERMINATED BY ':' //map中key和value之间的分隔符
LINES TERMINATED BY '\n'; //行与行之间的分隔符
LOCATION "/test" //可以设置源数据的位置,若不设置默认就在Hive的工作目录区
- 创建一张表和已存在的一张表的结构相同
create table gfstbl1 like gfstbl //只会创建表结构
- 创建一张表和已存在的一张表的结构相同,并且还可以带有数据
create table gfstbl2 AS SELECT id,name,gfs,address from gfstbl; //会创建相应的表
结构,并且插入数据。查哪个字段就会插入哪个字段的数据。
②查看表描述信息
DESCRIBE [EXTENDED|FORMATTED] table_name
EXTENDED极简的方式显示(默认就是极简的方式)
FORMATTED格式化方式来显示
③插入数据的方式
1、insert 新数据
2、load
3、查询其他表数据 insert 到新表中
模板:
insert into rest select count(*) from table;
习惯写法 from提前 减少SQL代码的冗余
from day_hour_table
insert into rest
select count(*) ;
④查询表中字段的值
- 字段类型为数组,采用 字段名[下标]来获取。
- 字段类型为map,采用 字段名[“map的key”]来获取。
- 字段类型为STRUCT,采用 字段名.STRUCT的属性来获取。
3.临时表
①创建临时表(临时表不支持分区)
create TEMPORARY table ttabc(id Int,name String) //TEMPORARY代表这是一个临时表。
②shell
凡是shell都遵循repl机制。
r :read 读
e:evaluate 计算
p:print 打印
l :loop 循环
③使用yarn命令干掉某一个application
yarn application -kill job_1540028621068_0006 // id是在job启动时就给出了。
④临时表也是一个内部表,操作和内部表一样。
4.外部表
①创建外部表
create external table wc_external //external代表这是一个外部表
(word1 STRING,
word2 STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
location '/test/external'; location可加可不加,不加location默认是在hive的工作目录区
②往表中添加数据
1、将按照wc_external表的规则 创建了一个新的文件
hello bj
hello sh
将这个问题拷贝到这个表的工作目录区中,然后查询这个表数据量增多
2、load data inpath "/test/external/t" into table wc_external;
将工作目录区中文件再次添加到这个表中,发现数据量没有增量
3、将文件上传到了hdfs的/根目录
load data inpath "/t" into table wc_external;
数据量增多了,但是/t文件被剪贴了
5.分区表
①为什么创建分区表?
为了防止暴力扫描全表。可以提高查询效率。
②静态分区表
创建单分区表
create table day_table (id int, content string)
partitioned by (dt string) //指定分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
操作单分区表
insert单条插入的方式往分区表中插入数据:
insert into day_table partition (dt = "9-26") values(1,"anb");
load批量插入的方式往分区表中插入数据:
load data local inpath "/root/ceshi" into table day_table partition (dt="9-27");
删除Hive分区表中的分区
ALTER TABLE day_table DROP PARTITION (dt="9-27");
创建多分区表
create table day_hour_table (id int, content string)
partitioned by (dt int,hour int) //指定多个分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
操作多分区表
加载数据:
insert单条插入的方式往分区表中插入数据:
insert into day_hour_table partition(dt=9,hour=1) values(1,"a2 bc");
insert into day_hour_table partition(dt=9,hour=2) values(3,"a2 bc");
insert into day_hour_table partition(dt=8,hour=1) values(3,"a2 bc");
insert into day_hour_table partition(dt=8,hour=2) values(3,"a2 bc");
load批量插入的方式往分区表中插入数据:
load data local inpath "/root/ceshi" into table day_table partition (dt=10,hour=10);
删除Hive分区表中的分区
ALTER TABLE day_table DROP PARTITION (dt=10,hour=10);
创建/添加分区
创建一个空分区:ALTER TABLE day_hour_table ADD PARTITION (dt=10000, hour=2000);
然后将数据上传到空分区对应的目录下,分区表中就会显示数据
创建一个空分区并且将空分区指向数据位置:
ALTER TABLE day_hour_table ADD PARTITION (dt=10000, hour=2000) location "/test"
往分区中添加数据的五种方式
(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)创建分区,并且指定分区数据的位置
③动态分区表
动态分区表的概念
静态分区表,一个文件数据只能导入到某一个分区中,并且分区是用户指定的,这种方式
不够灵活,业务场景比较局限。动态分区可以根据数据本身的特征自动来划分分区,比如
我们可以指定按照数据中的年龄、性别来动态分区。使用动态分区表,要修改两个配置信息
set hive.exec.dynamic.partition=true; //开启动态分区
set hive.exec.dynamic.partition.mode=nostrict; //使用非严格模式。严格模式是指必须要有一个静态分区
创建动态分区表
创建动态分区表的语句与创建静态分区表的语句是一模一样的,只是在指定分区的时候用表中的字段来指定。
partitioned by (sex string,age INT)
往动态分区表中加载数据
往动态分区表中加载数据不能使用 load data 。load data只是将数据上传到HDFS指定目录中。
我们之前使用load data往分区表导入数据的时候,都是要指定partition分区的,这样他才会知
道将数据上传到HDFS的哪一个分区。
但是如果我们还是采用load data指定分区的话,那就不是动态分区表,还依然是静态分区表
所以得采用 from insert的方式往动态分区表中插入数据。
④查看分区数
show partitions table_name //动态,静态都可以查看
6.分桶表
①分桶表的原理
分桶表是对列值取哈希值的方式,将不同数据放到不同文件中存储,一个文件对应一个桶
由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中
对于hive中每一个表、分区都可以进一步进行分桶。
好处:提高了join的效率;提高了随机抽样的效率。
使用分桶表要修改set hive.enforce.bucketing=true;
②创建分桶表
CREATE TABLE psnbucket( id INT, name STRING, age INT)
CLUSTERED BY (age) INTO 4 BUCKETS // 指定按照age的哈希值与4取模来分桶
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
③往分桶表中插入数据
不能使用load data,只能使用insert
④抽样
select * from psnbucket tablesample(bucket X out of Y on age);
X代表从哪个桶开始抽取数据
Y必须为该表总桶数的倍数或因子,表示步长,