【学习笔记】Hive

Hive 作为数仓应用工具，对比 RDBMS（关系型数据库） 有3个“不能”：

• 不能像 RDBMS 一般实时响应，Hive 查询延时大；

• 不能像 RDBMS 做事务型查询，Hive 没有事务机制；

• 不能像 RDBMS 做行级别的变更操作（包括插入、更新、删除）。

另外，Hive 相比 RDBMS 是一个更“宽松”的世界，比如：

• Hive 没有定长的 varchar 这种类型，字符串都是 string；

• Hive 是读时模式，它在保存表数据时不会对数据进行校验，而是在读数据时校验不符合格式的数据设置为NULL。

查看数据库基本信息 hive (myhive)>desc database myhive2;
查看数据库更多详细信息 hive (myhive)>desc database extended myhive2;

查询表结构  desc score5;

CURRENT_DATE 当前日期

语法: to_date(string timestamp) 返回值: string 说明: 返回日期时间字段中的日期部分。 hive> select to_date('2021-03-28 14:03:01') from tableName; 2021-03-28

语法: date_add(string startdate, int days) 返回值: string 说明: 返回开始日期startdate增加days天后的日期。 hive> select date_add('2020-12-08',10) from tableName; 2020-12-18

语法: date_sub (string startdate, int days) 返回值: string 说明: 返回开始日期startdate减少days天后的日期。 hive> select date_sub('2020-12-08',10) from tableName; 2020-11-28

• 子查询需要加上别名 否则报错cannot recognize input near 'xx' 'xx' 'xx'

select * from (select * from table) ;

• 解析json格式

Hive   get_json_object(column_name,'$.first_level_key_name.second_level_key_name')

Presto   json_extract_scalar(json,'$.book')

• 字符串操作

字符串拼接：CONCAT(string A, string B…)

指定分割字符的拼接：CONCAT_WS(separator, str1, str2,...)

判断子串在字符串中第一次出现的位置：instr(str,substr) 从1开始计数，如果substr在str中不存在则返回0

字符串截取：

substr(string A, int start) substring(string A, int start) 返回字符串A从start位置（从1计数）到结尾的字符串

substr(string A, int start, int len),substring(string A, intstart, int len) 返回字符串A从start位置开始，长度为len的字符串

拼接一列中的字符串：concat_ws('_',collect_set(col1)) presto代替方法：array_join(array_distinct(array_agg(order_id)), ',')

• 正则替换   regexp_replace(get_json_object(param_json, '$.par'), '\n', '') AS query

• 正则匹配   regexp_extract_all(data, '-?\d+(.\d+)(eE?\d+)?')

• sql查询含有换行符‘\n’的字符串导出的结果会有问题，字段实际上并不是空

原因：hive将'\n'换行符识别成结束符，导致一条数据分成多行

解决方法：替换掉'\n'换行符

• 批量插入数据

insertoverwritetableapp.jr_search_app_fund_zfb_tag_f_d PARTITION(dt ='{zfb_dt_str}')
select'004685','金元顺安元启','季季新高,持有体验极佳'UNIONALL
select'002910','易方达供给改革','业绩常红,3年2倍'

• 类型转换cast

• cast(字段名 as 转换的类型 )，其中类型可以为：CHAR[(N)] 字符型、DATE 日期型、DATETIME 日期和时间型、DECIMAL float型、TIME 时间型

• case-when用法

(
        CASE source_id
            WHEN'1'THEN'理财'
            WHEN'7'THEN'权益'
            ELSE'其他'
        END) AS source_name

• lateral view与explode：列转行

SELECT
    fund_code,
    num_label
FROM
    dmc_fund_rank_info_i_det_d
    LATERAL VIEW explode(split(perfo_filt_cond,",")) tmp AS num_label

• presto列转行

SELECT
    uuid,
    user_pin,
    data,
    prob
FROM
    dmc_model_score_multiply_ltv_smooth_i_det_d
    CROSS JOIN UNNEST(regexp_extract_all(data, '-?\d+(\.\d+)([eE][+-]?\d+)?')) AS t(prob)
WHERE
    dt = '2024-01-08'
    AND user_pin = 'xxx'

• with as用法：代替嵌套的子查询

with a as (select * from test)

select * from a;

• if用法

IFNULL：判断第一个表达式是否为NULL，如果为NULL则返回第二个参数的值，如果不为NULL则返回第一个参数的值。

IF(expr1 , expr2 , expr3)：expr1的值为TRUE，则返回值为expr2；expr1的值为FALSE，则返回值为expr3

• ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)函数：row_number()从1开始，为每一条分组记录返回一个数字