hive笔记-----表

一、托管表和外部表

托管表

加载数据到托管表时

create table managed_table(dummy string);

load data （local） inpath '/user/tom/data.txt' （overwrite） into table managed_table;

加入local是指从本地磁盘上上传文本文件，overwrite是删除目录中现有的所有内容，然后再去追加新的内容

丢弃表，元数据和数据会被一起删掉。最初的load操作是一个移动操作，所以数据会被彻底删除，这就是hive所谓的托管的含义

drop table managed_table;

外部表

create external table external_table(dummy string)

location '/user/tom/external_table';

load data inpath '/user/tom/data.txt' into table external_table;

在创建外部表时，hive不会检查外部表是否存在，这意味着可以把创建数据推迟到创建表之后才进行

丢弃表时，hive不会删除数据，只会删除相应的元数据

在选择使用哪种表时，普遍的做法是将存放在hdfs的初始数据集用作外部表进行使用，然后使用hive的变换功能把数据移到托管的hive表中。反之也成立，外部表可以用于从hive导出数据供其它应用程序使用

insert overwrite directory也可以把数据导出到Hadoop文件系统中，这种情况和外部表不同，不能控制输出的格式，导出的数据是用ctrl+A分隔的文本文件，复杂数据类型则以json表示进行序列化

二、分区和桶

分区

partitioned by子句中的列定义是表中正式的列，称为分区列，但是数据文件不包含这些列的值，它们源于目录名

create table logs (ts bigint,line string) partitioned by (dt string,country string);

在把数据加载到分区表的时候，要显示指定分区值

load data local inpath 'input/hive/partitions/file1' into table logs partition(dt='2016-05-05',country='GB');

显示某个表中有哪些分区

show partitions logs

桶

划分桶的理由有两个，一是获得更高的查询效率，连接两个在相同列上划分了桶的表，可以使用map端连接高效的实现

二是使取样更加高效，在处理大规模数据集时，在开发和修改阶段，如果能在数据集的一小部分数据上进行试运行查询，会带来很多方便

create table bucketed_users(id int,name string) clustered by (id) into 4 buckets;

桶中的数据可以根据一个或多个列进行排序，由于这样对每个桶的连接变成了高效的归并排序，因此可以进一步提升map端连接的效率

create table bucketed_users(id int,name string) clustered by (id) sorted by (id asc) into 4 buckets;

要向分桶后的表中填充成员，需要将hive.enforce.bucketing设置为true

之后使用命令插入数据insert overwrite table bucketed_users

一个作业产生的桶和reduce任务个数相同

取样分桶表非常高效，因为取样过程中不需要整体遍历输入样本集，只需要遍历相应的分桶即可

select * from bucketed_users tablesample(bucket 1 out of 2 on id);

三、存储格式

hive从两个维度对表的存储进行管理，行格式和文件格式

行格式是指一行中的字段如何存储，文件格式是指一行中的字段容器的格式

如果在创建表时没有使用row format 或stored as字句， 那么hive所使用的默认格式是分隔的文本，每行存储一个数据行

create table ...

等价于下面显示说明的语句

create table ...

row format delimited

 fields terminated by '\001'

 collection items terminated by '\002'

 map keys terminated by '\003'

 lines terminated by '\n'

stored as textfile;

这里的\001是行内的默认分隔符，\002是对array，struct，map的键值对中的元素进行分隔，\003用于分隔map的键和值

顺序文件是面向行的，如果想使用压缩顺序文件来存储hive产生的表，则需要设置几个相应的属性来使用压缩

create table compressed_users(id int,name string) stored as sequencefile;

set hive.exec.compress.output=true;

set mapred.output.compress=true;

set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;

insert overwirte table compressed_users select * from users;

可以使用如下的子句来启用面向列的存储(RCFILE)

create table ...

row format serde 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe‘

stored as RCFILE；

RegexSerDe

create table stations(usaf string,wban string,name string)

row format serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'

with serdeproperties("input.regex"="(\\d{6}) (\\d{5}) (.{29}) .*");

通过识别一组一组的括号来确定列，在这个示例中，有三个捕获组，usaf(六位数的标识符)，wban（五位数的标识符），name（29个字符的定长列）

在加载操作中不执行表的SerDe，查询的时候才会使用

四、导入数据

load data ...是一种加载数据的方法

第二种是

insert overwrite table target select col1,col2 from source;

等价于

from source insert overwrite table target select col1,col2

因此

多表插入就可以使用多个insert来完成了，该方法只需要对表扫描一次，就可以完成多种数据结果的插入，高效的没的说

即

from source

insert overwrite table target select col1,col2

insert overwrite table ...

...

对于分区的表，可以使用partition子句来指明数据要插入哪个分区

insert overwrite table target partition(dt='2016-05-05') select col1,col2 from source

也可以在select语句中通过使用分区值来动态指明分区

insert overwrite table target partition(dt) select col1,col2,dt from source

hive中不支持直接插入数据项

第三种

create table ...as select

该方法是原子的，如果select出现问题，则新表也不回产生

五、表的修改

alter table source  rename to target;

出更新元数据外，还将表目录移动到新名称下对应的目录下

alter table target add columns(col3 string)

hive不允许更新已有的记录，因此更常见的做法是创建一个带有新列的新表，然后将原来的数据导入到新表内

六、表的丢弃

drop table

对于托管表，元数据和数据都将被删除，对于外部表，只删除元数据

若想值删除数据，保留表的元素数据

dfs -rmr /usr/hive/warehouse/my_table

或

create table net_table like exist_table