Hive表DDL

一、hive数据模型

数据模型提供了一种组织数据元素并将它们相关关联的方法。hive的数据模型和各种关系数据库非常相似。

 

创建数据库

CREATE DATABASES shopping；

语法结构：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

　　[COMMENT database_comment]　　　　　　                                        //关于数据块的描述

　　[LOCATION hdfs_path]　　　　　　　　　　                                          //指定数据库在HDFS上的存储位置

　　[WITH DBPROPERTIES (property_name=property_value, ...)];　　　　 //指定数据块属性

完整示例：

CREATE DATABASE IF NOT EXISTS shopping COMMENT 'store all shopping basket data' LOCATION '/user/retail/hive/shopping.db' WITH DBPROPERTIES('purpose'='testing');

该命令将创建一个名test1的新命名空间，以及一个名为/user/retail/hive/test.db的目录。使用WITH DBPROPERTIES子句。可以将任何自定义属性指派给数据库。你可以使用DESCREBE DATABASE EXTENDED 命令来查看这些属性，如下：

查看表描述信息：

DESCRIBE DATABASE EXTENDED test1;

更改数据库：

一旦创建了数据库，就可以使用alter database 命令修改其元数据属性（DBPROPERTIES）或OWNER属性，如下所示。

ALTER DATABASES shopping SET DBPROPERTIES('department'='SALES');

删除数据库：

DROP DATABASE database_name [RESTRICT|CASCADE];(样例：DROP DATABASE shopping CASCADE);

在这个命令中，CASCADE的使用是可以选择的，这允许你删除数据库时将已有表一起删除。该命令将删除属于shopping数据库的所有内部表和外部表。

DROP DATABASE 的默认行为时RESTRICT，这意味着如果数据库中有任何表，则执行该命令将失败。

列出数据库：

SHOW DATABASES [LIKE 'identifier_with_wildcards'];

SHOW DATABASES LIKE 's*';  将会列出shopping数据库。

Hive中的数据类型

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

基本数据类型：

就像关系型数据库一样，Hive中的每个列值都有其数据类型，也有约束和有效取值范围。这些数据类型的行为就像它们在Java中实现底层数据类型。Hive中各个基本数据类型如下：

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

选择数据类型：

因为很多情况下用户不了解每天数据的类型和长度等等，因此采用这种具有限制性的数据类型可能并不是总是有效的。如果数据类型过于严格，Hive会按照预定义列的宽度将数据截断而不做任何警告。因此建议在hive中创建表时不要选择非常严格的数据类型。

例如：创建一个String类型的表比将其创建为VARCHAR(25)具有更好的灵活性。

复杂数据类型：

Hadoop允许将任何类型的数据存储到其文件系统中并且使用schema-on-read模式读取，因此传统的规范化规则不是总是适用于底层数据。复杂数据类型通常又称复合集，它们对于将实际数据映射到模式层非常有用。

Hive有以下四种复杂模式：

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

数组

Hive中数组是一个数据类型相似的数据元素构成的有序复合集。这些元素可用从0开始的顺序下标值来表示。你可以使用括号和相应的下标值来访问这些元素。与Java这样的编程语言的数组不同，不能在Hive数组中定义最大元素。

例如你可以声明一个ITEMS数组来保存字符串值，如下所示：

ITEMS ARRAY<"Bread","Butter","Organic Eggs">

字符串的复合集有一个预定义的排序，因此可以通过从0开始的索引来访问这些字符串：

ITEMS[0] return "Bread"

Map

在Hive中，map是一种无序的键值对集合。

例如：你可以声明一个包含商品项和数量的Basket集合，如下所示：

Basket Map<'string','int'>

Basket Map<'Eggs','12'>

通过在Map函数中指定商品项，可以打印出该项商品对应的数量。

Basket("Eggs") return 12.

结构体

Hive结构体类似于一些编程语言中的结构体，例如C语言。结构体是一个对象，其中含有多个字段，而这些字段又可以是任何数据类型。

例如：你可以使用下面的Struct定义来声明客户的地址记录。

address STRUCT<houseno:STRING,street:STRING,city:STRING,zipcode:INT,state:STRING,country:STRING>

address <"17","MAIN ST","SEATTLE","98104","WA","USA">

你可以使用点号来访问某一STRUCT的字段。在前面的示例中，可以使用address.zipcode来访问各个地址的邮编编码。

联合体

联合体提供了一种方法，可以将不同数据类型的元素存储在同一个字段的不同行中。当字段的底层数据不同质的时候，这种方法非常管用。

例如，如果数据文件中存放了客户的联系信息，但是每条联系信息中包含一个或多个电话号码，或者包含一个或多个电子邮件地址，那么可以声明一个contact变量来按述方式存储信息。

contact UNIONTYPE <int,array<int>,string,array<string>>

表

