留个备份。
hive用户可以通过alter语句更改table属性
Alter Partitions
增加partitions:
- ALTER TABLE table_name
- ADD [IF NOT EXISTS]
- PARTITION partition_spec [LOCATION 'location1']
- partition_spec [LOCATION 'location2'] ...
- partition_spec:
- (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
- ALTER TABLE table_name DROP [IF EXISTS] partition_spec, partition_spec,...
- hive> create table alter_test(id INT, name STRING)
- > partitioned by(dt STRING)
- > row format delimited fields terminated by ',';
- OK
- Time taken: 0.259 seconds
- hive> create table alter_tmp(id INT, name STRING,dt STRING)
- > row format delimited fields terminated by ',';
- OK
- Time taken: 2.078 seconds
- hive> load data local inpath '/home/work/data/alter_test.txt' into table alter_tmp;
- Copying data from file:/home/work/data/alter_test.txt
- Copying file: file:/home/work/data/alter_test.txt
- Loading data to table default.alter_tmp
- OK
- Time taken: 2.71 seconds
- hive> set hive.exec.dynamic.partition.mode=nonstrict;
- hive> set hive.exec.dynamic.partition=true;
- hive> insert overwrite table alter_test partition(dt)
- > select id,name,dt
- > from alter_tmp;
- OK
- Time taken: 25.988 seconds
- $ cat alter_test2.txt
- 1,zxm,2012-08-13
- 2,ljz,2012-08-13
- $ hadoop fs -put alter_test2.txt /data/
- hive> alter table alter_test add partition(dt='2012-08-13') location '/data';
- OK
- Time taken: 8.717 seconds
- $ hadoop fs -ls /user/hive/warehouse/alter_test/
- Found 3 items
- drwxr-xr-x - work supergroup 0 2012-08-12 20:50 /user/hive/warehouse/alter_test/dt=2012-08-10
- drwxr-xr-x - work supergroup 0 2012-08-12 20:50 /user/hive/warehouse/alter_test/dt=2012-08-11
- drwxr-xr-x - work supergroup 0 2012-08-12 20:50 /user/hive/warehouse/alter_test/dt=2012-08-12
- hive> select * from alter_test where dt='2012-08-13';
- OK
- 1 zxm 2012-08-13
- 2 ljz 2012-08-13
- Time taken: 6.064 seconds
- $ hadoop fs -rmr /data
- hive> select * from alter_test where dt='2012-08-13';
- OK
- Time taken: 1.903 seconds
- hive> show partitions alter_test;
- OK
- dt=2012-08-10
- dt=2012-08-11
- dt=2012-08-12
- dt=2012-08-13
- Time taken: 0.546 seconds
- > alter table alter_test add partition(dt='2012-08-14') partition(dt='2012-08-15');
- OK
- Time taken: 0.57 seconds
- hive> alter table alter_test drop partition(dt='2012-08-10');
- Dropping the partition dt=2012-08-10
- OK
- Time taken: 4.509 seconds
- $ hadoop fs -ls /user/hive/warehouse/alter_test/
- Found 4 items
- drwxr-xr-x - work supergroup 0 2012-08-12 20:50 /user/hive/warehouse/alter_test/dt=2012-08-11
- drwxr-xr-x - work supergroup 0 2012-08-12 20:50 /user/hive/warehouse/alter_test/dt=2012-08-12
- drwxr-xr-x - work supergroup 0 2012-08-13 02:15 /user/hive/warehouse/alter_test/dt=2012-08-14
- drwxr-xr-x - work supergroup 0 2012-08-13 02:15 /user/hive/warehouse/alter_test/dt=2012-08-15
1. hive可以同时增加或者删除多个partition
2. 使用location关键字时,增加的partition以类似extend table数据的形式存在外部。
Alter Column
修改column属性(列名,列字段类型,列注释):
- ALTER TABLE table_name
- CHANGE [COLUMN] col_old_name col_new_name
- column_type [COMMENT col_comment] [FIRST|AFTER column_name]
- ALTER TABLE table_name
- ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
- hive> alter table alter_test add columns(test_columen INT);
- OK
- Time taken: 2.096 seconds
- hive> desc alter_test;
- OK
- id int
- name string
- test_columen int
- dt string
- Time taken: 0.345 seconds
- hive> select * from alter_test;
- OK
- 3 cds NULL 2012-08-11
- 4 mac NULL 2012-08-11
- 1 zxm NULL 2012-08-12
- 2 ljz NULL 2012-08-12
- 1 zxm NULL 2012-08-13
- 2 ljz NULL 2012-08-13
- Time taken: 8.467 seconds
- hive> alter table alter_test replace columns (id int, name string);
- OK
- Time taken: 0.217 seconds
- hive> desc alter_test;
- OK
- id int
- name string
- dt string
- Time taken: 0.181 seconds
- hive> select * from alter_test;
- OK
- 3 cds 2012-08-11
- 4 mac 2012-08-11
- 1 zxm 2012-08-12
- 2 ljz 2012-08-12
- 1 zxm 2012-08-13
- 2 ljz 2012-08-13
- Time taken: 0.364 seconds
- hive> alter table alter_test change id myid INT;
- OK
- Time taken: 0.259 seconds
- hive> desc alter_test;
- OK
- myid int
- name string
- dt string
- Time taken: 0.053 seconds
其它:
hive alter语句还支持:
1.Alter Table Properties
2.Alter SerDe Properties
3.Alter Table/Partition File Format
4.Alter Table Storage Properties
5.Alter Table/Partition Location
6.Alter Table Touch
7.Alter Table (Un)Archive
8.Alter Table/Partition Protections
9.Alter Table Rename Partition
reference:
Hive LanguageManualDDL