prestoSQL 查询hive sql hive sql parser

1、函数： 2020 4 转为 202004

concat(kjnd,lpad(kjqj,2,‘0’))

2、hive中导入数据：

提前建好表，制定字段分隔符
LOAD DATA [local] INPATH '/workspace/bpUserinfo_201511.log'  INTO table testkv;

3、建表制定null值存储格式：
hive表：
null默认的存储都是\N，可以在建表时通过serialization.null.format=’’ 的设置，设置为’’
hive parquet格式压缩：

创建parquet table :
create table tabname(a int,b int) STORED AS PARQUET;

创建带压缩的parquet table:
create table tabname(a int,b int) STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');

如果原来创建表的时候没有指定压缩，后续可以通过修改表属性的方式添加压缩:
ALTER TABLE tabname SET TBLPROPERTIES ('parquet.compression'='SNAPPY');
或者在写入的时候
set parquet.compression=SNAPPY;


建表制定分隔符： 

CREATE external TABLE `table1`(
  `name` string COMMENT '姓名',
  `id` int COMMENT 'ID'
)
PARTITIONED BY (
     `province_id` int,
     `day` int)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim'=',',
  'serialization.format'=',')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
  -- 以字段间以“,”为分隔符，行间以tab为分隔符
     
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim'='|',
  'serialization.format'='|')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
  -- 以字段间以“|”为分隔符，行间以tab为分隔符

建表指定分隔符：
create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

4、hive脚本参数优化

set hive.exec.dynamic.partition.mode = true;　　--使用动态分区时，设置为ture。
set hive.exec.dynamic.partition.mode = nonstrict;　　--动态分区模式，默认值：strict，表示必须指定一个分区为静态分区；nonstrict模式表示允许所有的分区字段都可以使用动态分区。一般需要设置为nonstrict。
set hive.exec.max.dynamic.partitions.pernode =10;　　--在每个执行MR的节点上，最多可以创建多少个动态分区，默认值：100。
set hive.exec.max.dynamic.partitions =1000;　　--在所有执行MR的节点上，最多一共可以创建多少个动态分区，默认值：1000。
set hive.exec.max.created.files = 100000;　　--整个MR Job中最多可以创建多少个HDFS文件，默认值：100000。
控制Mapper的数量：
set hive.error.on.empty.partition = false;　　--当有空分区产生时，是否抛出异常，默认值：false。
set mapred.max.split.size=100000000; 　　-- 决定每个map处理的最大的文件大小，单位为B
set mapred.min.split.size.per.node=100000000; 　　-- 节点中可以处理的最小的文件大小
set mapred.min.split.size.per.rack=100000000; 　　-- 机架中可以处理的最小的文件大小
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;　　---实现map中的数据合并需要设置下面的参数，集群默认就是这个格式
不指定Reduce的个数的情况下，Hive会猜测确定一个Reduce个数，由下面两个参数决定：
　　　　1、hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G） 
　　　　2、hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
set hive.execution.engine=tez;
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
set hive.merge.mapredfiles = true ;
set hive.exec.reducers.bytes.per.reducer=128000000;
set hive.merge.size.per.task = 128000000;
set hive.exec.parallel=true; 
set hive.exec.parallel.thread.number=6;
set hive.auto.convert.join=false;
set mapreduce.map.memory.mb=10150;
set mapreduce.map.java.opts=-Xmx2048m;
set mapreduce.reduce.memory.mb=10150;
set mapreduce.reduce.java.opts=-Xmx2048m;

hive 查询 impala 生成parquet格式的数据时，可能会因为底层数据不均造成查询报错，可通过一下参数调优：

set hive.execution.engine=mr;
set mapreduce.map.memory.mb=6144;
set mapreduce.map.java.opts=-Xmx4096m;  
set mapreduce.input.fileinputformat.split.maxsize=1024000000;
set mapreduce.input.fileinputformat.split.minsize=1024000000;
set mapred.max.split.size=1024000000;
set mapred.min.split.size.per.node=1024000000;
set mapred.min.split.size.per.rack=1024000000; 
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
set parquet.memory.min.chunk.size=100000; # parquet文件格式配置
set mapreduce.reduce.memory.mb=8192;
set mapreduce.reduce.java.opts=-Xmx6144m;

合并小文件

1. Map输入合并小文件
对应参数：
set mapred.max.split.size=256000000;  #每个Map最大输入大小
set mapred.min.split.size.per.node=100000000; #一个节点上split的至少的大小 
set mapred.min.split.size.per.rack=100000000; #一个交换机下split的至少的大小
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;  #执行Map前进行小文件合并
在开启了org.apache.hadoop.hive.ql.io.CombineHiveInputFormat后，一个data node节点上多个小文件会进行合并，合并文件数由mapred.max.split.size限制的大小决定。
mapred.min.split.size.per.node决定了多个data node上的文件是否需要合并~
mapred.min.split.size.per.rack决定了多个交换机上的文件是否需要合并~

