hive实战读书笔记(第4章)hive表DDL

schema-on-read

hadoop提供了存储和处理任何结构化,半结构化,非结构化数据的能力,hive允许用户在这些数据上创建一个元数据层,并使用sql接口来访问该层.
hive并不是一个数据库,而是一个友好的接口,可以查询hdfs上的底层数据文件,是一种读取数据的模式,因而叫做schema-on-read

hive的数据模型

hive的数据模型与关系型数据库非常类似,由表,列,行和分区组成,这些对象是在名为hive metastore的元数据层中定义的逻辑单元,除了常见的数据段,hive引入了"桶"的结构.
hive的元数据存储被称为hive metastore,它由命名空间,对象定义,底层数据的详细内容组成,目前metastore是在RDBMS中创建的,快速访问这些信息非常重要.

模式/数据库

模式或者数据库用于保存一组表元数据信息的命名空间,hive管理的所有数据都存储在hive定义的顶层目录下,

为什么有多个模式/数据库

可以为不同数据库指派不同的属性

创建数据库

create database shopping;

创建了一个新的名为shopping的命名空间,hdfs中,对应的增加了shopping.do的目录

describe database extended shopping;

查看该database的属性

更改数据库

alter database shopping 
set dbproperties('department' = 'sales')

删除数据库

drop database shopping cascade;

cascade强制删除该数据库,如果内部有表的话也会删,默认是restrict,内部有表不会删

列出数据库

show databases;

hive 中的数据类型

hive中分为基本数据类型和复杂数据类型,这些类型都是用java实现的

基本数据类型

• 数值型:正负数字和浮点数
• 日期和时间型:存放时间值
• 字符型:将字符和数字存放在字符串中
• 布尔型:True和False
• 二进制型:二进制数的可变长数组

选择数据类型

建议在hive建表时,不要选择非常严格的数据类型,例如创建String类型比创建varchar(25)有更好的扩展性

复杂数据类型

由多个基本数据类型组成,使用本地的序列化器和反序列化器在内部实现,以集合格式存储数据,不需要对数据进一步分解为多个单独的字段;
复杂数据通常又称为复合集,
四种复杂类型

• 数组
• Map
• 结构体
• 联合体

数组

由数据类型相似的数据元素构成有序的符合集,但是不能定义最大的元素个数

Map

无序的key/value集合
key是基本类型,value可以是任意类型

结构体

结构体是一个对象,对象的字段可以是任何数据类型

联合体

联合体提供了一种方法,可以将不同的数据类型的元素存储在同一个字段的不同行中,
使用场景:客户的手机号,可能有一个或多个,这个时候使用联合体分多行存储

表

hive数据模型包含一个关于数据逻辑行和列视图,被称为表;和关系型数据库一样,hive表由一个关于数据的二维视图定义,数据独立于表存在,数据存储在hdfs中,表定义存储在名为HCataLog的关系数据库存储中.
hive表和关系型数据库表的重要区别

• hive表的数据与表定义是松耦合的,关系型数据库,删除表时会删除表定义和数据,而hive中,如果将表定义为外部表,删除表定义和删除底层数据是相互独立的
• hive中单个数据集可以有多个表定义
• hive表中的底层数据可以以多种格式存储

创建表

create table tablename(name1   type1,
name2 type2);
create external table tablename(name1   type1,
name2 type2) 
comment "   "
location '/path/you/want';

列出表

show tables in databaseName

内部表/外部表

hive的表分为内部表和外部表,表的类型决定了hive如何加载,存储,控制数据
外部表
底层数据可能被多个实例所使用,因此,删除表定义的时候,不想让底层数据被删除,这个时候使用外部表
下边列举一些场景:

• 删除定义无需担心删除底层数据
• 数据存储在文件系统而不是hdfs中,例如使用S3或微软的Azure的WASB存储数据,从多个集群访问这些数据
• 自定义位置存储表数据
• 不准备基于另一个表创建(create table as select)
• 数据将被多个处理引擎访问,使用hive来读表,spark也使用该表
• 希望一个数据集上创建多个表定义,删除一个定义,不会影响其他的表

内部表和受控表

内部表是指数据有hive管理的表,当删除内部表的时候,hive也将删除底层数据,这类表在hadoop中不常用,大多数环境中,将表删除了,数据还需要保留
一般使用场景:

• 数据时临时存储的
• 访问数据的唯一方式是通过hive,而且使用hive完全处理表和数据的声明周期

