Hive-3.1.2(二)内外部表、分区表、分桶表概念及hiveSQL命令

内部表的概念

Hive默认建立的表是内部表,内部表create之后,然后加载hdfs上的数据,会移动物理数据到Hive的数据仓库默认目录(/user/hive/warehouse/xx.db/)下。

内部表drop之后,元数据和物理数据都会删除。

外部表

外部表在导入hdfs的数据后,数据并没有移动到自己的数据仓库目录下,也就是说外部表中的数据并不是由它自己来管理的!

外部表drop掉之后,元数据删掉了,但是实际物理数据还存在原位置。

分区表

Hive也支持分区表,对数据进行分区可以提高查询时的效率,普通表和分区表的区别在于,有大量数据增加的需要建分区表,且可以避免全表查询。

内外部表都可以是分区表。

在 Hive 中,表中的一个 Partition 对应于表下的一个目录,所有的 Partition 的数据都存储在最子集的目录中

总的说来partition就是辅助查询,缩小查询范围,加快数据的检索速度和对数据按照一定的规格和条件进行管理

分桶表

分桶表首先必须是内部表,创建的分桶表会以该字段做hash分区存储到不同的文件,方便数据抽样。创建分桶表有如下几步

  1. 创建带通的 table
create table table_name(name string),clustered by (name) into 3 buckets row format delimited fields terminated by ' ';
  1. 开启分桶机制
set hive.enforrce.bucketing=true;
  1. 往表中插入数据
# tmp 是提前准备好的
insert overwrite table table_name select * from tmp
  1. 抽样语句,x 抽取哪个桶的数据,y为数值,自己定。y必须是 table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比列。列入 table 总共分了3份,当y=3时,抽取(3/3)=1个bucket的数据,当y=6时,抽取(3/6=)1/2 个bucket的数据。
select * from table_name tablesample(bucket x out of y on name);

hiveSQL 命令

会被转出 Mapreduce的 用 * 表示

数据库

查询所有数据库

show databases;

创建数据库

create database depot_name;

删除数据库

drop database  depot_name;

创建普通表

create table table_name(id int,name string);

查看表结构

desc table_name

查询表

select * from table_name;

删除表

drop table table_name;

创建表,并指定以 ' ' 空格为分隔符

create table table_name(id int,name string) row format delimited fields terminated by ' ';

快速创建相同结构的表

create table table_name1 like table_name2;

创建外部表,把hive的目录以外的hadoop数据加载到hive中,并以指定结构

create external table table_name(id int,name string,score int) row format delimited fields terminated by ' ' location '/score';

* 手动插入数据

insert into table_name(1,'zs');

从文件读取数据插入到表中

load data local inpath '/home/tmp/a.txt' into table table_name;

* 表其table_name1表的数据插入到table_name2

insert overwrite table table_name2 select * from table_name1

* 将 table_name1 的结果写到 tmp目录下,该文件内容以 ' ' 空格分割。

insert overwrite local directory '/home/tmp' row format delimited fields terminated by ' ' select * from table_name1

* 将 table_name1 的结果写到HDFS 文件系统table_name2 文件夹下,该文件内容以 ' ' 空格分割。

insert overwrite directory '/table_name2' row format delimited fields terminated by ' ' select * from table_name1

修改表结构,为table_name 增加age 类型为 int

alter table table_name add columns(age int);

修改表名称,将table_name 修改为 table_name1

alter table table_name to table_name1;

分区

显示表分区

show partitions table_name

创建分区表,partitioned 字段可以不在字段列表中,生成的表中自动就会具有该字段。

create table table_name(id int,name string) partitioned by(parition_field string) row format delimited fields terminated by ' ';

创建外部分区表,partitioned 字段可以不在字段列表中,生成的表中自动就会具有该字段。

create external table table_name(id int,name string) partitioned by(parition_field string) row format delimited fields terminated by ' ' location '/xxxx';
增加分区

从文件读取数据,并把这批数据 按分区(cn) 加载到hive,overwrite 会让执行相同分区的数据覆盖原有相同分区的数据。去掉 overwrite 会追加到相同分区。

load data local inpath '/home/tmp/a.txt' overwrite into table table_name parition(parition_field='cn');
删除分区
alter table table_name drop partition(parition_field='parition_value');
修改分区
alter table table_name partition(parition_field='parition_value') rename to partition(parition_field='parition_value1')l

给已存在hadoop的hive里的文件,但不存在hive DB里的文件添加分区,这样hive DB的SQL就能识别从外部添加的新的文件或表。

alter table table_name add partition(partition_field='partition_value') location '/user/hive/warehouse/xxx.db/xxx/partition_field=partition_value';

会修复表结构(分区结构),同步haddop从外部添加的文件,同步到hive DB。

msck repair table table_name;

join 操作

两张表

hive> select * from rdb_a;
OK
1       lucy
2       jack
3       tony
 
hive> select * from rdb_b;
OK
1       12
2       22
4       32

join 或 inner join

select a.id,a.name,b.age from rdb_a a inner join rdb_b b on a.id=b.id;

1       lucy    12
2       jack    22

left join

select a.id,a.name,b.age from rdb_a a left join rdb_b b on a.id=b.id;

1       lucy    12
2       jack    22
3       tony    NULL

right join

select a.id,a.name,b.age from rdb_a a right join rdb_b b on a.id=b.id;

1       lucy    12
2       jack    22
NULL    NULL    32

full join 返回两个表的记录去重之和,关联不上的字段为NULL。

select a.id,a.name,b.age from rdb_a a full join rdb_b b on a.id=b.id;

1       lucy    12
2       jack    22
3       tony    NULL
NULL    NULL    32

left semi join 返回主表的KEY也在副表中的记录

select a.id,a.name from rdb_a a left semi join rdb_b b on a.id=b.id;

1       lucy
2       jack

cross join 笛卡尔积结果

select a.id,a.name,b.age from rdb_a a cross join rdb_b b;

1       lucy    12
1       lucy    22
1       lucy    32
2       jack    12
2       jack    22
2       jack    32
3       tony    12
3       tony    22
3       tony    32

其他

抽样数据

select * from table_name tablesample(1 rows);

退出

exit;
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,125评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,293评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 162,054评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,077评论 1 291
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,096评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,062评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,988评论 3 417
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,817评论 0 273
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,266评论 1 310
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,486评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,646评论 1 347
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,375评论 5 342
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,974评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,621评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,796评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,642评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,538评论 2 352

推荐阅读更多精彩内容