Hive 操作表 hive常用操作

#创建内部表（管理表）删除表hive也会删除这个表中的数据以及元数据

createtableif notexists hive.usr(

namestring comment 'username',

pwd string comment 'password',

address struct<street:string,city:string,state:string,zip:int>comment 'home address',

identify map<int,tinyint> comment 'number,sex')

comment 'description of the table'

tblproperties('creator'='me','time'='2016.1.1');

#创建外部表 删除表hive不会会删除这个表中的数据但是会删除元数据

createexternal tableif notexists hive.usr2(

namestring,

pwd string,

address struct<street:string,city:string,state:string,zip:int>,

identify map<int,tinyint>)

row format delimited fields terminated by','

location '/usr/hive/warehouse/hive.db/usr2';

#创建分区表

createtableif notexists usr3(

namestring,

pwd string,

address struct<street:string,city:string,state:string,zip:int>,

identify map<int,tinyint>)

partitioned by(city string,state string);

#复制usr表的表模式

hive (hive)> alter table user3 rename to usr3;

#查看表结构信息

hive (hive)> describe extended hive.usr2;

#查看表中存在的所有分区

hive (hive)> show partitions usr3;

#修改表名称

hive (hive)> alter table user3 rename to usr3;

#增加分区

altertableusr3 addif notexists

partition(city='beijing',state='China')

location '/user/hive/warehouse/hive.db/usr3 /China/beijing';

#修改分区路径

altertableusr3 partition(city='beijing',state='China')

setlocation '/usr/hive/hive/warehouse/usr3/CH/beijing';

#删除分区

altertableusr3 dropif exists partition(city='beijing',state='China')

#修改表的分区

altertableusr3 partition(city='beijing',state='China')

#修改列信息

altertableusr3 change columnpwd passwordstring aftername;

#增加列

altertableusr addcolumns(hobby string);

#删除所有列并替换新列

altertableusr replacecolumns(uname string);

#修改表描述信息

altertableusr settblproperties('creator'='liming');

#修改存储属性

setfileformat sequencefile;

#切换到hive数据库下

use hive;

#删除表

droptableif exists usr1;

#hive不允许用户删除一个包含有表的数据库，加上关键字cascade，这样可以使hive自行先删除数据库中的表

dropdatabaseif exists hive cascade;