Hive SQL学习整理_5

本文最后更新于2019年6月16日,已超过 1 年没有更新,如果文章内容失效,还请反馈给我,谢谢!

=Start=

缘由:

继续整理最近学到或是用到的Hive SQL知识,方便以后参考。

正文:

参考解答:
1、Hive中的条件判断
IF( Test Condition, True Value, False Value )
// 如果「Test Condition」为真,则取「True Value」的值,否则取「False Value」的值
select IF(1=1,'TRUE','FALSE') as IF_CONDITION_TEST;
-- TRUE

COALESCE( value1,value2,… )
// 获取参数列表中的首个非空值,若均为NULL,则返回NULL
select coalesce(null,'a',null,'b');
-- a
CASE Statement
// 与其他语言中case语法相似,根据实际情况取实际的值,适用于多条件比较的情况
select case x 
 when 1 then 'one'
 when 2 then 'two'
 when 0 then 'zero'
 else 'out of range'
end
from
(select 3 as x
)t
;
-- out of range
2、Hive中如何获取今天的日期?是星期几?是一年中的第几周?
select current_timestamp,
from_unixtime(unix_timestamp()) as `from_unixtime(unix_timestamp())`,

current_date,
to_date(from_unixtime(unix_timestamp())) as `to_date(from_unixtime(unix_timestamp()))`
;
-- 2019-05-21 20:30:05.591 2019-05-21 20:30:06 2019-05-21 2019-05-21
-- 即,一般情况下使用 current_timestamp 和 current_date 即可
SELECT current_date AS `date`,
       CASE date_format(current_date,'u')
           WHEN 1 THEN 'Mon'
           WHEN 2 THEN 'Tues'
           WHEN 3 THEN 'Wed'
           WHEN 4 THEN 'Thu'
           WHEN 5 THEN 'Fri'
           WHEN 6 THEN 'Sat'
           WHEN 7 THEN 'Sun'
END AS day_of_week
;
-- 2019-05-21 Tues

&

select weekofyear(current_timestamp); -- 21
3、Hive中的数据类型转换

同Java语言一样,Hive也包括隐式转换(implicit conversions)和显式转换(explicitly conversions)。

任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都可以隐式地转换成DOUBLE;是的你没看错,STRING也可以隐式地转换成DOUBLE!但是你要记住,BOOLEAN类型不能转换为其他任何数据类型!

CAST的语法为:

cast(value AS TYPE)

如果转换失败,结果则会返回NULL。

4、Hive中如何获取本周、本月的第一天?

方法一:写 UDF 实现一个 function 供调用,这种最简单直接快速

方法二:用 SQL 进行日期函数的增减
在Hive SQL中如何获取本月的第一天?(date_format函数的第二个参数为’d’表示Day in month,即当月的第几天)
hive> select date_sub(current_date, cast(date_format(current_date,'d') as INT)) as month_first;

在Hive SQL中如何获取本周的第一天?(date_format函数的第二个参数为’u’表示Day number of week,即当周的第几天)
hive> select date_sub(current_date, cast(date_format(current_date,'u') as INT)) as week_first;

5、Hive中常用的正则表达式

1. 校验密码强度
密码的强度必须是包含大小写字母和数字的组合,不能使用特殊字符,长度在8-10之间。

^(?=.*\\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$

2. 校验中文
字符串仅能是中文。

^[\\u4e00-\\u9fa5]{0,}$

3. 由数字、26个英文字母或下划线组成的字符串

^\\w+$

4. 校验E-Mail 地址
同密码一样,下面是E-mail地址合规性的正则检查语句。

[\\w!#$%&'*+/=?^_`{|}~-]+(?:\\.[\\w!#$%&'*+/=?^_`{|}~-]+)*@(?:[\\w](?:[\\w-]*[\\w])?\\.)

5. 校验身份证号码

下面是身份证号码的正则校验。15 或 18位。(根据实际情况来看,符合身份证号码格式的可能会很多,但是,能通过身份证格式校验的就很少,所以还需要借助下面的身份证格式校验功能进行进一步的验证才行)

15位:

^[1-9]\\d{7}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])\\d{3}$

18位:

^[1-9]\\d{5}[1-9]\\d{3}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])\\d{3}([0-9]|X)$

6. 校验日期
“yyyy-mm-dd“ 格式的日期校验,已考虑平闰年。

^(?:(?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])

7. 校验手机号
下面是国内 13、15、18开头的手机号正则表达式。

^(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}$
6、Hive中如何进行身份证字符串格式的合法性校验
-- Hive 18位身份证号码验证(需要用「不符合规范」的身份证号码进行测试才会有结果,正确的号码查询结果为空)

select * from
(select trim(upper('440102198001021231')) idcard) t1
where
-- 号码位数不正确
length(idcard) <> 18 

