Hive DDL语法

Hive的语法和MySQL大部分都相同

一、数据库操作

1.1、创建数据库

CREATEDATABASE[IFNOTEXISTS]database name [COMMENT database_comment][LOCATION hdfs path][WITH DBPROPERTIES (property_name=property_value,...)];

语法：
[IF NOT EXISTS]：判断数据库是否存在
[COMMENT]：注释
[LOCATION]：数据库存储在hdfs的路径，默认为：${hive.metastore.warehouse.dir}/table_name.db

# 创建数据库createdatabase db_hive;

# 创建数据库,设置表注释createdatabase db_hive '测试用hive表';

# 创建数据库,指定路径createdatabase db_hive location '/db_hive';

# 创建数据库,指定kv键值对createdatabase db_hive with dbproperties('create_user'='tom','create_date'='2023-12-05');

1.2、查询数据库

SHOWDATABASE[LIKE'identifier_with_wildcards'];

语法：
[LIKE]：模糊匹配

# 查看所有数据库showdatabase;

# 查看所有db开头的数据库showdatabaselike'db*';

1.3、查看数据库详细信息

DESCRIBEDATABASE[EXTENDED] db_name;

语法：
[EXTENDED]：是否展示更为详细的信息

# 查看数据库descdatabase db_hive;

# 查看数据库更多信息descdatabaseextended db_hive;

1.4、修改数据库

# 修改dbpropertiesALTERDATABASE db_name SET DBPROPERTIES (property name=property_value， ...);# 修改locationALTERDATABASE db_name SET LOCATION hdfs_path;# 修改owner userALTERDATABASE db_name SET OWNER USER user_name

1.5、删除数据库

DROPDATABASE[IFEXISTS] db_name [RESTRICT][CASCADE];

语法：
[EXTENDED]：是否展示更为详细的信息
[RESTRICT]：严格模式，如果数据库不为空，则删除失败，默认为该模式
[CASCADE]：级联模式，如果数据库不为空，则会将数据库中的表一并删除

# 删除空数据库dropdatabase db_hive;

# 删除非空数据库dropdatabase db_hive cascade;

二、表操作

CREATE[TEMPORARY][EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_name   
[(col_name data_type [COMMENT col_comment],...)][COMMENT table_comment][PARTITIONED BY(col_name data_type [COMMENT col_comment],...)][CLUSTEREDBY(col_name, col_name,...)[SORTED BY(col_name [ASC|DESC],...)]INTO num_buckets BUCKETS][ROW FORMAT row_format][STORED AS file_format][LOCATION hdfs_path][TBLPROPERTIES (property_name=property_value,...)]

2.1、表类型

语法
TEMPORARY：临时表
EXTERNAL：外部表

1. 临时表：只有当前会话可见，会话结束，表会被删除
2. 内部表：完全交给hive托管，包括元数据和hdfs数据，删除表时会将元数据、hdfs数据全部删除。默认为内部表
3. 外部表：只将元数据交给hive托管，删除表时只会删除元数据，不会删除hdfs中的数据

2.2、数据类型

1. 基本数据类型

整型：tinyint、smallint、int、bigint
浮点型：float、double、decimal
字符型：varchar（需要指定最大长度[1,65535]）、string（不需要指定最大长度）
布尔型：boolean
时间戳：timestamp
二进制：binary

1. 复杂数据类型

array：数组类型
map：key-value类型
struct：对象类型

2.3、创建分区表

[PARTITIONED BY(col_name data_type [COMMENT col_comment],...)]

分区表的每一个分区都对应数据库中相应分区列的一个索引，但是其组织方式和传统的关系型数据库不同。在Hive中，分区表的每一个分区都对应表下的一个目录，所有的分区的数据都存储在对应的目录中

2.4、创建分桶表

CLUSTEREDBY... SORTED BY...INTO... BUCKETS

对指定列进行哈希(hash)计算，然后会根据hash值进行切分数据，将具有不同hash值的数据写到每个桶对应的文件中

2.5、序列化和反序列化

1. 使用DELIMITED关键字序列化反序列化

ROW FORAMT DELIMITED 
[FIELDSTERMINATEDBYchar][COLLECTION ITEMS TERMINATEDBYchar][MAP KEYSTERMINATEDBYchar][LINESTERMINATEDBYchar][NULL DEFINED ASchar]

FIELDS TERMINATED BY：列分隔符
COLLECTION ITEMS TERMINATED BY： map、struct和array中每个元素之间的分隔符
MAP KEYS TERMINATED BY：map中的key与value的分隔符
LINES TERMINATED BY：行分隔符
NULL DEFINED AS：如果数据为null时的占位符，默认为：\n

1. 使用SERDE自定义序列化和反序列化

ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value,...)]

2.6、指定文件格式

[STORED AS file_format]

常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等

2.7、指定表HDFS路径

[LOCATION hdfs_path]

指定表所对应的HDFS路径，若不指定路径。
其默认值为：
${hive.metastore.warehouse.dir}/db_name.db/table_name

2.8、配置kv键值对

[TBLPROPERTIES (property_name=property_value,...)]

用于配置表的一些KV键值对参数

2.9、创建其他表

1. 使用查询结果建表

CREATE[TEMPORARY]TABLE[IFNOTEXISTS] table_name 
[COMMENT table_comment][ROW FORMAT row_format][STORED AS file_format][LOCATION hdfs_path][TBLPROPERTIES (property_name=property_value,...)][AS select_statement]

1. 复制一张已经存在的表

CREATE[TEMPORARY][EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_name
[LIKE exist_table_name][ROW FORMAT row_format][STORED AS file_format][LOCATION hdfs_path][TBLPROPERTIES (property_name=property_value,...)]

2.10、查看数据库表信息

showtables

2.11、查看数据库表明细

desctabledescextendedtabledesc formatted table

2.12、修改表名

ALTERTABLE table_name RENAMETO new_table_name

2.13、修改列信息

只会修改元数据信息，不会修改HDFS文件数据

# 新增列ALTERTABLE table_name ADDCOLUMNS(col_name data_type [COMMENT col_comment],...)

# 修改列ALTERTABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment][FIRST|AFTER column_name]

2.14、删除表

DROPTABLE[IFEXISTS]table;

2.15、清空表

TRUNCATE[TABLE]table

三、参考地址

尚硅谷Hive视频教程