Hive常用の方法一

CREATE TABLE asmp.tm_partbizkind(
repair_type_code string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
LOCATION '/user/asmp/hive/asmp/tm_partbizkind'

Create 建表

-- 建内部表
create table if not exists asmp.student(
id string comment '编号 ',
name string comment '名字'  
) comment '内部表'
partitioned by (date string comment  '日期 ' );

-- 建外部表
create external table if not exists asmp.student(
id string comment '编号 ',
name string comment '名字'  
) comment '外部表'
partitioned by (date string comment  '日期 ' )
STORED AS parquet
LOCATION '/user/asmp/hive/asmp/student';

-- 复制表结构+数据
create table if not exists new_table as select * from  old_table;
-- 复制其它表结构
create table if not exists test_copy like old_table;
create table if not exists test_copy as select * from old_table where 1=2;

Alter 更改表结构

-- 添加分区
alter table student add if not exists partition (date='20190802');
-- 删除分区
alter table student DROP IF EXISTS PARTITION (date='20190802');
-- 重命名分区
alter table table_name PARTITION (date='20190802') RENAME TO PARTITION (date='20190804'); 
-- 删除列
ALTER TABLE student DROP COLUMN id;
-- 增加列
Alter table student add COLUMNS (id_level string comment 'ID');
-- 修改列 (&字段注释)
Alter table student CHANGE id_level string comment '层级';
-- 替换列
Alter table table_name REPLACE COLUMNS (id_new string COMMENT '新字段1', level_new string COMMENT '新字段2');
-- 重命名表名
Alter table old_table RENAME TO new_table;

Insert 插入

-- 单次插入数据
 insert into student values(201705,'lol');
-- 批量插入分区表
insert overwrite table student PARTITION (date='20190802')
select * from table_s where date >= '20190802'; 
-- LOAD载入分区表 
LOAD DATA LOCAL INPATH 'total.txt' overwrite into table student PARTITION (date='20190802');

Other 其它

-- 列举库或表
SHOW DATABASES/TABLES --LIKE 'keyword';
-- 列举所有函数
SHOW FUNCTIONS;
-- 查看分区
SHOW PARTITIONS student;
-- 修复分区
msck repair table student;
-- 查看建表语句
SHOW CREATE TABLE student;
-- 详细描述（建表时间）
DESC FORMATTED student;
-- 解释语句
EXPLAIN select * from student;
-- 清空表
truncate table student;
-- 查看当前数据库
select current_database();