Hive

一、創(chuàng)建/刪除/修改/使用數(shù)據(jù)庫

# 創(chuàng)建數(shù)據(jù)庫
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

# 刪除數(shù)據(jù)庫
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

# 修改數(shù)據(jù)庫
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;

# 使用數(shù)據(jù)庫
USE database_name;
USE DEFAULT;

二、創(chuàng)建/刪除/截斷表

1.創(chuàng)建表

# 建表語法大全

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name # 表示創(chuàng)建的是內(nèi)部表還是外部表
[(col_name data_type [COMMENT col_comment], ...)]  #表的字段
[COMMENT table_comment]   #表的描述信息
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  #指定分區(qū)表
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]   #指定分桶，排序規(guī)則，以及分桶個數(shù)
[ROW FORMAT row_format]   #指定分隔符
[STORED AS file_format]   | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  #指定數(shù)據(jù)存儲格式
[LOCATION hdfs_path]      #指定數(shù)據(jù)存儲目錄 （在創(chuàng)建外部表時使用）
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];


# LIKE形式允許準確地復制現(xiàn)有的表定義(而不復制其數(shù)據(jù))
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);

# 數(shù)據(jù)的導出
## 單重導出
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...


# 數(shù)據(jù)的裝載
load data local inpath 'linux path' into table t_name ;   #本地導入
local data inpath 'hdfs path' into table t_name    #從hdfs中導入
#注意：如果是內(nèi)部表的話，在hdfs導入，那么原本的數(shù)據(jù)會被移動到相應的表的目錄下
load data local inpath 'linux path ' overwrite into table 表名；  #覆蓋導入

# 查詢語句的執(zhí)行順序
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.改變表/分區(qū)/列

#  重命名表
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;

# 添加分區(qū)
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, ...)

#  重命名分區(qū)
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

#  恢復分區(qū)
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

# 刪除分區(qū)
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
  [IGNORE PROTECTION] [PURGE];

# 修改分區(qū)文件格式
ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;

# 修改分區(qū)位置
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]    

三、創(chuàng)建/刪除/更改視圖

# 創(chuàng)建視圖
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, ...)

四、創(chuàng)建/刪除/修改索引

# 創(chuàng)建索引
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;

五、創(chuàng)建/刪除/重新裝載函數(shù)

# 臨時函數(shù)
CREATE TEMPORARY FUNCTION function_name AS class_name;

#  刪除臨時函數(shù)
DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

# 創(chuàng)建永久函數(shù)
CREATE FUNCTION [db_name.]function_name AS class_name
  [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];

# 刪除函數(shù)
DROP FUNCTION [IF EXISTS] function_name;

# 重新加載函數(shù)
RELOAD FUNCTION;

六、創(chuàng)建/刪除/授予/撤銷角色和權限

# 將角色授予某個用戶、角色：
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)