hive表的创建:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
建表语句解析:
1)EXTERNAL 创建外部表需要指定的关键字,不指定则默认创建的是内部表
2)if not exits 建表的时候指定,避免报错
if exits 删除表的时候指定,避免报错
3)comment 添加描述信息
4)PARTITIONED BY:指定分区字段(分区字段一定不能在表的原始建表字段中)
5)[CLUSTERED BY(col_name,......) 指定分桶字段,分桶字段一定在建表字段中的某一个或几个[SORTED BY (col_name [ASC|DESC], ....)]
每一个桶中的排序规则 INTO num_buckets BUCKETS]指定分桶的个数
6)ROW FORMAT row_format 指定分割符
7)STORED AS file_format 指定hive表中的原始数据的存储格式
8)LOCATION hdfs_path 指定hive表中的数据的存储目录
建表语句实例:
1)创建一个内部表student_managed
create table if not exists student_managed(id int,name string,sex string,age int,department string) row
format delimited fields terminated by ',' location '/user/mytable/stu';
2)创建一个外部表student_external
create external table student_external(id int,name string,sex string,age int,department string)
row format delimited fields terminated by ',';
3)创建一个分区表
分区字段department
外部分区表student_ptn:
create external table if not exists student_ptn(id int,name string,sex string,age int) partitioned
by(department string) row format delimited fields terminated by ',';
4)创建一个分桶表-- 按照age asc,指定分桶字段:id
create external table if not exists student_buk(id int,name string,sex string,age int,department string)
clustered by (id) sorted by (age asc) into 3 buckets row format delimited fields terminated by ',';
5)进行表复制
create table if not exists student_copy like student_external;
6)ctas进行建表
create table ..... as select .... from .....where .....
表的修改操作:
修改表名:
alter table student_copy rename to stu_copy;
修改表的列信息:
1)增加列
alter table stu_copy add columns (salary double);
2)替换列
将所有的列替换为新给的列
alter table stu_copy replace columns(test01 int,test02 string);
3)修改列
修改列名
alter table stu_copy change test01 aa01 int;
修改列的类型
alter table stu_copy change aa01 aa01 string;
alter table stu_copy change aa01 aa01 int;
删除列:不支持
4)修改表的分区信息:针对分区表的
1)增加/添加表的分区
add partition(分区信息)
alter table student_ptn add partition(department="CS");
一次性添加多个分区
alter table student_ptn add partition(department="CS") partition(department="") partition(department="");
2)修改分区
1)添加分区的时候使用location关键字进行指定
alter table student_ptn add partition(department="test") location '/user/hive/hivehome/test_ptn';
2)针对已经添加的分区 可以手动修改存储目录
alter table student_ptn partition(department="test") set location '/user/hive/hivehome/test_ptn';
3)删除分区 ***
alter table student_ptn drop partition(department="test");
查看表列表信息
show tables;
show tables in db;
show tables like '';
show partitions table_name;
查看表的建表语句:
show create table tablename;
最全的建表语句:
CREATE EXTERNAL TABLE `student_ptn`(
`id` int,
`name` string,
`sex` string,
`age` int)
PARTITIONED BY (
`department` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim'=',',
'serialization.format'=',')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://hadoop01:9000/user/hive/hivehome/bd1807.db/student_ptn'
TBLPROPERTIES (
'transient_lastDdlTime'='1539840706')
查看表的信息:
desc tablename;
desc formatted tablename;
删除表:
drop table tablename;
清空表:
truncate table table_name;
查看表分区:
show partitions student_ptn partition(department="IS");