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编程指南》
|