Hive表的修改Alter
1.查看创建表的信息 【show create table】
hive> show create table student;
OK
createtab_stmt
CREATE TABLE `student`(
`age` int,
`name` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://hadoop01:9000/user/hive/warehouse/test.db/student'
TBLPROPERTIES (
'numFiles'='0',
'last_modified_by'='root',
'last_modified_time'='1439964845',
'COLUMN_STATS_ACCURATE'='false',
'transient_lastDdlTime'='1439964845',
'numRows'='-1',
'totalSize'='0',
'rawDataSize'='-1')
Time taken: 0.059 seconds, Fetched: 20 row(s)
2.查看表的字段信息 【desc】
hive> desc student;
OK
col_name data_type comment
age int
name string
Time taken: 0.08 seconds, Fetched: 2 row(s)
3.查看表的详细属性信息 【desc formatted】
hive> desc formatted student;
OK
col_name data_type comment
# col_name data_type comment
age int
name string
# Detailed Table Information
Database: test
Owner: root
CreateTime: Wed Aug 19 14:13:07 SGT 2015
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://hadoop01:9000/user/hive/warehouse/test.db/student
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE false
last_modified_by root
last_modified_time 1439964845
numFiles 0
numRows -1
rawDataSize -1
totalSize 0
transient_lastDdlTime 1439964845
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim \t
serialization.format \t
Time taken: 0.084 seconds, Fetched: 35 row(s)
4.重命名表 【Rename To】
- 对于内部表，除了更新表的元数据之外，还对表的目录名称进行修改。
- 对于外部表，这个操作只更新元数据，但不会更改存放数据的目录名称。
hive> alter table student rename to students;
OK
Time taken: 0.107 seconds
5.添加新列 【Add Column】
hive> desc students; --修改前的列名及对应类型
OK
col_name data_type comment
age int
name string
Time taken: 0.077 seconds, Fetched: 2 row(s)
hive> alter table students
> add columns(id int); --增加id列，类型int
OK
Time taken: 0.173 seconds
hive> desc students; --修改后的列名及对应类型
OK
col_name data_type comment
age int
name string
id int
Time taken: 0.127 seconds, Fetched: 3 row(s)
6.修改列名及对应类型 【Change Column】
hive> desc t081901;
OK
col_name data_type comment
te int --旧的列名和数据类型
name string
date string
country string
Time taken: 0.091 seconds, Fetched: 4 row(s)
hive> alter table t081901
> change column te --要进行修改的列名
> temperature int; --修改后的列名和数据类型
OK
Time taken: 0.164 seconds
hive> desc t081901;
OK
col_name data_type comment
temperature int --查看修改后的效果
name string
date string
country string
Time taken: 0.101 seconds, Fetched: 4 row(s)
7.替换【重置】表的列名和类型 【Replace Column】
这个操作其实是将原有的列删除，然后再添加新的指定的列。
hive> create table student(
> name int, --旧的列名及对应的数据类型
> age string)
> row format delimited
> fields terminated by '\t';
OK
Time taken: 0.103 seconds
hive> alter table student
> replace columns( --replace columns替换数据列
> age int,
> name string);
OK
Time taken: 0.121 seconds
hive> desc student;
OK
col_name data_type comment
age int --替换后的列名及对应的数据类型
name string
Time taken: 0.105 seconds, Fetched: 2 row(s)
8.创建一个模式一样的新表 【Like】
CREATE TABLE new_table LIKE existing_table;
8.1.清空表【delete和truncate】
- Hive中不支持delete table T_Name操作。
- Hive中支持 truncate table T_Name操作。对于分区表，它是将各个分区下面的数据文件删除，但是分区的目录还存在。相当于执行了以下命令：hive > dfs -rmr /user/hive/warehouse/my_table;
9.增加分区【Add Partition】
hive> alter table logs add
> partition(date='2015-01-03',country='USA') --分区
> location '/user/hive/warehouse/logs/date=2015-01-03/country=USA' --分区路径
hive> alter table logs add
> partition(date='2015-01-03',country='USA'); --新增分区，不指定路径
OK
Time taken: 0.261 seconds
10.删除分区【Drop Partition】
hive> alter table logs drop partition(date='2015-01-03',country='USA');
Dropped the partition date=2015-01-03/country=USA
OK
Time taken: 1.344 seconds
hive> alter table logs DROP partition(date='2015-01-03'); --直接删除一级分区
Dropped the partition date=2015-01-03/country=USA
OK
Time taken: 0.464 seconds
11.创建视图【Create View】
hive> CREATE VIEW stu_view AS select stu.name,stu.age from test.stu; --创建视图
hive> show tables; --查看视图(show views命令) OK stu stu_view hive> select * from stu_view; OK Tie 18 Coat 19 Hat 21 Scarf 37 hive> drop view stu_view; --删除视图(不能使用命令drop table) OK Time taken: 1.366 seconds hive> show tables; OK stu
12.设置NULL值的替代字符
create table test (id string) ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ( 'field.delim'='\t', 'serialization.null.format'=' ' ) STORED AS TEXTFILE; OR 或者 alter table test SET SERDEPROPERTIES('serialization.null.format' = '');
