Hive应用(2.1)DDL

HiveQL时Hive的查询语言,语法和Mysql有很多相似之处

1.数据库

1.1 查看所有数据库

Hive中默认有一个default数据库,使用如下命令查看hive中的数据库:

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

1.2创建数据库

创建一个新的数据库:

1
2
3
4
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path] --自定义数据库文件位置
[WITH DBPROPERTIES (property_name=property_value, ...)];

创建好的数据库目录位于hdfs中的 /user/hive/warehouse/数据库名.db

1.3 查看数据库

DESCRIBE可以查看数据库的详细信息,加上EXTENDED参数还会显示数据库的配置参数

1
DESCRIBE DATABASE [EXTENDED] db_name;

1.4 修改数据库

Hive数据库只有极少数元数据可以被修改,其他都不能被修改

1
2
3
4
5
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- 修改数据库properties(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)

1.5 删除数据库

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

当数据库中有表存在时RESTRICT模式会失败,CASCADE则会级联删除表

2.表

2.1 创建表

如下为创建表的所有参数,注释中未提到的内容会在下面提及

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    --TEMPORARY表只存在当前session中,session结束被清空;(Note: TEMPORARY available in Hive 0.14.0 and later)
--EXTERNAL用于创建外部表,通常搭配LOCATION指定外部表的路径。外部表删除时只会删除元数据,hdfs中的数据文件不会删除。不带此参数的表被称为管理表
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])] --constraint_specification用于指定约束,目前支持主键和外键,但是hive不会验证这些约束是否满足
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --PARTITIONED用于对表进行分区,表分区通常为了提升查询性能。用于分区的列和值不会保存在表中,而是作为文件夹名对分区数据进行存储。注意分区过多会导致性能下降。
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --
[SKEWED BY (col_name, col_name, ...) -- SKEWED用于优化表中存在数据倾斜的查询性能,通过指定倾斜的列和出现最多的值,hive会把这些常见值分布到多个文件中,剩余的值分配到另外的文件中(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, ...)] -- 自定义table的properties熟悉(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 --LIKE用于复制一张表结构相同的表,数据则不会复制,新生成的表是否时外部表与原表保持一致
[LOCATION hdfs_path];


data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later)

primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)

array_type
: ARRAY < data_type >

map_type
: MAP < primitive_type, data_type >

struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>

union_type
: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)

row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

2.2 删除表

1
DROP TABLE [IF EXISTS] table_name [PURGE];     -- PURGE参数使得表数据文件直接被删除,不会放到回收站(Note: PURGE available in Hive 0.14.0 and later)

2.3 修改表

  1. 重命名

    1
    ALTER TABLE test RENAME TO test2;

  2. 修改分区

    1
    2
    3
    4
    5
    ALTER TABLE users ADD IF NOT EXISTS PARTITION (country = 'CN') LOCATION '/test/CN'; --新增分区

    ALTER TABLE users PARTITION (country = 'CN') SET LOCATION '/test2/CN'; --修改分区路径,但旧数据还在原来目录下

    ALTER TABLE users DROP PARTITION (country = 'CN'); --删除分区,外部表数据文件不会被删除

  3. 修改列

    1
    2
    3
    4
    5
    6
    7
    ALTER TABLE users CHANGE COLUMN country state STRING  --修改列名、数据类型
    COMMENT 'Where you live' --修改注释,可选
    AFTER 'name' --修改列顺序,可选。(修改顺序只是修改元数据,数据文件需要手动修改)

    ALTER TABLE users ADD COLUMNS (phone STRING, address STRING); --增加列,追加到现有列后

    ALTER TABLE users REPLACE COLUMNS (id BIGINT, name STRING) --删除现有列,替换为指定的列

  4. 修改表属性

    1
    2
    3
    ALTER TABLE users SET TBLPROPERTIES (
    'test' = 'hello world'
    ); --只能新增修改属性,不能删除

2.4 表存储格式

Hive默认使用TEXTFILE作为数据存储格式,但是Hive还支持SEQUENCEFILE和RCFILE两种二进制可压缩文件。Hive对于记录的解析是由SerDe(序列器/反序列器)来控制的,通过扩展第三方SerDe可以使Hive支持其他类型的文件。