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形式允許準(zhǔn)確地復(fù)制現(xiàn)有的表定義(而不復(fù)制其數(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ù)的導(dǎo)出
## 單重導(dǎo)出
insert overwrite local directory 'linux path' select * from t_name;
## 多重導(dǎo)出
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 ; #本地導(dǎo)入
local data inpath 'hdfs path' into table t_name #從hdfs中導(dǎo)入
#注意：如果是內(nèi)部表的話代兵，在hdfs導(dǎo)入，那么原本的數(shù)據(jù)會被移動到相應(yīng)的表的目錄下
load data local inpath 'linux path ' overwrite into table 表名裳擎； #覆蓋導(dǎo)入
# 查詢語句的執(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;
## 設(shè)置傾斜表存儲位置
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;
# 恢復(fù)分區(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)建/刪除/授予/撤銷角色和權(quán)限
# 將角色授予某個用戶、角色：
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
# 將權(quán)限授予某個用戶宏所、角色：
GRANT
priv_type [, priv_type ] ...
ON table_or_view_name
TO principal_specification [, principal_specification] ...
[WITH GRANT OPTION];
# 移除某個用戶摊溶、角色的權(quán)限：
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
# 查看某個用戶、角色的權(quán)限：
SHOW GRANT [principal_name] ON (ALL| ([TABLE] table_or_view_name)
