【Hive】DDL 与 DML 操作

阿泽 Crz

发布于 2020-07-20 15:38:34

1.5K0

发布于 2020-07-20 15:38:34

文章被收录于专栏：阿泽的学习笔记阿泽的学习笔记

SQL 语言分为四大类：

数据查询语言 DQL：基本结构由 SELECT、FROM、WEHERE 子句构成查询块；
数据操纵语言 DML：包括插入、更新、删除；
数据定义语言 DDL：包括创建数据库中的对象——表、视图、索引等；
数据控制语言 DCL：授予或者收回数据库的权限，控制或者操纵事务发生的时间及效果、对数据库进行监视等。

而 HQL 中，分类如下（以 Hive 的 wiki 分类为准）：

HQL DDL 语法包括：

创建：CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX；
删除：DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX；
替代：ALTER DATABASE/SCHEMA, TABLE, VIEW
清空：TRUNCATE TABLE；
修复：MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)；
展示：SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE；
描述：DESCRIBE DATABASE/SCHEMA, table_name, view_name, materialized_view_name。

HQL DML 语法包括：

导入：Load file to table；
导出：Writing data into thie filesystem from queries；
插入：Inserting data into table from queries/ SQL；
更新：Update；
删除：Delete；
合并：Merge。

1.DDL

1.1 DATABASE

1.1.1 Create Database

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

SCHEMA 和 DATABASE 的用法是可互换，因为含义相同；
IF NOT EXISTS 最好加上，防止冲突；
LOCATION hdfs_path 加载 hdfs 上的数据；
MANAGEDLOCATION 出现在 Hive 4.0 中，指外部表的默认目录；
WITH DBPROPERTIES 可以设置属性和值，会存储在 Mysql 中的元数据库中。

1.1.2 Drop Database

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

删除数据库的模型行为是 RESTRICT，如果数据库不为空，需要添加 CASCADE 进行级联删除。

1.1.3 Alter Database

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)

可以修改数据库的属性（property）、所属人（owner）、位置（location）、外部表位置（menaged location）。

修改位置时，并不会将数据库的当前目录的内容移动到新的位置，只是更改了默认的父目录，在该目录中为此数据库添加新表。

数据库的其他元素无法进行更改。

1.1.4 User Database

USE database_name;
USE DEFAULT;

1.2 TABLE

1.2.1 Create Table

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)

Hive 表名和列名不区分大小写，但 SerDe（序列化/反序列化）和属性名称是区分大小写的；
TEMPORARY：临时表只对此次 session 有效，退出后自动删除；
EXTERNAL：由 hdfs 托管的外部表，不加则为由 hive 管理的内部表；
PARTITIONED：分区，可以用一个或多个字段进行分区，「分区的好处在于只需要针对分区进行查询，而不必全表扫描」；
CLUSTERED：分桶，并非所有的数据集都可以形成合理的分区。可以对表和分区进一步细分成桶，桶是对数据进行更细粒度的划分。Hive 默认采用对某一列的数据进行 Hash 分桶。分桶实际上和 MapReduce 中的分区是一样的。分桶数和 Reduce 数对应；
SKEWED：数据倾斜，通过制定经常出现的值（严重倾斜），hive 会在元数据中记录这些倾斜的列名和值，在 join 时能够进行优化。若是指定了 STORED AS DIRECTORIES，也就是使用列表桶（ListBucketing），hive 会对倾斜的值建立子目录，查询会更加得到优化；
STORED AS file_format：文件存储类型；
LOCATION hdfs_path：hdfs 的位置；
TBLPROPERTIES：表的属性和值；
AS select_statement：可以设置一个代号，不支持外部表；
CTAS：Create table as select，用查询结果来创建和填充。CTAS 有些限制：目标表不能是分区表、不能是外部表、不能是列表桶表。

当然，我们也可以从已有的数据中进行 Copy，使用 Like 字段：

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

1.2.2 Drop Table

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

删除表的元数据和数据。如果加 PURGE 字段，则数据不会转移到 .Trash/Current 目录下。因此，误操作后将无法恢复。

1.2.3 Truncate Table

TRUNCATE [TABLE] table_name [PARTITION partition_spec];
 
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

清空表或分区（一个或多个分区）的所有行。

1.2.3 Alter Table/Partition/Column

1.2.3.1 Table

修改表名：

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, ... )

修改表注释：

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, ... )

Hive 4.0 支持删除 SerDe 属性：

ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

更改表存储属性：

ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)]
  INTO num_buckets BUCKETS;

修改表倾斜：

ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...)
  ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...]
  [STORED AS DIRECTORIES];

更改表不倾斜：

ALTER TABLE table_name NOT SKEWED;

更改表未存储为目录：

ALTER TABLE table_name NOT STORED AS DIRECTORIES;

