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;