hive分区对应hadoop_Hadoop之hive中sql常用函数汇总

1、hive执行引擎 mr/tez/spark

set hive.execution.engine = mr;

2、开启动态分区

set hive.exec.dynamic.partition = true;

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

## 删除分区：

ALTER TABLE dm.user_action_self_help_w_wi DROP IF EXISTS PARTITION (dt='2019-08-15',pd=2);

3、with 连接词

with TABLE_NAME AS (

SELECT ... FROM ... WHERE ...

)

-- 首个连接需要with，后续不要with：

TABLE_NAME AS (

SELECT ... FROM ... WHERE ...

)

4、为字段重命名

old_name as new_name

-- 或(不加as)：

old_name new_name

5、row_number()  over(partition by A order by B asc/desc)

row_number() over(partition by A,B,C order by D asc/desc)

-- 将查询结果按照A,B,C字段分组(partition)，

-- 然后组内按照D字段排序，至于asc还是desc，可自行选择，

-- 然后为每行记录返回一个row_number用于标记顺序(编号)

特色功能1：给 已有hive表(dm.official_accounts_funscount_w) 添加一列序号(sample_key)，例：

select

row_number() over(

partition by case when t.source is not null then 1 end

order by t.source asc,t.funCounts desc

) as sample_key,

t.source,

t.cityName,

t.weight,

t.strArea,

t.end_date,

t.funCounts

from dm.official_accounts_funscount_w t;

特色功能2：给表(多个字段)中某个字段去重，例：

-- 临时表2：去重数据

drop table if exists dm.table_info__02;

create table dm.table_info_02 stored as parquet as

select

*

from

(

select

*,

row_number() over(partition by id order by ti