Hive笔记三之内部表,外部表,分区表,桶表

内部表

也称为受控表，表中的数据受表定义影响，表删除后表中数据随之删除。在COLUMNS_V2表中TBL_TYPE显示为MANAGED_TABLE

在http://shb01:50070/可以看到表信息

表删除后实际上是从hdfs上将t1目录移到回收站中，另外删除TBLS表中的表定义信息

hive> drop table t1;

Moved:'hdfs://shb01:9000/user/hive/warehouse/t1' to trash at:hdfs://shb01:9000/user/root/.Trash/Current

OK

Time taken: 1.789 seconds

外部表

数据不受表定义影响，表删除后数据仍在。

[root@shb01 data]# more t4_hql.hql

create external table t4_exter1(

id string,name string)

row format delimited

fields terminated by '\t' location'hdfs://shb01:9000/data/';

 

hive> source /usr/local/data/t4_hql.hql;

在TBLS表中可以看到外部表信息

在http://shb01:50070/中也可以看到外部表信息，这里的t4表就是一个外部表

也可以在创建表时不指定数据，之后通过alter指定数据

alter table t4_exter1 set location'hdfs://shb01:9000/data/t4';

 

hive> drop table t4_exter1;                     

OK

Time taken: 0.274 seconds

外部表只是对数据的引用，如果删除外部表data/t4的数据不会被删除，hdfs中/user/hive/warehouse目录下的信息不会被删除，而删除的只是TBLS表中的表定义信息。

 

实际中当系统只有一份公共数据但是多个数据组都需要使用时为避免误删除就需要使用外部表。

内部表和外部表之间还可以互相转换，但是这是个非常危险的操作应尽量避免。

alter table t2 set tblproperties('EXTERNAL'='TRUE');

alter table t2 settblproperties('EXTERNAL'='FALSE');

分区表

分区表也是内部表，创建表时可以同时为表创建一个或多个分区，这样我们在加载数据时为其指定具体的分区，查询数据时可以指定具体的分区从而提高效率，分区可以理解为表的一个特殊的列。关键字是partitioned。

分区表实际上是将表文件分成多个有标记的小文件方便查询。

创建分区表

hive> create table t5_part(id int,namestring)      

   > partitioned by (year int,city string)

   > row format delimited fields terminated by '\t';

OK

Time taken: 0.349 seconds

 

查看分区表，其中# Partition Information为分区信息，有两个分区year和city

hive> desc extended t5_part;

OK

id                      int                                        

name                    string                                     

year                    int                                        

city                    string                                     

                

# Partition Information         

# col_name              data_type               comment            

                

year                    int                                        

city                    string

 

加载数据

第一次分区信息为year=2010, city=BeiJing

hive> load data local inpath'/usr/local/data/t4' into table t5_partpartition(year=2010,city='BeiJing'); 

Loading data to table default.t5_partpartition (year=2010, city=BeiJing)

Partition default.t5_part{year=2010,city=BeiJing} stats: [numFiles=1, numRows=0, totalSize=13, rawDataSize=0]

OK

Time taken: 3.317 seconds

 

第二次分区为year=2013, city=ShangHai

hive> load data local inpath'/usr/local/data/t4' into table t5_part partition(year=2013,city='ShangHai');

Loading data to table default.t5_partpartition (year=2013, city=ShangHai)

Partition default.t5_part{year=2013,city=ShangHai} stats: [numFiles=1, numRows=0, totalSize=13, rawDataSize=0]

OK

Time taken: 0.801 seconds

 

查询全部信息

hive> select * from t5_part;

OK

1      jack    2010    BeiJing

2      tom     2010    BeiJing

1      jack    2013    ShangHai

2      tom     2013    ShangHai

Time taken: 0.165 seconds, Fetched: 4row(s)

 

根据分区查询，分区很像是一个特殊的列

hive> select * from t5_part whereyear=2010 and city='BeiJing';

OK

1      jack    2010    BeiJing

2      tom     2010    BeiJing

Time taken: 0.112 seconds, Fetched: 2row(s)

 

分区表加载数据必须指定分区否则会报错

hive> load data local inpath'/usr/local/data/t4' into table t5_part;

FAILED: SemanticException [Error 10062]:Need to specify partition columns because the destination table is partitioned

查看分区表的分区信息

hive> show partitions t5_part;

OK

year=2010/city=BeiJing

year=2013/city=ShangHai

year=2016/city=nanjing

Time taken: 1.685 seconds, Fetched: 3row(s)

hive>

在http://shb01:50070下可以看到分区表信息

在hadoop下分区表是把分区当成目录的

其实我觉得分区表在加载数据时就是让数据带着标签入库方便后续大数据量条件下的查询操作。

桶表

之前介绍分区表是将大的表文件划分成多个小文件以利于查询，但是如果数据分布不均衡也会影响查询效率。桶表可以对数据进行哈希取模目的是让数据能够均匀的分布在表的各个数据文件中，它其实是对分区表的补充。以往查询时需要将数据全部加载到内存中再过滤而分桶后则可以只将表的部分文件加载到内存中提高查询效率。

分桶表属于内部表，关键字clustered。

 

hive> sethive.exec.mode.local.auto=true;

hive> set hive.enforce.bucketing=true;

修改参数允许强制分桶，也可以修改hive_site.xml文件中的参数改为true

<property>

   <name>hive.enforce.bucketing</name>

   <value>false</value>

   <description>Whether bucketing is enforced. If true, whileinserting into the table, bucketing is enforced.</description>

 </property>

 

创建分桶表

下面的例子是根据id取模(id%3),into 3 buckets中的3就是取模的值。

hive> create table t6_bucket (id string)clustered by (id) into 3 buckets;

OK

Time taken: 2.773 seconds

 

加载数据

桶表数据必须从其他表转换

hive> insert into table t6_bucket selectid from t5_part;

不知道怎么的我的机器反应很慢

可以看到产生3个数据文件，这个过程有一次说明hive是sql解析引擎最终又转化为MapReduce任务。

 

在http://shb01:50070上查看

分别产生了3个文件

 下面是hdfs上显示的其中一个文件的内容

[root@shb01 data]# hadoop fs -text/user/hive/warehouse/t6_bucket/000001_0

16/12/26 06:50:18 WARNutil.NativeCodeLoader: Unable to load native-hadoop library for yourplatform... using builtin-java classes where applicable

1

1

1