2.输出合并
set hive.merge.mapfiles = true #在Map-only的任务结束时合并小文件
set hive.merge.mapredfiles = true #在Map-Reduce的任务结束时合并小文件
set hive.merge.size.per.task = 256*1000*1000 #合并文件的大小
set hive.merge.smallfiles.avgsize=16000000 #当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge

5、批量导出hive建表语句的脚本

#!/bin/bash
for hiveTabName in $(hive -e "show tables from datax;")
do
hive -e "show create table datax.${hiveTabName};" >>tablesDDL.txt
echo -e "---------------------------- $hiveTabName table structure generate finished! -------------------------------\n\n">>tablesDDL.txt
done

6、HIVE增删改字段、增删分区、加载数据

hive表增字段：
 alter table detail_flow_test add columns(original_union_id string);
hive表删字段：
// 删除字段(使用新schema替换原有的)
ALTER TABLE test REPLACE COLUMNS(id BIGINT, name STRING);
hive表修改字段：
 alter table fct_user_ctag_today CHANGE COLUMN rpt_tag rpt_tag int comment ' ';
备注：增删改hive表后需要删除分区，重新添加元数据才可生效

hive表添加分区：
alter table  ads.ads_dingding_bill_info_d  add if  not  exists    partition (dt = '${pd_date}')   location '/apps/data/warehouse/ads/ads_dingding_bill_info_d/dt=${pd_date}'  ;
hive表删除分区：
alter table  ads.ads_dingding_bill_info_d  drop if  exists    partition (dt = '${pd_date}')  ;


load数据：
LOAD DATA 【LOCAL】 INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1 PARTITION（pt=’xxxx）

合并小文件脚本

#!/bin/bash

pd_date=$1

#ng日志接入hdfs的存储目录
ng_log_dir=/init/data/warehouse/tmp/safe_interface/${pd_date}

