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_idWHEN'1'THEN'理财'WHEN'7'THEN'权益'ELSE'其他'END) AS source_name-
lateral view与explode：列转行
SELECTfund_code,num_label
FROMdmc_fund_rank_info_i_det_dLATERAL VIEW explode(split(perfo_filt_cond,",")) tmp AS num_label-
presto列转行
SELECTuuid,user_pin,data,prob
FROMdmc_model_score_multiply_ltv_smooth_i_det_dCROSS JOIN UNNEST(regexp_extract_all(data, '-?\d+(\.\d+)([eE][+-]?\d+)?')) AS t(prob)
WHEREdt = '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开始，为每一条分组记录返回一个数字
