外置表

添加分区

alter table orig_report_voice add PARTITION(`date`='20180929') location '/warehouse/original/report_voice/date=20180929';

移除分区

ALTER TABLE orig_report_voice DROP IF EXISTS PARTITION(`date`='20180929');
ALTER TABLE orig_report_voice DROP IF EXISTS PARTITION(`date`>='20180929', `date`<'20180930');

查看分区

show partitions orig_report_voice;

内部表和外部表转换

-- 内部表转外部表
alter table tableA set TBLPROPERTIES('EXTERNAL'='true');
-- 外部表转内部表
alter table tableA set TBLPROPERTIES('EXTERNAL'='false');

创建表

-- 直接建表法
create table t_page_view (
  page_id bigint comment '页面ID',
  page_name string comment '页面名称',
  page_url string comment '页面URL'  
)
comment '页面视图'
partitioned by (ds string comment '当前时间，用于分区字段')
stored as parquet
location '/user/hive/warehouse/t_page_view';

-- 查询建表法
create table t_page_view2  as select * from t_page_view;

-- like建表法(克隆表)
create table t_page_view3 like t_page_view;

Alter

-- 修改表明
ALTER TABLE page_view RENAME to page_view_new;

-- 修改字段
ALTER TABLE page_view CHANGE ip ip_address string AFTER refererurl;

-- 添加字段
ALTER TABLE page_view ADD COLUMNS (name string comment 'view name');

-- 修改location
ALTER TABLE page_view PARTITION(dt='20190706') SET LOCATION "/data/page_view/dt=20190706";

-- 修改分隔符
ALTER TABLE page_view SET SERDEPROPERTIES ('field.delim' = ',');

-- 修复分区
msck repair table page_view;

数据导入导出

-- 将本地文件导入到hive
load data local inpath '/home/hadoop/student' overwrite into table student partition(state='Sichuan', city='Chengdu');

-- 将hdfs上文件导入到hive
load data inpath '/user/hadoop/add.txt' into table student partition(state='Sichuan', city='Chengdu');

-- 从别的表中查询出相应的数据并导入到hive表中
insert into table test partition(age='25') select id ,name from wyp where age='25';

-- 在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中
create table tmp as select * from student where age>'18';

-- 导出到本地文件系统
insert overwrite local directory '/home/hadoop/student' select * from student;

-- 导出到hdfs
insert overwrite directory '/user/hadoop/student' select * from student;

-- 将查询结果插入到表中（追加）
insert into table student_new select id,name from student where age='25';

-- 将查询结果插入到表中（覆盖）
insert into table student_new select id,name from student where age='25';