hive操作DDL

作为参考即可。

在cmd下使用
$hive -f 指定hive脚本文件
$hive -e 'select * from test_change' 在cmd里执行hive语句
在hive里面
hive>source file_name 执行hive脚本文件

-- 删除表
drop table if exists test_change;
-- 创建普通表
drop table if exists page_view;
create table page_view(
 viewTime int,
 userid bigint,
 page_url string,
 refer_url string,
 ip string comment 'ip address'
)
comment 'this is the page view table';
-- 创建分区表（需要指定分区字段 虚拟列）
drop table if exists page_view;
create table page_view(
 viewTime int,
 userid bigint,
 page_url string,
 refer_url string,
 ip string comment 'ip address'
)
comment 'this is the page view table'
partitioned by (dt string,country string)
row format delimited
fields terminated by '\001'
stored by sequencefile;

-- 增加分区
alter table page_view add partition(dt='2014-2-1',country='China');

如果是外部表，可指定外部分区数据位置
alter table page_view add partition(dt='2014-2-1',country='China') location '此分区数据所在的文件路径hdfs上的'
-- 删除分区
alter table page_view drop partition(dt='2014-2-1',country='China');

-- 增加聚类存储 桶表
drop table if exists page_view;
create table page_view(
 viewTime int,
 userid bigint,
 page_url string,
 refer_url string,
 ip string comment 'ip address'
)
comment 'this is the page view table'
partitioned by (dt string,country string)
clustered by (userid) sorted by (viewTime) into 32 buckets
row format delimited
fields terminated by '\001'
collections items terminated by '\002'
map keys terminated by '\003'
stored by sequencefile;
-- 创建外部表
drop table if exists page_view;
create external table page_view(
 viewTime int,
 userid bigint,
 page_url string,
 refer_url string,
 ip string comment 'ip address'
)
comment 'this is the page view table'
row format delimited
fields terminated by '\t'
collections items terminated by '\002'
map keys terminated by '\003'
stored by textfile
location 'hdfs_location';
-- 表 重命名
alter table page_view rename to page_view;
-- 改变列的名字，类型，位置 注释
alter table table_name change
 column_old_name column_new_name column_new_type comment 'comment' first | after column_name

--示例
create table test_change(a int,b int,c int);
alter table test_change change a a1 string comment 'comment';
alter table test_change change a1 a string comment 'comment' after b;
alter table test_change change b b1 string comment 'comment' first;

-- 增加列
alter table test_change add columns(d string comment 'test add');
-- 增加或修改表属性
alter table test_change set tblproperties ('test1'='prop1','test2'='prop2');
-- 查看表
describe test_change;
-- 查看表的详细信息
describe extended test_change;
-- 增加serde属性(修改serde类)
alter table test_change set serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' with serdeproperties('name1'='prop1');

-- 创建视图(只读的，不能用于load,insert,alter),可以包含order by 和limit，hive会对其优化
create view if not exists v_t1(c1 comment 'c1',c2 comment 'c2')
comment 'comment view'
as
select c1,c2 from table_name;

create view v_refer(url comment 'url of refer page')
comment 'refer to the onion website'
as
select distinct refer_url from page_view where page_url = 'http://www.baidu.com';

-- 删除view
drop view v_refer;
-- 创建和删除函数(将class增加classpath)
create temporary function function_name as class_name;
-- 增加文件到类路径
add files '路径';
-- drop temporary function function_name;
-- 显示表
show tables 'page*'
正则：'page_view','page_v*','*view|page*'
-- 显示分区
show partitions table_name
-- 显示表或分区扩展
show table extended [IN|FROM database_name] like '正则'
show table extended like 'page*';
show table extended in default like 'page*';

-- 显示函数
show functions 'sum*';
-- 描述表和列
describe extended page_view;
describe extended page_view.ip;
-- 描述表的分区
describe page_view country

-- 创建电影评分表
create table u1_data(
 userid int,
 movieid int,
 rating int,
 city string,
 viewTime string
)
row format delimited
fields terminated by '\t'
stored as textfile;
-- 带分区的，分区名不能与表的字段名重名
create table u2_data(
 userid int,
 movieid int,
 rating int,
 city string,
 viewTime string
)
partitioned by (dt string)
row format delimited
fields terminated by '\t'
stored as textfile;

alter table u2_data add partition(dt='20140301');使用带-提示报错将-去掉2013-2-1
-- 两个分区
create table u3_data(
 userid int,
 movieid int,
 rating int,
 viewTime string
)
partitioned by (dt string,city string)
row format delimited
fields terminated by '\t'
stored as textfile;

alter table u3_data add partition(dt='20140606',city='jinan');

drop table t1;