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

这里罗列常用操作，更多参考 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create%2FDrop%2FTruncateTable

 

简单的创建表

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 '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS TEXTFILE;

 

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

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

 

[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 '\t'
 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 '\054'
 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');

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

转载自 http://www.yanbit.com/?p=394

Hive创建表和分区

2013年10月17日 YanBit

CREATE EXTERNAL TABLE IF NOT EXISTS data_zh(

ROWKEY STRING,

STATION INT,

YEAR INT,

MONTH INT,

DAY INT,

HOUR INT,

MINUTE INT,

)

PARTITIONED BY (AGE INT) 指定分区(此列并没真正存储列，也就是不存于你的数据中。但是如果你的数据从Oracle按年份导出，按照年份分区，把每一年数据放到age对应的目录下)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’ 字段分隔符

LINES TERMINATED BY ‘\n’ 行分隔符

STORED AS TEXTFILE; 作为文本存储

Hive加载数据到表中

加载数据

LOAD DATA INPATH ‘/data/’ OVERWRITE INTO TABLE data_zh; #加载某个目录下所有数据，存在分区数据不能这样加载

分区加载数据

创建分区

ALTER TABLE data_zhp ADD PARTITION(AGE= 1998)

加载数据

LOAD DATA INPATH ‘/data/1998.txt’ INTO TABLE data_zhp PARTITION(YEAR=1998);

指定分区目录

LOCATION ‘ /hiveuser/hive/warehouse/data_zh.db/data_zh/2012.txt’;(指定分区所在位置)

加载数据到指定分区，分区表加载方法

下面为将现有表，修改为分区表。注意创建分区的时候未指定分区表，不能创建分区。需要新建表哥，用动态分区导入数据，动态分区数量有限。参考Programming Hive记得最大上限应该是10000

Hive修改现有表添加分区

添加分区

ALTER TABLE data_zh ADD IF NOT EXISTS

PARTITION (year = 1998) LOCATION ‘/hiveuser/hive/warehouse/data_zh.db/data_zh/1998.txt’

删除分区

ALTER TABLE data_zhp DROP IF EXISTS PARTITION(year =1998);

修改现有表添加分区

创建分区表

CREATE EXTERNAL TABLE IF NOT EXISTS data_zhp(

ROWKEY STRING,

STATION INT,

MONTH INT,

DAY INT,

HOUR INT,

MINUTE INT,

)

PARTITIONED BY (YEAR INT)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

LINES TERMINATED BY ‘\n’

STORED AS TEXTFILE;

动态分区指令

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

#set hive.enforce.bucketing = true;

未分区表数据导入分区表

insert overwrite table data_zhp partition (YEAR,MONTH) select * from data_zh;