HiveQL 常见用法说明

配置

动态分区配置

SET hive.exec.dynamic.partition.mode=nonstrict;

建 parquet 格式分区表

create table user_db.test_table (
    user_id string COMMENT '用户id', 
    adress string COMMENT '地址'
)  partitioned by (date string)
row format delimited
stored as parquet 
location '/user/hadoop/hive/user_db/test_table';

本周周一

select date_sub('2020-03-15',cast(date_format('2020-03-15','u') as int)-1);

内表改外表

alter table dws.dws_test  SET TBLPROPERTIES ('EXTERNAL'='TRUE');

外表改内表

alter table dws.dws_test  SET TBLPROPERTIES ('EXTERNAL'='FALSE');

改location

alter table dws.dws_test set location 'xxxxxx';

添加空分区

alter table dws.dws_test add partition(ds = '2019-02-12');

删除分区

alter table dws.dws_test drop partition(ds = '2019-02-12');

修复分区

msck repair table dws.dws_test;

字段加注释

ALTER TABLE table_name CHANGE COLUMN muid muid_new STRING COMMENT '这里是列注释!';

表加注释

ALTER TABLE table_name SET TBLPROPERTIES('comment' = '这是表注释!');

改表生命周期

alter table table_name set lifecycle 100;