HiveQL：数据定义

内容提要

l  Hive中的数据库操作

l  Hive中的表操作

 

HiveQL是Hive查询语言。和普遍使用的所有SQL方言一样，它不完全遵守任意一种ANSISQL标准的修订版。HiveQL可能和MySQL的方言最接近，但是两者还是存在显著性差异的。Hive不支持行级插入操作、更新操作和删除操作。Hive增加了在Hadoop背景下的可以提供更高性能的扩展，以及一些个性化的扩展，甚至还增加了一些外部程序。

当然了，大部分的HiveQL还是很常见的。本章以及随后的几章将会使用一些典型的例子来讲解HiveQL的那些特性。在某些情况下，我们会从整体上简要地谈到一些细节，然后会在后面的章节里再去比较完整地进行讨论。

随后的几章将会研究HiveQL的数据操作语言部分，其用于将数据导入Hive表中，以及将数据抽取到文件系统中。由于第二章中简单介绍了Hive的一些基本操作，知道大概流程之后，后面的章节中还会详细介绍如何通过查询、分组、过滤、连接等操作研究和操作数据。

4.1  Hive中的数据库

Hive中数据库的概念本质上仅仅是表的一个目录或者命名空间。然而，对于具有很多组和用户的大集群来说，这是非常有用的，因为这样可以避免表命名冲突。通常会使用数据库来将生产表组织成逻辑组。

如果用户没有指定数据库，那么将会使用默认的数据库default。使用use databasename;可以切换到某个数据库下（同mysql）。

Hive中的数据库在HDFS上的存储路径为

 

${hive.metastore.warehouse.dir}/databasename.db

 

下面这个例子就展示了如何创建一个数据库：

 

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

[COMMENT database_comment]

[LOCALTION hdfs_path]

[WITH DBPROPERTIES(property_name=property_value,...)];

 

例如，创建名为lxw1234的数据库

 

create DATABASE IF NOT EXISTS lxw1234

COMMENT 'lxw的大数据田地-lxw1234.com'

Location 'hdfs://namenode/user/lxw1234/lxw1234.db/';

 

创建时可以指定数据库在HDFS上的存储位置。

注意：使用HDFS超级用户创建数据库后，该数据库在HDFS上的存储路径的属主为超级用户，如果该数据库是为某个或某些用户所使用的，则需要修改路径属主，或者在Hive中进行授权。

虽然通常情况下用户还是期望在同名数据库已经存在的情况下能够抛出警告信息的，但是IF NOT EXISTS这个子句对于那些在继续执行之前需要根据需要实时创建数据库的情况来说上非常有用的。

在所有的数据库相关的命令中，都可以使用SCHEMA这个关键字来替代关键字TABLE。

   用户还可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置：

 

ALTER (DATABASE|SCHEMA) database_name

SET DBPROPERTIES (property_name=property_value, …);

 

修改数据库属主

 

ALTER (DATABASE|SCHEMA) database_name

SET OWNER [USER|ROLE] user_or_role;

 

   没有办法可以删除或者“重置”数据库属性。

   最后，用户可以删除数据库：

 

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name

[RESTRICT|CASCADE];

 

   IF EXISTS子句是可选的，如果加了这个子句，就可以避免因数据库finanacials不存在而抛出警告信息。

   默认情况下，Hive是不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表，然后再删除数据库；要么在删除命令的最后面加上关键字CASCADE，这样可以使Hive自行先删除数据库中的表。如果使用的是RESTRICT这个关键字而不是CASCADE这个关键字的话，那么就和默认情况一样，也就是，如果想删除数据库，那么必须先要删除掉该数据库中的所有表。如果某个数据库被删除了，那么其对应的目录也同时会被删除。

 

4.2  Hive中的表

CREATE TABLE语句遵从SQL语法惯例，但是Hive的这个语句中具有显著的功能扩展，使其可以具有更广泛的灵活性。例如，可以定义表的数据文件存储在什么位置，使用什么样的存储格式，等等。默认情况下，表的存储路径为

 

${hive.metastore.warehouse.dir}/databasename.db/tablename/

 

首先，如下所示创建一个表。

 

CREATE EXTERNAL IF NOT EXITS TABLE lqq.test (

id INT,

ip STRING COMMENT '访问者IP',

avg_view_depth DECIMAL(5,1),

bounce_rate DECIMAL(6,5)

) COMMENT 'lqq’s table'

PARTITIONED BY (day STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ', '

STORED AS textfile

LOCATION 'hdfs://lqq/tmp/test/';

 

首先，我们可以注意到，如果用户当前所处的数据库并非是目标数据库，那么用户是可以在表名前增加一个数据库名进行指定的，也就是例子中的lqq。

如果用户增加上可选IF NOT EXITS，那么若表已经存在了，Hive就会忽略掉后面的执行语句，而且不会有任何提示。在那些第一次执行时需要创建表的脚本中，这么写是非常有用的。

然而，这个语句还有一个用户需要注意的问题。如果用户所指定的表的模式和已经存在的这个表的模式不同的话，Hive不会为此作出提示。如果用户的意图时使这个表具有重新指定的那个新的模式的话，那么就需要先删除这个表，也就是丢弃之前的数据，然后再重建这张表。用户可以考虑使用一个或多个ALTER TABLE语句来修改已经存在的表的结构。

 

注：Hive v0.10.0版本中有一个功能增强计划，也就是增加一个SHOW TBLPROPERTIES table_name命令，用于列举出某个表的TBLPROPERTIES属性信息。

 

最后，可以看到我们可以根据情况为表中的数据指定一个存储路径（和元数据截然不同的时元数据总是会保存这个路径）。在这个例子中，我们使用的Hive将会使用的默认的路径/user/hive/warehouse/mydb.db/test。其中，/user/hive/warehouse是默认的“数据仓库”路径地址（这个之前有讨论过），mydb.db是数据库目录，test是表目录。

默认情况下，Hive总是将创建的表的目录放置在这个表所属的数据库目录之后。不过，default数据库是个例外，其在/user/hive/warehouse下并没有对应一个数据库目录。因此default数据库中的表目录会直接位于/user/hive/warehouse目录之后（除了用户明确指定为其他路径）。

 

注：为了避免潜在产生混淆的可能性，如果用户不想使用默认的表路径，那么最好是使用外部表。我们的使用场景大多数情况下是每天将收集到的网站日志定义存入HDFS文本文件，一天一个目录。在Hive中建立外部表作为源表，通过添加分区的方式将每天HDFS上的原始日志映射到外部表的分区中。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT命令进入内部表。Hive数据库和表对应的是HDFS的文件夹，Hive表中的数据对应的是HDFS的数据文件，Hive表中的数据文件在Metastore中是没有体现的。这里同时提到内部表和外部表，那么它们俩最大的区别是什么呢？（1）内部表进行删除操作时会删除HDFS上的数据（2）外部表进行删除操作时不会删除HDFS上的数据。（3）内部表比较适合在Hive中间表、结果表以及一般不需要从外部（如本地文件、HDFS上）下载数据的情况。

 

前面提到partition分区，让我们一起来看一下partition的相关参数：

 

hive.exec.dynamic.partition（默认为false）:设置true允许使用dynamic partition。