【大数据】HiveQL关于数据库和表的操作命令

HiveQL关于数据库和表的操作命令

HiveQL 是 Hive 查询语言。Hive 和 MySQL 最接近，但二者仍存在显著差别。Hive 不支持行级插入操作、更新操作和删除操作。Hive 也不支持事务。

Hive 中数据库的概念，本质上仅仅是表的一个目录或者命名空间。如果用户没有显式指定数据库，那么将会使用默认的数据库

default

。

一、数据库

1. 创建数据库

CREATEDATABASE financials;

CREATEDATABASEIFNOTEXISTS financials;

2. 查看 Hive 中所包含的数据库

SHOWDATABASES;

3. 将某个数据库设置为用户当前的工作数据库

USE financials;

4. 删除数据库

DROPDATABASEIFEXISTS financials;

默认情况下，Hive 是不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表，然后再删除数据库；要么在删除命令的最后面加上关键字

CASCADE

。

DROPDATABASEIFEXISTS financials CASCADE;

5. 修改数据库
用户可以使用

ALTER DATABASE

命令为某个数据库的

DBPROPERTITES

设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

ALTERDATABASE financials SET DBPROPERTITES ('edited-by'='Joe Dba');

二、表

管理表

管理表，也被称为内部表。这种表，Hive 会控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项

hive.metastore.warehouse.dir

所定义的目录的子目录下。当我们删除一个管理表时，也会删除这个表中的数据。

但是，管理表不方便和其他工作共享数据。例如，假设我们有一份由 Pig 或者其他工具创建并且主要由这一工具使用的数据，同时我们还想使用 hive 在这份数据上执行一些查询，可是并没有给予 Hive 对数据的所有权，此时，我们可以创建一个 外部表 指向这份数据，而并不需要对其具有所有权。

外部表

我们可以使用

CREATE EXTERNAL TABLE

创建一个外部表。因为表是外部的，所以 Hive 并没有完全拥有这份数据，因此删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

管理表和外部表有一些小小的区别，但其实这种差异非常小。如果数据会被多个工具共享，那么可以创建一个外部表，来明确对数据的所有权。

分区表

数据分区的概念大家并不陌生。通常是使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方。Hive 中也有分区表的概念。分区表具有重要的性能优势，而且分区表还可以将数据以一种符合逻辑的方式进行组织，比如分层存储。

对于非常大的数据集，分区可以显著的提高查询性能，并且节省数据存储空间。分区的概念同样可以使用在外部表上，即外部分区表。

1. 创建表（管理表）

CREATETABLEIFNOTEXISTS mydb.employees (
  name          STRING COMMENT'Employee name',
  salary        FLOATCOMMENT'Employee salary',
  subordinates  ARRAY<STRING>COMMENT'Names of subordinates',
  deductions    MAP<STRING,FLOAT>COMMENT'Keys are deductions names, values are percentages',
  address       STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>COMMENT'Home address')COMMENT'Description of the table'
TBLPROPERTIES ('creator'='me','created_at'='2012-01-02 10:00:00',...)
LOCATION '/user/hive/warehouse/mydb.db/employees';

如果用户使用了

IF NOT EXISTS

，而且这个已经存在的表和

CREATE TABLE

语句后指定的模式是不同的。Hive 会忽略掉这个差异。

Hive 会自动增加两个表属性：一个是

last_modified_by

，其表保留着最后修改这个表的用户的用户名；另一个是

last_modified_time

，其保存着最后一次修改的新纪元时间秒。

2. 显示当前工作数据库下的表

SHOWTABLES;

3. 列举指定数据库下的表

SHOWTABLESIN mydb;

4. 查看表的详细结构信息

DESCRIBEEXTENDED mydb.employees;

使用

FORMATTED

关键字的输出结果比

EXTENDED

具有更强的可读性。

DESCRIBE FORMATTED mydb.employees;

查看某一个列的信息。

DESCRIBE mydb.employees.salary;

5. 删除表

DROPTABLEIFEXISTS employees;

大多数的表属性可以通过

ALTER TABLE

语句来进行修改，这种操作会修改元数据，但不会修改数据本身。这些语句可以用于修改表模式中出现的错误、改变分区路径，以及其他一些操作。

6. 表重命名

ALTERTABLE log_messages RENAMETO logmsgs;

7. 为表（通常是外部表）增加一个新的分区

ALTERTABLE log_messages ADDIFNOTEXISTSPARTITION(year=2011,month=1,day=1) LOCATION '/logs/2011/01/01';

8. 修改某个分区的路径

ALTERTABLE log_messages PARTITION(year=2011,month=12,day=2)SET LOCATION 's3n://ourbucket/logs/2011/01/02';

9. 删除某个分区

ALTERTABLE log_messages DROPIFEXISTSPARTITION(year=2011,month=12,day=2);

10. 修改列信息

用户可以对某个字段进行重命名，并修改其位置、类型或者注释。

ALTERTABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INTCOMMENT'The hours, minutes, and seconds part of the timestamp'AFTER severity;

即使字段名或者字段类型没有改变，用户也需要完全指定旧的字段名，并给出新的字段名及新的字段类型。关键字

COLUMN

和

COMMENT

子句都是可选的。上面的示例中，我们将字段转移到 severity 字段之后。如果用户想将这个字段移动到第一个位置，那么只需要使用

FIRST

关键字替代

AFTER other_column

子句即可。

11. 增加列

ALTERTABLE log_messages ADDCOLUMNS(
    app_name STRING COMMENT'Application name',
    session_id LONG COMMENT'The current session id');

12. 删除或者替换列

ALTERTABLE log_messages REPLACECOLUMNS(
    hours_mins_secs INTCOMMENT'hour, minute, seconds from timestamp',
    severity STRING COMMENT'The message severity'
    message STRING COMMENT'The rest of the message');

13. 修改表属性
用户可以增加附加的表属性或者修改已经存在的属性，但无法删除属性。

ALTERTABLE log_messages SET TBLPROPERTIES ('notes'='The process id is no longer captured; this column is always NULL');

14. 修改分区的存储格式

ALTERTABLE log_messages
PARTITION(year=2012,month=1,day=1)SET FILEFORMAT SEQUENCEFILE;

15. Hive 提供各种保护

防止分区被删除

ALTERTABLE log_messages
PARTITION(year=2012,month=1,day=1)ENABLE NO_DROP;

防止分区被查询

ALTERTABLE log_messages
PARTITION(year=2012,month=1,day=1)ENABLE OFFLINE;

使用

Disable

替换

Enable

可以达到反向操作的目的。

参考：《Hive编程指南》