好记性不如烂笔头，何况记性不好
本文都来自hive文档, 记下来方便查询。
Database操作
- create database
文法CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; - drop database
文法DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
默认是RESTRICT，在数据库不空是，drop失败。CASCADE会删除掉db中的表。
- alter database
文法
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
- 查询当前db
SELECT current_database()
Table 操作
- 创建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 [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
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 -- (Note: Available in Hive 0.7.0 and later)
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)
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, ... > -- (Note: Available in Hive 0.7.0 and later)
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: Available in Hive 0.13 and later)
| 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 -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.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 TEMPRARY table ..., 临时表和当前session相关，session断开之后HIVE会删除临时表。临时表不支持分区和索引内表
使用CREATE table ...模式创建内表，内表元数据、表数据都有HIVE管理。表数据存放在hive.metastore.warehouse.dir/table_name下，drop内表时，表数据和元数据都会被删除。外表
使用CREATE EXTERNAL table ...创建外表，创建时可是使用CREATE EXTERNAL table ... LOCATION hdfs_path指定外表数据存储的位置，当你已经有一份数据时，可以将数据挡在hdfs_path路径下，然后创建外表。drop外表时只会删除表的元数据，表数据不会删除。更新表元数据库
当通过手动的方式(hdfs dfs -put)向hdfs某表路径下添加分区数据时，hive管理的元数据无法自动追踪分区信息，可以通过MSCK REPAIR TABLE table_name;检查并修复元数据库。表数据存储格式
STORED AS INPUTFORMAT input_format_class OUTPUTFORMAT output_format_class，自定义input format和output format从hdfs读和写。-
ROW FORMAT
有两种方式：DELIMITED，指定符号分割行，行内各个field分割方式，如果存在column是map或者collection类型，分别通过
MAP KEYS TERMIATED BY char分割key和value，或者通过COLLECTION ITEMS TERMINATED BY char分割列表元素。SERDE, 自定义序列化反序列化类，可以参考hive serde.
-
分区表
通过语句create table table_name(...) partitioned by (col_name1 col_type1, col_name2 col_type2 )按照指定列创建分区表，可以像partitioned by (col_name1 col_type1, col_name2 col_type2，.. )创建多个分区，一个分区对应一个子目录，比如col_name1可以table_name目录下的子路径，col_name2又是col_name1的下一级路径。被用来分区的列(col_name1,col_name2等)不能在出现在创建表的列里面。
-
通过从select语句的查询结果创建表
create table as select xxx from xxx,这是一个原子操作，意思就是在select语句产生全部结果之后，才会基于结果创建table，用户不会看见执行一部分的select结果。通过这中方式创建的表要求创建出来的表不可以是：分区表(PARTITIONED BY xxx)、分桶表（CLUSTERD BY xxx）、外表。
create table like
通过CREATE table table_name like another_table这种方式可以创建一个新的表table_name，其表定义和another_table一样，但是新表是一个空表，不会复制another_table的数据，仅仅表结构一样。-
分桶表
CREATE TABLE table_name(...) CLUSTERED BY (col_name col_type, ...) SORTED BY (col_name ...) INTO num_bucker BUCKETS
创建分桶表，用来创建bucket的列名必须出现在创建表时的列里，这和partition不一样。同时分桶和分区可以同时出现，不冲突。往分桶表中插入数据时，HIVE不会自动的分桶和排序（分区表也不会），因此需要指定分桶，有两种方式：
- 打开分桶开关，强制分桶
SET hive.enforce.bucketing=true;
使用如下语句插入：
insert into table table_name sort by col_name [desc|asc],...
此时不需要使用CLUSTER BY，但是需要指定sort by，且和创建时SORTED BY一样。 - 设置
SET mapred.reduce.tasks = <num_buckets>;，reducer个数和创建时桶个数一样。
使用如下语句插入：
insert into table table_name cluster by col_name,... sort by col_name [desc|asc],...
- 打开分桶开关，强制分桶
- ALTER table|partition
- 设置table|partition 的serde class
ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]; - 增加分区
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;
增加分区只会修改元数据，不检查数据是否存在、不加载数据。所以指定的LOCATION即使不存在也不会出错。
- 设置table|partition 的serde class
-
修改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];-
col_old_name col_new_name column_type将旧的column改成新的。 - FIRST|AFTER column_name表示将列置于column_name之前和之后。
- CASCADE会修改表的元数据和partition的元数据。RESTRICT只会修改表的元数据。
-
-
ADD/REPLACE column
ALTER TABLE table_name [PARTITION partition_spec] ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) [CASCADE|RESTRICT]- ADD 新增列，新增的列在分区列之前，其他非分区列之后。
- REPLACE，会删除现在所有列，然后加入新的列。
- 视图操作
- 创建视图
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT view_comment] [TBLPROPERTIES (property_name = property_value, ...)] AS SELECT ...;- view_name 不能和已有的表或者视图重名。
- 不指定column_name 时会从SELECT中推断列名。
- 当视图所基于的底层表schema发生改变时，视图的schema不会改变。
- 视图是只读的，不可以使用INSERT/DELETE/ALTER.
- 删除视图
DROP VIEW [IF EXISTS]db_name.view_name - 修改视图
ALTER VIEW [db_name.]view_name AS select_statement;
其效果和CREATE VIEW一样，但是要求view_name必须存在。
- 索引操作
- 创建索引
持续更新...
