HIVE常用正则表达式
1、在关联作前尽量减小数据集,能先聚合的先聚合、能过滤的先过滤（如设置查询条件、合理设置分区，有分区必须设置分区范围）。一、校验数字的表达式
hive日期格式转换 hive日期格式转换字符串
hive日期格式转换 hive日期格式转换字符串
数字：^[0-9]$
n位的数字：^d{n}$
至少n位的数字：^d{n,}$
m-n位的数字：^d{m,n}$
零和非零开头的数字：^(0|[1-9][0-9])$
非零开头的最多带两位小数的数字：^([1-9][0-9])+(.[0-9]{1,2})?$
带1-2位小数的正数或负数：^(-)?d+(.d{1,2})$
正数、负数、和小数：^(-|+)?d+(.d+)?$
有两位小数的正实数：^[0-9]+(.[0-9]{2})?$
有1~3位小数的正实数：^[0-9]+(.[0-9]{1,3})?$
非零的正整数：^[1-9]d$ 或 ^([1-9][0-9]){1,3}$ 或 ^+?[1-9][0-9]$
非零的负整数：^-[1-9][]0-9"$ 或 ^-[1-9]d$
非正整数：^-[1-9]d|0$ 或 ^((-d+)|(0+))$
非负浮点数：^d+(.d+)?$ 或 ^[1-9]d.d|0.d[1-9]d|0?.0+|0$
非正浮点数：^((-d+(.d+)?)|(0+(.0+)?))$ 或 ^(-([1-9]d.d|0.d[1-9]d))|0?.0+|0$
负浮点数：^-([1-9]d.d|0.d[1-9]d)$ 或 ^(-(([0-9]+.[0-9][1-9][0-9])|([0-9][1-9][0-9].[0-9]+)|([0-9][1-9][0-9])))$
浮点数：^(-?d+)(.d+)?$ 或 ^-?([1-9]d.d|0.d[1-9]d|0?.0+|0)$
校验字符的表达式
汉字：^[u4e00-u9fa5]{0,}$
英文和数字：^[A-Za-z0-9]+$ 或 ^[A-Za-z0-9]{4,40}$
长度为3-20的所有字符：^.{3,20}$
由26个英文字母组成的字符串：^[A-Za-z]+$
由26个大写英文字母组成的字符串：^[A-Z]+$
由26个小写英文字母组成的字符串：^[a-z]+$
由数字和26个英文字母组成的字符串：^[A-Za-z0-9]+$
由数字、26个英文字母或者下划线组成的字符串：^w+$ 或 ^w{3,20}$
中文、英文、数字包括下划线：^[u4E00-u9FA5A-Za-z0-9_]+$
中文、英文、数字但不包括下划线等符号：^[u4E00-u9FA5A-Za-z0-9]+$ 或 ^[u4E00-u9FA5A-Za-z0-9]{2,20}$
可以输入含有^%&',;=?$"等字符：[^%&',;=?$x22]+
禁止输入含有~的字符：[^~x22]+
三、特殊需求表达式
Email地址：^w+([-+.]w+)@w+([-.]w+).w+([-.]w+)$
域名：[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(/.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+/.?
InternetURL：[a-zA-z]+://[^s]对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。 或 ^
手机号码：^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])d{8}$
电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX)：^((d{3,4}-)|d{3.4}-)?d{7,8}$
国内电话号码(0511-4405222、021-87888822)：d{3}-d{8}|d{4}-d{7}
电话号码正则表达式（支持手机号码，3-4位区号，7-8位直播号码，1－4位分机号）: ((d{11})|^((d{7,8})|(d{4}|d{3})-(d{7,8})|(d{4}|d{3})-(d{7,8})-(d{4}|d{3}|d{2}|d{1})|(d{7,8})-(d{4}|d{3}|d{2}|d{1}))$)
号(15位、18位数字)，一位是校验位，可能为数字或字符X：(^d{15}$)|(^d{18}$)|(^d{17}(d|X|x)$)
帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$
密码(以字母开头，长度在6~18之间，只能包含字母、数字和下划线)：^[a-zA-Z]w{5,17}$
强密码(必须包含大小写字母和数字的组合，不能使用特殊字符，长度在 8-10 之间)：^(?=.d)(?=.[a-z])(?=.[A-Z])[a-zA-Z0-9]{8,10}$
强密码(必须包含大小写字母和数字的组合，可以使用特殊字符，长度在8-10之间)：^(?=.d)(?=.[a-z])(?=.[A-Z]).{8,10}$
日期格式：^d{4}-d{1,2}-d{1,2}
一年的12个月(01～09和1～12)：^(0?[1-9]|1[0-2])$
一个月的31天(01～09和1～31)：^((0?[1-9])|((1|2)[0-9])|30|31)$
钱的输入格式：
有四种钱的表示形式我们可以接受:"10000.00" 和 "10,000.00", 和没有 "分" 的 "10000" 和 "10,000"：^[1-9][0-9]$
一个0或者一个不以0开头的数字.我们还可以允许开头有一个负号：^(0|-?[1-9][0-9])$
这表示一个0或者一个可能为负的开头不为0的数字.让用户以0开头好了.把负号的也去掉,因为钱总不能是负的吧。下面我们要加的是说明可能的小数部分：^[0-9]+(.[0-9]+)?$
必须说明的是,小数点后面至少应该有1位数,所以"10."是不通过的,但是 "10" 和 "10.2" 是通过的：^[0-9]+(.[0-9]{2})?$
这样我们规定小数点后面必须有两位,如果你认为太苛刻了,可以这样：^[0-9]+(.[0-9]{1,2})?$
这样就允许用户只写一位小数.下面我们该考虑数字中的逗号了,我们可以这样：^[0-9]{1,3}(,[0-9]{3})(.[0-9]{1,2})?$
1到3个数字,后面跟着任意个 逗号+3个数字,逗号成为可选,而不是必须：^([0-9]+|[0-9]{1,3}(,[0-9]{3}))(.[0-9]{1,2})?$
备注：这就是最终结果了,别忘了"+"可以用""替代如果你觉得空字符串也可以接受的话(奇怪,为什么?),别忘了在用函数时去掉去掉那个反斜杠,一般的错误都在这里
xml文件：^([a-zA-Z]+-?)+[a-zA-Z0-9]+.[x|X][m|M][l|L]$
中文字符的正则表达式：[u4e00-u9fa5]
双字节字符：[^x00-xff] (包括汉字在内，可以用来计算字符串的长度(一个双字节字符长度计2，ASCII字符计1))
空白行的正则表达式：nsr (可以用来删除空白行)
HTML标记的正则表达式：<(S?)[^>]>.?|<.? /> ( 首尾空白字符的正则表达式：^s|s$或(^s)|(s$) (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式)
腾讯QQ号：[1-9][0-9]{4,} (腾讯QQ号从10000开始)
邮政编码：[1-9]d{5}(?!d) (邮政编码为6位数字)
IP地址：((?:(?:25[0-5]|2[0-4]d|[01]?d?d).){3}(?:25[0-5]|2[0-4]d|[01]?d?d))
HIVE 在原始时间上加八小时的函数怎么写
可结合Gzip、Bzip2、Snappy等使用（系统自动检查，执行查询时自动解压），但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行作。hive时间控制常用是转成时间戳再加减，例如:
date_format:格式化时间select from_unixtime(unix_timestamp('2017-10-31 16:00:00')+28800,'yyyy-MM-dd HH:mm:ss')
其中unix_timestamp是日期转时间戳，28800=86060（小时换为秒）
from_unixtime是转回日期格式，大概是这个意思
hive的几种文件格式
hive支持的存储格式：
hive支持的存储格式包括TextFile、SequenceFile、RCFile、Avro Files、ORC Files、Parquet。
TextFile：
Hive默认格式，数据不做压缩，磁盘开销大，数据解析开销大。
SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。
SequenceFile的文件结构图：
Header通用头文件格式：
SEQ 3BYTE
Nun 1byte数字
keyClassName
ValueClassName
comp["1","2","3","4"] --------- aression （boolean）指明了在文件中是否启用压缩
blockCompression （bo返回值: stringolean，指明是否是block压缩）
compression codec
Metadata 文件元数据
Sync 头文件结束标志
Block-Compressed SequenceFile格式
RCFile
RCFile是Hive推出的一种专门面向列的数据格式。 它遵循“先按列划分，再垂直划分”的设计理念。当查询过程中，针对它并不关心的列时，它会在IO上跳过这些列。需要说明的是，RCFile在map阶段从 远端拷贝仍然是拷贝整个数据块，并且拷贝到本地目录后RCFile并不是真正直接跳过不需要的列，并跳到需要读取的列， 而是通过扫描每一个row group的头部定义来实现的，但是在整个HDFS Block 级别的头部并没有定义每个列从哪个row group起始到哪个row group结束。所以在读取所有列的情况下，RCFile的性能反而没有SequenceFile高。
Hive日常数据需求
返回值: int笔记：
1.窗口函数边界：
复购率: 当前月份购买2次及以上的客户占所有客户比例
回购率：当前月份购买且上个月份也购买的客户占当月所有月份客户比例
思路：
复购率
1、对当月（2月份）的客户分组，计数购买次数
2、筛选购买次数为2以上的，认为是复购群体
回购率
1、筛选当月及上月部分
2、利用客户id进行当月连上月，左连
3、对同一条客户id均有购买记录的，认为是回购群体
注意：
1.连接条件中，条件1为客户key字段，表示同一个客户；条件2为两个月的信息连接（本月日期 = 上月TIME 时间型日期 +1个月）
2.ADD_MONTHS：别忘了有个's'，add_months只能对完整日期格式进行月份加减(add_months（‘yyyy-MM-dd hh:mm:ss’，N），add_months（‘yyyy-MM-dd’，N）)，所以这里先用concat给'年月'增加一个'日'字段，其中N为正则增加，N为负则减少
cpzl_zw1是cpzl_zw的上一个购买的产品
第三步：为相邻的产品添加排序（即上面lag函数添加的产品）
若相邻产品的排序为3，则表示第三件产品，题目要求前两件产品，则可以根据小于3进行筛选
步骤：
步：将两表进行连接（customer为主表）
第二步：按城市、产品进行聚合分组，求出销量
第三步：窗口函数对销量进行排序
第四步：where进行筛选（where不能直接对窗口函数结果进行筛选，所以要再次使用表子连接）
提示：只用到订单表 ods_sales_orders，并列排序 dense_rank 窗口函数
在hive中将数据按日期划分，适合建什么表 外部表 分区表 分桶表 本地表
这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加返回的结果如图：到被连接的字符串之间;首先，Hive 没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由的组织 Hive 中的表，只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。
Hive中CAST（）函数用法
["1","2","3","4"] --------- aCast(字段名 as 转换的类型 )，其中类型可以为：
CHAR[(N)] 字符型
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
例如表table1
2015-11-03 15:31:26
select cast(date as signed) as date from table1;
结果如0 是显示与之匹配的整个字符串下：
20151103153126
select cast(date as char) as date from table1;
结果如下：
2015-11-03 15:31:26
select cast(date as datetime) as date from table1;
结果如下：
2015-11-03 15:31:26
select cast(date as date) as date from table1;
结果如下：
2015-11-03
select cast(date as time) as date from table1;
结果如下：
15:31:26
这里date对应日期，time对应时间
Hive中的字符类型String/VARCHAR/CHAR有什么不同
步：根结果如图：据用户分组，求出用户购买的产品及顺序上表我们看到hive不支持日期类型，在hive里日期都是用字符串来表示的，而常用的日期格式转化作则是通过自定义函数进行作。 hive是用Ja开发的，hive里的基本数据类型和ja的基本数据类型也是一一对应的，除了string类型。
Hive中常用的字符串作
group分组一组一个值，over给每一条数据开窗创建虚表：
语法: length(string A)
说明：返回字符串A的长度
语法: rrse(string A)
说明：返回字符串A的反转结果
语法: concat(string A, string B…)
说明：返回输入字符串连接后的结果，支持任意个输入字符串
语法: concat_ws(string SEP, string A, string B…)
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
语法: substr(string A, int start),substring(string A, int start)
说明：返回字符串A从start位置到结尾的字符串
语法: substr(string A, int start, int len),substring(string A, int start, int len)
说明：返回字符串A从start位置开始，长度为len的字符串
语法: upper(string A) ucase(string A)
说明：返回字符串A的大写格式
语法: lower(string A) lcase(string A)
说明：返回字符串A的小写格式
说明：去除字符串两边的空格
语法: ltrim(string A)
说明：去除字符串左边的空格
语法: rtrim(string A)
说明：去除字符串右边的空n PRECEDING：往前 n 行数据；格
语法: regexp_replace(string A, string B, string C)
说明：将字符串A中的符合ja正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
语法: regexp_extract(string subject, string pattern, int index)
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
第三个参数:
1 是显示个括号里面的
2 是显示第二个括号里面的字段
语法: parse_(string String, string partToExtract [, string keyToExtract])
说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
语法: get_json_object(string json_string, string path)
说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
语法: space(int n)
说明：返回长度为n的空字符串
语法: repeat(string str, int n)
说明：返回重复n次后的str字符串
语法: ascii(string str)
说明：返回字符串str个字符的ascii码
语法: lpad(string str, int len, string pad)
说明：将str进行用pad进行左补足到len位
语法: rpad(string str, int len, string pad)
说明：将str进行用pad进行右补足到len位
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
语法: find_in_set(string str, string strList)
说明: 返回str在strlist次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
返回:int。substr在str中次出现的位置，若任何参数为null返回null，若substr不在str中返回0，Str中个字符的位置为1
说明：C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
24、使用两个分隔符将文本拆分为键值对：str_to_map(text[, delimiter1, delimiter2])
返回：map
Delimiter1将文本分成K-V对，Delimiter2分割每个K-V对。对于delimiter1默认分隔符是','，对于delimiter2默认分隔符是'='
25、unix_timestamp() 返回当前时间戳。另外，current_timestamp() 也有同样作用。
unix_timestamp(string date) 返回 date 对应的时间戳，date 格式必须为 yyyy-MM-dd HH:mm:ss。
unix_timestamp(string date, string format) 返回 date 对应的时间戳，date 格式由 format 指定。
26、from_unixtime(int/bigint timestamp) 返回 timestamp 时间戳对应的日期，格式为 yyyy-MM-dd HH:mm:ss。
from_unixtime(int/bigint timestamp, string format) 返回 timestamp 时间戳对应的日期，格式由 format 指定。
hive关于行转列，列转行的使用
行转列：
定义：就是把形如
id --------- name
1 --------- hive.mapjoin.alltable.filesize= 0000；--早期hive版本小表文件大小设置默认25M。a
2 --------- a
3返回结果： --------- a
4 --------- a
的数据转换为
id ------------- name
1，2，3，4 --------- a
这里把四行的数据变成了由一行显示，这就是行转列。
首先要用到的是collect_set或者collect_list，前者去重，后者不去重，一般配合group使用，能把形如：
id --------- name
1 --------- a
2 --------- a
3 --------- a
4 --------- a
转换为
id --------- name
然后需要用到的是concat_ws函数，这个函数需要传递指定分隔符，以及字符串或者字符串数组。能起到把多个字符串拼接到一起的作用。
形如：
id --------- name
通过concat_ws(',',[1,2,3,4])转换为
id --------- name
1，2，3，4 --------- a
连起来就完成了行转列
一般sql为:
列转行
定义：就是把形如
id ------------- name
1，2，3，4 --------- a
的数据转换为
id --------- name
1 --------- a
2 --------- a
3 --------- a
4 --------- a
这里把一行的数据变成了由四行显示，这就是列转行。
首先要用到split函数，这个行数可以把字符串按照指定的规则切分为字符串数组。
形如：
id --------- name
1，2，3，4 --------- a
通过split('1,2,3,4',',')转换为
id --------- name
然后是explode函数，这个行数可以把数组的每个元素转换为一行。
形如
id
["1","2","3","4"]
通过explode(["1","2","3","4"] )转换为
id
12
为了拼接其它字段，还需要用到lateral view把explode获得的行当虚拟表来用。
使用方法为 lateral view explode(split(["1","2","3","4"] , ',')) tmptable as new_id ;
其中new_id元素为转换为行后的字段名。
把以上综合起来就完成了列转行
一般sql为:
大数据之-HIVE入门（十四）
set hive.auto.convert.join.noconditionaltask = true; --默认开启当join时有一个或多个小表可以装载进内存时可以使用mapjoin完成。
种方法是可以加上mapjoin指示
第二种方法是设置 set hive.auto.convert.join=true;来让hive自动优化。同时可以指定
set hive.auto.convert.join.noconditionaltask.size = 10000000; --默认10M，可以根据需求做调整。
当数据量比较大启动mapjoin后会造成问题请关闭
set hive.map.aggr = true; //是否在 Map 端进行聚合，默认为 True ；
set hive.groupby.mapaggr.checkinterval = 100000000; //在 Map 端进行聚合作的条目数目
2、关联时数据类型要做到一致，如果不一致请用cast先转换类型。
3、慎用count(distinct) ，容易产生数据倾斜，可以先group by 再count。
4、减少小文件，合理设置输入文件大小、合理设置map job 、reduce job数。
set hive.非负整数：^d+$ 或 ^[1-9]d|0$merge.mapredfiles=true；--设置合并map文件标识。
set mapred.max.split.size=100000000; --设置输入文件大小，大于此数值都会进行拆分。
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; – 执行前进行小文件合并。
set mapred.reduce.tasks=10; -- 设置reduce的数量
set hive.exec.reducers.bytes.per.reducer=1073741824 --设置每个reduce所处理的数据大小
5、选取字段避免用select ，只引用你要用的字段，如select a.uid,a.pr。
6、关联值有null值的情况下，可以将null值过滤出来单独处理或者将null值随机赋值。当存在某key有热点问题，也可以同样处理。
7、合理设置数据模型、文件存储格式有利于查询效率优化。
8、善用union all 合并对于同一个表的查询，有利于整体提高效率。
9、合理使用中间临时表，数据量巨大时，如统计一年的数据，可先小规模聚合如按月聚合生成中间表，再合并统计出结果。
10、有order by 要限制输出条数。
11、合理设置并行查询
set hive.exec.parallel= true ； --以开启并发执行。
set hive.exec.parallel.thread.number= 10 ; //同一个sql允许并行度，默认为8。