Hive字符串和时间处理函数(二）

1.空值的处理

nvl 
COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值
COALESCE(case when( three_grade  =='' or three_grade =='-') then NULL else three_grade  end,
     case when( second_grade =='' or second_grade=='-') then NULL else second_grade end,
     case when( top_grade    =='' or top_grade   =='-') then NULL else top_grade 
     end) as  student_grade

2.concat与concat_ws区别

 注意不同的SQL的处理情况
 CONCAT(VARCHAR var1, VARCHAR var2, ...)
-- CONCAT_WS(VARCHAR separator, VARCHAR var1, VARCHAR var2, ...)

select concat('a','b') as size,concat('a',NULL) 
--查询出结果为：ab null
-- SparkSQL  concat中有一个参数为NULL，则查出来的数据为NULL
-- FlinkSQL  任一参数为NULL，跳过该参数

select concat_ws('','a','b') ,concat_ws('','a',NULL),concat_ws(NULL,'a','b')
--查询出结果为：ab  a
-- CONCAT_WS()表示连接之用分隔符. 第一个参数是分隔符,如果separato分隔符是NULL，则结果为NULL。
-- 其余的参数为NULL，则会忽略
-- FlinkSQL  当separator取值为null，则将separator视作空串进行拼接

3.split

-- split 返回值为一个数组
--查询数组的个数：
   select size(split('TEST0400|TEST0401|TEST0402|TEST0403','\\|') )
--注意事项：如果split 内的字符串是空或者null，那么split成数组后，计算数据的长度是1

4.explode

-- explode(ARRAY) 列表中的每个元素生成一行
select explode(split('TEST0400|TEST0401|TEST0402|TEST0403','\\|')) as test_id;

select  examples_id1
from(
select  'TEST0400|TEST0401|TEST0402|TEST0403' as examplestring
) t1
LATERAL VIEW explode(split(t1.examplestring,'\\|')) examples as examples_id1 ;

-- Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题
-- Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据
-- LATERAL VIEW OUTER explode(array())

5.隐式转换和显式转换

在hive中，当一个string类型和int类型比较的时候会出现问题
   数字和数字类型用 可以用 ！= 比较
   带引号的数字和数字类型 可以用！= 比较：
   带引号的数字和带引号数字类型 可以用 ！= 比较：
   字符串和数字类型 不可以用 ！=比较：
  使用的数据类型
  01.不同数据类型进行比较，进行手动数据类型变换  
       and TEST_ID is not null 
       and  cast(TEST_ID as string ) != '' 
       and  cast(TEST_ID as string ) != '-' 
       and  cast(TEST_ID as string ) != '0/0.0'

6.相关函数

encode  decode
base64  unbase64
ascii
binary
instr()    instr(string str, string substr) 
 返回值   int
 功能 查找字符串str中子字符串substr出现的位置，
     如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的
locate()
translate
sentences 字符串str将被转换成单词数组

7.日期的函数

01. date_sub  date_diff 其中string的格式有要求 date_sub(current_date,7)
02.分区表分区字段是日期类型时限制分区条件使用 to_unix_timestamp 方法，如果使用 unix_timestamp 方法仍会扫全表
03-- 时间格式变换 格式化日期格式 
   yyyy-MM-dd --> yyyyMMdd    select current_date,date_format(current_date,'yyyyMMdd')
   yyyyMMdd   --> yyyy-MM-dd  select '20191010',from_unixtime(unix_timestamp('20191010','yyyyMMdd'),'yyyy-MM-dd')

8.with as

--  with as查询作为临时中间表,（会把查询的表数据放到内存中，供其他查询随时使用）
--  with as 最后必须跟sql语句结束，不允许单独使用。
--  可以通过with查询来提高查询性能，因为先通过with语法将数据查询到内存，然后后面其它查询可以直接使用

9.Hive存储格式

and  (t1. lect + t1. lart + t1.tord) >0
and  (t1. lect >0 OR t1.lart >0  OR t1.tord >0 )
存储格式 ： SequenceFile RCFile
1. serde  序列化和反序列化的简写
2. WITH serdeProperties

ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
;

 ROW FORMAT SERDE 
      'org.openx.data.jsonserde.JsonSerDe' 
    WITH SERDEPROPERTIES ( 
      'ignore.malformed.json'='true') 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
;

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.RCFileInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
;


ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
;

-- 查看 
desc  extended database_name.table_name_d;

10.需求案例：

      jsonData字符串如下格式
       [{"gradeCD":"UnitN","attrs":["年级"]},{"gradeCD":"CroN","attrs":["个人"]}]
      把中括号里面的东西取出来取出来的是  
        年级     
        个人

  Hive执行的命令
      select  get_json_object(ttt1.colarray,'$.attrs') as parseString,
              regexp_replace( get_json_object(ttt1.colarray,'$.attrs'),'\\[\\"|\\"\\]','') as parName
      from
      ( select tt1.colarray
       from
       (select  split(
           regexp_replace(
              regexp_extract('[{"gradeCD":"UnitN","attrs":["年级"]},{"gradeCD":"CroN","attrs":["个人"]}]',
                                    '^\\[(.+)\\]$',1)
            , '\\}\\,\\{'
            , '\\}\\|\\|\\{' )
         ,'\\|\\|') as parsestr) t1
       lateral view  explode(t1.parsestr) tt1 as colarray )ttt1
      ;
  示例二
       select tt1.colarray,
              tt2.attrs
       from
       (select  split(
           regexp_replace(
              regexp_extract('[{"gradeCD":"UnitN","attrs":["年级"]},{"gradeCD":"CroN","attrs":["个人"]}]',
                                    '^\\[(.+)\\]$',1)
            , '\\}\\,\\{'
            , '\\}\\|\\|\\{' )
         ,'\\|\\|') as parsestr) t1
       lateral view  explode(t1.parsestr) tt1 as colarray 
       lateral view  json_tuple(tt1.colarray,'attrs') tt2 as attrs
      ;
  思路：
    步骤一： 删除字符串前后的[],  提取数据格式是 ： {json},{json}
        {"[{"gradeCD":"UnitN","attrs":["年级"]},{"gradeCD":"CroN","attrs":["个人"]}
    步骤二：将josn字符串中的分隔符代换成||,格式{json}||{json}
        {"[{"gradeCD":"UnitN","attrs":["年级"]}||{"gradeCD":"CroN","attrs":["个人"]}
    步骤三  按||分隔符切割成一个hive数组
       ["{"[{"gradeCD":"UnitN","attrs":["年级"]}","{"gradeCD":"CroN","attrs":["个人"]}"]
    步骤四： 将hive数组转成行，并使用 get_json_object
  附录：
      JSON：JSON对象   JSON数组
       JSON 名称 字符串
       JSON 值 ：数字（整数或浮点数） 字符串（在双引号中） 逻辑值（true 或 false）null
                 数组（在方括号中）  对象（在花括号中）
    json以字符串的方式整个入Hive表
       get_json_object()
       json_tuple() -- 
       get_json_object函数第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；
       json_tuple对象时，其中，需要使用lateral view 视图方法来写，不需要加$标示符读取对象

参考

hive中解析json数组 https://www.cnblogs.com/chenzechao/p/9887542.html

blogroll

social