1. locate

判断 name 字段中是否包含字符串 “aaa”：

select * from temp where locate("aaa", name) > 0;

ocate(字符串，字段名)，如果包含，返回大于0的index位置；否则，返回0；

2.in

select * from temp where event_day in ('20201212', '20201213');

3. 不等于

hive 中‘不等于’不管是用！ 或者<>符号实现，都会将空值即null过滤掉，此时要用：

where （white_level<>'3' or  white_level is null）

或者

where (white_level!='3' or white_level is null )

来保留null 的情况。

4.计算字符串长度

select * from temp where length(item)=7;

5.计算uv

SELECT os_name, COUNT(DISTINCT cuid) as uv
FROM user_action_log
WHERE event_day = '20160901'
    AND os_name in ('android', 'iphone')
GROUP BY os_name

6. if

语法：

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

返回值T

说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

举例：

SELECT if(val1 is not null and val1 <> '', val2, val3) as val
FROM user_action_log

7. get_json_object

（1）最简单的形式

语法：

get_json_object(json_string, '$.key')

说明：解析json的字符串json_string,返回以’key’为key对应的value。如果输入的json字符串无效，那么返回NULL。这个函数每次只能返回一个数据项。

举例：

select get_json_object('{"key1":"val1","key2":"val2","key3":"val3","key4":"val4"}',"$.key1");

结果：

val1

（2）json里有列表的情况

  • 拿到列表里第一个item的"NAME"
SELECT get_json_object('[{"NAME":"张三","ID":"1"},{"NAME":"李四","ID":"2"}]','$[0].NAME');

结果：

"张三"
  • 拿到列表里所有item的"NAME"
SELECT get_json_object('[{"NAME":"张三","ID":"1"},{"NAME":"李四","ID":"2"}]','$[].NAME');

结果：

["张三", "李四"]
  • 既拿到名字又拿到年龄
select  get_json_object('{"name":"zhangsan","age":18}','$.name'), get_json_object('{"name":"zhangsan","age":18}','$.age');


8. regexp_replace

语法：

regexp_replace(target_item, a, b)

说明：将target_item中的a替换成b

举例：

regexp_replace(target_item, '\n', '')

将target_item中的换行符去掉

9. 数据抽样

（1）数据块抽样

  • tablesample(n percent)
    根据hive表数据的大小按比例抽取数据，并保存到新的hive表中。如：抽取原hive表中10%的数据
    （注意：测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或使用随机抽样解决）
create table xxx_new 
as 
  select * 
  from xxx 
  tablesample(10 percent)
  • tablesample(n M)
    指定抽样数据的大小，单位为M。
  • tablesample(n rows)
    指定抽样数据的行数，其中n代表每个map任务均取n行数据，map数量可通过hive表的简单查询语句确认（关键词：number of mappers: x)

（2）分桶抽样

hive中分桶其实就是根据某一个字段Hash取模，放入指定数据的桶中，比如将表table_1按照ID分成100个桶，其算法是hash(id) % 100，这样，hash(id) % 100 = 0的数据被放到第一个桶中，hash(id) % 100 = 1的记录被放到第二个桶中。创建分桶表的关键语句为：CLUSTER BY语句。

分桶抽样语法：

TABLESAMPLE (BUCKET x OUT OF y [ON colname])

其中x是要抽样的桶编号，桶编号从1开始，colname表示抽样的列，y表示桶的数量。
例如：将表随机分成10组，抽取其中的第一个桶的数据

select * 
from table_01 
tablesample(bucket 1 out of 10 on rand())

（3）随机抽样（rand()函数）

  • 使用rand()函数进行随机抽样
    limit关键字限制抽样返回的数据，其中rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的，案例如下：
select * 
from table_name 
where col=xxx 
distribute by rand() 
sort by rand() 
limit num;
  • 使用order 关键词
    案例如下：
select * 
from table_name 
where col=xxx 
order by rand() 
limit num;

参考