hive学习之二：hive sql使用总结及遇到的问题

---------------------------hive sql使用总结-------------------------------------------------
1.hive在连接中不支持不等值连接，不支持or，where条件后不支持子查询。分别举例如下及实现解决办法。
  1.1.不支持不等值连接
       错误:select * from a inner join b on a.id<>b.id
       替代方法:select * from a inner join b on a.id=b.id and a.id is null;
  1.2.不支持or
       错误:select * from a inner join b on a.id=b.id or a.name=b.name
       替代方法:select * from a inner join b on a.id=b.id
                union all
                select * from a inner join b on a.name=b.name
  1.3.where后不支持子查询
       错误：select * from a where a.id in (select id from b)
       替代方法:select * from a inner join b on a.id=b.id 或in的另外一种高效实现left-semi join
       select a.* from a left-semi join b on a.id=b.id a的id在b的id中，但是只能select a表中的字段，不能把b表中的字段查出来。另外right join 和full join
       不支持这种写法。
  1.4.两个sql union all的字段名必须一样或者列别名要一样。
2.hive不支持where条件后的列别名
  错误:select sum(a.amt) as total from a where a.total>20
  替代方法:select total from (select sum(a.amt) as total from a) a where total>20
  这种方式下例外:select id,count(*) as amt from a where dt='20160101' group by id having amt>1
3.谓词前推
  数据量提前过滤，加分区过滤。
4.计算日期间相隔天数，函数datediff要求日期格式为10位，如2012-10-12
5.为了提高hive sql的执行效率可以在执行hql前设置性能参数，只针对当前会话有效。如
  set hive.auto.convert.join=false;
  set hive.ignore.mapjoin.hint=false;
  set hive.exec.parallel=true;
  set hive.exec.parallel.thread.number=16;
  set hive.groupby.skewindata=true;
  set mapred.reduce.tasks=30;
6.按模式匹配字符串取其中的部分。
  regexp_extract(a.ed_logo,'(.*)(.0)',1) --字段ed_logo按正则表达式'(.*)(.0)'匹配， 匹配后取第一个部分。如187.0，得187
7.hive排序
  row_number() over (distribute by ED_APPLICATION_ID sort by ED_CARD_SEQ desc)，按字段ED_APPLICATION_ID分组，按ED_CARD_SEQ降序排序。distribute也可用partition,sort by也可以用
  order by。
  三种排序及例子:
  row_number() ---
  rank()          |+ distribute by field1 sort by field2 [desc|asc]     
  dense_rank()----
  表结果及值如下:
  hive> desc mid;
  OK
  id                   string                                  
  value                string 
 hive> select * from mid;
 OK
 1001 12
 1001 13
 1001 14
 1001 12
 1002 13
 1003 14
 1004 15
 
 select *,row_number() over(distribute by id sort by value) as rank from mid;
 1001 12 1
 1001 12 2
 1001 13 3
 1001 14 4
 1002 13 1
 1003 14 1
 1004 15 1
 row_number()--重复项依然会递增编号
 
 select *,dense_rank() over(distribute by id sort by value) as rank from mid;
 1001 12 1
 1001 12 1
 1001 13 2
 1001 14 3
 1002 13 1
 1003 14 1
 1004 15 1
 dense_rank()--重复项编号相同
 
 select *,rank() over(partition by id sort by value) as rank from mid;
 1001 12 1
 1001 12 1
 1001 13 3
 1001 14 4
 1002 13 1
 1003 14 1
 1004 15 1
 rank()----重复项编号相同，但是后续会跳过一个编号。

8.collect_set(field)[index]--去重
  collect_list(field)[index]--不去重

  select id,collect_set(value)[0] from mid group by id;
  1001 14
  1002 13
  1003 14
  1004 15
  目测去重后从大到小放入集合
 
  select id,collect_list(value)[0] from mid group by id;
  1001 12
  1002 13
  1003 14
  1004 15
 
9.在sql中使用udf，
  定义类继承udf类，重写evaluate方法，该方法支持重载，打成jar包，使用如下:
  add jar /home/myudf.jar;
  create temporary function as myfunction as 'com.comp.udf.UDFClass';
  select myfunction(args) from table_name
例子如下：
package com.huateng.spdbccc.mkt24.udf;

import org.apache.hadoop.hive.ql.exec.UDF;

public class FieldLengthFill extends UDF{
 
 /**
  * 字段长度填充，如账号acctno，不够10位填充10位，以空格补充
  * @param field 要填充的字段名
  * @param value 字段值
  * @return 填充后的值
  */
 public static String evaluate(String field,String value){
  if("acctno".equals(field)){
   if(value==null){
    for(int i=0;i<10;i++){
     value = value+" ";
    }
    return value;
   }else if(value.length()<10){
    int dis = 10-value.length();
    for(int i=0;i<dis;i++){
     value = value+" ";
    }
   }else{
    return value;
   }
  }else if("cardno".equals(field)){
   if(value==null){
    for(int i=0;i<19;i++){
     value = value+" ";
    }
    return value;
   }else if(value.length()<19){
    int dis = 19-value.length();
    for(int i=0;i<dis;i++){
     value = value+" ";
    }
   }else{
    return value;
   }
  }
  return value;
 }
 
