【Hive笔记】 三、Hive的DDL和DML操作

首先明确定义什么是 DDL 和 DML:

• DDL(Data Definition Language) 数据定义语言: 对数据库中的对象进行操作
• DML(Data Manipulation Language) 数据操纵语言: 对数据库中的数据进行操作

DDL操作

新建数据库

-- 新建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name   --DATABASE|SCHEMA 是等价的
[COMMENT database_comment] --数据库注释
[LOCATION hdfs_path] --存储在 HDFS 上的位置
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性

操作数据库

-- 查看数据库
SHOW DATABASES;
-- 使用数据库
USE database_name;
-- 查看数据库信息
DESC DATABASE [EXTENDED] db_name; --EXTENDED 表示是否显示额外属性
-- 删除数据库: 默认RESTRICT若数据库中存在表则报错
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

新建数据表

-- 新建数据表
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name     --表名
    [
        (col_name data_type [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, ...) ON ((col_value, col_value, ...)...)
    [STORED AS DIRECTORIES]
]  --指定倾斜列和值
[
    [ROW FORMAT row_format]
    [STORED AS file_format]
    | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
]  -- 指定行分隔符、存储文件格式或采用自定义存储格式
[LOCATION hdfs_path]  -- 指定表的存储位置
[TBLPROPERTIES (property_name=property_value, ...)]  --指定表的属性
[AS select_statement];   --从查询结果创建表

操作数据表

-- 加载数据
load data local inpath "data.txt" into table table_name;
-- 重命名表
ALTER TABLE table_name RENAME TO new_table_name;
-- 修改列
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];
-- 新增列
ALTER TABLE table_name ADD COLUMNS (col_name column_type COMMENT col_comment);
-- 清空整个表或表指定分区中的数据
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];
-- 删除表
DROP TABLE [IF EXISTS] table_name [PURGE];

DML操作

整体更新数据

-- 通过加载文件覆盖数据表分区
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE]
INTO TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 ...)]
-- 通过查询结果覆盖数据表分区
INSERT OVERWRITE TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]]
SELECT * FROM (select_statement);

更新删除数据

-- 更新
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
--删除
DELETE FROM tablename [WHERE expression]

该部分语法需要 HIVE 支持事务处理, 即在 hive-set.xml 中开启如下配置

<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
</property>
<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>
<property>
    <name>hive.in.test</name>
    <value>true</value>
</property>

注意: 事务表存在必须为分桶表+ORC的限制, 并且不支持 LOAD DATA 加载操作.

查询结果写入文件系统

INSERT OVERWRITE [LOCAL] DIRECTORY file_path
  [ROW FORMAT row_format] [STORED AS file_format]
  SELECT ... FROM ...

参考资料

• Hive 常用 DDL 操作
• Hive 常用 DML 操作