hive> load data local inpath '/home/test/people.bcp' into table people;
...
hive> select * from people;
OK
ZHAO beijing 30
QIAN nanjing 26
SUN nanjing 28
LI shanghai NULL //!!!注意NULL!!!
ZHOU shanghai 29
people表在HDFS上存储的数据:
hive> dfs -cat /hive/people/people.bcp;
ZHAO beijing 30
QIAN nanjing 26
SUN nanjing 28
LI shanghai age
ZHOU shanghai 29
hive> insert into table njpeople
> select * from people where city='nanjing';
数据导出
hive> insert overwrite local directory '/home/test/people' row format delimited fields terminated by '\t'select * from people;
LOCAL[可选]:local表示数据导出到本地磁盘,否则数据导出到HDFS
OVERWRITE[可选]:目标文件夹中之前存在的数据会被删除。
删除表
hive> drop table tablename; //直接丢弃
hive> drop table if exists tablename; //带有if的判断语句
修改表
表重命名:
altertable tablename01 rename to tablename02;
增加列:
altertable tablename01 add columns(
field1 string comment 'add field string',
field2 int comment 'add field int'
);
删除/替换列:
altertable people replace columns(
firstname string,
location string,
nianling int
);
因为新的字段和原来的字段数相同,因此上述操作实际上只是重定义了字段的名称。如果是”alter table people replace columns(firstname string);”,那么会重命名name字段的元数据信息,并且删除city和age属性。