更改表的约束：

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE;
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column, ...) REFERENCES table_name(column, ...) DISABLE NOVALIDATE RELY;
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column, ...) DISABLE NOVALIDATE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name NOT NULL ENABLE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name DEFAULT default_value ENABLE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name CHECK check_expression ENABLE;
 
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

1.2.3.2 Partition

添加分区：

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, ...)

重命名分区：

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

交换分区：

-- Move partition from table_name_1 to table_name_2
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_1;
-- multiple partitions
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec, partition_spec2, ...) WITH TABLE table_name_1;

恢复分区：

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

如果新的分区被直接加入到 HDFS（比如 hadoop fs -put），或从 HDFS 移除，metastore 并将不知道这些变化，除非用户在分区表上每次新添或删除分区时分别运行 ALTER TABLE table_name ADD/DROP PARTITION 命令。

我们可以运行恢复分区来进行维修。

删除分区：

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.3.3 Column

更改列名称/类型/位置/注释：

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 
  [PARTITION partition_spec]                 -- (Note: Hive 0.14.0 and later)
  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  [CASCADE|RESTRICT]                         -- (Note: Hive 1.1.0 and later)

1.3 VIEW

1.3.1 Create View

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
 ``[COMMENT view_comment]
 ``[TBLPROPERTIES (property_name = property_value, ...)]
 ``AS SELECT ...;

视图是纯逻辑对象，没有相关的存储；
如果视图的定义 SELECT 表达式无效，则 CREATE VIEW 语句将失败；
视图只读，不能用作 LOAD/INSERT/ALTER 的目标；
视图可能包含 ORDER BY 和 LIMIT 子句；

1.3.2 Delete View

DROP VIEW [IF EXISTS] [db_name.]view_name;

1.3.3 Alter View

ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;
 
table_properties:
  : (property_name = property_value, property_name = property_value, ...)

同 Table。

1.3.4 Alter View As Select

ALTER VIEW [db_name.]view_name AS select_statement;

更改视图的定义。

1.4 INDEX

1.4.1 Create Index

CREATE INDEX index_name
  ON TABLE base_table_name (col_name, ...)
  AS index_type
  [WITH DEFERRED REBUILD]
  [IDXPROPERTIES (property_name=property_value, ...)]
  [IN TABLE index_table_name]
  [
     [ ROW FORMAT ...] STORED AS ...
     | STORED BY ...
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (...)]
  [COMMENT "index comment"];

使用给定的列作为键在表上创建索引

1.4.2 Drop Index

DROP INDEX [IF EXISTS] index_name ON table_name;

1.4.3 Alter Index

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

REBUILD 为使用 WITH DEFERRED REBUILD 子句的索引建立索引或重建先前建立的索引。如果指定分区，那么只有该分区重建。

1.5 MACRO

宏命令，与 Java 中的宏一致。

1.5.1 Create Macro

CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;

举个例子：

CREATE TEMPORARY MACRO fixed_number() 42;
CREATE TEMPORARY MACRO string_len_plus_two(x string) length(x) + 2;
CREATE TEMPORARY MACRO simple_add (x int, y int) x + y;

宏的有效期存在于该 Session 内。

1.5.2 Drop Macro

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

1.6 FUNCTION

1.6.1 Temporary Function

创建和删除临时函数：

CREATE TEMPORARY FUNCTION function_name AS class_name;

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

1.6.2 Permanent Function

在 Hive0.13 或更高版本中，函数可以注册到 metastore，这样就可以在每次查询中进行引用，而不需要每次都创建临时函数。

创建和删除永久函数：

CREATE FUNCTION [db_name.]function_name AS class_name
 ``[USING JAR|FILE|ARCHIVE ``'file_uri'` `[, JAR|FILE|ARCHIVE ``'file_uri'``] ];
 
DROP FUNCTION [IF EXISTS] function_name;

重载函数：

RELOAD (FUNCTIONS|FUNCTION);

1.7 SHOW

Show 操作可以利用正则表达式进行过滤，而正则表达式中的通配符只能是“ *”或“ |” 供选择。

展示数据库：

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

将列出了元存储中定义的所有数据库。

展示表：

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

展示视图

SHOW VIEWS [IN/FROM database_name] [LIKE ``'pattern_with_wildcards'``];

展示表/分区扩展

SHOW TABLE EXTENDED [IN|FROM database_name] LIKE ``'identifier_with_wildcards'` `[PARTITION(partition_spec)];

展示表的属性

SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname(``"foo"``);

展示函数

SHOW FUNCTIONS [LIKE ``"<pattern>"``];

1.8 DESCRIBE

描述数据库，包括数据库名、注释、位置等。EXTENDED 还会显示了数据库属性。

DESCRIBE DATABASE [EXTENDED] db_name;
DESCRIBE SCHEMA [EXTENDED] db_name;   -- (Note: Hive ``1.1``.``0` `and later)

描述表/视图/材料化视图/列：

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)

