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 time desc) as rn
from dm.table_info_01
) a
where a.rn = 1;

6、concat(a,b,c...)