Hive中语法规则大全，建议收藏

Hive

一、创建/删除/修改/使用数据库

# 创建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

# 删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

# 修改数据库
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

# 使用数据库
USE database_name;
USE DEFAULT;

二、创建/删除/截断表

1.创建表

# 建表语法大全

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name # 表示创建的是内部表还是外部表
[(col_name data_type [COMMENT col_comment], ...)]  #表的字段
[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]   #指定分桶，排序规则，以及分桶个数
[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];


# LIKE形式允许准确地复制现有的表定义(而不复制其数据)
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

# 支持的字段的类型
 data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type

primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION
  | STRING
  | BINARY
  | TIMESTAMP
  | DECIMAL
  | DECIMAL(precision, scale)
  | DATE
  | VARCHAR
  | CHAR

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

# 行格式化的形式
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]
  | 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
  | ORC
  | PARQUET
  | AVRO
  | JSONFILE    -- (Note: Available in Hive 4.0.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

# 约束示例
create table fk(id1 integer, id2 integer,
  constraint c1 foreign key(id1, id2) references pk(id2, id1) disable novalidate);

# 数据的导出
## 单重导出
insert overwrite local directory 'linux path' select * from t_name;
## 多重导出
from t_name insert overwrite local directory 'linux path' select * where ...
insert overwrite local directory 'linux path' select * where...


# 数据的装载
load data local inpath 'linux path' into table t_name ;   #本地导入
local data inpath 'hdfs path' into table t_name    #从hdfs中导入
#注意：如果是内部表的话，在hdfs导入，那么原本的数据会被移动到相应的表的目录下
load data local inpath 'linux path ' overwrite into table 表名；  #覆盖导入

# 查询语句的执行顺序
from ----join ---on----where----group by ---having ---select ----distinct ----ordey by -----limit

2.截断表和删除表

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

# 删除表
DROP TABLE [IF EXISTS] table_name [PURGE];

3.改变表/分区/列

#  重命名表
ALTER TABLE table_name RENAME TO new_table_name;

# 修改属性
ALTER TABLE table_name SET TBLPROPERTIES table_properties;

TBLPROPERTIES:
  : (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;

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

# 修改存储属性
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 SET SKEWED LOCATION (col_name1="location1" [, col_name2="location2", ...] );


# 修改约束
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 DROP CONSTRAINT constraint_name;

# 添加分区
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;

#  恢复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

# 删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
  [IGNORE PROTECTION] [PURGE];

# 修改分区文件格式
ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;

# 修改分区位置
ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";

# 更改列名称/类型/位置/注释
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]    

三、创建/删除/更改视图

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

# 删除视图
DROP VIEW [IF EXISTS] [db_name.]view_name;

# 改变视图属性
ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;
table_properties:
  : (property_name = property_value, property_name = property_value, ...)

四、创建/删除/修改索引

# 创建索引
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"];


# 删除索引
DROP INDEX [IF EXISTS] index_name ON table_name;

# 修改索引
ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

五、创建/删除/重新装载函数

# 临时函数
CREATE TEMPORARY FUNCTION function_name AS class_name;

#  删除临时函数
DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

# 创建永久函数
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 FUNCTION;

六、创建/删除/授予/撤销角色和权限

# 将角色授予某个用户、角色：
GRANT role_name [, role_name] ...
TO principal_specification [, principal_specification] ...
[ WITH ADMIN OPTION ];

principal_specification
  : USER user
  | ROLE role


# 查看授予某个用户、角色的角色列表
SHOW ROLE GRANT (USER|ROLE) principal_name;

# 查看属于某种角色的用户、角色列表
SHOW PRINCIPALS role_name;

# 移除某个用户、角色的角色：
REVOKE [ADMIN OPTION FOR] role_name [, role_name] ...
FROM principal_specification [, principal_specification] ... ;
 
principal_specification
  : USER user
  | ROLE role


# 将权限授予某个用户、角色：
GRANT
    priv_type [, priv_type ] ...
    ON table_or_view_name
    TO principal_specification [, principal_specification] ...
    [WITH GRANT OPTION];

# 移除某个用户、角色的权限：
REVOKE [GRANT OPTION FOR]
    priv_type [, priv_type ] ...
    ON table_or_view_name
    FROM principal_specification [, principal_specification] ... ;
    
principal_specification
  : USER user
  | ROLE role
 
priv_type
  : INSERT | SELECT | UPDATE | DELETE | ALL

# 查看某个用户、角色的权限：
SHOW GRANT [principal_name] ON (ALL| ([TABLE] table_or_view_name)