hive的表修改
alter table修改表的元数据:
1)修改表名称:
hive> alter table student rename to student1;
2)修改表分区
hive> alter table hive.logs add partition (dt=‘2019-08-20’,country=‘changchun’);
hive> alter table hive.logs drop partition (dt=‘2019-08-’,country=‘changchun’);
3)添加列
hive> alter table hive.logs add columns (id int,name string);
4)删除和替换列
hive> alter table hive.logs drop columns (id int,name string);
hive> alter table hive.logs replace columns (id int,name string);
hive向管理表装载数据
- 装载数据:
hive>load data [local] inpath '/data/hive/' [overwrite] into table tbname
说明:含local关键字:加载本地目录数据到warehouse下;如果不含local关键字,加载数据目录默认为hdfs路径
含overwrite关键字:覆写数据至表中,将原来的表覆盖了 - 通过查询语句向表中插入数据:在原始数据的基础上,进行业务分析后生成的表数据
hive>insert overwrite table new_table select [column字段] from 原始数据表 where 条件;
或者
hive> from 原始数据表 别名 insert overwrite table new_table select [column字段] where 条件;
【后面可以跟多条 insert 语句,用于不同条件】 - 创建表时,也可以执行插入数据
hive>create table new_table as select id,name,age from student where age>23; - 导出数据到指定目录:
hive>insert overwrite directory ‘/data/stocks/2009-06-23’ select * from stocks where ymd=‘2009-06-23’;
hive查询(几个例子)
- 对array查询
hive> select name, subordinates[1] from hive.employees where name = ‘John Doe’; - 对map的查询
hive> select name,deductions[‘State Taxes’] from hive.employees where name = ‘John Doe’; - 对struct查询
hive> select name,address.state
from hive.employees where name = ‘John Doe’; - limit
hive> select upper(name),salary,deductions[‘Federal Taxes’],round(salary*(1-deductions[‘Federal Taxes’])) from hive.employees limit 6; - 列别名
hive> select upper(name),salary,deductions[‘Federal Taxes’] as Taxes,round(salary*(1-deductions[‘Federal Taxes’])) as sal from hive.employees limit 6; - 嵌套
hive> from (select upper(name) as name,salary,deductions[‘Federal Taxes’] as Taxes,round(salary*(1-deductions[‘Federal Taxes’])) as sal from hive.employees) e select e.name,e.Taxes,e.sal where e.sal >70000;