-- 省份代码不正确
or substr(idcard,1,2) not in 
('11','12','13','14','15','21','22','23','31',
'32','33','34','35','36','37','41','42','43',
'44','45','46','50','51','52','53','54','61',
'62','63','64','65','71','81','82','91') 

-- 身份证号码的正则表达式判断
or (if(pmod(cast(substr(idcard, 7, 4) as int),400) = 0 or (pmod(cast(substr(idcard, 7, 4) as int),100) <> 0 and pmod(cast(substr(idcard, 7, 4) as int),4) = 0), -- 闰年
if(idcard regexp '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9X]$',1,0),
if(idcard regexp '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9X]$',1,0)
)) = 0

-- 校验位不正确
or substr('10X98765432',pmod(
(cast(substr(idcard,1,1) as int)+cast(substr(idcard,11,1) as int))*7
+(cast(substr(idcard,2,1) as int)+cast(substr(idcard,12,1) as int))*9
+(cast(substr(idcard,3,1) as int)+cast(substr(idcard,13,1) as int))*10
+(cast(substr(idcard,4,1) as int)+cast(substr(idcard,14,1) as int))*5
+(cast(substr(idcard,5,1) as int)+cast(substr(idcard,15,1) as int))*8
+(cast(substr(idcard,6,1) as int)+cast(substr(idcard,16,1) as int))*4
+(cast(substr(idcard,7,1) as int)+cast(substr(idcard,17,1) as int))*2
+cast(substr(idcard, 8,1) as int)*1
+cast(substr(idcard, 9,1) as int)*6
+cast(substr(idcard,10,1) as int)*3,11)+1,1) 
<> cast(substr(idcard,18,1) as int)
;
参考链接:

=END=

声明: 除非注明,ixyzero.com文章均为原创,转载请以链接形式标明本文地址,谢谢!
https://ixyzero.com/blog/archives/4453.html

