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

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

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

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

一、数据库

1. 创建数据库

CREATE DATABASE financials;

CREATE DATABASE IF NOT EXISTS financials;

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

SHOW DATABASES;

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

USE financials;

4. 删除数据库

DROP DATABASE IF EXISTS financials;

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

DROP DATABASE IF EXISTS financials CASCADE;

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

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

二、表

管理表

管理表，也被称为内部表。这种表，Hive 会控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项 hive.metastore.warehouse.dir 所定义的目录的子目录下。当我们删除一个管理表时，也会删除这个表中的数据。

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

外部表

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

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

分区表

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

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

1. 创建表（管理表）

CREATE TABLE IF NOT EXISTS mydb.employees (
  name          STRING COMMENT 'Employee name',
  salary        FLOAT COMMENT '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. 显示当前工作数据库下的表

SHOW TABLES;

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

SHOW TABLES IN mydb;

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

DESCRIBE EXTENDED mydb.employees;

使用 FORMATTED 关键字的输出结果比 EXTENDED 具有更强的可读性。

DESCRIBE FORMATTED mydb.employees;

查看某一个列的信息。

DESCRIBE mydb.employees.salary;

5. 删除表

DROP TABLE IF EXISTS employees;

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

6. 表重命名

ALTER TABLE log_messages RENAME TO logmsgs;

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

ALTER TABLE log_messages ADD IF NOT EXISTS
PARTITION (year=2011, month=1, day=1) LOCATION '/logs/2011/01/01';

8. 修改某个分区的路径

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

9. 删除某个分区

ALTER TABLE log_messages DROP IF EXISTS PARTITION(year = 2011, month = 12, day = 2);

10. 修改列信息

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

ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'The hours, minutes, and seconds part of the timestamp'
AFTER severity;

即使字段名或者字段类型没有改变，用户也需要完全指定旧的字段名，并给出新的字段名及新的字段类型。关键字 COLUMN 和 COMMENT 子句都是可选的。上面的示例中，我们将字段转移到 severity 字段之后。如果用户想将这个字段移动到第一个位置，那么只需要使用 FIRST 关键字替代 AFTER other_column 子句即可。

11. 增加列

ALTER TABLE log_messages ADD COLUMNS (
    app_name STRING COMMENT 'Application name',
    session_id LONG COMMENT 'The current session id');

12. 删除或者替换列

ALTER TABLE log_messages REPLACE COLUMNS (
    hours_mins_secs INT COMMENT 'hour, minute, seconds from timestamp',
    severity STRING COMMENT 'The message severity'
    message STRING COMMENT 'The rest of the message');

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

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

14. 修改分区的存储格式

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

15. Hive 提供各种保护

防止分区被删除

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

防止分区被查询

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

使用 Disable 替换 Enable 可以达到反向操作的目的。

参考：《Hive编程指南》