 /**
 * 去除尾部的"-"
 * @param field 处理的字段
 * @return 处理后的值
 */
 public static String evaluate(String field){
  String str = field.substring(field.trim().length()-1);
  if("-".endsWith(str.trim())){
   return field.substring(0,field.trim().length()-1);
  }else{
   return field;
  }
 }
}
10.udaf的使用:
  udaf一般和group by使用，udaf的定义有两种方式，简单的写法的是继承udaf类，内部类实现UDAFEvaluator接口，实现init，iterate，terminatePartial，merge，terminate方法。
  init--map阶段调用
  iterate--map阶段调用，遍历处理记录
  terminatePartial--返回map的结果
  merge--合并，相当于combiner
  terminate--reduce阶段调用，例子如下：
  package com.huateng.spdbccc.mkt24.udaf;

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
/**
 * 自定义udaf实现字符串拼接
 * 1.继承udaf
 * 2.实现UDAFEvaluator接口
 * @author uatxj990267
 *
 */
@SuppressWarnings("deprecation")
public class Myconcat extends UDAF {
 public static class ConcatUDAFEvaluator implements UDAFEvaluator {
  public static class PartialResult {
   String result;
   String delimiter;
  }

  private PartialResult partial;

  public void init() {
   partial = null;
  }

  public boolean iterate(String value, String deli) {

   if (value == null) {
    return true;
   }
   if (partial == null) {
    partial = new PartialResult();
    partial.result = new String("");
    if (deli == null || deli.equals("")) {
     partial.delimiter = new String(",");
    } else {
     partial.delimiter = new String(deli);
    }
   }
   if (partial.result.length() > 0) {
    partial.result = partial.result.concat(partial.delimiter);
   }

   partial.result = partial.result.concat(value);

   return true;
  }

  public PartialResult terminatePartial() {
   return partial;
  }

  public boolean merge(PartialResult other) {
   if (other == null) {
    return true;
   }
   if (partial == null) {
    partial = new PartialResult();
    partial.result = new String(other.result);
    partial.delimiter = new String(other.delimiter);
   } else {
    if (partial.result.length() > 0) {
     partial.result = partial.result.concat(partial.delimiter);
    }
    partial.result = partial.result.concat(other.result);
   }
   return true;
  }

  public String terminate() {
   return new String(partial.result);
  }
 }
}

11.数据初始化通用脚本。
脚本名:init.sh,内容如下:
--------------------------------------------------------------------------------------
#!/bin/bash
echo "begin to init sas_nasrdwn......"

beg_date='20160407';#设定起始日期
end_date='20160505';#设定结束日期，每次循环加1天
beg_s=`date -d "$beg_date" +%s`;#开始日期的秒数
end_s=`date -d "$end_date" +%s`;#结束日期的秒数

echo "the beg_date:$beg_date,the end_date:$end_date......"
#比对，小于结束日期，执行动作。
while [ "$beg_s" -le "$end_s" ]
do

  beg_s=$((beg_s+86400));#加一天，结果是秒数
  beg_s=`date -d @$beg_s +"%Y%m%d"`;#将秒数转为字符串
  echo "now date:$beg_s";
  hive -e "load data local inpath '/home/hdfs/spdb/work/MKT24/NAS/dt=$beg_s/000000_0' overwrite into table sas_nasrdwn partition(dt='$beg_s')";#加载数据到指定分区-------1
  #hive -e "alter table sas_nasrdwn ADD IF NOT EXISTS PARTITION(dt='$beg_s') location '/spdbccc/data/dest/SAS/SAS_NASRDWN/dt=$beg_s/'" ;#添加分区,数据已在目录下
  beg_s=`date -d "$beg_s" +%s`;#把字符串变成秒数
done

echo "sas_nasrdwn init  success......"
-----------------------------------------------------------------------------------------
在上述脚本的1处，如果要执行其它的hql脚本，如下:
sed -i "s/para_date/$beg_s/g" init.hql;#替换init.hql中的参数para_date为$beg_s,g表示全局
sed -i "s/para_bef_date/$beg_tmp/g" init.hql;#同上
hive -f "init.hql";#执行替换后的脚本
sed -i "s/$beg_s/para_date/g" init.hql;#回替
sed -i "s/$beg_tmp/para_bef_date/g" init.hql;#回替

 
 
 
------------------------问题总结-----------------------------------------------------------
1.内存溢出
原因：使用了mapjoin，表数据过大，放在内存join时空间不足导致溢出
解决办法：不使用mapjoin，增大reduce数量
2.return code 1 from 。。。。。
原因:读写源数据或目标数据的权限不够
解决办法：赋权限
3.return code 2 from ..........
原因:1。权限
     2。数据文件损坏
     3。磁盘空间
解决办法：1。赋权限
          2。更换损坏的数据文件
          3。清理磁盘
4.数据倾斜
原因:空值过多，和业务数据相关，具体情况具体看。
解决办法:目前启动系统的防止数据倾斜参数         
         hive.groupby.skewindata=true;