内部表的概念

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 ' ';

2. 开启分桶机制

set hive.enforrce.bucketing=true;

3. 往表中插入数据

# tmp 是提前准备好的
insert overwrite table table_name select * from tmp

4. 抽样语句，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;