Hive SQL学习整理_5

=Start=

缘由:

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

正文:

参考解答:
1、Hive中的条件判断
2、Hive中如何获取今天的日期?是星期几?是一年中的第几周?

&

3、Hive中的数据类型转换

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

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

CAST的语法为:

如果转换失败,结果则会返回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之间。

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

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

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

5. 校验身份证号码

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

15位:

18位:

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

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

6、Hive中如何进行身份证字符串格式的合法性校验
参考链接:

=END=

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

《Hive SQL学习整理_5》上有13条评论

  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

发表评论

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