HIVE常用命令(DDL)

好记性不如烂笔头，何况记性不好
本文都来自hive文档, 记下来方便查询。

1. create database
   文法

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


2. drop database
   文法

   DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];


默认是RESTRICT，在数据库不空是，drop失败。CASCADE会删除掉db中的表。

1. 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;


1. 查询当前db

SELECT current_database()
Table 操作

1. 创建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
map_type
: MAP
struct_type
: STRUCT
union_type
: UNIONTYPE -- (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
  有两种方式：

  1. DELIMITED，指定符号分割行，行内各个field分割方式，如果存在column是map或者collection类型，分别通过MAP KEYS TERMIATED BY char分割key和value，或者通过COLLECTION ITEMS TERMINATED BY char分割列表元素。

  2. 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不会自动的分桶和排序（分区表也不会），因此需要指定分桶，有两种方式：

  1. 打开分桶开关，强制分桶SET hive.enforce.bucketing=true;
     使用如下语句插入：
     insert into table table_name sort by col_name [desc|asc],&＃8230;
     此时不需要使用CLUSTER BY，但是需要指定sort by，且和创建时SORTED BY一样。
  2. 设置SET mapred.reduce.tasks = ;，reducer个数和创建时桶个数一样。
     使用如下语句插入：
     insert into table table_name cluster by col_name,&＃8230; sort by col_name [desc|asc],&＃8230;

1. 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即使不存在也不会出错。

• 修改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];

  1. col_old_name col_new_name column_type将旧的column改成新的。
  2. FIRST|AFTER column_name表示将列置于column_name之前和之后。
  3. 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]

  1. ADD 新增列，新增的列在分区列之前，其他非分区列之后。
  2. REPLACE，会删除现在所有列，然后加入新的列。

1. 视图操作

• 创建视图
  CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT view_comment] [TBLPROPERTIES (property_name = property_value, ...)] AS SELECT ...;
  1. view_name 不能和已有的表或者视图重名。
  2. 不指定column_name 时会从SELECT中推断列名。
  3. 当视图所基于的底层表schema发生改变时，视图的schema不会改变。
  4. 视图是只读的，不可以使用INSERT/DELETE/ALTER.
• 删除视图
  DROP VIEW [IF EXISTS]db_name.view_name
• 修改视图
  ALTER VIEW [db_name.]view_name AS select_statement;
  其效果和CREATE VIEW一样，但是要求view_name必须存在。

1. 索引操作
   • 创建索引

持续更新&＃8230;