第4章：HiveQL:数据定义

Hive中的数据库

Hive中数据库的概念本质上仅仅是表的一个目录或者命名空间。

如果用户没有显式指定数据库，那么将会使用默认的数据库default。

创建数据库

hive> CREATE DATABASE financials;

使用如下语句可以避免在financials已经存在的抛出错误信息：

hive> CREATE DATABASE IF NOT EXISTS financials;

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

查看Hive中所包含的数据库

hive> SHOW DATABASES;

正则表达式:

hive> SHOW DATABASES LIKE ‘h.*’;

Hive 会为每个数据库创建一个目录。数据库中的表会以这个数据库目录的子目录形式存储。

有一个例外就是default数据库中的表，因为这个数据库本身没有自己的目录。

数据库所在的目录位于属性hive.metastore.warehouse.dir所指定的顶层目录之后，当我们创建数据库financials时，Hive会创建一个目录/user/hive/warehouse/financials.db

修改数据库默认位置

hive> CREATE DATABASE financials

  > LOCATION ‘/my/preferred/directory’;

为数据库增加描述信息

hive> CREATE DATABASE financials

  > COMMENT ‘Holds all financials tables’;

查看数据库信息

hive> DESCRIBE DATABASE financials;

financials Holds all financials tables

 hdfs://master-server/user/hive/warehouse/financials.db

给数据库增加键-值对属性信息

hive> CREATE DATABASE financials

  > WITH DBPROPERTIES (‘creator’ = ‘Liu Ming’, ‘date’ = ‘2012-01-02’);

hive> DESCRIBE DATABASE EXTENDED financials；

financials  hdfs://master-server/user/hive/warehouse/financials.db

 {date=2012-01-02, creator=Liu Ming};

设置当前工作数据库

hive> USE financials;

显示当前所在数据库

hive> set hive.cli.print.current.db=true

删除数据库

hive> DROP DATABASE IF EXISTS financials;

默认情况下Hive不允许用户删除一个包含有表的数据库,可以在删除命令的最后面加上CASCADE,这样Hive可以自行先删除数据库中的表

hive> DROP DATABASE IF EXISTS financials CASCADE;

修改数据库

只能修改,不可以删除或者重置数据库的DBPROPERTIES属性值，并且不可修改其他元数据信息。

hive> ALTER DATABASE financials SET DBPROPERTIES (‘edited-by’ = ‘Joe Dba’);

Hive中的表

