hive sql基本语法及注意事项

1、 基本语法

sql 原理参考：从执行原理看调优的本质 - https://www.cnblogs.com/arthinking/p/13205303.html

1.1 建表语句

create table IF NOT EXISTS tmp.table_name
(
uid string comment 'uid'
,salary double comment '薪资'

)
partitioned by (pt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

1.2 增加comment

alter table tmp.table_name change column 
age age double comment '年龄'

1.3 更改表名

alter table tmp.table_name rename to tmp.table_name1

1.4 删除分区

alter table tmp.table_name drop partition (pt='****')

1.5 查看 create table 的具体信息

show create table  tmp.table_name1

1.6 建表降低表的存储空间

create table IF NOT EXISTS tmp.table_name1
(
uid string comment 'uid'
,salary double comment '薪资'
)
partitioned by (pt string)
row format delimited fields terminated by '\001' lines terminated by '\n'
stored as orc
tblproperties('orc.compress'='ZLIB');

-- 覆盖增加数据
insert overwrite table tmp.table_name1 partition (pt='${dt}') 
select * from tmp.table_name2

1.7 删除分区表

-- 删除分区表
 alter table tmp.table_name1 drop partition (pt='*****');

1.8 调度平台引入包

-- 调度引入包
set hive.execution.engine=tez;
-- 合并小文件
set hive.map.aggr=true
set hive.merge.mapfiles = true;
set hive.merge.mapredfiles = true;
set hive.exec.max.dynamic.partitions=3000;
set hive.exec.max.dynamic.partitions.pernode=500;
SET hive.tez.container.size=6656;
SET hive.tez.java.opts=-Xmx5120m;
set hive.merge.tezfiles=true;
set hive.merge.smallfiles.avgsize=1280000000;
set hive.merge.size.per.task=1280000000;
set hive.execution.engine=tez;
set tez.am.resource.memory.mb=8192;
-- 涉及数据倾斜的话，主要是reduce中数据倾斜的问题，
-- 可能通过设置hive中reduce的并行数
-- reduce的内存大小单位为m
-- reduce中 shuffle的刷磁盘的比例
SET mapred.reduce.tasks=50;
SET mapreduce.reduce.memory.mb=6000;
SET mapreduce.reduce.shuffle.memory.limit.percent=0.06;

1.9 hive sql shell 脚本

h_pt=`date -d "+1 hour" +%Y%m%d%H`
re_time_index=`date -d "+1 hour" +%H`
b_2h_time=`date -d "-2 hour" +%H`

`date -d "-2 day" +%Y%m%d`
获取今天时期：`date +%Y%m%d` 或 `date +%F` 或 $(date +%y%m%d)
获取昨天时期：`date -d yesterday +%Y%m%d`
获取前天日期：`date -d -2day +%Y%m%d`
依次类推比如获取10天前的日期：`date -d -10day +%Y%m%d`
或n天前的 `date -d "n days ago" +%y%m%d`
明天：`date -d tomorrow +%y%m%d`

-- 本月第一天
nowdate=`date +%Y%m01` 
-- 本月最后一天
enddate=`date -d"$(nowdate -d"1 month" +"%Y%m01") -1 day" +"%Y%m%d"`
-- 上个月第一天
startdate=`date -d"$nowdate last month" +%Y%m%d` 
-- 上个月最后一天
enddate=`date -d"$nowdate last day" +%Y%m%d`
-- 短时订单
(unix_timestamp(stop_time) - unix_timestamp(start_time)) >120
-- 当前时间的前一个小时
select from_unixtime(unix_timestamp(concat(substr('2019073112',1,4),'-',substr('2019073112',5,2),'-',substr('2019073112',7,2),' ',substr('2019073112',9,2),':00:00'))-3600,'yyyyMMddHH')

-- week
IF(pmod(datediff(concat(substr(dt,1,4),'-',substr(dt,5,2),'-',substr(dt,7,2)), '1920-01-01') - 3, 7)='0', 7, pmod(datediff(concat(substr(dt,1,4),'-',substr(dt,5,2),'-',substr(dt,7,2)), '1920-01-01') - 3, 7)) as week

2.0 cron 表达式

-- cron表达式 每周一到周五调度
0 15 10 ? * MON-FRI
"0 0 12 * * ? " 每天12点运行
"0 15 10 ? * *" 每天10:15运行
"0 15 10 * * ?" 每天10:15运行
"0 15 10 * * ? *" 每天10:15运行
"0 15 10 * * ? 2008" 在2008年的每天10：15运行
"0 * 14 * * ?" 每天14点到15点之间每分钟运行一次，开始于14:00，结束于14:59。
"0 0/5 14 * * ?" 每天14点到15点每5分钟运行一次，开始于14:00，结束于14:55。
"0 0/5 14,18 * * ?" 每天14点到15点每5分钟运行一次，此外每天18点到19点每5钟也运行一次。
"0 0-5 14 * * ?" 每天14:00点到14:05，每分钟运行一次。
"0 10,44 14 ? 3 WED" 3月每周三的14:10分到14:44，每分钟运行一次。
"0 15 10 ? * MON-FRI" 每周一，二，三，四，五的10:15分运行。
"0 15 10 1 * ?"
"0 15 10 15 * ?" 每月15日10:15分运行。
"0 15 10 L * ?" 每月最后一天10:15分运行。
"0 15 10 ? * 6L" 每月最后一个星期五10:15分运行。
"0 15 10 ? * 6L 2007-2009" 在2007,2008,2009年每个月的最后一个星期五的10:15分运行。
"0 15 10 ? * 6#3" 每月第三个星期五的10:15分运行。

2.1日期加减

-- date_add 增加
select replace(date_add(current_date,1),'-','')
-- date_sub 减少
select replace(date_sub(current_date,1),'-','')
-- 两个日期相减
select datediff('2019-12-01 23:23:23','2019-02-01 23:23:23')

2.2 row_number()

--row_number()
row_number() OVER (partition by pt,uid order by time_index desc)as row_index

2.3上个月

-- 上个月最后一天
select SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAYOFMONTH(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,10)

-- 上个月第一天
select concat(SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAYOFMONTH(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,7),'-01')

2、注意事项

2.1 join

sql left join和 not in 比较

• 建议在写sql语句的时候，尽量避免用not in 而 优先选择left join，这样效率会提高很多

2.2 count

• 尽量用count(1) 而不是count(*)
• distinct col 返回值中含有null
• count(col) 返回值不包含null
• count(distinct col) 返回值不包含（数据量大尽量尽量不要使用count distinct col 换成group by col 然后计算count(1)替代）

2.3 数据倾斜

2.3.1 通过增加reduce数缓解

– 涉及数据倾斜的话，主要是reduce中数据倾斜的问题，
– 可能通过设置hive中reduce的并行数
– reduce的内存大小单位为m
– reduce中 shuffle的刷磁盘的比例

SET mapred.reduce.tasks=50;
SET mapreduce.reduce.memory.mb=6000;
SET mapreduce.reduce.shuffle.memory.limit.percent=0.06;

2.3.2 map join

在map端完成聚合操作

set hive.map.aggr=true;
-- set hive.exec.parallel 为true 可控制一个sql中多个可并行执行的job的运行方式
--parallel.thread.number 控制对于同一个sql来说同时可以运行的job的最大值,该参数默认为8.此时最大可以同时运行8个job.
set hive.exec.parallel.thread.number=16）
-- 决定  group by 操作是否支持倾斜数据（只能对单个字段聚合）生成两个mr job
set hive.groupby.skewindata=true 

2.4 合并小文件

-- 加快运行速度
set hive.execution.engine=tez;

-- 合并小文件
set hive.merge.mapfiles = true;
set hive.merge.mapredfiles = true;
set hive.exec.max.dynamic.partitions=3000;
set hive.exec.max.dynamic.partitions.pernode=500;
SET hive.tez.container.size=6656;
SET hive.tez.java.opts=-Xmx5120m;
set hive.merge.tezfiles=true;
set hive.merge.smallfiles.avgsize=1280000000;
set hive.merge.size.per.task=1280000000;
set tez.am.resource.memory.mb=8192;

3 分区表

3.1 全量表

每天全量的数据进行分区，全量表无分区

3.2 增量表

按天分区，每一天的分区存储那一天产生的数据，如20190512这一天产生的订单数据，会存储在分区为20190512这个分区里面

3.3 快照表

按天分区，每一天的数据截止到那一天的全量数据，包含历史数据