常用函数
- 计算字段长度：
LENGTH() - 计算array长度：
SIZE()
查询一个a对应多个b的情况
select
a,
collect_list(distinct(b)) as b
from
table
group by
a
查询出现次数最多的一些记录
SELECT keyword, count( * ) AS count
FROM article_keyword
GROUP BY keyword
ORDER BY count DESC
LIMIT 20
查询数据库里是否存在重复数据
SELECT count(*),column1,column2,...columnN
FROM table1
GROUP BY column1,column2,...columnN
HAVING count(*) > 1
模糊查询
-
% ：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
比如 SELECT * FROM [user] WHERE u_name LIKE '%三%' -
_ ： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
比如 SELECT * FROM [user] WHERE u_name LIKE '三' -
[ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三' -
[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'
hive解析json数据
- 普通json数据
str = {"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
select
get_json_object('str', '$.movie') as movie;
...
说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NUll，这个函数每次只能返回一个数据项。
select b.b_movie,b.b_rate,b.b_timeStamp,b.b_uid
from json a lateral view
json_tuple(a.data,'movie','rate','timeStamp','uid') b as b_movie,b_rate,b_timeStamp,b_uid
参数为一组键和json字符串，返回值的元组。该方法比get_json_object高效，因此可以在一次调用中输入多次键。
- json数组
使用Hive的内置的explode函数，explode()接收一个 array或者map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。其可以配合 LATERAL VIEW 一起使用。
SELECT
subview.actor,
get_json_object(subview.actor, '$.score') as score,
get_json_object(subview.actor, '$.name') as name
FROM
a lateral view explode(split(regexp_replace(regexp_replace(actor, '\\[|\\]',''), '\\}\\,\\{', '\\}\\-\\{'), '\\-')) subview as actor
lateral view为侧视图，把某一行数据拆分成多行数据，加上lateral view可以将拆分的单个字段数据与原始表数据关联上。
注意：在使用lateral view的时候需要指定视图别名和生成的新列别名
写入hive表分区
# 往表里加parquet内容
alter table xxx
add if not exists partition (p_date={{ ds_nodash }})
location '/xxx/yyy/p_date={{ ds_nodash }}'"
随机抽取
DISTRIBUTE BY rand(0) --随机打散到不同的节点
sort by rand(0) --每个节点内随机
网友评论