Hive查询语句

Hive查询语法

1. SELECT .. FROM

   CREATE TABLE emplyees(
   name STRING,
   addr STUCT<street:STRING>,
   edu MAP(STRING,INT),
   sub ARRAY<STRING>
   ....
   )
   MAP 字段使用
   field["key"]
   STRUCT 字段使用
   field.key
   ARRAY 字段使用 数组索引
   field[index] 
   index =[0,1...]

• 正则表达式列选择
  price.*

2. 计算函数

• 算术运算符
  • +-*/ 数值
  • A%B 取余
  • A&B 与
  • A|B 或
  • A^B 亦或
  • ~A 非
• 数学函数
  • round() 取整
  • round(d,n) 保留n位
  • floor() 向上取整
  • ceil() 向下取整
  • rand() Double 型随机数
  • exp() 幂次方
  • hex()
  • unhex()
  • …..
• 聚合函数(对多行进行计算，得到一个结果值)
  • count() count(col) count(DISTINCT col1)
  • sum()
  • avg()
  • min()
  • max()
  • variance() 方差
  • var_samp() 样本方差
  • stddev_pop() 标准偏差
  • stddev_samp() 标准样本偏差
  • covar_pop(col1,col2) 协方差
  • covar_samp(col1,col2) 样本协方差
  • corr(col1,col2) 相关系数
  • percentile(int_expr,p)
  • collect_set()
  • collect_list()
    备注设置hive.map.aggr=true 提高聚合性能
• 表生成函数(单列扩展成多列或多行)
  • explode(Array array) 返回多行结果
  • explode(Map map) 返回0到多行结果，每行对应每个map键值对
  • inline()结构体数组提取出来并插入表中
  • json_tuple(STRING,p1)
  • parse_url_tuple() parse_url_tuple(url, ‘HOST’, ‘PATH’,’QUERY’, ‘QUERY:id’)
    -stack(int r) M列转换成N行

lateral view Lateral View一般与用户自定义表生成函数（如explode()）结合使用。

• 其他函数
  • ascii()
  • base64()
  • binary()
  • cast( AS FLOAT)
  • concat() 字符串拼接
  • concat_ws(separator,s1,s2)
  • decode(BINARY,charset)
  • encode()
  • find_in_set(s,comma)
  • format_number()
  • get_json_object(string json_string, string path) 示例get_json_object(data, ‘$.owner’)
  • in
  • in_file()
  • instr() 字符串第一次出现的地方
  • length()
  • locate(substr,str)
  • lower()
  • lpad(s,len,pad)
  • ltrim() 去空格
  • ngrams() 估算文件前K个字尾
  • parse_url() parse_url(‘http://facebook.com/path/p1.php?query=1', ‘HOST’)
  • printf(format,args)
  • regexp_extract(subject,regex_pattern,index)
  • regexp_replace(s,regex,replacement)
  • repeat(s,n)
  • reverse()
  • rpad()
  • rtrim()
  • sentence() 字符串转换成句子数组
  • size(MAP<k,v>)
  • size(ARRAY)
  • space(n) 返回n个空格
  • split(s,pattern)
  • str_to_map(s,delim1,delim2)
  • substr(s,start_index)
  • translate()
  • trim()
  • unbase64()
  • upper()
  • from_unixtime(timestamp,format) 时间戳转换成UTC时间
  • unix_timestamp() 本地时区下的当前时间戳
  • unix_timestamp(“2021-01-01 11:11:11”) 转换成时间戳
  • to_date()返回时间字符串日期部分
  • year()
  • month()
  • day()
  • hour()
  • minute()
  • second()
  • weekofyear()
  • date_diff()
  • date_add(startdate,days)
  • date_sub(startdate,days)
  • from_utc_timestamp()
  • to_utc_timestamp()
  • round(unix_timestamp()-unix_timestamp(),2) 时间差计算
  • current_timestamp()
  • current_date()

3. LIMIT 语句 限制返回行数
4. 列别名 as
5. 嵌套SELECT语句

   e SELECT e.name WHERE e.name

   6. CASE WHEN 语句

CASE
WHEN 条件 THEN 结果
ELSE default
END
`

7. WHERE 语句 用于条件过滤

8.谓词操作符 可用于JOIN..ON 和HAVING

• A=B
• A<=>B 都为NULL 返回TRUE
• A<>B,A!=B
• A<B
• A<=B
• A>B
• A>=B
• A [NOT] BETWEEN B AND C
• A IS [NOT] NULL
• A [NOT] LIKE B
• A RLIKE B B是一个正则表达式
  9 LIKE和RLIKE
  10. GROUP BY 语句 按照一个或多个列对结果进行分组

11. HAVING 语句 允许用户通过一个简单的语法完成原本需要通过子查询才能 对GROUP BY 语句产生的分组进行条件过滤任务

12. JOIN 语句 只支持等值连接

    • INNER JOIN 只有进行连接的两个表都存在与连接标准相匹配的数据才会被保存下来
    • LEFT JOIN
    • LEFT OUTER JOIN
    • RIGHT JOIN
    • RIGHT OUTER JOIN 返回右表所有符合WHERE语句的记录，左表中匹配不上的字段值用NULL代替
    • FULL OUTER JOIN 返回所有表中符合WHERE语句条件的所有记录
    • LEFT SEMI JOIN： 并不拼接两张表，两个表对 on 的条件字段做交集，返回前面表的记录，相较于其他的方法，这样子 hive 处理速度比较快
    • CROSS JOIN： 返回两个表的笛卡尔积结果（数目为左表乘右表），不需要指定关联键
    • map-side JOIN 大表Join小表，小表放左边

      set hive.auto.convert.join=true;
      属性配置文件$HOME/.hiverc

13. ORDER BY 和 SORT BY
    ORDER BY 一个所有的数据都通过一个reducer进行处理的过程(全局有序)
    SORT BY 只会在每个reducer中数据进行排序，局部排序

14. DISTRIBUTE BY 控制map的输出在reducer中如何划分，将相同的key记录分发到同一个reducer中进行处理。

15. CLUSTER BY 等于DISTRIBUTE BY 和SORT BY 简化版

16. 类型转换 cast()函数 cast( value AS TYPE)

17. 抽样查询

    • TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand())
    • 百分比抽样 TABLESAMPLE(0.1 PERCENT)

18. UNION ALL 与 UNION 区别
    多个表进行合并 UNION 返回去重后结果，UNION ALL 返回所有结果

赏

谢谢你请我喝可乐

支付宝

微信