表的属性

创建表,或者使用tblproperties子句修改表的时候,可以在表层级上指定一些属性
一些重要的表层级属性

• last_modified_user
• last_modified_time
• immutable 如果表中有数据,将不能添加,添加会报错
• orc.compress orc存储使用的压缩算法
• skip.header.line.count 如果直接是使用文件导入的,前n行不做为数据展示

生成已有表的create table的命令

show create table tableName;

查看建表语句

分区和分桶

一个库有多个表
一张表由多个分区
一个分区有多个桶

分区

分区在关系型数据库中通常用于提高性能和实现更好的数据管理,hive中也相同
hive中的分区表有一个或多个分区键,数据被分割成若干个逻辑块并存放在单独的路径中,每个分区键都为表的存储添加了一个目录层结构;

分区注意事项

分区对于非常特别的查询子集而言,可改善性能,不需要检索查询结果的分区进行剪枝,让hdfs上的数据更加的分片化,改进可维护性
如果分区不得当,也会导致性能下降,增加数据加载,检索的开销,比如:创建了很多的分区,每个分区都含有很多小的数据块,文件可能会比较小,hadoop存放小文件会比存放大文件慢的多
hive分区时需要按照以下最佳实践:

• 挑选一列作为分区键,唯一值的个数在较低值到中间值之间
• 避免分区小于1GB(越大越好)
• 分区较多时,调整hiveserver2和hive metastore的内存
• 当使用多列作为分区键时,对于每一个分区键列的组合都要创建一个子目录的嵌套树,避免深入嵌套
• 使用hive流处理添加数据,如果多个会话同时向同一个分区写数据,会导致锁闭
• 可以修改某一分区表的模式,然而,一旦结构发生改变,无法在已有分区中修改数据了
• 如果要将数据并行插入到多个分区,应将hive.optimize.sort.dynamic.partition设置为true

对日期列高效分区

日期型是分区键最常用的候选对象,最常见的就是日志文件加载到hdfs中并且希望使用hive来查询
分区查询时可以直接当做普通字段使用where语句进行查询

分桶

分桶是hive中另一种将数据切分成更小片的方法,如果分区键有很多不同的值,而且每个分区都没有多少值,这样就会导致分区数过多,分区就不是一个好的选择
分桶可以为每个表的分桶列定义桶的最大数目,hive中的一个分区就是一个目录,分区键的值存放在实际的分区目录名中,分区键实际上是一个虚拟列
分桶中,每个桶都是一个保存实际数据的文件,这些数据基于一种散列算法进行分割,分桶并不会增加一个虚拟列
分桶可以提升多种查询的性能

分桶的注意事项

对于高效抽样和改进查询性能,分桶是个很好的特性,但也有一些限制
最佳实践

• 选择唯一值个数多的桶键,减少数据倾斜
• 采用质数作为桶的编号
• 桶的数据倾斜可以为倾斜的值单独建桶
• 分桶对于连接在一起的事实表非常有用
• 需要连接在一起的表,桶的数目需要相同,或者一个桶数是另一个的因子
• 一个cpu核只会对一个桶写入操作,大型集群,如果桶数目少,集群会利用率不足
• 一旦建好,桶的数目将不能修改
• 仔细选择建桶的列
• 获取桶文件的大小至少是1gb
• 通过设置hive.enforce.bucketing=true实现强制分桶
• 对于map端连接,分桶表要比非分桶表速度快的多
• 分桶也允许按照一列或多列对每个桶中的数据进行排序
  临时表
  某一会话存续期间保留数据

更改表

alter table修改已有的表结构

• 重命名表
• 修改表的存储属性

ORC文件格式

用于减少从磁盘读取的数据量,有很多新的性能优化都仅采用ORC文件,推荐将原始数据修转换为ORC文件.
合并表的文件

alter table states concatenate

会将多个数据文件合并成较大的文件

更改表分区

• 添加分区
• 重命名分区

修改列

添加列

新加的列添加在当前列之后,分区列之前

删除表/分区

删除表

drop table table_name;  删除的数据会移动到trash中
drop table table_name purge; 

删除分区

alter table drop parition

保护表/分区

alter table enable no_drop ;

防止用户删除表

alter table transactions enable offline

表离线状态,防止用户查询到

create table的其他选项

CTAS

create table as select (ctas)

利用结果集创建表

create table table_name1 like table_name2

只创建表结构,不复制数据