Hive基本操作

查看分区
describe formatted tableName partition(date_id="2019-01-07");
 
查看table在hdfs上的存储路径及建表语句
show create table  tableName ;
 
 
操作分区和表语句
 
alter table tableName add IF NOT EXISTS partition(date_id="$year-$mon-$day") location "${dest}";
 
删除分区
ALTER TABLEtableName  DROP IF EXISTS PARTITION (date_id='2019-01-07');
 
新增分区
ALTER TABLE tableName  ADD PARTITION (date_id = '2019-01-07') location 'hdfs://wecloud-cluster/project/logcenter/dw_logs/2019-01-07';
 
添加分区
ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1'; //示例
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (date_id='2019-01-07') LOCATION '/user/hadoop/warehouse/table_name/date_id=20190107'; //一次添加一个分区
ALTER TABLE page_view ADD PARTITION (date_id='2019-01-07', country='us') location '/path/to/us/part1' PARTITION (date_id='2019-01-07', country='us') location '/path/to/us/part2';  //一次添加多个分区
 
删除分区
ALTER TABLE tableName DROP IF EXISTS PARTITION (date_id='2019-01-07');
ALTER TABLE tableName DROP IF EXISTS PARTITION (date_id='2019-01-07', country='us');
 
修改分区
ALTER TABLE tableName PARTITION (date_id='2019-01-07') SET LOCATION "new location";
ALTER TABLE tableName PARTITION (date_id='2019-01-07') RENAME TO PARTITION (date_id='20190107');
 
添加列
ALTER TABLE tableName ADD COLUMNS (col_name STRING);  //在所有存在的列后面，但是在分区列之前添加一列

ALTER TABLE tableName ADD COLUMNS(other STRING COMMENT '这里是列注释!');
 
修改列
CREATE TABLE tableName (a int, b int, c int);
// will change column a's name to a1
ALTER TABLE tableName CHANGE a a1 INT;
// will change column a's name to a1, a's data type to string, and put it after column b. The new table's structure is: b int, a1 string, c int
ALTER TABLE tableName CHANGE a a1 STRING AFTER b;
// will change column b's name to b1, and put it as the first column. The new table's structure is: b1 int, a string, c int
ALTER TABLE tableName CHANGE b b1 INT FIRST;
 
修改表属性
alter table tableName set TBLPROPERTIES ('EXTERNAL'='TRUE');  //内部表转外部表
alter table tableName set TBLPROPERTIES ('EXTERNAL'='FALSE');  //外部表转内部表
 
表的重命名
ALTER TABLE tableName RENAME TO newTableName 
 
增加字段
ALTER TABLE tableName ADD COLUMNS(other STRING);
--将 a 列的名字改为 a1.
ALTER TABLE tableName CHANGE a a1 INT;
 
--将 a 列的名字改为 a1，a 列的数据类型改为 string，并将它放置在列 b 之后。新的表结构为： b int, a1 string, c int.
ALTER TABLE tableName CHANGE a a1 STRING AFTER b;
 
--将 b 列的名字修改为 b1, 并将它放在第一列。新表的结构为： b1 int, a string, c int.
ALTER TABLE tableName CHANGE b b1 INT FIRST;
 
 
修改表注释:
ALTER TABLE tableName SET TBLPROPERTIES('comment' = '这是表注释!');
 
修改字段注释:
ALTER TABLE tableName CHANGE COLUMN column newColumn STRING COMMENT '这里是列注释!'; 
 
剔除不可见符号
regexp_replace("内容",'<[^>^<]*>|&amp|nbsp|rdquo|&|&quot|\n|\r|[\\000-\\037]','')