#测试今日路径下是否存在.json文件，如重跑不会删除已存在表的数据
hadoop fs -test -f  ${ng_log_dir}/*.json

if [ $? -eq 0 ] ;then 
    echo ----------日期：${pd_date} ----------
    echo 'ng日志存在，执行数据加载任务' 
hive  -e  "LOAD DATA INPATH  '/init/data/warehouse/tmp/safe_interface/${pd_date}/safe_interface_*.json'   OVERWRITE INTO TABLE log_ods.ods_tmp_safe_interface_log; "
hive  -e  "set mapred.max.split.size=2147483648; set mapred.min.split.size.per.node=100000000; set mapred.min.split.size.per.rack=100000000;	set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;  INSERT OVERWRITE table log_ods.ods_safe_interface_log  partition(dt='${pd_date}')  SELECT   *  from  log_ods.ods_tmp_safe_interface_log ; "
else 
    echo ----------日期：${pd_date} ----------
    echo 'ng日志不存在，请查看是否已加载过数据'
fi

--hivesql合并小文件，仅设置map端参数即可，因为select *  做操作不产生reduce任务
set mapreduce.map.memory.mb=1024;
set mapreduce.map.java.opts=-Xmx1024m;  
set mapred.max.split.size=512000000;  		--一个map读取的文件大小	
set mapred.min.split.size.per.node=100000000;	
set mapred.min.split.size.per.rack=100000000; 	
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;  

insert overwrite table log_ods.ods_***** 
partition(dt = '${pd_date}' )
select   *  
 from  log_ods.ods_*****
where dt = '${pd_date}'
;

7、Hive脚本的头信息及创表模本
头信息

--Title：管理报表
--Target:   ads.ads_byd_***********_d  
--Description: 工作进度统计表
--Source：
--Author: ndndnl
--CreateDate: 202101-28
--UpdateDate: 

--指定hive执行引擎及资源
set hive.execution.engine=mr;
set mapreduce.map.memory.mb=6144;
set mapreduce.map.java.opts=-Xmx4096m;  
set mapreduce.reduce.memory.mb=8192;
set mapreduce.reduce.java.opts=-Xmx6144m;
--禁用hive的mapjoin，默认开启，默认表小于25mb则开启mapjoin
--set hive.auto.convert.join=false;
--#map端合并，控制map阶段task数
set mapred.max.split.size=256000000;  						--每个Map最大输入大小
set mapred.min.split.size.per.node=100000000;		-- 一个节点上split的至少的大小 
set mapred.min.split.size.per.rack=100000000; 		--一个交换机下split的至少的大小
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;  			--执行Map前进行小文件合并
--reduce阶段小文件合并，控制生成表的文件大小
set hive.merge.mapfiles = true ; 										--#在Map-only的任务结束时合并小文件
set hive.merge.mapredfiles = true ;								--#在Map-Reduce的任务结束时合并小文件
set hive.merge.smallfiles.avgsize=16000000 ;				--#当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge
set hive.merge.size.per.task = 256000000;			--#合并文件的大小（生成文件大小256m）


--【hive创建临时表默认使用textfile格式，如果有字段值有换行符会造成数据串行数据量增大等问题，建议显示指定使用PARQUET格式】
--创建临时表
drop table if exists default.tmp_dwb_acloud_kp_info_diff_01;
create table default.tmp_dwb_acloud_kp_info_diff_01 
STORED AS PARQUET 
as 
select  

--结果表落地
alter table ads.ads_xxxxxxxxxxx  drop if exists partition (dt = '${pd_date}')  ;
alter table ads.ads_xxxxxxxxxxx  add if not exists partition (dt='${pd_date}')    location '/apps/data/warehouse/ads/ads_xxxxxxxxxxx/dt=${pd_date}' ;
insert overwrite table ads.ads_xxxxxxxxxxx
partition (dt = '${pd_date}') 



--删除临时表(可不加)
drop table if exists default.tmp_dwb_acloud_kp_info_diff_01;

8、SQL函数

1、left join 的on、where条件过滤的执行顺序
select * from a left join b on a.id = b.id  where 过滤条件
--参考链接： 
(1) 如果是对左表（a）字段过滤数据，则可以直接写在where后面，此时执行的顺序是：先对a表的where条件过滤数据然后再join b 表
(2) 如果是对右表（b）字段过滤数据，则应该写在on 条件后面或者单独写个子查询嵌套进去，这样才能实现先过滤b表数据再进行join 操作；
如果直接把b表过滤条件放在where后面，执行顺序是：先对a表数据过滤，然后和b表全部数据关联之后，在reduce 阶段才会对b表过滤条件进行过滤数据，此时如果b表数据量很大的话，效率就会很低。因此对于应该在map 阶段尽可能对右表进行数据过滤。
(3)至于是否全表扫描取决于是否对表的分区字段过滤。这个具体从业务方面考虑是否需要对分区过滤，要想sql 高效那么尽可能在map阶段将不需要的数据过滤，减少后面资源的占用，提高效率

2、left semi join

在left semi join中，关联右侧的表只能在on中设置过滤条件，在where， select或其他地方均不可出现。这主要是因为left semi join只会传递关联条件中key给每一个map，这导致了查询结果中只会出现左侧表中的字段，如果出现右表的字段则会报错。
在left semi join时，功能等同于a.key in (b.keyset)。当右表的key存在重复数据时，会直接跳过，不会重复关联。因此不会产生笛卡尔积。

left semi join与in的功能基本相同，上面的left semi join案例可以用in达到一样的效果：

--参考链接： 

2、求 并集、补集、差集、交集
并集：  union all  或 union   
补集：left join   右表为null  右表不存在  
差集：union all  后主键分组后count（1）=1的为左右两表的差集
交集：  join  或   left  semi join    或  in    

3、NTILE(n) 函数
用于将分组数据按照顺序切分成n片，返回当前切片值

 ntile(3) over() as sample1 , --全局数据切片
 ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
 ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
 ntile(3) over(partition by name order by cost ) --按照name分组，在分组内按照cost升序排列,数据切成3份

4、开窗函数   rows between …… and ……
unbounded preceding 起始行
unbounded following 结尾行
current row 当前行
1 preceding 前1行
1 following 后1行
示例：
rows between unbounded preceding and unbounded following             -- 表示起始行到末尾行（分组范围内）
--分组排序后逐条累加
sum(gl_pzx.k_jf + gl_pzx.k_df) over(partition by gl_pz.k_ztdm, gl_pz.k_kjnd, gl_pz.k_kjqj, gl_pzx.k_kmnm,gl_pzx.k_bzid order by gl_pz.K_LSH, gl_pzx.K_ROW rows between UNBOUNDED PRECEDING and current row) as balance, -- 凭证的累加发生额

**若要用到取今天和昨天的某字段差值时**
 Offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。
salary, LAG(salary, 1, 0) OVER(PARTITION BY user_name ORDER BY salary_vaild_date) AS last_salary      ---往前差值行  
lead(exp_str,offset,defval) over(partion by ..order by …)       --往后差值行

5、自定义udf函数

永久函数：
CREATE FUNCTION hadoop.gps_to_bd AS 'cn.hive.Gps_to_Bd' USING JAR 'hdfs://ns1//hive/private/lib/gps_to_bd.jar';
临时函数：
add jar hdfs://ns1//hive/private/lib/gps_to_bd.jar;
CREATE TEMPORARY FUNCTION gps_to_bd AS 'cn.hive.Gps_to_Bd';
删除函数：
drop function db_name.fun_name ;

查看自定义或内置函数：

1 查看所有的函数：
show functions;
2. 查看date相关的函数：（模糊查询）
show functions like 函数名 
示例：show functions like '*date*' （模糊查询）
3.粗粒度查看函数的使用方法：
desc function 函数名
示例：desc function round;
4. 细粒度查看函数使用方法：
desc function extended 函数名