《Hive SQL学习整理_5》上的15个想法

  1. Hive常用函数大全一览
    https://www.iteblog.com/archives/2258.html
    `
    1 关系运算
      1.1、等值比较: =
      1.2、不等值比较:
      1.3、小于比较: <
      1.4、小于等于比较:
      1.6、大于等于比较: >=
      1.7、空值判断: IS NULL
      1.8、非空判断: IS NOT NULL
      1.9、LIKE比较: LIKE (字符”_”表示任意单个字符,而字符”%”表示任意数量的字符)
      1.10、JAVA的LIKE操作: RLIKE (JAVA正则表达式的正则语法)
      1.11、REGEXP操作: REGEXP
    2 数学运算
      2.1、加法操作: +
      2.2、减法操作: –
      2.3、乘法操作: *
      2.4、除法操作: /
      2.5、取余操作: %
      2.6、位与操作: &
      2.7、位或操作: |
      2.8、位异或操作: ^
      2.9、位取反操作: ~
    3 逻辑运算
      3.1、逻辑与操作: AND
      3.2、逻辑或操作: OR
      3.3、逻辑非操作: NOT
    4 数值计算
      4.1、取整函数: round(double a)
      4.2、指定精度取整函数: round(double a, int d)
      4.3、向下取整函数: floor
      4.4、向上取整函数: ceil
      4.5、向上取整函数: ceiling
      4.6、取随机数函数: rand (返回一个0到1范围内的随机数)
      4.7、自然指数函数: exp (返回自然对数e的a次方)
      4.8、以10为底对数函数: log10
      4.9、以2为底对数函数: log2
      4.10、对数函数: log
      4.11、幂运算函数: pow
      4.12、幂运算函数: power
      4.13、开平方函数: sqrt
      4.14、二进制函数: bin
      4.15、十六进制函数: hex
      4.16、反转十六进制函数: unhex
      4.17、进制转换函数: conv
      4.18、绝对值函数: abs
      4.19、正取余函数: pmod(int a,int b) (返回正的a除以b的余数)
      4.20、正弦函数: sin
      4.21、反正弦函数: asin
      4.22、余弦函数: cos
      4.23、反余弦函数: acos
      4.24、返回原数据的函数: positive
      4.25、取负数的函数: negative
    5 日期函数
      5.1、UNIX时间戳转日期函数: from_unixtime
      5.2、获取当前UNIX时间戳函数: unix_timestamp
      5.3、日期转UNIX时间戳函数: unix_timestamp
      5.4、指定格式日期转UNIX时间戳函数: unix_timestamp
      5.5、日期时间转日期函数: to_date
      5.6、日期转年函数: year
      5.7、日期转月函数: month
      5.8、日期转天函数: day
      5.9、日期转小时函数: hour
      5.10、日期转分钟函数: minute
      5.11、日期转秒函数: second
      5.12、日期转周函数: weekofyear
      5.13、日期比较函数: datediff
      5.14、日期增加函数: date_add
      5.15、日期减少函数: date_sub
    6 条件函数
      6.1、If函数: if
      6.2、非空查找函数: COALESCE
      6.3、条件判断函数: CASE
      6.4、条件判断函数: CASE
    7 字符串函数
      7.1、字符串长度函数: length
      7.2、字符串反转函数: reverse
      7.3、字符串连接函数: concat
      7.4、带分隔符字符串连接函数: concat_ws
      7.5、字符串截取函数: substr,substring
      7.6、字符串截取函数: substr,substring
      7.7、字符串转大写函数: upper,ucase
      7.8、字符串转小写函数: lower,lcase
      7.9、去空格函数: trim
      7.10、左边去空格函数: ltrim
      7.11、右边去空格函数: rtrim
      7.12、正则表达式替换函数: regexp_replace
      7.13、正则表达式解析函数: regexp_extract
      7.14、URL解析函数: parse_url
      7.15、json解析函数: get_json_object
      7.16、空格字符串函数: space
      7.17、重复字符串函数: repeat
      7.18、首字符ascii函数: ascii
      7.19、左补足函数: lpad
      7.20、右补足函数: rpad
      7.21、分割字符串函数: split
      7.22、集合查找函数: find_in_set
    8 集合统计函数
      8.1、个数统计函数: count
      8.2、总和统计函数: sum
      8.3、平均值统计函数: avg
      8.4、最小值统计函数: min
      8.5、最大值统计函数: max
      8.6、非空集合总体变量函数: var_pop
      8.7、非空集合样本变量函数: var_samp
      8.8、总体标准偏离函数: stddev_pop
      8.9、样本标准偏离函数: stddev_samp
      8.10、中位数函数: percentile
      8.11、中位数函数: percentile
      8.12、近似中位数函数: percentile_approx
      8.13、近似中位数函数: percentile_approx
      8.14、直方图: histogram_numeric
    9 复合类型构建操作
      9.1、Map类型构建: map
      9.2、Struct类型构建: struct
      9.3、array类型构建: array
    10 复杂类型访问操作
      10.1、array类型访问: A[n]
      10.2、map类型访问: M[key]
      10.3、struct类型访问: S.x
    11 复杂类型长度统计函数
      11.1、Map类型长度函数: size(Map)
      11.2、array类型长度函数: size(Array)
      11.3、类型转换函数
    `

  2. Apache Hive 内置函数(Builtin Function)列表
    https://www.iteblog.com/archives/2032.html
    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
    `
    本文所列的 Hive 函数均为 Hive 内置的,共计294个,Hive 版本为 3.1.0。

    26 array_contains
    27 ascii
    33 base64
    36 bloom_filter
    43 char_length
    44 character_length
    47 collect_list
    48 collect_set
    50 concat
    51 concat_ws
    62 current_authorizer
    63 current_database
    64 current_date
    65 current_groups
    66 current_timestamp
    67 current_user
    69 date_format
    72 day
    73 dayofmonth
    74 dayofweek
    77 dense_rank
    107 get_json_object
    131 json_tuple
    283 xpath
    284 xpath_boolean
    287 xpath_int
    291 xpath_string
    `

  3. Hive中如何获取前几天、后几天的日期?(Date Functions in Hive)
    https://www.folkstalk.com/2011/11/date-functions-in-hive.html
    `
    hive> select current_date();
    2012-12-08

    13、日期比较函数: datediff
    语法: datediff(string enddate, string startdate)
    返回值: int
    说明: 返回结束日期减去开始日期的天数。

    hive> select datediff(‘2012-12-08′,’2012-05-09’);
    213

    14、日期增加函数: date_add
    语法: date_add(string startdate, int days)
    返回值: string
    说明: 返回开始日期startdate增加days天后的日期。

    hive> select date_add(‘2012-12-08’,10);
    2012-12-18

    15、日期减少函数: date_sub
    语法: date_sub(string startdate, int days)
    返回值: string
    说明: 返回开始日期startdate减少days天后的日期。

    hive> select date_sub(‘2012-12-08’,10);
    2012-11-28
    `

  4. 在Hive SQL中如何获取本月、本周的第一天的日期?
    https://www.iteblog.com/archives/2032.html#date_format
    https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
    `
    # 方法一:写 UDF 实现一个 function 供调用,这种最简单直接快速

    # 方法二:用 SQL 进行日期函数的增减

    # 在Hive SQL中如何获取本月的第一天?(date_format函数的第二个参数为’d’表示Day in month,即当月的第几天)
    hive> select date_sub(current_date, date_format(current_date,’d’)) as month_first;

    # 在Hive SQL中如何获取本周的第一天?(date_format函数的第二个参数为’u’表示Day number of week,即当周的第几天)
    hive> select date_sub(current_date, date_format(current_date,’u’)) as week_first;
    `

  5. 【hive 日期转换】Hive中yyyymmdd和yyyy-mm-dd日期之间的切换
    https://blog.csdn.net/u013421629/article/details/80068090
    `
    方法1: from_unixtime + unix_timestamp
    — 20190626转成2019-06-26
    select from_unixtime(unix_timestamp(‘20190626′,’yyyymmdd’),’yyyy-mm-dd’);

    — 2019-06-26转成20190626
    select from_unixtime(unix_timestamp(‘2019-06-26′,’yyyy-mm-dd’),’yyyymmdd’);
    `

    Hive中yyyymmdd和yyyy-mm-dd日期之间的切换
    https://blog.csdn.net/yuxeaotao/article/details/86073179

  6. Hive条件判断
    https://blog.csdn.net/u012378570/article/details/62216722
    `
    hive中可能会遇到根据判断不同值,产生对应结果的场景,有三种实现方式:

    IF( Test Condition, True Value, False Value )
    — if(max(un_wifi_name_cnt)>10, ”, concat_ws(‘#’,collect_set(un_wifi_name))) as un_wifi_names

    COALESCE( value1,value2,… )

    CASE Statement
    case
    when max(un_wifi_name_cnt)>10 then ”
    else concat_ws(‘#’,collect_set(un_wifi_name))
    end as un_wifi_names
    `

  7. hive函数总结
    https://www.cnblogs.com/yejibigdata/p/6380744.html
    `
    substring_index(string A, string delim, int count)
    # 截取第count分隔符之前的字符串,如count为正则从左边开始截取,如果为负则从右边开始截取

    select
    raw,
    substring_index(raw, ‘_’, -1) as item,
    raw2,
    substring_index(raw2, ‘_’, -1) as item2
    from
    (select
    ‘1_2_3’ as raw,
    “x1_x2_null” as raw2
    )tmp
    ;
    /*
    1_2_3 3 x1_x2_null null
    */
    `
    Hive中自定义实现Mysql的substring_index函数
    https://blog.csdn.net/weixin_43215250/article/details/85233967

  8. 身份证号码的秘密
    https://mp.weixin.qq.com/s/yYuS5_2obt50kH4yrLg1Jw
    https://zh.wikipedia.org/wiki/%E4%B8%AD%E5%8D%8E%E4%BA%BA%E6%B0%91%E5%85%B1%E5%92%8C%E5%9B%BD%E5%B1%85%E6%B0%91%E8%BA%AB%E4%BB%BD%E8%AF%81
    `
    身份证号码组成
    身份号码由17位数字本体码和1位校验码组成,校验码的作用是为了防止在填写身份证号码时出错而设计的。

    前6位为所在地区(市、镇、区)的行政区划代码,接着后面8位是年月日生日 ,顺序码 3位,防止同一个地方同一天出生的人重号,第17位奇数代表男偶数代表女。校验码1位(0-9和 x),用来校验身份证号码正确,通过前17位计算模11得出来的,就是0-10 这11个数字,其中10用x表示。

    身份证校验码计算方法如下:
    将身份证号码前17位数分别乘以不同的系数,从第一位到第十七位的系数分别为:7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2。将这17位数字和系数相乘的结果相加。用加出来和除以11,取余数。余数只可能有0-1-2-3-4-5-6-7-8-9-10这11个数字。其分别对应的最后一位身份证的号码为1-0-X-9-8-7-6-5-4-3-2。如果余数是2,身份证的最后一位号码就是罗马数字x。如果余数是10, 最后一位就是2。
    `

    最安全,最快速的纯前端图片加水印,拒绝上传保证个人信息安全。
    http://watermark.dxcweb.com/
    https://github.com/dxcweb/watermark

    居民身份证查询验证
    http://www.ip33.com/shenfenzheng.html

  9. hive中with…as…的用法
    https://www.jianshu.com/p/d518e9f5d5f9
    `
    — with table_name as(子查询语句) 其他sql

    with temp as (
    select * from xxx
    )
    select * from temp;
    `
    Hive中使用 with as 优化SQL
    https://blog.csdn.net/Abysscarry/java/article/details/81322669
    `
    with as 也叫做子查询部分,首先定义一个sql片段,该sql片段会被整个sql语句所用到,为了让sql语句的可读性更高些,作为提供数据的部分,也常常用在union等集合操作中。

    with as就类似于一个视图或临时表,可以用来存储一部分的sql语句作为别名,不同的是with as 属于一次性的,而且必须要和其他sql一起使用才可以!

    其最大的好处就是适当的提高代码可读性,而且如果with子句在后面要多次使用到,这可以大大的简化SQL;更重要的是:一次分析,多次使用,这也是为什么会提供性能的地方,达到了“少读”的目标。
    `
    hive中使用with as查询作为临时中间表
    https://www.cnblogs.com/30go/p/10116815.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注