hive 表结构

一、数据存储

数据存储位置:

    
      hive.metastore.warehouse.dir
      /apps/hive/warehouse
    

二、内部表（Table）

• 与数据库中的表相似。
• 每一个都有相对应的目录， 存储在hive.metastore.warehouse.dir下，以表名为目录。
• 删除表时，元数据与数据都会被删除。

2.1、创建一个内部表，和mysql创建表基本一致,默认表存储在hive.metastore.warehouse.dir.

-- 创建内部表
create table t1(
id int,
name string,
age int);
 insert into t1 values(1, 'chb', 20), (2, 'ling',20),(3,'zhangsan', 34);

2.1.1、可以指定表的存储位置， 通过location

-- 指定内部表位于hdfs中的位置
create table t2(
id int,
name string,
age int)
location '/mytable/hive/t2';

2.1.2、设置分割符， 默认存储按照制表符。

-- 设置分割符
create table t3(
id int,
name string,
age int)
row format delimited fields terminated by ',';

2.2、通过子表查询创建内部表

首先向他中插入几条数据

2.2.1、创建表：

-- 通过子查询创建表
create table t4 
as 
select * from t1;

提交job创建表：

2.2.2、子查询创建表，同时指定分隔符

-- 指定分隔符 
create table t5
row format delimited fields terminated by ','
as 
select * from t1;

查看文件中分割符

三、分区表：根据某种规则进行分区，

  分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多

3.1、创建分区表

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

3.1.1、添加分区

设置分区的location 
alter table idc_20180315 add if not exists  partition(dt='20180315', hour='11') \
location '20180315/11';

3.1.2、删除分区

ALTER TABLE table_name DROP PARTITION(...);

hive> ALTER TABLE idc_20180315 DROP PARTITION(dt='20180315', hour='11');
Dropped the partition dt=20180315/hour=11
OK
Time taken: 0.343 seconds
hive> show partitions idc_20180315;
OK
dt=20180315/hour=09
dt=20180315/hour=10
Time taken: 0.122 seconds, Fetched: 2 row(s)
hive> 

2.1.3、查看分区

hive> show partitions idc_20180315;
OK
dt=20180315/hour=09
dt=20180315/hour=10
dt=20180315/hour=11
Time taken: 0.096 seconds, Fetched: 3 row(s)
hive> 

3.2、范例表p0

hive> select * from p0
    > ;
OK
1       chb     M
2       a       F
3       b       M
4       c       F
5       d       M
Time taken: 0.435 seconds, Fetched: 5 row(s)
hive> 

3.2.1、向分区表中插入数据

错误： 分区表只有两列， 不应该使用select *

hive> insert into partition_table partition(gender='M') select * from p0 where gender='M';
FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different ''M'': Table insclause-0 has 2 columns, but query has 3 columns.

正确的执行语句

3.2.2、数据存储位置， 一个分区一个目录

3.2.3、如何验证分区表效率提高： 使用explain生成一条sql的执行计划 3.2.3.1、对于没有分区的p0

hive> explain select * from p0 where gender='M';
OK
Plan not optimized by CBO.

Stage-0
   Fetch Operator
      limit:-1
      Select Operator [SEL_2]
         outputColumnNames:["_col0","_col1","_col2"]
         Filter Operator [FIL_4]
            predicate:(gender = 'M') (type: boolean)
            TableScan [TS_0]
               alias:p0

Time taken: 0.064 seconds, Fetched: 12 row(s)

3.2.3.2、分区表partition_table

hive> explain select * from partition_table where gender='M';
OK
Plan not optimized by CBO.

Stage-0
   Fetch Operator
      limit:-1
      Select Operator [SEL_2]
         outputColumnNames:["_col0","_col1","_col2"]
         TableScan [TS_0]
            alias:partition_table

Time taken: 0.397 seconds, Fetched: 10 row(s)

明显少了两行。

3.3、分区表注意事项

1．创建二级分区表
	hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';
2．正常的加载数据
（1）加载数据到二级分区表中
	hive (default)> load data inpath '/tmp/hivetest/dept' into table  dept_partition2 partition(month='202007', day='22');
（2）查询分区数据
	hive (default)> select * from dept_partition2 where month='201708' and day='12';

3.3.1、把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

create table deptp3(
deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t'
location '/tmp/hivetest/deptp3';

（1）方式一：上传数据后修复
上传数据
	hive (default)> dfs -mkdir -p /tmp/hivetest/deptp3/month=202007/day=22;
	hive (default)> dfs -put /uardata1/hivetest/dept /tmp/hivetest/deptp3/month=202007/day=22/;
查询数据（查询不到刚上传的数据）
	hive (default)> select * from deptp3 where month='202007' and day='22';
执行修复命令
	hive> msck repair table deptp3;
再次查询数据
	hive (default)> select * from deptp3 where month='202007' and day='22';


（2）方式二：上传数据后添加分区
上传数据
	hive (default)> dfs -mkdir  /tmp/hivetest/deptp3/month=202007/day=20;
	hive (default)> dfs -put /uardata1/hivetest/dept  /tmp/hivetest/deptp3/month=202007/day=20;
执行添加分区
	hive (default)> alter table deptp3 add partition(month='202007', day='20');
查询数据
	hive (default)> select * from deptp3 where month='202007' and day='20';

（3）方式三：创建文件夹后load数据到分区
创建目录
	hive (default)> dfs -mkdir -p /user/hive/warehouse/deptp3/month=202007/day=19;
上传数据
	hive (default)> load data local inpath '/uardata1/hivetest/dept' into table deptp3 partition(month='202007',day='19');
查询数据
	hive (default)> select * from deptp3 where month='202007' and day='19';

四、外部表

  数据不存在与hive仓库中，而是在hdfs上；数据加载与表的创建是同时产生的。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

4.1、创建三个文件a,b,c ，将文件上传到hdfs上/input/hive/的目录，

[hdfs@test]cat a b c
a,tom
b,jone
c,mike
d,jsper
e,john


hdfs dfs -put a  /input/hive/    
hdfs dfs -put b /input/hive/    
hdfs dfs -put c /input/hive/    

4.2、创建外部表， 使用关键字external, 指定分隔符, 表的创建，与数据加载同时发生， 所以使用location指定数据的存储位置。

create external table ext_table(
id string,
name string)
row format delimited fields terminated by ','
location '/input/hive/';

4.3、外部表只是一个过程,数据加载和创建表同时完成 ，（这个在上面已经体现了) 并不会移动到数据仓库目录中，只是与外部数据建立一个链接，当删除一个外部表时，只会删除该链接，但是输出外部数据， 外部表就会减少数据。

####删除文件a

hdfs dfs -rm /input/hive/a

外部表数据减少两条

hive> select * from ext_table;
OK
c       mike
d       jsper
e       john
Time taken: 0.402 seconds, Fetched: 3 row(s)
hive> 

4.4、内部表与外部表的相互转换

（1）查询表的类型
hive (default)> desc formatted student2;
Table Type:             MANAGED_TABLE
（2）修改内部表student2为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
（3）查询表的类型
hive (default)> desc formatted student2;
Table Type:             EXTERNAL_TABLE
（4）修改外部表student2为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
（5）查询表的类型
hive (default)> desc formatted student2;
Table Type:             MANAGED_TABLE

注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！

五、桶表（Bucket table) 六、视图： 简化复杂的查询

6.1、基表

6.2、创建视图

6.3、视图的查询和普通表一样 6.4、视图是一个虚表， 不存储数据的, 如果存储数据只有一种特殊的视图（物化视图），只存在于orcale和myql中， hive不存在物化视图.