Hive（一） 创建数据库 创建表

1.创建/删除/修改 数据库

创建数据库

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

LOCATION 指定数据存储在HDFS的路径
WITH DBPROPERTIES 可设置KV格式的数据库属性，KEY可以自定义

CREATE DATABASE IF NOT EXISTS TEST 
    COMMENT 'THIS IS TEST DATABASE'
    LOCATION '/USER/ROOT/' 
    WITH DBPROPERTIES('CREATOR'='XXX','DATE'='2016-11-12');

删除数据库

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

默认情况下Hive不允许删除表里有数据的数据库，使用CASCADE会在删除数据库时删除该库下的所有对象，RESTRICT既为默认情况设置

修改数据库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; 

2.创建/删除/截断表

创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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]
  [SKEWED BY (col_name, col_name, ...)
     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 (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement];

设置行、字段分隔符

行与字段分隔符可让Hive在从文件加载数据时明确数据是怎么分隔的，具体设置方法如下：

设置字段分隔符
ROW FORMAT DELIMITED  FIELDS TERMINATED BY char

Hive 默认使用'\001'，'\001'代表控制符，用ctrl+v然后再ctrl+a可以输入这个控制符；分隔符可用八进制ASCII码表示，或者直接写字符，不过特殊字符需要转义，如空格'\t'，换行'\n'。

设置转义符
ROW FORMAT DELIMITED  FIELDS ESCAPED BY  char

如果字段中有些符号需要保留，可以用ESCAPED BY设置转义符。

设置行分隔符
LINES TERMINATED BY char

一般设置为 '\n'

内部表

表的元数据和数据都由Hive维护，创建表时会自动在HDFS的/user/hive/warehouse 目录下创建与表同名的文件夹，删除表时HDFS上的数据和文件夹会删除

CREATE TABLE IF NOT EXISTS person 
    (id int COMMENT '编号',name string COMMENT '姓名') 
    COMMENT '人员表' 
    STORED AS TEXTFILE;

外部表（EXTERNAL）

删除表时只删除元数据，HDFS上的数据和文件夹保留，创建表时如果指定了HDFS的路径，则数据文件会直接存在指定路径下；如果创建表时不指定HDFS路径，会默认在/user/hive/warehouse目录创建表文件夹。

CREATE EXTERNAL TABLE IF NOT EXISTS person 
    (id int COMMENT '编号',name string COMMENT '姓名') 
    COMMENT '人员表' 
    STORED AS TEXTFILE 
    LOCATION '/user/hdfs/';

临时表（TEMPORARY）

临时表的数据和元数据都由Hive维护，只对当前Session有效,Session退出时表自动删除;

CREATE TEMPORARY TABLE IF NOT EXISTS person_tmp 
    (id int COMMENT '编号',name string COMMENT '姓名') 
    COMMENT '人员临时表' 
    STORED AS TEXTFILE 
    LOCATION '/user/hdfs/';

Create Table As Select (CTAS)

Create Table 部分使用Select的查询结果创建目标表，且可以指定SerDe 和存储格式，不过也存在以下限制：
1）要创建的表不能是分区表；
2）要创建的表不能是外部表；
3）要创建的表不能是List Bucketing表。

CREATE TABLE new_person 
    ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"  
    STORED AS RCFile  
    AS 
select id,name from person;

Create Table Like

Create Table table1 Like table2 会创建与table2 结构完全相同的table1，通常用于复制表结构但不复制数据。

创建表的参数很多，有些涉及到存储格式、压缩、表的属性等内容，后面的文章会逐一说明。

删除表

DROP TABLE [IF EXISTS] table_name [PURGE];

如果被删除的表有视图引用，在删除时不会警告，需要手动检查或重建视图
如果指定了PURGE,删除表时数据会从HDFS上完全清除，而不会转入回收站

截断表

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

截断表会清空表里的数据，也可以指定清空特定分区