描述列统计：

DESCRIBE FORMATTED [db_name.]table_name column_name;               -- (Note: Hive ``0.14``.``0` `and later)
DESCRIBE FORMATTED [db_name.]table_name column_name PARTITION (partition_spec);  -- (Note: Hive ``0.14``.``0` `to ``1``.x.x)
                                         ``-- (see ``"Hive 2.0+: New Syntax"` `below)

描述分区：

DESCRIBE [EXTENDED|FORMATTED] table_name[.column_name] PARTITION partition_spec;
                    ``-- (Note: Hive ``1``.x.x and ``0``.x.x only. See ``"Hive 2.0+: New Syntax"` `below)

2.DML

2.1 Load data

在将数据加载到表中时，Hive 不执行任何转换。Load 操作是纯复制/移动操作，仅将数据文件移动到与 Hive 表对应的位置。

LOAD DATA [LOCAL] INPATH ``'filepath'` `[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOAD DATA [LOCAL] INPATH ``'filepath'` `[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT ``'inputformat'` `SERDE ``'serde'``] (``3.0` `or later)

filepath 可以是绝对路径也可以是相对路径，也可以是一个 URI；
加载到目标可以是一个表或一个分区。如果是分区表，则必须制定所有分区列的值来确定加载特定分区；
filepath 可以是文件，也可以是目录；
制定 LOCAL 可以加载本地文件系统，否则默认为 HDFS；
如果使用了 OVERWRITE，则原内容将被删除；否则，将直接追加数据。

Hive 3.0 开始支持 Load 操作。

举例子：

CREATE TABLE tab1 (col1 ``int``, col2 ``int``) PARTITIONED BY (col3 ``int``) STORED AS ORC;
LOAD DATA LOCAL INPATH ``'filepath'` `INTO TABLE tab1;

2.2 Insert data

将查询数据插入到 Hive 表中。

-- 标准语法:
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

-- Hive 扩展(多表插入模式):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;

FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;

-- Hive 扩展 (动态分区插入模式):
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

INSERT OVERWRITE 将覆盖在表或分区的任何现有数据；
INSERT INTO将追加到表或分区，保留原有数据不变；
插入目标可以是一个表或分区。如果是分区表，则必须由设定所有分区列的值来指定表的特定分区；
可以在同一个查询中指定多个INSERT子句(也称为多表插入)。多表插入可使数据扫描所需的次数最小化。通过对输入数据只扫描一次(并应用不同的查询操作符)，Hive可以将数据插入多个表中；
如果给出分区列值，我们将其称为静态分区，否则就是动态分区；

2.3 Export data

将查询数据写入到文件系统中。

-- 标准语法:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...

-- Hive 扩展 (多表插入):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

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: Only available starting with Hive 0.13)

目录可以是一个完整的 URI；
使用 LOCAL，可以将数据写入到本地文件系统的目录上；
写入文件系统的数据被序列化为由 ^A 做列分割符，换行做行分隔符的文本。如果任何列都不是原始类型（而是 MAP、ARRAY、STRUCT、UNION），则这些列被序列化为 JSON 格式；
可以在同一查询中，INSERT OVERWRITE到目录，到本地目录和到表（或分区）；
INSERT OVERWRITE 语句是 Hive 提取大量数据到 HDFS 文件目录的最佳方式。Hive 可以从 map-reduce 作业中的并行写入 HDFS 目录；

2.4 Insert values

直接从 SQL 将数据插入到表中。

--标准语法:
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

-- 此处的values_row is:
-- ( value [, value ...] )
-- 此处的value或者是NULL或者是任何有效的sql表达式。

在 VALUES 子句中列出的每一行插入到表 tablename 中；
以 INSERT ... SELECT 同样的方式，来支持动态分区。
不支持 INSERT INTO VALUES 子句将数据插入复杂的数据类型（数组、映射、结构、联合）列中。

2.5 Update

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

被引用的列必须是被更新表中的列；
设置的值必须是 Hive Select 子句中支持的表达式。算术运算符，UDF，转换，文字等，是支持的，子查询是不支持的；
只有符合 WHERE 子句的行才会被更新；
分区列不能被更新；
分桶列不能被更新；

2.6 Delete

DELETE FROM tablename [WHERE expression]

只有符合WHERE子句的行会被删除。

2.7 Merge

-- 标准语法:
MERGE INTO <target table> AS T USING <source expression/table> AS S
ON <boolean expression1>
WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list>
WHEN MATCHED [AND <boolean expression3>] THEN DELETE
WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>

Merge 允许根据与源表 Join 的结果对目标表执行操作；
on 语句会对源与目标进行检查，此计算开销很大；

3.Reference

LanguageManual DDL
LanguageManual DML

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-07-19，如有侵权请联系 cloudcommunity@tencent.com 删除

hive

本文分享自阿泽的学习笔记微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度