数据处理
Hive

Hive DDL

DDL 全称 Data Definition Language，即数据定义语言，主要是用在定义、修改数据库对象的结构或数据类型，命令有 create、drop、alter、truncate、show、describe、use 等。

# 数据库操作

Hive 有一个默认的数据库 default，如果在执行 SQL 的时候没有明确指定数据库名字，则使用默认数据库。Hive 的库名需要注意：

Hive 的数据库名、表名均不区分大小写
名字不能使用数字开头
不能使用关键字，尽量不使用特殊符号

数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。

后续的 HQL 语法中会频繁出现[]、()、|和...符号，这里做统一说明：

[]里面的部分表示非必须项，也就是说可以根据具体情况决定是否出现
()里面的部分为必须项，必须出现
|分隔的多个选项中可以按需出现其中一个
...出现的部分表示可以出现一个或多个...之前的选项

SQL 中也会遵循上面的约定。

# 查看所有库

语法：

SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

例：

show databases;

show databases like 'ods*';

1
2
3

# 切换库

语法：

USE database_name;
USE DEFAULT;

1
2

# 查看库信息

语法：

DESCRIBE DATABASE [EXTENDED] db_name;
DESCRIBE SCHEMA [EXTENDED] db_name;     -- (Note: Hive 1.1.0 and later)

1
2

例：

-- describe 可以用 desc 代替
desc database test;

-- extended 会显示数据库详细信息
desc database extended test;

1
2
3
4
5

# 创建数据库

语法：

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

1
2
3
4
5

例：

create database test;

create database if not exists test;

create database if not exists location '/hive/test.db';

1
2
3
4
5

# 删除数据库

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

例：

drop database test;

-- 如果删除的数据库不为空，使用 cascade 强制删除，默认是 restrict，数据库不为空删除会报错
drop database test cascade;

1
2
3
4

# 修改数据库

语法：

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)

ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; -- (Note: Hive 4.0.0 and later)

1
2
3
4
5
6
7

# 表操作

# 查看所有表

语法：

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

# 查看表信息

查看表信息有两种语法，如果未指定数据库，则 col_name 之前是.：

DESCRIBE [EXTENDED|FORMATTED]
  table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];
                                        -- (Note: Hive 1.x.x and 0.x.x only. See "Hive 2.0+: New Syntax" below)

1
2
3

如果指定了数据库，则 col_name 之前是空格：

DESCRIBE [EXTENDED|FORMATTED]
  [db_name.]table_name[ col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];
                                        -- (Note: Hive 1.x.x and 0.x.x only. See "Hive 2.0+: New Syntax" below)

1
2
3

# 创建表

详见官网 (opens new window)

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format]
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

说明：

CREATE TABLE：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
EXTERNAL：关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
COMMENT：添加注释。
LIKE：允许用户复制现有的表结构，但是不复制数据。
PARTITIONED BY：创建分区表
CLUSTERED BY：对于分区表 Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
TBLPROPERTIES：TBLPROPERTIES 子句允许您使用自己的元数据键/值对标记表定义。还存在一些预定义的表属性，例如由 Hive 自动添加和管理的 last_modified_user 和 last_modified_time。官网地址 (opens new window)。

# 删除表

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];     -- (Note: PURGE available in Hive 0.14.0 and later)

drop table 删除此表的元数据和数据。如果配置了 Trash（并且未指定 PURGE），则数据实际上会移动到 .Trash/Current 目录，元数据完全丢失。如果指定了 Purge，数据不会放到 Trash 中，会直接删除。

# 清空表

语法：

TRUNCATE [TABLE] table_name [PARTITION partition_spec];

partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

1
2
3
4

# 更改表属性

# 重命名

语法：

ALTER TABLE table_name RENAME TO new_table_name;

# 更改表属性

语法：

ALTER TABLE table_name SET TBLPROPERTIES table_properties;

table_properties:
  : (property_name = property_value, property_name = property_value, ... )

1
2
3
4

说明

TBLPROPERTIES 不可小写；
TBLPROPERTIES 用来指定表的相关属性，除了表内置的几个属性之外，用户还可以自己添加表属性，如:

alter table set TBLPROPERTIES('create_user'='will');

# 查看表属性

语法：

-- 查看所有属性
SHOW TBLPROPERTIES tblname;

-- 查看 tbl_prop 属性
SHOW TBLPROPERTIES tblname("tbl_prop");

1
2
3
4
5

例：

show TBLPROPERTIES student;

show TBLPROPERTIES student('create_user');

1
2
3

# 删除自定义表属性

语法：

ALTER TABLE table_name UNSET TBLPROPERTIES table_properties;

table_properties:
  : (property_name, property_name, ... )

1
2
3
4

例：

alter table student unset TBLPROPERTIES('create_user', 'create_time');

说明

Hive 的官方文档中没有说明 UNSET 的用法。

# 修改表注释

语法：

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

# 增加 SerDe

语法：

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];

ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;

serde_properties:
  : (property_name = property_value, property_name = property_value, ... )

1
2
3
4
5
6

例：

alter table table_name set serdeproperties ('field.delim' = ',');

# 增加分区

语法：

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
 
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

1
2
3
4

例：

alter table page_view add partition (dt='2008-08-08', country='us') location '/path/to/us/part080808'
                          partition (dt='2008-08-09', country='us') location '/path/to/us/part080809';

1
2

# 重命名分区

语法：

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

# 删除分区

语法：

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
  [IGNORE PROTECTION] [PURGE];            -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)

1
2

和删除数据一样，如果不指定 PURGE，数据会移动到 Trash，但是元数据会丢失。

# 修改字段

修改列名、类型、位置、注释等。

语法：

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

1
2

上次更新: 2023/11/01, 03:11:44

← Hive 数据类型 Hive 表类型→