hive 中的数据定义

saltedfish520 的个人博客 / 0 / 0 / 创建于 6年前 / 更新于 6年前

数据库
创建一个数据库(IF NOT EXISTS)
```
CREATE DATABASE test;
CREATE DATABASE IF NOT EXISTS test;
```
查看数据库(可使用正则查看)
```
SHOW DATABASES;
SHOW DATABASES LIKE 'h.*';
```
创建数据库时,hive会为每个数据库创建一个目录,数据库中的表会以这个数据库的子目录形式存储。
数据库所在的目录位于属性hive.metastore.warehouse.dir所指定的目录。默认为/user/hive/warehouse.
当创建test数据库时,hive会创建/user/hive/warehouse/test.db的目录。
```
CREATE TABLE test 
LOCATION '/user/hive/mydatabase';
```
查看数据库信息
```
DESCRIBE DATABASE test;
```
删除数据库
```
DROP DATABASE IF EXISTS test;
```
删除数据库时,hive是不允许删除一个包含有表的数据库,要么先删表,再删库,要么加上关键字CASCADE
```
DROP DATABASE IF EXISTS test CASCADE;
```
数据库是只可以增加属性，不可修改名字，目录位置等。
```
ALTER DATABASE test SET DEPROPERTIES(‘edit-by’ = 'me');
```

表
创建表

CREATE TABLE IF NOT EXISTS test.employees (
name  STRING COMMENT 'employees name',
salary FLOAT COMMENT 'employees salary',
subordinateds ARRAY<STRING> COMMENT 'name of subordinateds',
deductions MAP<STRING, FLOAT> COMMENT 'keys are deductions names, name of percentages',
address STRUCT<street:STRING, city:STRING, state:STRING, zip: INT> COMMENT 'home address',)
COMMENT 'description of the table'
LOCATION '/user/hive/warehouse/test.db/employees';

若表已存在,有IF NOT EXISTS时hive会忽略掉后面的语句,不会有任何提示。
可使用SHOW CREATE TABLE 来查看创建表结构。
复制一个已存在的表的表结构创建表:

CREATE TABLE IF NOT EXISTS test.employees2 like test.employees;

列出当前数据库的表。

USE test;
SHOW TABLES;
SHOW TABLES IN test;

外部表

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
exchange STRING,
symbol STRING,
ymd STRING )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

EXTERNAL关键字表示表示外部表,删除外部表时数据不会被删除。

分区表

CREATE TABLE employees (
name STRING,
salary FLOAT,
subordinateds ARRAY<STRING> COMMENT 'name of subordinateds',
deductions MAP<STRING, FLOAT> COMMENT 'keys are deductions names, name of percentages',
address STRUCT<street:STRING, city:STRING, state:STRING, zip: INT> COMMENT 'home address',)
PARTITIONED BY (country STRING, state STRING );

按照country(国家)再按照state(州)对数据进行分区。
分区表 hive会创建好反映分区结构的子目录,如

.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
.../employees/country=US/state=AL

对于分区表,如果将hive设置为"strict(严格)"模式,WHERE语句没有分区过滤,将禁止提交任务。

外部分区表

    CREATE EXTERNAL TABLE IF NOT EXISTS log_messages (
    hms INT,
    severity STRING,
    server STRING,
    process_id INT,
    message STRING)
    PARTITIONED BY (year INT, month INT, day INT)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

把日志表按照天划分.
增加一个2019年2月3日的分区:

ALTER TABLE log_messages ADD PARTITION(year = 2019, month = 2, day = 3)
LOCATION 'hdfs:///data/log_messages/2019/02/03';

表的存储格式
TEXTFILE(文本文件)，SEQUENCEFILE和RCFILE (都是二进制编码和压缩)

删除表

DROP TABLE IF EXISTS employees;

修改表(ALTER)
重命名

ALTER TABLE log_messages RENAME TO logmsgs;

增加修改和删除分区

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

增加列

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

修改列(把hms修改为hours_minutes_seconds并放置在severity后面)

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

删除列或替换列

ALTER TABLE log_messages REPLACE COLUMNS (
hour_mins_secs INT COMMENT '',
severity STRING COMMENT '',
message STRING COMMENT '');

以上例子移除所有字段后重新定义。

修改表属性

ALTER TABLE log_messages SET TBLPROPERTIES (
'notes' = '...');

修改存储属性
将2019年1月1日这个分区的存储格式改为SEQUENCEFILE：

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

将分区内的文件打包压缩为HAR文件(仅仅可以降低文件个数减轻NameNode压力,不会减少任何的存储空间)

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

Hive提供了保护,防止分区被删除和被查询。

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

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

使用DISABLE替换ENABLE可以达到反向操作。这些操作不可用于分区表。

hive学习