hive 创建/删除/截断表(翻译自Hive wiki)

简单的创建表

create table table_name (
  id                int,
  dtDontQuery       string,
  name              string
)

创建有分区的表

create table table_name (
  id                int,
  dtDontQuery       string,
  name              string
)
partitioned by (date string)

一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。

分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。

在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。表中的一个 Partition 对应于表下的一个目录,Partition 就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。

典型的默认创建表

CREATE TABLE page_view(
     viewTime INT, 
     userid BIGINT,
     page_url STRING, 
     referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ' 01'
   COLLECTION ITEMS TERMINATED BY ' 02'
   MAP KEYS TERMINATED BY ' 03'
 STORED AS TEXTFILE;

这里创建了表page_view,有表的注释，一个字段ip的注释，分区有两列,分别是dt和country。

[ROW FORMAT DELIMITED]关键字，是用来设置创建的表在加载数据的时候，支持的列分隔符。不同列之间用一个' 01'分割,集合(例如array,map)的元素之间以' 02'隔开,map中key和value用' 03'分割。

[STORED AS file_format]关键字是用来设置加载数据的数据类型,默认是TEXTFILE，如果文件数据是纯文本，就是使用 [STORED AS TEXTFILE]，然后从本地直接拷贝到HDFS上，hive直接可以识别数据。

常用的创建表

CREATE TABLE login(
     userid BIGINT,
     ip STRING, 
     time BIGINT)
 PARTITIONED BY(dt STRING)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '	'
 STORED AS TEXTFILE;

创建外部表

如果数据已经存在HDFS的'/user/hadoop/warehouse/page_view'上了，如果想创建表，指向这个路径，就需要创建外部表:

CREATE EXTERNAL TABLE page_view(
     viewTime INT, 
     userid BIGINT,
     page_url STRING, 
     referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY ' 54'
 STORED AS TEXTFILE
 LOCATION '/user/hadoop/warehouse/page_view';

创建表，有指定EXTERNAL就是外部表，没有指定就是内部表，内部表在drop的时候会从HDFS上删除数据，而外部表不会删除。

外部表和内部表一样，都可以有分区，如果指定了分区，那外部表建了之后，还要修改表添加分区。

外部表如果有分区，还可以加载数据，覆盖分区数据，但是外部表删除分区，对应分区的数据不会从HDFS上删除，而内部表会删除分区数据。

指定数据库创建表

如果不指定数据库，hive会把表创建在default数据库下，假设有一个hive的数据库mydb,要创建表到mydb,如下:

CREATE TABLE mydb.pokes(foo INT,bar STRING);

或者是

use mydb; --把当前数据库指向mydb
CREATE TABLE pokes(foo INT,bar STRING);

复制表结构

CREATE TABLE empty_table_name LIKE table_name;

根据table_name创建一个空表empty_table_name,empty_table_name没有任何数据。

create-table-as-selectt (CTAS)

CTAS创建的表是原子性的，这意味着，该表直到所有的查询结果完成后，其他用户才可以看到完整的查询结果表。

CTAS唯一的限制是目标表，不能是一个有分区的表，也不能是外部表。

简单的方式

CREATE TABLE new_key_value_store
  AS 
SELECT (key % 1024) new_key, concat(key, value) key_value_pair FROM key_value_store;

复杂的方式

CREATE TABLE new_key_value_store
   ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
   STORED AS RCFile AS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT BY new_key, key_value_pair;

删除表

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

删除表会移除表的元数据和数据，而HDFS上的数据，如果配置了Trash，会移到.Trash/Current目录下。

删除外部表时，表中的数据不会被删除。

截断表

TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name PARTITION (dt='20080808');

从表或者表分区删除所有行，不指定分区，将截断表中的所有分区，也可以一次指定多个分区，截断多个分区。

hive 创建/删除/截断 表(翻译自Hive wiki)