Hive数据模型包含一个数据的逻辑行/列视图，被称作表。就像关系数据库一样，Hive表由一个关于数据的二维视图定义组成。然而，数据独立于表存在。Hive表中的数据存在于HDFS目录中，而表的定义存储在一个名为HCatalog的关系数据库存储中。Hive表和关系数据库的表之间存在一些重要区别。

1. Hive表中的数据和表的定义是松耦合的。在关系数据库中，删除一个表时可以从存储中删除表的定义和底层数据，然而在Hive表中，如果将表定义为外部表，那么删除表定义和删除底层数据是相互独立的。
2. Hive中的单个数据集可以有多个表定义。
3. Hive表中得到底层数据可以以多种的格式存储。

创建表

在创建表的过程中所制定的配置的定义了Hive应该如何解释那些存储为HDFS数据文件的底层数据。Hive有许多内置的数据格式解释器，用Hive术语来说就是serDe。hive还允许你定义自己的序列化器和反序列化器，并插入到CREATE

 TABLE语句中，使Hive能够理解数据的格式。

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

　　[(col_name data_type [COMMENT col_comment], ...)]

　　[COMMENT table_comment]

　　[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

　　[CLUSTERED BY (col_name, col_name, ...)

　　　　[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

　　[ROW FORMAT row_format]

　　[STORED AS file_format]

　　[LOCATION hdfs_path]

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
•LIKE 允许用户复制现有的表结构，但是不复制数据
•COMMENT可以为表与字段增加描述
•PARTITIONED BY 指定分区
•ROW FORMAT 
　　DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] 
　　　　MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
　　　　| SERDE serde_name [WITH SERDEPROPERTIES 
　　　　(property_name=property_value, property_name=property_value, ...)] 
　　用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，
用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。 
•STORED AS 
　　SEQUENCEFILE //序列化文件
　　| TEXTFILE //普通的文本文件格式
　　| RCFILE　　//行列存储相结合的文件
　　| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式
　　如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

•LOCATION指定表在HDFS的存储路径

一个简单的CREATE TABLE 语句：

CREATE EXTERNAL TABLE customers(

fname STRING,

lname STRING,

address STRUCT<HOUSENO:STRING,STREET:STRING,CITY:STRING,ZIPCODE:INT,STATE:STRING,COUNTRY:STRING>,

active BOOLEAN,

created DATE,

LOCATION '/user/demo/customer'

);

可以直接表名之前跟上库名.就可以创建某一个库里面的表了。

列出表

• show tables;
• show tables in retail;
• 通配符来搜索特定的表

内部表/外部表

Hive表可以创建为内部或外部的。Hive表的类型决定了Hive如何加载、存储和控制数据。

外部表

通过在CREATE TABLE 语句红使用external关键字可以创建外部表。这是hadoop所有生产部署中推荐的表类型。这是因为在大多数情况下，底层数据将被用于多个用例。即使并非如此，也不应该在删除表定义时删除底层数据。因此对于外部表来说，Hive不会将数据从文件系统中删除，因为它无法控制这些数据。在下列情况下可以使用外部表：

• 你想删除表定义而不须担心删除底层数据
• 数据存储在文件系统中而不是hdfs之上。例如，你可以使用亚马逊的S3或者微软Azure的WASB来存储数据，并且从多个集群访问这些数据
• 你希望使用自定义位置存储表数据
• 不准备基于另一个表来创建表
• 数据将被多个处理引擎访问。例如，你既希望使用Hive来读取表，又希望在Spark程序中使用该表。
• 你希望在同一数据集上创建多个表的定义。当你有多个表定义时，在删除其中一个定义时不应该删除底层数据，此时外部表就可重要的了

内部表/受控表

Hive中的内部表是指数据由Hive管理的表。这意味着当你删除一个内部表时，Hive也将删除除它的底层数据。这类表在Hadoop中不经常使用，因为在大多数情况下，即使将表删除了，文件系统中的数据仍然需要保留。在Hive中，由于数据和元素据没有绑定在一起，因此它允许底层数据与其它工具/处理范式一起使用。以下情况下使用内部表：

• 数据是临时存储的
• 访问数据的唯一方式是通过Hive，而且你需要用Hive来完全管理表和数据的生命周期

表的属性

在创建表或者使用TBLPROPERTIES子句更改表的时候，你也可以在表层级上指定一个属性。Hive由预定义的表属性，通过这些属性可以在表层级上定义一些配置，以供Hive管理表使用。然而，你也可以使用一种键值对的格式来定义一些自定义属性，以便存储一些表层级的元数据或有关表的额外信息。下面是Hive表中一些重要的表层级属性：

• last_modified_user
• last_modified_time
• immutable
• orc.compress
• skip.header.line.count

在该列表中，前面两个属性是可控的，由于Hive自动增加。正如它们的名称所暗示的，Hive通过他们将上次修改的用户和时间信息存放在Metastore中。

当immutable属性被设置成true的时候，如果表中由数据，则无法向其插入新数据。如果强行就会报错。

orc.compress属性用于指定基于ORC的存储所采用的压缩算法。

skip.header.line.count 属性对于Hive中的外部表来说是最重要的属性之一。在大多数生产环境中，该属性都用的非常频繁。当处理真实数据时，你经常会发现，数据文件中的标题行永远都令人头疼。使用该属性，你可以跳过底层数据文件的标题行。

举例如下：CREATE EXTERNAL TABLE table_external(state string) LOCATION '/user/hive/demo/state.txt' TBLPROPERTIES("skip.header.line.count"="2");

生成已有表的CREATE TABLE 命令

对于给定的表，你也可以使用SHOW CREATE TABLE命令来生成它的CREATE TABLE 语句

例子：SHOW TABLE shopping;

分区和分桶

Hive表可以进一步划分成若干逻辑快，以便于管理和改进性能。在Hive中有好几种可用于抽象数据的方式，参见下图：

在关系型数据库中，分区通常用于提高性能和实现更好的数据管理。Hive中分区的概念也类似

Hive中的分区表有一个或多个分区键，基于这些分区键，数据被分割成若干个逻辑块并存放在单独的路径中。每个分区键都为表的存储添加了一个目录层结构。让我们来看一个含有一些分区键的客户事务处理表：

CREATE EXTERNAL TABLE table_transactions(Transdate Date,transid int) PARTITIONED BY (store STRING);

上面建表语句中，是以store的字符串列进行分区。注意，在分区中用到的列实际上在CREATE TABLE 结构中并不存在。

分区注意事项

Hive分区对于非常特别的查询子集而言可以改进其性能，可以对不需要检索查询结果的分区进行剪枝。折耳根过程叫做分区消除。分区是一种支持用户在HDFS上以更加分片化的方式组织数据的方式，这样可以改进可维护性。如果数据被分割放在若干子目录下，你既可以将各分区指向子目录，也可以采用递归分区使一个表访问所有子目录。

不必要的分区会增加数据加载和数据检索的开销。如果你创建了很多分区且每个分区中都含有很多小数据块，那么你的文件就很可能比较小。在Hadoop中大量的小文件要比存放相对较少较大的文件慢很多。所以应遵循以下原则：

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

对日期列进行高效分区

日期型经常时分区键最常用的候选对象。

举例：

查询选择特定的日期

SELECT * FROM TABLE a WHERE DateStamp IN ('2018-01-01','2019-01-01');

查询一年中所有日期

SELECT * FROM TABLE b WHERE DateStamp LIKE '2016-%'；

查询所有以5开始/结束的日子

SELECT * FROM TABLE c WHERE DateStamp LIKE '%-%-%5'；

查询2015年1月1日到2015年3月1日的所有日子

SELECT * FROM TABLE d WHERE DateStamp BETWEEN '2015-01-01' AND '2015-03-01'；

分桶

Hive中的分桶是另一种将数据切分成更小片段的方式。如果你的分区键有很多不同的值，导致最后会有很多小文件存在于hdfs上，那么该次分区不是最佳的选择。那么这个时候分桶就可以上。

分桶让你可以为每一个表的分桶列定义桶的最大数目。hive中的一个分区就是一个目录，分区键的值存放在实际的分区目录名中，而分区键是表中的一个虚拟列。然而在分桶中，每个桶都是一个保存实际数据的文件，这些数据基于一种散列算法进行分割。分桶并不会为当前表添加一个虚拟列。

与分区相比优势，主要能够提升多种查询的性能。

分桶建表语句：

CREATE EXTERNAL TABLE table_transactions(Transdate Date,transid int,store STRING) CLUSTERED BY (store) INTO 11 BUCKETS LOCATION '/user/demo/customers'; 

现在，当你向该表中插入数据时，Hive将store用于散列函数，将数据分发到11个桶中，对于有些数据类型来说，这就意味着那些含有相同store值得行被存放在相同得桶中。

注意：Hive.enforce.bucketing=TRUE 没有这个参数，你就需要为表定义与桶得数量相同得映射器。

分桶得注意事项

对于高效抽样和改进某些查询得性能来说，分桶是一种很好得特性；然而，它也有自己得一些限制。非对称时真实数据最常见得问题之一。如果不能正确处理，会对分桶造成很大影响。为分桶选择正确得键也是非常重要得。下面是应该遵循得一些最佳实践：

• 选择唯一值得个数较多得桶键。这样会减少出现倾斜得可能性
• 采用质数作为桶得编号
• 如果桶键中得数据是倾斜得，为倾斜得值单独创建桶。这可以通过列表分桶来实现
• 分桶对于通常连接在一起得事实表来说非常有用
• 需要连接在一起的表，其桶得数目必须相同，或者一个表得桶数是另一个表得桶数得因子
• 要仔细选择桶得数目。一个cpu核只会对一个桶进行写入操作，因此对于一个大型集群而言，适当得桶得数目适当得资源利用
• 一旦表建好，桶的数目就不能变了
• 仔细选择进行分桶的列，因为散列函数会引发倾斜。字符串散列更有这种倾向，因为通常使用的字符串集很小。
• 你应该考虑到获取的桶文件大小至少是1GB
• 通过设置hive.enforce.bucketing =TRUE实现强制分桶
• 对于Map端连接，分桶表要比非分桶的速度更快。在Map端连接中，映射器处理左侧表的某个桶时，知道右侧表中相匹配的行在其对应的桶中，因此只需要检索该桶即可，这只是右侧表中存储的全部数据中的一小部分
• 分桶也允许你按照一列或多列对每个桶中的数据进行排序。这样就可以把Map端连接转换成排序-合并连接，使速度更快

临时表

到Hive 0.14为止，Hive也支持临时表

CREATE TEMPORARY TABLE states(state STRING);  //创建临时表

DESCRIBE EXTERNAL states;    //查看表属性

更改表

ALTER TABLE 类似sql中的alter table，但是hive有一点不同的是，支持修改表的结构，但是不支持修改数据

• 重命名表 --- ALTER TABLE A RENAME TO B;
• 修改表存储属性--- ALTER TABLE A RENAME name TO name_new int;

ORC文件格式

orc文件格式用于减少要从磁盘读取的数据量。Hive中有很多新的性能优化都仅采用ORC文件，因此对于大多数用例来说，推荐将原始数据转换成ORC文件

CREATE TABLE B STORE AS ORC TBLPROPERTIES("ORC.COMPRESS"="SNAPPY") AS SELECT * FROM A;

合并表的文件

小文件如何合并，首先小文件合并动作建议放在数据入hdfs之前，因为namenode进程维护了hdfs上所有文件的元数据信息，所以要把小而多的文件合并，用ALTER TABLE命令来实现，对于以RCFile或ORCFile格式存储的Hive表，可以作如下操作：

ALTER TABLE state CONCATENATE; 该命令会将多个小文件合并成较大的文件

更改表分区

使用ALTER TABLE ADD PARTITION为已有表添加分区

CREATE EXTERNAL TABLE ids(a INT) PARTITION BY(dateStamp STRING) LOCATION 'user/hive/ids';

ALTER TABLE ids ADD PARTITION(dateStamp='2018-01-01') LOCATION 'user/hive/ids/2018-01-01';

SHOW PARTITION ids; //显示分区情况

MSCK REPAIR TABLE ids_internal; 仅适用于内部表

重命名分区

使用ALTER TABLE 命令来对表的分区进行重命名。

ALTER TABLE ids PARTITION(dateStamp='2016-01-01') RENAME to PARTITION(dateStamp='2018-01-01');

上面的命令仅限于外部表，内部表不适用。

修改列（一般不建议使用）

ALTER TABLE ids ADD COLUMNS(NAME STRING);//仅仅适用于外部表，不需要关注数据

删除表/分区

DROP TABLE 命令来删除Hive中的表。运行DROP TABLE命令时，表的元数据总会被删除。然而，Hive仅仅删除受控表中的数据。如果你已经使HDFS的trash特性可用，该表的数据文件就会被移到/user/$USER/.trash文件夹下。可以通过/etc/hadoop/conf/core-site.xml中的设置fs.trash.interval参数来使该特性可用。

DROP TABLE <TABLE_NAME>;    

DROP TABLE <TABLE_NAME> PURGE;      //加上PURGE关键字彻底删除

ALTER TABLE transactions DROP PARTITION(store='oakdrive');  在本例中，数据仍然存在于HDFS（假设你使用了外部表，内部表数据已经被删除），但是针对该事务处理表的查询无法再读取该分区。因此，查询结果集中没有含有store=oakdrive的行，因为这个表已经没有该分区。

保护表/分区

ALTER TABLE transactions ENABLE NO_DROP;  //保护表，防止被删除

ALTER TABLE transactions ENABLE OFFLINE; //某个表处于离线状态，以防止该表得数据被用户查询到。这并不会对另一个访问同一底层数据的表造成影响。

ALTER TABLE <TABLE_NAME> PARTITION <PARTITION_SPEC> ENABLE OFFLINE;

其它CREATE TABLE命令选项

CREATE TABLE a AS SELECT * FROM b WHERE custid<101;

CREATE TABLE a STORED AS ORCFILE AS SELECT * FROM b WHERE custid<101;

CREATE TABLE a LIKE b;   //只复制某个已有表的模式而不复制它的数据

DROP TABLE transactions;     //先给transactions 赋能后，无法删除该表