创建表

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS mydb.employees (
    name            STRING COMMENT 'Employee name',
    salary          FLOAT COMMENT 'Employee salary',
    suborddinates   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', 'create_at'='2012-01-02 10:00:00', ...)
LOCATION '/user/hive/warehouse/mydb.db/employees';

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

last_modified_by : 保存最后修改这个表的用户的用户名；

last_modified_time : 保存最后一次修改的新纪元时间秒；

拷贝一张已经存在的表的表模式（无需拷贝数据）

1
2
CREATE TABLE IF NOT EXISTS mydb.employees2
LIKE mydb.employees;

查看表的详细信息

1
DESCRIBE FORMATTED/EXTENDED mydb.employees;

管理表

也称为内部表，Hive控制着数据的生命周期，删除管理表的时候也会删除这个表中的数据。

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

外部表

创建外部表：

1
2
3
4
5
6
7
CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
    exchange STRING,
    symbol   STRING,
    ymd      STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

  • EXTERNAL 表示这个表示外部的；

  • LOCATION 子句用于告诉Hive数据位于哪个路径下

  • Hive并非认为其完全拥有这份数据，所以删除表并不会删除这份数据，不过描述表的元数据信息会被删除掉

用户可以对管理表进行表结构复制而不复制数据：

1
2
3
CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3
LIKE mydb.employees
LOCATION '/path/to/data';
- 如果语句中省略掉EXTERNAL关键字，源表是管理表，生成的新表是管理表。
- 如果语句中省略掉EXTERNAL关键字，源表是外部表，生成的新表也是外部表。
- 如果语句中包含EXTERNAL关键字，源表是管理表，生成的新表是外部表。

分区表、管理表

创建分区表

1
2
3
4
5
6
7
8
CREATE TABLE employees (
    name            STRING,
    salary          FLOAT,
    suborddinates   ARRAY<STRING>,
    deductions      MAP<STRING, FLOAT>,
    address         STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

表目录：

hdfs://master_server/user/hive/warehouse/mydb.db/employees

反应分区结构的子目录：

…

…/employees/country=CA/state=AB

…/employees/country=CA/state=BC

…

…/employees/country=US/state=AL

…/employees/country=US/state=AK

…

严格模式

hive> set hive.mapred.mode=strict; (nostrict)

开启严格模式之后，如果对分区表进行查询而WHERE子句没有加分区过滤，将会禁止提交这个任务。

查看表的所有分区

hive> SHOW PARTITIONS employees;

hive> SHOW PARTITIONS employees PARTITON(country=’US’);

外部分区表

外部表同样可以使用分区。

创建非分区外部表要求使用LOCATION子句，对于外部分区表则不用。

可以通过ALTER TABLE语句单独进行增加分区。

1
2
ALTER TALBE log_messages ADD PARTITON(year = 2012, month = 1, day = 2)
LOCATION 'hdfs://master_server/data/log_messages/2012/01/02';

自定义表的存储格式

Hive默认的存储格式是文本文件格式，用户可以通过STORED AS TEXTFILE显式指定。

也可以将TEXTFILE替换成其他Hive所支持的内置文件格式，包括SEQUENCEFILE和RCFILE，这两种文件格式都是使用二进制编码和压缩来优化磁盘空间使用和I/O带宽性能的。

记录编码是通过一个inputformat对象来控制的，Hive使用了一个名为org.apache.hadoop.mapred.TextInputFormat的类

记录的解析是由序列化器/反序列化器(缩写为SerDe)来控制的。对于TEXTFILE，Hive使用的SerDe是org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

为保持完整性，Hive还使用outputformat对象来将查询的输出写入到文件中或者输出到控制台。对于TEXTFILE，用于输出的类为org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

Hive使用一个inputformat对象将输入流分割成记录，然后使用一个outputformat对象来记录格式化为输出流（例如查询的输出结果）， 
再使用一个SerDe在读数据时将记录解析成列，在写数据时将列编码成记录。

自定义SerDe、输入格式、输出格式：

1
2
3
4
5
6
7
CREATE TABLE kst
PARTITION BY (ds string)
ROW FORMAT SERDE 'com.linkedin.haivvreo.AvroSerDe'
WITH SERDEPROPERTIES ('schema.url'='http://schema_provider/kst.avsc')
STORED AS
INPUTFORMAT 'com.linkedin.haivvreo.AvroContainerInputFormat'
OUTPUTFORMAT 'com.linkedin.haivvreo.AvroContainerOutputFormt';

删除表

1
DROP TABLE IF EXISTS employees;
如果开启了Hadoop回收站功能，数据会被转移到/user/$USER/.Trash目录。 
如果不小心删除了一张管理表，可以先重建表，重建分区，再从.Trash目录中将误删的文件移动到正确的文件目录下。

修改表

ALTER TABLE仅仅会修改表元数据，表数据本身不会有任何修改，需要用户自己确认所有的修改都和真实的数据是一致的。

表重命名

1
ALTER TABLE log_messages RENAME TO logmsgs;

增加、修改和删除表分区

增加分区

1
2
3
4
ALTER TABLE log_messages ADD IF NOT EXISTS
PARTITION (year = 2011, month = 1, day = 1) LOCATION '/logs/2011/01/01'
PARTITION (year = 2011, month = 1, day = 2) LOCATION '/logs/2011/01/02'
PARTITION (year = 2011, month = 1, day = 3) LOCATION '/logs/2011/01/03'

通过移动位置来修改某个分区的路径

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

删除某个分区

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

修改列信息

1
2
3
4
ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'The hours,minutes and seconds part of the timestamp'
AFTER severity;
如果想将这个字段移动到第一个位置，只需要使用FIRST替换AFTER severity即可。

增加列

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

删除或者替换列

1
2
3
4
5
ALTER TABLE log_messages REPLACE COLUMNS(
    hours_mins_secs   INT COMMENT 'The hours,minutes and seconds part of the timestamp',
    severity          STRING COMMENT 'The message severity',
    message           STRING COMMENT 'The rest of the message'
    );
REPLACE语句只能用于使用了DynamicSerDe或者MetadataTypeColumnSerDe两种内置SerDe模块的表

修改表的属性

1
2
3
4
5
用户可以增加附加的表属性或者修改已经存在的属性，但是无法删除属性。

ALTER TABLE log_messages SET TBLPROPERTIES(
    'notes' = 'The processid is no longer captured'
);

修改存储属性

1.修改存储格式：

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

2.指定一个新的SerDe，并制定SerDe属性

1
2
3
4
5
6
7
8
ALTER TABLE table_using_JSON_storage
SET SERDE 'com.example.JSONSerDe'
WITH SERDEPROPERTIES (
    'prop1' = 'value1',
    'prop2' = 'value2'
)

属性值和属性名都应当是带引号的字符串

3.向一个已存在的SerDe增加新的SERDEPROPERTIES属性：

1
2
3
4
5
ALTER TABLE table_using_JSON_storage
SET SERDEPROPERTIES(
    'prop3' = 'value3',
    'prop4' = 'value4'
);

众多的修改表语句

1.ALTER TABLE … TOUCH

用于触发钩子

例如：某个脚本往分区2012/01/01中写入了新的日志信息文件，可以在Hive CLI中进行下面的调用：

hive -e ‘ALTER TABLE log_messages TOUCH PARTITON(year = 2012, month = 1, day = 1);’

2.ALTER TABLE … ARCHIVE PARTITION

将分区内的文件打成一个Hadoop压缩包（HAR）文件，可以降低文件系统中的文件数从而减轻NameNode压力，而不会减少任何的存储空间。

1
2
ALTER TABLE log_messages ARCHIVE
PARTITION(year = 2012, month = 1, day = 1);

使用UNARCHIVE替换ARCHIVE即可反向操作。这个功能只能用于分区表中独立的分区。

3.防止分区被删除和被查询

1
2
3
4
5
6
7
ALTER TABLE log_messages
PARTITION(year = 2012, month = 1, day = 1) ENABLE/DISABLE NO_DROP;

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

不可用于非分区表